Use shell scripts to view SQL statements with high resource consumption in Undo

Source: Internet
Author: User

Use shell scripts to view SQL statements with high resource consumption in Undo

When undo usage is checked, when Undo_management is set to auto, it is often seen that undo is constantly scaling and adjusting itself.

Sometimes we can see that Undo is very tight, so we want to know which SQL statements are running and what potential problems may arise. For online business systems, if an SQL statement runs for a long time and consumes extremely high undo resources, the SQL statement may be faulty.

You can find a list of SQL _id through the following SQL statement to view the Undo resources consumed by each SQL _id.

Sqlplus-s $ DB_CONN_STR @ $ SH_DB_SID <EOF
Set pages 53
Select sum (undoblks) * 8/1024 total_size_MB from v \ $ undostat;
Select * from (
Select maxqueryid,
Round (sum (undoblks) * 8/1024) consumed_size_MB
From v \ $ undostat group by maxqueryid order by consumed_size_MB desc
) Where rownum <50;
EOF
Exit

The script runs as follows:

TOTAL_SIZE_MB
-------------
70299.2188

MAXQUERYID CONSUMED_SIZE_MB
-----------------------------
7wx3cgjqsmn4 39990
210ndtcx5fwgs 20738
648600hq1s1s8 5795
Cjqdgd14x1_jm 1116
4ad8ypr3nf6vm 869
0my2xfpqrk6gw 597
F3pq3mdycwcd2455
Cwp9zk1y7cthy 312
Ddtx15a9nzmjt 139
Csrj5pnpx4wtr 72
6 tshctswzutbk 49
3a4vsqkf8yaxs 49
Gpzkq2kv9vhan 27
Fa311gg43yjyf 21
Cysbbg2h86xc6 19
Fjzknc02f7019 18
Aty7a3bv0000xxx 17
Ftmvqxfzq1fv0 16

We can see that the SQL statement with SQL _id 7wx3cgjqsmnn4 consumes the most resources and may have certain performance problems. Check the execution plan and find that.
The detailed rules are not listed. A few hundred words are omitted here.

In short, it is also a way to view possible performance SQL statements through the use of undo. Of course, the usage of undo changes frequently. You can monitor undo within a certain range based on your own situation. I believe there will be some gains.

--------------------------------------------------------------------------------

Undo tablespace loss caused by rman backup and recovery

About Oracle releasing over-used undo tablespace

Oracle undo

Oracle undo image data exploration

Oracle ROLLBACK and undo)

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.