How to find Oracle high-cost statements_javascript skills-JS Tutorial-php.cn

How to find Oracle high-cost statements_javascript skills

WBOY

Release： 2016-05-16 16:54:49

Original

1532 people have browsed it

Before running the following script, you need to use the SQL to generate the AWR report (the program script is generally saved in rdbms/admin under $ORACLE_HOME, named awrrpt.sql, and you need to enter the range of days to generate the AWR report) to find the start and end snapshots Number: begin_snap and end_snap.

Copy code The code is as follows:

 
set line 1000 
set linesize 200 
set pagesize 2000 
set long 999999 
set echo on 
set markup html on 
select res.* 
from (select to_char(d.end_interval_time,'yyyy-mm-dd'), 
a.PARSING_SCHEMA_NAME, 
c.MODULE, 
a.sql_id, 
a.execs as number of executions, 
ROUND (a.cpu_times / a.execs, 2) as single execution time, 
a.cpu_times as cpu consumption time, 
ROUND(a.cpu_times / b.sum_time * 100, 2) as cpu consumption percentage, 
a.buffer_gets as logical read, 
ROUND(a.buffer_gets / b.sum_buffer * 100, 2) as logical read percentage, 
a.disk_read as physical read, 
ROUND(a.disk_read / b.sum_disk * 100, 2) as physical read percentage, 
c.sql_fulltext 
from (select PARSING_SCHEMA_NAME, 
sql_id, 
sum(EXECUTIONS_DELTA) AS execs, 
round(sum( CPU_TIME_DELTA) / 1000000, 2) AS cpu_times, 
round(sum(ELAPSED_TIME_DELTA) / 1000000, 2) AS elapsed_time, 
sum(BUFFER_GETS_DELTA) AS buffer_gets, 
sum(DISK_READS_DELTA) AS disk_read 
from sys.WRH$_SQLSTAT wr, gv$instance i 
where SNAP_ID <= &end_snap 
and snap_id >= &begin_snap 
and wr.INSTANCE_NUMBER = i.INSTANCE_NUMBER 
and i.instance_number = &instance_number 
group by PARSING_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 
FROM sys.WRH$_SQLSTAT wr, gv$instance i 
where SNAP_ID <= &end_snap 
and snap_id >= &begin_snap 
and wr.INSTANCE_NUMBER = i.INSTANCE_NUMBER 
and i.instance_number = &instance_number) b, 
v$sqlarea c, 
dba_hist_snapshot d 
where a.execs > 0 
and a.sql_id = c.sql_id 
and a.PARSING_SCHEMA_NAME <> 'SYS' 
and d.snap_id = &end_snap 
order by cpu time consumption desc) res 
where rownum < 41; 
exit 

will Save the script output content to Notepad txt, and change the suffix name of Notepad to .html, so that the following web page content can be output:
How to find Oracle high-cost statements_javascript skills

How to find Oracle high-cost statements_javascript skills