Excellent tool SQL Monitor, dedicated to catch the bad SQL Expert!!!

In recent days by the manufacturer's database made big head, they apply an hour to run 2TB of reading data out

Baidu has a search to the SQL Monitor this tool came out and learned a bit

Changed to a script, the execution will be under the/home/oracle directory generated badsql.txt files, with more commands can be seen

VI badsql.sh

Add the following as content

#!/bin/bash
Su-oracle<<eof
Sqlplus/as SYSDBA
Spool Badsql.txt
Col Status Fro A15
Col username for A10
Col Module for A20
Col Program for A20
Set Linesize 1000
Set pagesize 300
Select status,username,module,program,sql_id,physical_read_bytes/1024/1024 read_io_mb,sql_text from v\ $sql _monitor ORDER BY physical_read_bytes Desc;
Spool off;
Exit
Eof

Save exit

Open Badsql.txt File

Take out the sql_id inside and substitute the command below to get a detailed report.

Su-oracle

More Badsql.txt

--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------
Done (all ROWS) eomsgz jdbc Thin client jdbc Thin Client3371xmyjfzmxw3936.85938
                                                                                                                                                                               
                                                                                                                                                                               
                                                                                                                                                                               
                                                                                                                                                                               
                                                                                                                                                                             
Select Commonfaul0_.id as Id322_, Commonfaul0_.mainid as Mainid322_, commonfaul0_.title as Title322_, commonfaul0_. Mainapplysheetid as Mainappl4_322_, Commonfaul0_.mai
Nalarmlevel as Mainalar5_322_, Commonfaul0_.mainifurgentfault as Mainifur6_322_, Commonfaul0_.mainfaultresponselevel As Mainfaul7_322_, Commonfaul0_.mainalarmnum as Ma
Inalar8_322_, commonfaul0_.mainalarmstate as Mainalar9_322_, Commonfaul0_.mainalarmdesc as MainAla10_322_, Commonfaul0_.mainalarmsolvedate as Mainala11_322_, Commonfau
L0_.mainequipmentfactory as Mainequ12_322_, commonfaul0_.mainequipmentname as Mainequ13_322_, commonfaul0_. Mainequipmentmodel as Mainequ14_322_, Commonfaul0_.mainfault
Generanttime as Mainfau15_322_, commonfaul0_.mainifaffectoperation as Mainifa16_322_, commonfaul0_. Mainfaultdiscoverablemode as Mainfau17_322_, Commonfaul0_.mainalarmi
D as Mainala18_322_, Commonfaul0_.mainalarmsource as Mainala19_322_, Commonfaul0_.mainalarmlogicsort as MainAla20_322_ , Commonfaul0_.mainalarmlogicsortsub as Mainala
21_322_, commonfaul0_.mainfaultspecialty as Mainfau22_322_, Commonfaul0_.mainequipmenttype as MainEqu23_322_, Commonfaul0_.mainnetname as Mainnet24_322_, commonfaul0_.
Mainfaultgenerantprovince as Mainfau25_322_, commonfaul0_.mainfaultgenerantcity as Mainfau26_322_, commonfaul0_. Mainnetsortone as Mainnet27_322_, commonfaul0_.mainnets
Orttwo as Mainnet28_322_, Commonfaul0_.mainnetsortthree as mainnet29_322_, commonfaul0_.replyfaultavoidtime as Replyfa30_322_, commonfaul0_.replyoperrenewtime as reply
Op31_322_, commonfaul0_.replyaffecttimelength as Replyaf32_322_, commonfaul0_.replydealstep as ReplyDe33_322_, Commonfaul0_.replyfaultdealresult as replyfa34_322_, com
Monfaul0_.replyfaultreasonsort as Replyfa35_322_, commonfaul0_.replyfaultreasonsubsection as ReplyFa36_322_, Commonfaul0_.replyifgreatfault as Replyif37_322_, COMMONFA
Ul0_.replyifexcutenetchange as Replyif38_322_, commonfaul0_.replyiffinallysolveproject as ReplyIf39_322_, Commonfaul0_.mainsheettype as Mainshe40_322_, commonfaul0_.

Sql> Col Comm Format a200
Sql> Set Linesize 3000
Sql> Set pages 0
Sql> Select Dbms_sqltune.report_sql_monitor (sql_id=> '3371xmyjfzmxw',report_level=> ' all ', type= > ' TEXT ') comm from dual;

Global Information
------------------------------
Status:done (All ROWS)
Instance id:1
Session:eomsgz (1545:18,373)
SQL Id:3371xmyjfzmxw
SQL Execution id:16777216
Execution started:07/22/2016 15:42:36
First Refresh time:07/22/2016 15:42:40
Last Refresh time:07/22/2016 15:42:45
Duration:9s
MODULE/ACTION:JDBC Thin client/-
Service:gzeomsdb
PROGRAM:JDBC Thin Client
Fetch calls:1

Global Stats
====================================================================================================
|   Elapsed |    Cpu | IO | Application |  Cluster | Other | Fetch | Buffer | Read | Read |
| Time (s) | Time (s) |  Waits (s) | Waits (s) | Waits (s) | Waits (s) |  Calls | Gets | reqs | Bytes |
====================================================================================================
|    8.81 |     3.69 |        4.44 |     0.00 |     0.00 |     0.68 |   1 | 504K |   3974 | 4GB |
====================================================================================================

SQL Plan monitoring Details (plan Hash value=3739816812)
=============================================================================================================== ==============================================
|      Id |         Operation |  Name | Rows |   Cost | Time | Start |   Execs | Rows | Read | Read |   Activity | Activity Detail |
|                     |                      | |      (Estim) | | Active (s) |       Active | | (Actual) | reqs |   Bytes |     (%)    | (# samples) |
=============================================================================================================== ==============================================
| 0 |                      SELECT STATEMENT |         |      |           |        |     |          1 |      |       |          |                      | |
|   1 | TABLE ACCESS Full |      Commonfaultpack_main | 64 |         137K |     9 |     +2 |        1 | 0 |   2789 |   3GB | 100.00 | Cpu (3) |
|                     |                      |         |      |           |        |       |          |      |       |          | | Direct path Read (6) |
=============================================================================================================== ==============================================

This article from "Do not forget Beginner's mind Oracle" blog, reproduced please contact the author!

Excellent tool SQL Monitor, dedicated to catch the bad SQL Expert!!!