Oracle script to find Oracle High-consumption statements

Before running the following script, you need to use the SQL that generated the AWR report (the program scripts are generally stored in the rdbms/admin under $oracle_home. The name is awrrpt.sql and you need to enter the number of days to generate the AWR report) to find the start and end snapshot numbers: Begin_snap and End_snap.

SetLine +  SetLinesize $  SetPageSize -  Set Long 999999  SetEcho on  SetMarkup HTML on  SelectRes.*     from(SelectTo_char (D.end_interval_time,'YYYY-MM-DD'), A.parsing_schema_name, C.module, A.sql_ ID, A.execs asnumber of executions,ROUND(A.cpu_times/A.execs,2) assingle execution time, A.cpu_times asCPU consumption time,ROUND(A.cpu_times/B.sum_time*  -,2) ascpu percent consumed, a.buffer_gets aslogical Reading,ROUND(a.buffer_gets/B.sum_buffer*  -,2) aslogical read percentage, A.disk_read asphysical Reading,ROUND(A.disk_read/B.sum_disk*  -,2) asPhysical read percentage, C.sql_fulltext from(SelectParsing_schema_name, sql_id,sum(Executions_delta) asexecs,round(sum(Cpu_time_delta)/ 1000000,2) asCpu_times,round(sum(Elapsed_time_delta)/ 1000000,2) asElapsed_time,sum(Buffer_gets_delta) asBuffer_gets,sum(Disk_reads_delta) asDisk_read fromsys. Wrh$_sqlstat WR, gv$instance iwheresnap_id<= &End_snap andsnap_id>= &Begin_snap andwr. Instance_number=I.instance_number andI.instance_number= &Instance_numberGroup  byParsing_schema_name, WR. Instance_number, sql_id) A, (SELECT round(SUM(Cpu_time_delta)/ 1000000,2) Sum_time,SUM(Buffer_gets_delta) Sum_buffer,sum(Disk_reads_delta) Sum_disk fromsys. Wrh$_sqlstat WR, gv$instance iwheresnap_id<= &End_snap andsnap_id>= &Begin_snap andUrl Instance_number=I.instance_number andI.instance_number= &instance_number) b, V$sqlarea C, dba_hist_snapshot DwhereA.execs> 0              anda.sql_id=c.sql_id andA.parsing_schema_name<> 'SYS'              andd.snap_id= &End_snapOrder  byCPU Consumption timedesc) ReswhereRowNum<  A;

Save the script output to Notepad txt, and change the suffix name of Notepad to. html so that you can output the following Web page content:

650) this.width=650; "Src=" http://img.blog.csdn.net/20140321225947406?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqvbgvleglkzq==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "" 918 "height="/>

Oracle script to find Oracle High-consumption statements