++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***为了后续报表中获取比例,一次性计算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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-701151/