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
--------------------------------------------------------------------------------