基於AWR實現STATSPACK報告(5-TOPSQL)
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***為了後續報表中獲取比例，一次性計算sql_summary：
col bufcappct new_value bufcappct noprint
col getsa new_value getsa noprint
col phycappct new_value phycappct noprint
col phyra new_value phyra noprint
col execappct new_value execappct noprint
col exea new_value exea noprint
col prscappct new_value prscappct noprint
col prsea new_value prsea noprint
col cpucappct new_value cpucappct noprint
col elacappct new_value elacappct noprint
col dbcpua new_value dbcpua noprint
col dbcpu_s new_value dbcpu_s noprint
col dbtima new_value dbtima noprint
col dbtim_s new_value dbtim_s noprint
SELECT decode(&slr,
0,
to_number(NULL),
100 * SUM(CASE ct.command_type
WHEN 47 THEN
0
ELSE
e.buffer_gets_total - nvl(b.buffer_gets_total, 0)
END) / &slr) bufcappct,
&slr getsa,
decode(&phyr,
0,
to_number(NULL),
100 * SUM(CASE ct.command_type
WHEN 47 THEN
0
ELSE
e.disk_reads_total - nvl(b.disk_reads_total, 0)
END) / &phyr) phycappct,
&phyr phyra,
decode(&exe,
0,
to_number(NULL),
100 * SUM(e.executions_total - nvl(b.executions_total, 0)) / &exe) execappct,
&exe exea,
decode(&prse,
0,
to_number(NULL),
100 * SUM(e.parse_calls_total - nvl(b.parse_calls_total, 0)) /
&prse) prscappct,
&prse prsea,
decode(&dbcpu,
0,
to_number(NULL),
100 * SUM(e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
&dbcpu) cpucappct,
decode(&dbcpu, 0, to_number(NULL), &dbcpu) dbcpua,
decode(&dbcpu, 0, to_number(NULL), &dbcpu / 1000000) dbcpu_s,
decode(&dbtim,
0,
to_number(NULL),
100 * SUM(e.elapsed_time_total - nvl(b.elapsed_time_total, 0)) /
&dbtim) elacappct,
decode(&dbtim, 0, to_number(NULL), &dbtim) dbtima,
decode(&dbtim, 0, to_number(NULL), &dbtim / 1000000) dbtim_s
FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext ct
WHERE b.snap_id(+) = &bid
AND e.snap_id = &eid
AND b.dbid(+) = &dbid
AND e.dbid = &dbid
AND b.instance_number(+) = &inst_num
AND e.instance_number = &inst_num
AND e.executions_total > nvl(b.executions_total, 0)
AND b.sql_id(+) = e.sql_id
AND b.sql_id = ct.sql_id
AND b.dbid = ct.dbid;
其中：
slr-->session logical reads
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='session logical reads';
phyr-->physical reads
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='physical reads';
exe-->execute count
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='execute count';
prse-->parse count (total)
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='parse count (total)';
dbcpu-->
SELECT e.VALUE - b.VALUE
FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='DB CPU';
dbtim-->
SELECT e.VALUE - b.VALUE
FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='DB time';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL ordered by CPU DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 3,803
-> Captured SQL accounts for 39.2% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
298.66 352,071 0.00 7.9 12043.35 9,287,906 3362116323
select CURCDE_S,CURCDE_D,BID,ASK,FLAG,CRT_DATE,RATE
from EXC_RATE where TYPE=:2
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Total DB CPU (s)-->sql_summary.dbcpu_s
Captured SQL accounts for XXX% of Total DB CPU-->sql_summary.cpucappct ***此處Captured SQL指計算總量時sql,即executions_total有變化的所有sql
SQL reported below exceeded 1.0% of Total DB CPU-->1.0
SELECT aa
FROM (SELECT /*+ ordered use_nl (b st) */
decode(0,
0,
lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
1000000,
'999990.00'),
' '),
10) || ' ' ||
lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
'999,999,999'),
12) || ' ' ||
lpad((to_char(decode(e.executions_total -
nvl(b.executions_total, 0),
0,
to_number(NULL),
((e.cpu_time_total -
nvl(b.cpu_time_total, 0)) /
(e.executions_total -
nvl(b.executions_total, 0))) / 1000000),
'999990.00')),
10) || ' ' ||
lpad(decode(&dbcpu,
0,
' ',
NULL,
' ',
to_char((100 * (e.cpu_time_total -
nvl(b.cpu_time_total, 0)) / &dbcpu),
'990.0')),
6) || ' ' ||
lpad(nvl(to_char((e.elapsed_time_total -
nvl(b.elapsed_time_total, 0)) / 1000000,
'999990.00'),
' '),
10) || ' ' || lpad(to_char((e.buffer_gets_total -
nvl(b.buffer_gets_total, 0)),
'99,999,999,999'),
15) || ' ' || lpad(e.sql_id, 10) || ' ' ||
decode(e.module,
NULL,
dbms_lob.substr(st.sql_text, 100, 1),
rpad('Module: ' || e.module, 80) ||
dbms_lob.substr(st.sql_text, 100, 1)),
dbms_lob.substr(st.sql_text, 100, 1)) aa
FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
WHERE b.snap_id(+) = &bid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = e.instance_number
AND b.sql_id(+) = e.sql_id
AND e.snap_id = &eid
AND e.dbid = &dbid
AND e.instance_number = &inst_num
AND e.sql_id = st.sql_id
AND e.dbid = st.dbid
AND e.executions_total > nvl(b.executions_total, 0)
AND decode(&dbcpu,
0,
2,
NULL,
2,
100 * (e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
&dbcpu) > decode(&dbcpu, 0, 1, NULL, 2, 1)
ORDER BY (e.cpu_time_total - nvl(b.cpu_time_total, 0)) DESC,
e.sql_id)
WHERE rownum < 10;
其中：
dbcpu-->
SELECT e.VALUE - b.VALUE
FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='DB CPU';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL ordered by Elapsed DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB Time (s): 16,987
-> Captured SQL accounts for 81.9% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
12043.35 352,071 0.03 70.9 298.66 4 3362116323
select CURCDE_S,CURCDE_D,BID,ASK,FLAG,CRT_DATE,RATE
from EXC_RATE where TYPE=:2
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Total DB Time (s): -->sql_summary.dbtim_s
Captured SQL accounts for xxx% of Total DB Time -->sql_summary.elacappct
SQL reported below exceeded 1.0% of Total DB Time -->1.0
SELECT aa
FROM (SELECT /*+ ordered use_nl (b st) */
decode(0,
0,
lpad(nvl(to_char((e.elapsed_time_total -
nvl(b.elapsed_time_total, 0)) / 1000000,
'999990.00'),
' '),
10) || ' ' ||
lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
'999,999,999'),
12) || ' ' ||
lpad((to_char(decode(e.executions_total -
nvl(b.executions_total, 0),
0,
to_number(NULL),
((e.elapsed_time_total -
nvl(b.elapsed_time_total, 0)) /
(e.executions_total -
nvl(b.executions_total, 0))) / 1000000),
'999990.00')),
10) || ' ' ||
lpad(decode(&dbtim,
0,
' ',
to_char((100 * (e.elapsed_time_total -
nvl(b.elapsed_time_total, 0)) / &dbtim),
'990.0')),
6) || ' ' ||
lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
1000000,
'999990.00'),
' '),
10) || ' ' ||
lpad(to_char((e.disk_reads_total - nvl(b.disk_reads_total, 0)),
'99,999,999,999'),
15) || ' ' || lpad(e.sql_id, 10) || ' ' ||
decode(e.module,
NULL,
dbms_lob.substr(st.sql_text, 100, 1),
rpad('Module: ' || e.module, 80) ||
dbms_lob.substr(st.sql_text, 100, 1)),
dbms_lob.substr(st.sql_text, 100, 1)) aa
FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
WHERE b.snap_id(+) = &bid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = e.instance_number
AND b.sql_id(+) = e.sql_id
AND e.snap_id = &eid
AND e.dbid = &dbid
AND e.instance_number = &inst_num
AND e.sql_id = st.sql_id
AND e.dbid = st.dbid
AND e.executions_total > nvl(b.executions_total, 0)
AND decode(&dbtim,
0,
2,
100 *
(e.elapsed_time_total - nvl(b.elapsed_time_total, 0)) /
&dbtim) > decode(&dbtim, 0, 1, 1)
ORDER BY (e.elapsed_time_total - nvl(b.elapsed_time_total, 0)) DESC,
e.sql_id)
WHERE rownum < 10;
其中：
dbtim-->
SELECT e.VALUE - b.VALUE
FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='DB time';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL ordered by Gets DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 120,922,778
-> Captured SQL accounts for 34.8% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
9,287,906 352,071 26.4 7.7 298.66 12043.35 3362116323
select CURCDE_S,CURCDE_D,BID,ASK,FLAG,CRT_DATE,RATE
from EXC_RATE where TYPE=:2
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End Buffer Gets Threshold: 10000 Total Buffer Gets: -->sql_summary.getsa
Captured SQL accounts for xxx% of Total Buffer Gets -->sql_summary.bufcappct
SQL reported below exceeded 1.0% of Total Buffer Gets -->1.0
SELECT aa
FROM (SELECT /*+ ordered use_nl (b st) */
decode(0,
0,
lpad(to_char((e.buffer_gets_total -
nvl(b.buffer_gets_total, 0)),
'99,999,999,999'),
15) || ' ' ||
lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
'999,999,999'),
12) || ' ' ||
lpad((to_char(decode(e.executions_total -
nvl(b.executions_total, 0),
0,
to_number(NULL),
(e.buffer_gets_total -
nvl(b.buffer_gets_total, 0)) /
(e.executions_total -
nvl(b.executions_total, 0))),
'999,999,990.0')),
14) || ' ' ||
lpad((to_char(100 * (e.buffer_gets_total -
nvl(b.buffer_gets_total, 0)) / &slr,
'990.0')),
6) || ' ' ||
lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
1000000,
'9990.00'),
' '),
8) || ' ' ||
lpad(nvl(to_char((e.elapsed_time_total -
nvl(b.elapsed_time_total, 0)) / 1000000,
'99990.00'),
' '),
9) || ' ' || lpad(e.sql_id, 10) || '' ||
decode(e.module,
NULL,
dbms_lob.substr(st.sql_text, 100, 1),
rpad('Module: ' || e.module, 80) ||
dbms_lob.substr(st.sql_text, 100, 1)),
dbms_lob.substr(st.sql_text, 100, 1)) aa
FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
WHERE b.snap_id(+) = &bid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = e.instance_number
AND b.sql_id(+) = e.sql_id
AND e.snap_id = &eid
AND e.dbid = &dbid
AND e.instance_number = &inst_num
AND e.sql_id = st.sql_id
AND e.dbid = st.dbid
AND e.executions_total > nvl(b.executions_total, 0)
AND 100 * (e.buffer_gets_total - nvl(b.buffer_gets_total, 0)) / &slr > 1
ORDER BY (e.buffer_gets_total - nvl(b.buffer_gets_total, 0)) DESC,
e.sql_id)
WHERE rownum < 10;
其中：
slr-->session logical reads
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='session logical reads';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL ordered by Reads DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> End Disk Reads Threshold: 1000 Total Disk Reads: 5,579,793
-> Captured SQL accounts for 67.8% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd Old
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
1,269,083 5 253,816.6 22.7 108.02 299.19 2878843975
select t.TRANS_ID, t.STATUS, t.TRF_CUR, t.TRF_AMT, t.SERVICE_ID
from TRANS_S t where t.SERVICE_ID=:2 and t.RETURN_DATE >= :3
and t.RETURN_DATE < :4+1
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-> End Disk Reads Threshold: 1000 Total Disk Reads: -->sql_summary.phyra
-> Captured SQL accounts for xxx% of Total Disk Reads -->sql_summary.phycappct
-> SQL reported below exceeded 1.0% of Total Disk Reads -->1.0
SELECT aa
FROM (SELECT /*+ ordered use_nl (b st) */
decode(0,
0,
lpad(to_char((e.disk_reads_total - nvl(b.disk_reads_total, 0)),
'99,999,999,999'),
15) || ' ' ||
lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
'999,999,999'),
12) || ' ' ||
lpad((to_char(decode(e.executions_total -
nvl(b.executions_total, 0),
0,
to_number(NULL),
(e.disk_reads_total -
nvl(b.disk_reads_total, 0)) /
(e.executions_total -
nvl(b.executions_total, 0))),
'999,999,990.0')),
14) || ' ' ||
lpad((to_char(100 * (e.disk_reads_total -
nvl(b.disk_reads_total, 0)) / &phyr,
'990.0')),
6) || ' ' ||
lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
1000000,
'9990.00'),
' '),
8) || ' ' ||
lpad(nvl(to_char((e.elapsed_time_total -
nvl(b.elapsed_time_total, 0)) / 1000000,
'99990.00'),
' '),
9) || ' ' || lpad(e.sql_id, 10) || '' ||
decode(e.module,
NULL,
dbms_lob.substr(st.sql_text, 100, 1),
rpad('Module: ' || e.module, 80) ||
dbms_lob.substr(st.sql_text, 100, 1)),
dbms_lob.substr(st.sql_text, 100, 1)) aa
FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
WHERE b.snap_id(+) = &bid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = e.instance_number
AND b.sql_id(+) = e.sql_id
AND e.snap_id = &eid
AND e.dbid = &dbid
AND e.instance_number = &inst_num
AND e.sql_id = st.sql_id
AND e.dbid = st.dbid
AND e.executions_total > nvl(b.executions_total, 0)
AND &phyr > 0
AND 100 * (e.disk_reads_total - nvl(b.disk_reads_total, 0)) /
&phyr > 1.0
ORDER BY (e.disk_reads_total - nvl(b.disk_reads_total, 0)) DESC,
e.sql_id)
WHERE rownum < 10;
其中：
phyr-->physical reads
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='physical reads';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL ordered by Executions DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> End Executions Threshold: 100 Total Executions: 1,993,974
-> Captured SQL accounts for 88.7% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
455,447 444,727 1.0 0.00 0.00 1359734244
SELECT SESSION_ID FROM LOG_CON WHERE USER_ID=:1
and USER_TYPE='0'
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-> End Executions Threshold: 100 Total Executions: -->sql_summary.exea
-> Captured SQL accounts for 88.7% of Total Executions -->sql_summary.execappct
-> SQL reported below exceeded 1.0% of Total Executions -->1.0
SELECT aa
FROM (SELECT /*+ ordered use_nl (b st) */
decode(0,
0,
lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
'999,999,999'),
12) || ' ' ||
lpad(to_char((nvl(e.rows_processed_total, 0) -
nvl(b.rows_processed_total, 0)),
'99,999,999,999'),
15) || ' ' ||
lpad((to_char(decode(nvl(e.rows_processed_total, 0) -
nvl(b.rows_processed_total, 0),
0,
0,
(e.rows_processed_total -
nvl(b.rows_processed_total, 0)) /
(e.executions_total -
nvl(b.executions_total, 0))),
'9,999,999,990.0')),
16) || ' ' ||
lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
(e.executions_total -
nvl(b.executions_total, 0)) / 1000000,
'999990.00'),
' '),
10) || ' ' ||
lpad(nvl(to_char((e.elapsed_time_total -
nvl(b.elapsed_time_total, 0)) /
(e.executions_total -
nvl(b.executions_total, 0)) / 1000000,
'9999990.00'),
' '),
11) || ' ' || lpad(e.sql_id, 10) || ' ' ||
decode(e.module,
NULL,
dbms_lob.substr(st.sql_text, 100, 1),
rpad('Module& ' || e.module, 80) ||
dbms_lob.substr(st.sql_text, 100, 1)),
dbms_lob.substr(st.sql_text, 100, 1)) aa
FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
WHERE b.snap_id(+) = &bid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = e.instance_number
AND b.sql_id(+) = e.sql_id
AND e.snap_id = &eid
AND e.dbid = &dbid
AND e.instance_number = &inst_num
AND e.sql_id = st.sql_id
AND e.dbid = st.dbid
AND e.executions_total > nvl(b.executions_total, 0)
AND 100 * (e.executions_total - nvl(b.executions_total, 0)) / &exe > 1
ORDER BY (e.executions_total - nvl(b.executions_total, 0)) DESC,
e.sql_id)
WHERE rownum < 10;
其中：
exe-->execute count
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='execute count';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL ordered by Parse Calls DB/Inst: ORADB/oraDB1 Snaps: 66-67
-> End Parse Calls Threshold: 1000 Total Parse Calls: 461,034
-> Captured SQL accounts for 65.2% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
22,615 45,556 4.91 2828026679
select sysdate from dual
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End Parse Calls Threshold: 1000 Total Parse Calls: -->sql_summary.prsea
Captured SQL accounts for xxx% of Total Parse Calls -->sql_summary.prscappct
SQL reported below exceeded 1.0% of Total Parse Calls -->1.0
SELECT aa
FROM (SELECT /*+ ordered use_nl (b st) */
decode(0,
0,
lpad(to_char((e.parse_calls_total -
nvl(b.parse_calls_total, 0)),
'999,999,999'),
12) || ' ' ||
lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
'999,999,999'),
12) || ' ' ||
lpad(to_char(100 * (e.parse_calls_total -
nvl(b.parse_calls_total, 0)) / &prse,
'990.09'),
8) || ' ' || lpad(e.sql_id, 10) || ' ' || rpad(' ', 34) ||
decode(e.module,
NULL,
dbms_lob.substr(st.sql_text, 100, 1),
rpad('Module& ' || e.module, 80) ||
dbms_lob.substr(st.sql_text, 100, 1)),
dbms_lob.substr(st.sql_text, 100, 1)) aa
FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
WHERE b.snap_id(+) = &bid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = e.instance_number
AND b.sql_id(+) = e.sql_id
AND e.snap_id = &eid
AND e.dbid = &dbid
AND e.instance_number = &inst_num
AND e.sql_id = st.sql_id
AND e.dbid = st.dbid
AND 100 * (e.parse_calls_total - nvl(b.parse_calls_total, 0)) /
&prse > 1
ORDER BY (e.parse_calls_total - nvl(b.parse_calls_total, 0)) DESC,
e.sql_id)
WHERE rownum < 10;
其中：
prse-->parse count (total)
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='parse count (total)';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL statements ordered by Sharable Memory
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT aa
FROM (SELECT /*+ ordered use_nl (b st) */
decode(0,
0,
lpad(to_char(e.sharable_mem, '999,999,999,999'), 16) || ' ' ||
lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
'999,999,999'),
12) || ' ' ||
lpad((to_char(100 * e.sharable_mem / &espm, '990.0')), 7) || ' ' ||
lpad(e.sql_id, 12) || ' ' || rpad(' ', 29) ||
decode(e.module,
NULL,
dbms_lob.substr(st.sql_text, 100, 1),
rpad('Module& ' || e.module, 80) ||
dbms_lob.substr(st.sql_text, 100, 1)),
dbms_lob.substr(st.sql_text, 100, 1)) aa
FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
WHERE b.snap_id(+) = &bid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = e.instance_number
AND b.sql_id(+) = e.sql_id
AND e.snap_id = &eid
AND e.dbid = &dbid
AND e.instance_number = &inst_num
AND e.sql_id = st.sql_id
AND e.dbid = st.dbid
AND e.executions_total > nvl(b.executions_total, 0)
AND e.sharable_mem > &esmt
ORDER BY e.sharable_mem DESC, e.sql_id)
WHERE rownum < 10;
其中：
espm=esp-->shared pool
SELECT VALUE
FROM dba_hist_parameter t
WHERE t.dbid = &dbid
AND t.instance_number = &inst_num
AND t.snap_id = &bid
AND t.parameter_name = 'shared_pool_size';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL statements ordered by Version Count
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT aa
FROM (SELECT /*+ ordered use_nl (b st) */
decode(0,
0,
lpad(to_char(e.version_count, '999,999'), 8) || ' ' ||
lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
'999,999,999'),
12) || ' ' || lpad(e.sql_id, 12) || ' ' || rpad(' ', 45) ||
decode(e.module,
NULL,
dbms_lob.substr(st.sql_text, 100, 1),
rpad('Module& ' || e.module, 80) ||
dbms_lob.substr(st.sql_text, 100, 1)),
dbms_lob.substr(st.sql_text, 100, 1)) aa
FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
WHERE b.snap_id(+) = &bid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = e.instance_number
AND b.sql_id(+) = e.sql_id
AND e.snap_id = &eid
AND e.dbid = &dbid
AND e.instance_number = &inst_num
AND e.sql_id = st.sql_id
AND e.dbid = st.dbid
AND e.executions_total > nvl(b.executions_total, 0)
AND e.version_count > &evc
ORDER BY e.version_count DESC, e.sql_id)
WHERE rownum < 10;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL ordered by Cluster Wait Time DB/Inst: ORADB/oraDB1 Snaps: 66-67
Cluster CWT % of Elapsd CPU Old
Wait Time (s) Elapsd Time Time (s) Time (s) Executions Hash Value
------------- ----------- ----------- ----------- -------------- ----------
11,408.89 94.7 12,043.35 298.66 352,071 3362116323
select CURCDE_S,CURCDE_D,BID,ASK,FLAG,CRT_DATE,RATE
from EXC_RATE where TYPE=:2
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT aa
FROM (SELECT /*+ ordered use_nl (b st) */
decode(0,
0,
lpad(nvl(to_char((e.clwait_total - nvl(b.clwait_total, 0)) /
1000000,
'9,999,999.00'),
' '),
13) || ' ' ||
lpad(nvl(to_char(100 *
(e.clwait_total - nvl(b.clwait_total, 0)) /
(e.elapsed_time_total -
nvl(b.elapsed_time_total, 0)),
'990.0'),
' '),
11) || ' ' ||
lpad(nvl(to_char((e.elapsed_time_total -
nvl(b.elapsed_time_total, 0)) / 1000000,
'999,990.00'),
' '),
11) || ' ' ||
lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
1000000,
'999,990.00'),
' '),
11) || ' ' ||
lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
'9,999,999,999'),
14) || ' ' || lpad(e.sql_id, 10) || '' || rpad(' ', 5) ||
decode(e.module,
NULL,
dbms_lob.substr(st.sql_text, 100, 1),
rpad('Module& ' || e.module, 80) ||
dbms_lob.substr(st.sql_text, 100, 1)),
dbms_lob.substr(st.sql_text, 100, 1)) aa,
e.sql_id hv
FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
WHERE b.snap_id(+) = &bid
AND b.dbid(+) = e.dbid
AND b.instance_number(+) = e.instance_number
AND b.sql_id(+) = e.sql_id
AND e.snap_id = &eid
AND e.dbid = &dbid
AND e.instance_number = &inst_num
AND e.sql_id = st.sql_id
AND e.dbid = st.dbid
AND e.clwait_total > nvl(b.clwait_total, 0)
ORDER BY (e.clwait_total - nvl(b.clwait_total, 0)) DESC, e.sql_id)
WHERE rownum < 10;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-701151/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- statspack、awr、addm,ash影片分享
- AWR報告基礎操作
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- AWR TOP SQL實現SQL
- Oracle生成awr報告操作步驟Oracle
- ORACLE AWR效能報告和ASH效能報告的解讀Oracle
- awr-----一份經典的負載很高的awr報告負載
- awr報告每天自動生成指令碼指令碼
- 12.2 如何單為PDB建立AWR報告
- Oracle 12.2 physical standby備庫收集AWR報告Oracle
- 【AWR】Oracle資料庫建立awr基線Oracle資料庫
- 本機生成遠端資料庫AWR報告資料庫
- 【效能調優】Oracle AWR報告指標全解析Oracle指標
- 達夢資料庫AWR報告日常管理方法資料庫
- 基於Python實現MapReducePython
- Swagger基於SpringBoot實現SwaggerSpring Boot
- 如何在12.2版本ADG備庫生成AWR報告
- 達夢資料庫如何來配置並生成AWR報告資料庫
- oracle之 AWR固定基線Oracle
- 基於jquery實現的ExceljQueryExcel
- JsonUtil（基於Jackson的實現）JSON
- 基於JVMTI的Agent實現JVM
- 基於YARP實現的FastGithubASTGithub
- 基於pytorch實現模型剪枝PyTorch模型
- Location Sciences：基於位置的營銷報告
- oracle rac 單個例項不能生成awr報告的問題Oracle
- 宜信資料庫實踐|解讀Oracle AWR效能分析報告，更快定位效能瓶頸資料庫Oracle
- 分散式鎖與實現(一)基於Redis實現！分散式Redis
- 備忘錄——基於rdlc報表實現列印產品標籤
- Oracle 11.2.0.3.0中執行awrrpt.sql生成awr報告報ora-06502錯誤OracleSQL
- Factual：2018年基於位置的營銷報告
- [20201106]奇怪的awr報表.txt
- 基於多 goroutine 實現令牌桶Go
- 基於redis實現分散式鎖Redis分散式
- 基於spring實現事件驅動Spring事件
- 基於ZK實現分散式鎖分散式
- Python基於TCP實現聊天功能PythonTCP
- 基於Masstransit實現Eventbus的功能
- 基於jquery實現穿梭框效果jQuery