此脚本转自梁敬彬老师的《收获，不止SQL优化》，感谢老师的分享
构造环境脚本，构造部分执行效率低下的SQL
create user hj identified by hj;
alter user hj default tablespace users;
grant dba to hj;
drop table hj.spoolsql_dba_objects purge;
create table hj.spoolsql_dba_objects as select * from dba_objects nologging;
drop table hj.spoolsql_dba_objects purge;
create global temporary table hj.spoolsql_dba_objects
on commit delete rows
as (select * from dba_objects where 1=2)
;
drop table hj.spoolsql_dba_users purge;
create table hj.spoolsql_dba_users as select * from dba_users nologging;
drop table hj.spoolsql_dba_segments purge;
create table hj.spoolsql_dba_segments as select * from dba_segments nologging;
begin
for i in 1 .. 8 loop
insert /*+ append */
into hj.spoolsql_dba_segments
select * from hj.spoolsql_dba_segments nologging;
commit;
end loop;
end;
/
create index hj.idx_spoolsql_dba_segments_name on hj.spoolsql_dba_segments(segment_name) nologging;
exec dbms_stats.gather_table_stats(ownname => 'HJ', tabname => 'spoolsql_dba_objects',cascade => true);
exec dbms_stats.gather_table_stats(ownname => 'HJ', tabname => 'spoolsql_dba_users',cascade => true);
exec dbms_stats.gather_table_stats(ownname => 'HJ', tabname => 'spoolsql_dba_segments',cascade => true);
set linesize 1000
set pagesize 3000
set trim on
set trimspool on
set autot traceonly statistics;
set timing on;
exec dbms_workload_repository.create_snapshot;
insert into hj.spoolsql_dba_objects select * from dba_objects nologging;
variable vbeginid number;
variable vendid number;
exec :vbeginid := 4900;
exec :vendid := 5000;
select t1.owner,
t1.object_name,
t1.subobject_name,
t1.last_ddl_time,
t2.user_id,
t2.account_status,
t3.segment_name,
t3.partition_name,
t3.bytes,
t3.blocks
from hj.spoolsql_dba_objects t1,
hj.spoolsql_dba_users t2,
hj.spoolsql_dba_segments t3
where t1.owner = t2.username
and t1.object_name = t3.segment_name
and t1.object_id between :vbeginid and :vendid
;
exec :vbeginid := 1;
exec :vendid := 5000;
select t1.owner,
t1.object_name,
t1.subobject_name,
t1.last_ddl_time,
t2.user_id,
t2.account_status,
t3.segment_name,
t3.partition_name,
t3.bytes,
t3.blocks
from hj.spoolsql_dba_objects t1,
hj.spoolsql_dba_users t2,
hj.spoolsql_dba_segments t3
where t1.owner = t2.username
and t1.object_name = t3.segment_name
and t1.object_id between :vbeginid and :vendid
;
exec :vbeginid := 1;
exec :vendid := 10000;
select t1.owner,
t1.object_name,
t1.subobject_name,
t1.last_ddl_time,
t2.user_id,
t2.account_status,
t3.segment_name,
t3.partition_name,
t3.bytes,
t3.blocks
from hj.spoolsql_dba_objects t1,
hj.spoolsql_dba_users t2,
hj.spoolsql_dba_segments t3
where t1.owner = t2.username
and t1.object_name = t3.segment_name
and t1.object_id between :vbeginid and :vendid
;
exec :vbeginid := 4990;
exec :vendid := 5000;
select /*+ index(t3 idx_spoolsql_dba_segments_name) */
t1.owner,
t1.object_name,
t1.subobject_name,
t1.last_ddl_time,
t2.user_id,
t2.account_status,
t3.segment_name,
t3.partition_name,
t3.bytes,
t3.blocks
from hj.spoolsql_dba_objects t1,
hj.spoolsql_dba_users t2,
hj.spoolsql_dba_segments t3
where t1.owner = t2.username
and t1.object_name = t3.segment_name
and t1.object_id between :vbeginid and :vendid
;
exec :vbeginid := 1;
exec :vendid := 10000;
select /*+ no_index(t3) */
t1.owner,
t1.object_name,
t1.subobject_name,
t1.last_ddl_time,
t2.user_id,
t2.account_status,
t3.segment_name,
t3.partition_name,
t3.bytes,
t3.blocks
from hj.spoolsql_dba_objects t1,
hj.spoolsql_dba_users t2,
hj.spoolsql_dba_segments t3
where t1.owner = t2.username
and t1.object_name = t3.segment_name
and t1.object_id between :vbeginid and :vendid
;
exec dbms_workload_repository.create_snapshot;
alter index hj.idx_spoolsql_dba_segments_name unusable;
exec :vbeginid := 4900;
exec :vendid := 5000;
select t1.owner,
t1.object_name,
t1.subobject_name,
t1.last_ddl_time,
t2.user_id,
t2.account_status,
t3.segment_name,
t3.partition_name,
t3.bytes,
t3.blocks
from hj.spoolsql_dba_objects t1,
hj.spoolsql_dba_users t2,
hj.spoolsql_dba_segments t3
where t1.owner = t2.username
and t1.object_name = t3.segment_name
and t1.object_id between :vbeginid and :vendid
;
set autot off;
variable v_dbid number;
variable v_inst_num number;
variable v_beginsnap number;
variable v_endsnap number;
begin
with params as
(select beginsnap, endsnap, dbid, instance_number
from (select sum(decode(rn, 2, snap_id, 0)) beginsnap,
sum(decode(rn, 1, snap_id, 0)) endsnap
from (select snap_id,
row_number() over(order by end_interval_time desc) rn
from dba_hist_snapshot)
where rn < 3) t1,
(select dbid from v$database where rownum = 1) t2,
(select instance_number from v$instance where rownum = 1) t3)
select dbid, instance_number, beginsnap, endsnap
into :v_dbid, :v_inst_num, :v_beginsnap, :v_endsnap
from params;
end;
/
col module format a30
col text format a30
select *
from (select sqt.sql_id,
to_char(nvl((round(sqt.elap / 1000000, 3)), to_number(null)),
'9999990.999') elapsed,
to_char(nvl((round(sqt.cput / 1000000, 3)), to_number(null)),
'9999990.999') cputime,
sqt.exec execs,
to_char(decode(sqt.exec,
0,
to_number(null),
round(sqt.elap / sqt.exec / 1000000, 3)),
'9999990.999') "elapsed/exec",
to_char(decode(dbt.dbtime,
0,
100,
100 * (sqt.elap / 10000 / dbt.dbtime)),
'9999990.999') "%DB time",
nvl(sqt.module, '') module,
decode(dbms_lob.substr(st.sql_text, 100,1),
null,
'**********************',
'',
'**********************',
substr(st.sql_text, 1, 50)) sqltxt
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = :v_dbid
and instance_number = :v_inst_num
and :v_beginsnap < snap_id
and snap_id <= :v_endsnap
group by sql_id) sqt,
dba_hist_sqltext st,
(SELECT nvl(sum(e.VALUE) - sum(b.value), 0) dbtime
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :v_beginsnap
AND E.SNAP_ID = :v_endsnap
AND B.DBID = :v_dbid
AND E.DBID = B.DBID
AND B.INSTANCE_NUMBER = :v_inst_num
AND E.INSTANCE_NUMBER = B.INSTANCE_NUMBER
and e.STAT_NAME = 'DB time'
and b.stat_name = 'DB time') dbt
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :v_dbid
order by nvl(sqt.elap, -1) desc, sqt.sql_id)
where rownum < 10;
col username format a15
col event format a30
col sql_txt format a50
select c.USERNAME, a.event, a.cnt as "TIME(SECOND)", a.sql_id, substr(b.SQL_TEXT,1,50) sql_txt
from (select rownum rn, t.*
from (select decode(s.session_state,
'WAITING',
s.event,
'Cpu + Wait For Cpu') Event,
s.sql_id,
s.user_id,
count(*) CNT
from v$active_session_history s
where sample_time > sysdate - 15 / 1440
group by s.user_id,
decode(s.session_state,
'WAITING',
s.event,
'Cpu + Wait For Cpu'),
s.sql_id
order by CNT desc) t
where rownum < 20) a,
v$sqlarea b,
dba_users c
where a.sql_id = b.sql_id
and a.user_id = c.user_id
order by CNT desc
;
exit
执行脚本，构造环境
[oracle@orasql ~]$ sqlplus / as sysdba
SQL> set echo on
SQL> @mksql.sql性能低下的SQL信息收集脚本
set term off
set termout off
set heading on
set feedback off
set verify off
set echo off
set term on
set termout on
set heading off
set feedback off
set verify off
set echo off
SET markup html off spool ON pre off entmap off
set define ^
var sqlid varchar2(50);
var outputdir varchar2(256);
prompt "input sqlid:"
define tmp_sqlid=^SQL_ID
exec :sqlid := '^tmp_sqlid'
column outputdir_sqlid new_value outputfile
select './'||:sqlid||'.html' as outputdir_sqlid from dual;
SET markup html off spool ON pre off entmap off
set term off
set termout off
set heading off
set feedback off
set verify off
set trimspool on
set trim on
set echo off
set linesize 32767
set pagesize 999999
set serveroutput on
spool ^^outputfile
declare
type refcursor is ref cursor;
vCur refcursor;
vsqlid varchar2(4000) := :sqlid;
vOwnerstr varchar2(32767) := '';
vOwnerCnt number(3) := 0;
vTableStr varchar2(32767) := '';
vTableCnt number(3) := 0;
vtmpchldnum number;
vtmpplnval number;
type VARCHARTAB is table of varchar2(32767) index by BINARY_INTEGER;
vPlanAddition VARCHARTAB;
arr_iter BINARY_INTEGER;
vsqltxt_c clob;
vsqltxt_a dbms_sql.varchar2a;
vsqltxt_lb number(1):= 0;
vsqltxt_le number(12);
vsqlcur number;
vsqlmsg varchar2(4000);
begin
----打开服务器output
dbms_output.enable(10000000000000);
----打印html头和样式
dbms_output.put_line('<html><title>Reports About SQL:'||vsqlid||'</title><head>');
dbms_output.put_line('<style>');
dbms_output.put_line('th {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}');
dbms_output.put_line('tr {font:10pt SimSun,SimSun,SimSun,SimSun;}');
dbms_output.put_line('td {border:1px solid; font:10pt SimSun,SimSun,SimSun,SimSun;}');
dbms_output.put_line('</style>');
dbms_output.put_line('</head><body>');
----获取SQL文本
dbms_output.put_line('<b>sql_text</b><br>');
dbms_output.put_line('<table><tr><td>');
select sqtxt
into vsqltxt_c
from (select sql_text sqtxt
from dba_hist_sqltext
where sql_id = vsqlid
union all
select sql_fulltext sqtxt from v$sqlarea where sql_id = vsqlid)
where rownum = 1;
vsqltxt_a.delete;
vsqltxt_le := DBMS_LOB.GETLENGTH(vsqltxt_c) / 2000 + 1;
for i in 1 .. vsqltxt_le loop
vsqltxt_a(i) := substr(vsqltxt_c, 1900 * (i - 1) + 1, 1900);
dbms_output.put_line(vsqltxt_a(i));
end loop;
dbms_output.put_line('</td></tr></table>');
dbms_output.put_line('<br>');
----获取执行计划--explain plan for
dbms_output.put_line('<b>explain plan for</b><table>');
for v in (select distinct parsing_schema_name from dba_hist_sqlstat where sql_id = vsqlid) loop
dbms_output.put_line('<tr><td><pre>');
begin
execute immediate 'alter session set current_schema='||v.parsing_schema_name;
vsqlcur := dbms_sql.open_cursor;
vsqltxt_a(0) := 'explain plan for ';
dbms_sql.parse(vsqlcur, vsqltxt_a, vsqltxt_lb, vsqltxt_le, false, dbms_sql.native);
vsqltxt_le := dbms_sql.execute(vsqlcur);
for v_in in (select plan_table_output as planline from table(dbms_xplan.display)) loop
dbms_output.put_line(v_in.planline);
end loop;
dbms_sql.close_cursor(vsqlcur);
exception
when others then
vsqlmsg := sqlerrm;
dbms_output.put_line(vsqlmsg);
end;
dbms_output.put_line('</pre></td></tr>');
end loop;
dbms_output.put_line('</table>');
dbms_output.put_line('<br>');
----获取执行计划--V$SQLPLAN
dbms_output.put_line('<b>Execution Plan From V$SQLPLAN</b><br>');
for hashval in (select distinct plan_hash_value from v$sql_plan where sql_id = vsqlid) loop
dbms_output.put_line('SQL:'||vsqlid||', PLAN_HASH_VALUE:'||hashval.plan_hash_value);
dbms_output.put_line('<table>');
dbms_output.put_line('<tr><th>ID</th><th>DEP</th><th width="400px">OPERITION</th><th>NAME</th><th>ROWS</th><th>BYTES</th><th>COST(%CPU)</th><th>IO_COST</th><th>TIME</th></tr>');
vPlanAddition.delete;
arr_iter := 0;
for v in (select decode(trim(predicates), '', '' || ID, '*' || ID) ID,
DEPTH,
OPERATION,
NAME,
PREDICATES,
"ROWS",
BYTES,
"COST(%CPU)",
IO_COST,
TIME
from (select a.plan_hash_value || ' ' plan_hash_value,
id ID,
depth DEPTH,
lpad(operation, length(operation) + depth * 6, ' ') || ' ' || options OPERATION,
decode(OBJECT_NAME,
null,
'',
'[' || OBJECT_TYPE || ']' || OBJECT_OWNER || '.' ||
OBJECT_NAME) || ' ' NAME,
case
when (length(access_predicates) < 3 or access_predicates is null) then
case
when (length(filter_predicates) < 3 or filter_predicates is null) then
' '
else
'[filter]' || substr(filter_predicates, 1, 3990)
end
else
'[access]' || substr(access_predicates, 1, 3990)
end PREDICATES,
decode(cardinality, null, '', cardinality) || ' ' "ROWS",
decode(Bytes, null, '', Bytes) || ' ' BYTES,
decode(io_cost,
null,
decode(cost, null, '', cost),
decode(cost, null, '', cost) || '(' ||
decode(cost, 0, 0, round((cost - io_cost) / cost * 100)) || ')') || ' ' "COST(%CPU)",
--decode(cpu_cost, null, '', cpu_cost) || ' ' CPU_COST,
decode(io_cost, null, '', io_cost) || ' ' IO_COST,
trim(to_char(round(nvl(time, 0) / 3600), '00')) || ':' ||
to_char(to_date('19000101', 'yyyymmdd') + nvl(time, 0) / 3600 / 24,
'mi:ss') TIME
from v$sql_plan a,
(select plan_hash_value, max(child_number) child_number, sql_id
from v$sql_plan
where sql_id = vsqlid
group by sql_id, plan_hash_value) b
where a.sql_id = b.sql_id
and a.plan_hash_value = b.plan_hash_value
and a.plan_hash_value = hashval.plan_hash_value
and a.child_number = b.child_number
order by a.plan_hash_value, id asc
)) loop
arr_iter := arr_iter+1;
vPlanAddition(arr_iter) := case when trim(v.predicates)||' ' = ' ' then ''
else v.id||' => '||v.predicates || '<br>' end;
dbms_output.put_line('<tr><td>' || v.ID || '</td><td>' || v.DEPTH ||
'</td><td width="400px">' || v.OPERATION ||
'</td><td>'|| v.NAME || '</td><td>' || v."ROWS" ||
'</td><td>' || v.BYTES || '</td><td>' || v."COST(%CPU)" ||
'</td><td>' || v.IO_COST || '</td><td>' || v.TIME || '</td></tr>');
end loop;
dbms_output.put_line('</table><br>');
dbms_output.put_line('<p style="font:10pt SimSun,SimSun,SimSun,SimSun">');
dbms_output.put_line('Predicate Information (identified by operation id):<br>');
dbms_output.put_line('---------------------------------------------------------------------------<br>');
arr_iter := 0;
for v1 in 1..vPlanAddition.count loop
arr_iter := arr_iter+1;
dbms_output.put_line(vPlanAddition(arr_iter));
end loop;
dbms_output.put_line('<p>');
end loop;
dbms_output.put_line('<br>');
----SQL统计信息（SQL STATISTICS - V$SQLSTATS)
dbms_output.put_line('<b>SQL STATISTICS- V$SQLPLAN</b>');
dbms_output.put_line('<table><tr><th>ID</th><th>HASH</th><th>DB_TIME</th><th>MEM</th><th>VERS</th><th>EXES</th><th>DISKS</th><th>GETS</th><th>ROWS</th><th>CPU</th><th>ELAPSED</th><th>CLUSTER_WAIT</th><th>CONCURRENCE_WAIT</th></tr>');
for v in (select mem.SQL_ID "ID",
mem.PLAN_HASH_VALUE "HASH",
mem_dbtime.VAL DB_TIME,
mem.SHARABLE_MEM MEM,
mem.VERSION_COUNT VERS,
mem.EXECUTIONS EXES,
mem.PARSE_CALLS PARSE_CALLS,
mem.DISK_READS "DISKS",
mem.BUFFER_GETS GETS,
mem.ROWS_PROCESSED "ROWS",
mem.CPU_TIME "CPU",
mem.ELAPSED_TIME ELAPSED,
mem.CLWAIT CLUSTER_WAIT,
mem.CCWAIT CONCURRENCE_WAIT
from (select SQL_ID,
PLAN_HASH_VALUE,
SHARABLE_MEM,
VERSION_COUNT,
FETCHES,
END_OF_FETCH_COUNT,
SORTS,
EXECUTIONS,
PX_SERVERS_EXECUTIONS PX_SERVERS_EXECS,
LOADS,
INVALIDATIONS,
PARSE_CALLS,
DISK_READS,
BUFFER_GETS,
ROWS_PROCESSED,
CPU_TIME,
ELAPSED_TIME,
CLUSTER_WAIT_TIME CLWAIT,
APPLICATION_WAIT_TIME APWAIT,
CONCURRENCY_WAIT_TIME CCWAIT,
DIRECT_WRITES,
PLSQL_EXEC_TIME PLSEXEC_TIME,
JAVA_EXEC_TIME JAVEXEC_TIME
from v$sqlstats
where sql_id = vsqlid) mem,(select sum(value) val from v$sysstat where name = 'DB time') mem_dbtime
) loop
dbms_output.put_line('<tr>');
dbms_output.put_line('<td>'||v."ID"||'</td>');
dbms_output.put_line('<td>'||v."HASH"||'</td>');
dbms_output.put_line('<td>'||v.DB_TIME||'</td>');
dbms_output.put_line('<td>'||v.MEM||'</td>');
dbms_output.put_line('<td>'||v.VERS||'</td>');
dbms_output.put_line('<td>'||v.EXES||'</td>');
dbms_output.put_line('<td>'||v."DISKS"||'</td>');
dbms_output.put_line('<td>'||v.GETS||'</td>');
dbms_output.put_line('<td>'||v."ROWS"||'</td>');
dbms_output.put_line('<td>'||v."CPU"||'</td>');
dbms_output.put_line('<td>'||v.ELAPSED||'</td>');
dbms_output.put_line('<td>'||v.CLUSTER_WAIT||'</td>');
dbms_output.put_line('<td>'||v.CONCURRENCE_WAIT||'</td>');
dbms_output.put_line('</tr>');
end loop;
dbms_output.put_line('</table></br>');
----获取执行计划--AWR
dbms_output.put_line('<b>Execution Plan From AWR</b><br><br>');
for hashval in (select distinct plan_hash_value from dba_hist_sql_plan where sql_id = vsqlid) loop
dbms_output.put_line('SQL:'||vsqlid||', PLAN_HASH_VALUE:'||hashval.plan_hash_value);
dbms_output.put_line('<table>');
dbms_output.put_line('<tr><th>ID</th><th>DEP</th><th width="400px">OPERITION</th><th>NAME</th><th>ROWS</th><th>BYTES</th><th>COST(%CPU)</th><th>IO_COST</th><th>TIME</th></tr>');
vPlanAddition.delete;
arr_iter := 0;
for v in (select decode(trim(predicates), '', '' || ID, '*' || ID) ID,
DEPTH,
OPERATION,
NAME,
PREDICATES,
"ROWS",
BYTES,
"COST(%CPU)",
IO_COST,
TIME
from (select a.plan_hash_value || ' ' plan_hash_value,
id ID,
depth DEPTH,
lpad(operation, length(operation) + depth * 6, ' ') || ' ' || options OPERATION,
decode(OBJECT_NAME,
null,
'',
'[' || OBJECT_TYPE || ']' || OBJECT_OWNER || '.' ||
OBJECT_NAME) || ' ' NAME,
case
when (length(access_predicates) < 3 or access_predicates is null) then
case
when (length(filter_predicates) < 3 or filter_predicates is null) then
' '
else
'[filter]' || substr(filter_predicates, 1, 3990)
end
else
'[access]' || substr(access_predicates, 1, 3990)
end PREDICATES,
decode(cardinality, null, '', cardinality) || ' ' "ROWS",
decode(Bytes, null, '', Bytes) || ' ' BYTES,
decode(io_cost,
null,
decode(cost, null, '', cost),
decode(cost, null, '', cost) || '(' ||
decode(cost, 0, 0, round((cost - io_cost) / cost * 100)) || ')') || ' ' "COST(%CPU)",
--decode(cpu_cost, null, '', cpu_cost) || ' ' CPU_COST,
decode(io_cost, null, '', io_cost) || ' ' IO_COST,
trim(to_char(round(nvl(time, 0) / 3600), '00')) || ':' ||
to_char(to_date('19000101', 'yyyymmdd') + nvl(time, 0) / 3600 / 24,
'mi:ss') TIME
from dba_hist_sql_plan a
where a.sql_id = vsqlid
and a.plan_hash_value = hashval.plan_hash_value
order by a.plan_hash_value, id asc
)) loop
/*AWR里没有Predicate信息
arr_iter := arr_iter+1;
vPlanAddition(arr_iter) := case when trim(v.predicates)||' ' = ' ' then ''
else ' '|| v.id||' => '||v.predicates || '<br>' end;
*/
dbms_output.put_line('<tr><td>' || v.ID || '</td><td>' || v.DEPTH ||
'</td><td width="400px">' || v.OPERATION ||
'</td><td>'|| v.NAME || '</td><td>' || v."ROWS" ||
'</td><td>' || v.BYTES || '</td><td>' || v."COST(%CPU)" ||
'</td><td>' || v.IO_COST || '</td><td>' || v.TIME || '</td></tr>');
end loop;
dbms_output.put_line('</table><br>');
/*AWR里没有Predicate信息
dbms_output.put_line('<p style="font:10pt SimSun,SimSun,SimSun,SimSun">');
dbms_output.put_line(' Predicate Information (identified by operation id):<br>');
dbms_output.put_line(' ---------------------------------------------------------------------------<br>');
arr_iter := 0;
for v1 in 1..vPlanAddition.count loop
arr_iter := arr_iter+1;
dbms_output.put_line(vPlanAddition(arr_iter));
end loop;
*/
dbms_output.put_line('<p>');
end loop;
/*
----SQL统计信息（SQL STATISTICS - AWR)
dbms_output.put_line('<b>SQL STATISTICS</b>');
dbms_output.put_line('<table><tr><th>ID</th><th>HASH</th><th>DB_TIME_TOTAL</th><th>DB_TIME_CURRENT</th><th>MEM</th><th>VERS</th><th>EXES</th><th>DISKS</th><th>GETS</th><th>ROWS</th><th>CPU</th><th>ELAPSED</th><th>CCWAIT</th></tr>');
for v in (select mem.SQL_ID "ID",
mem.PLAN_HASH_VALUE "HASH",
mem_dbtime.VAL - awr_dbtime.VAL DB_TIME_TOTAL,
mem_dbtime.VAL - (select max(value) VAL
from dba_hist_sysstat
where stat_name = 'DB time') DB_TIME_CURRENT,
mem.SHARABLE_MEM MEM,
mem.VERSION_COUNT VERS,
mem.EXECUTIONS - awr.EXECUTIONS EXES,
mem.PARSE_CALLS - awr.PARSE_CALLS PARSE_CALLS,
mem.DISK_READS - awr.DISK_READS "DISKS",
mem.BUFFER_GETS - awr.BUFFER_GETS GETS,
mem.ROWS_PROCESSED - awr.ROWS_PROCESSED "ROWS",
mem.CPU_TIME - awr.CPU_TIME "CPU",
mem.ELAPSED_TIME - awr.ELAPSED_TIME ELAPSED,
mem.CCWAIT - awr.CCWAIT CCWAIT
from (select SQL_ID,
PLAN_HASH_VALUE,
SHARABLE_MEM,
VERSION_COUNT,
FETCHES,
END_OF_FETCH_COUNT,
SORTS,
EXECUTIONS,
PX_SERVERS_EXECUTIONS PX_SERVERS_EXECS,
LOADS,
INVALIDATIONS,
PARSE_CALLS,
DISK_READS,
BUFFER_GETS,
ROWS_PROCESSED,
CPU_TIME,
ELAPSED_TIME,
--IOWAIT,
CLUSTER_WAIT_TIME CLWAIT,
APPLICATION_WAIT_TIME APWAIT,
CONCURRENCY_WAIT_TIME CCWAIT,
DIRECT_WRITES,
PLSQL_EXEC_TIME PLSEXEC_TIME,
JAVA_EXEC_TIME JAVEXEC_TIME
from v$sqlstats
where sql_id = vsqlid) mem,
(select max(SNAP_ID) SNAP_ID,
SQL_ID,
PLAN_HASH_VALUE,
max(SHARABLE_MEM) SHARABLE_MEM,
max(VERSION_COUNT) VERSION_COUNT,
max(FETCHES_TOTAL) FETCHES,
max(END_OF_FETCH_COUNT_TOTAL) END_OF_FETCH_COUNT,
max(SORTS_TOTAL) SORTS,
max(EXECUTIONS_TOTAL) EXECUTIONS,
max(PX_SERVERS_EXECS_TOTAL) PX_SERVERS_EXECS,
max(LOADS_TOTAL) LOADS,
max(INVALIDATIONS_TOTAL) INVALIDATIONS,
max(PARSE_CALLS_TOTAL) PARSE_CALLS,
max(DISK_READS_TOTAL) DISK_READS,
max(BUFFER_GETS_TOTAL) BUFFER_GETS,
max(ROWS_PROCESSED_TOTAL) ROWS_PROCESSED,
max(CPU_TIME_TOTAL) CPU_TIME,
max(ELAPSED_TIME_TOTAL) ELAPSED_TIME,
max(CLWAIT_TOTAL) CLWAIT,
max(APWAIT_TOTAL) APWAIT,
max(CCWAIT_TOTAL) CCWAIT,
max(DIRECT_WRITES_TOTAL) DIRECT_WRITES,
max(PLSEXEC_TIME_TOTAL) PLSEXEC_TIME,
max(JAVEXEC_TIME_TOTAL) JAVEXEC_TIME
from dba_hist_sqlstat
where snap_id >
(select min(snap_id)
from dba_hist_snapshot
where startup_time =
(select max(startup_time) from dba_hist_snapshot))
and sql_id = vsqlid
group by sql_id, plan_hash_value) awr,
(select sum(value) val from v$sysstat where name = 'DB time') mem_dbtime,
(select a.snap_id, a.value val
from dba_hist_sysstat a
where a.snap_id >
(select min(snap_id)
from dba_hist_snapshot
where startup_time =
(select max(startup_time) from dba_hist_snapshot))
and a.stat_name = 'DB time') awr_dbtime
where awr.sql_id(+) = mem.sql_id
and awr.plan_hash_value(+) = mem.plan_hash_value
and awr.snap_id = awr_dbtime.snap_id
) loop
dbms_output.put_line('<tr>');
dbms_output.put_line('<td>'||v."ID"||'</td>');
dbms_output.put_line('<td>'||v."HASH"||'</td>');
dbms_output.put_line('<td>'||v.DB_TIME_TOTAL||'</td>');
dbms_output.put_line('<td>'||v.DB_TIME_CURRENT||'</td>');
dbms_output.put_line('<td>'||v.MEM||'</td>');
dbms_output.put_line('<td>'||v.VERS||'</td>');
dbms_output.put_line('<td>'||v.EXES||'</td>');
dbms_output.put_line('<td>'||v."DISKS"||'</td>');
dbms_output.put_line('<td>'||v.GETS||'</td>');
dbms_output.put_line('<td>'||v."ROWS"||'</td>');
dbms_output.put_line('<td>'||v."CPU"||'</td>');
dbms_output.put_line('<td>'||v.ELAPSED||'</td>');
dbms_output.put_line('<td>'||v.CCWAIT||'</td>');
dbms_output.put_line('</tr>');
end loop;
dbms_output.put_line('</table></br>');
*/
----获取SQL语句所涉及的表
vOwnerCnt := 0;
vOwnerstr := '';
vTableCnt := 0;
vTablestr := '';
for v in (select OBJECT_OWNER, object_name
from v$sql_plan
where sql_id = vsqlid
and operation like '%TABLE%'
union
select OBJECT_OWNER, object_name from dba_hist_sql_plan
where sql_id = vsqlid
and operation like '%TABLE%'
union
select OBJECT_OWNER, object_name from PLAN_TABLE
where operation like '%TABLE%'
) loop
vTablestr := vTablestr || v.OBJECT_NAME || ',';
vTableCnt := vTableCnt + 1;
vOwnerstr := vOwnerstr || v.OBJECT_OWNER || ',';
vOwnerCnt := vOwnerCnt + 1;
end loop;
vOwnerstr := substr(vOwnerstr, 1, length(vOwnerstr) - 1);
vTablestr := substr(vTablestr, 1, length(vTablestr) - 1);
----SQL涉及所有表的尺寸（Table Segment Size)
dbms_output.put_line('<b>Table Segment Size</b>');
dbms_output.put_line('<table><tr><th>owner</th><th>segment_name</th><th>MB</th>');
for v in (select owner, segment_name, to_char(sum(bytes) / 1024 / 1024, '9999990.99') MB
from dba_segments
where segment_name in
(SELECT REGEXP_SUBSTR(vTablestr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vTableCnt)
and owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)
group by owner, segment_name
order by owner, segment_name) loop
dbms_output.put_line('<tr><td>' || v.OWNER || '</td><td>' ||
v.segment_name || '</td><td>' || v.MB ||
'</td></tr>');
end loop;
dbms_output.put_line('</table></br>');
----SQL涉及所有表的相关信息(Table Statistics)
dbms_output.put_line('<b>Table Statistics</b>');
dbms_output.put_line('<table><tr>' || '<th>OWNER</th>' ||
'<th>table_name</th>' || '<th>num_rows</th>' ||
'<th>blocks</th>' || '<th>degree</th>' ||
'<th>last_analyzed</th>' || '<th>temporary</th>' ||
'<th>partitioned</th>' || '<th>pct_free</th>' ||
'<th>tablespace_name</th>');
for v in (select t.OWNER,
t.table_name,
t.num_rows,
t.blocks,
t.degree,
t.last_analyzed,
t.temporary,
t.partitioned,
t.pct_free,
t.tablespace_name
from dba_tables t
where table_name in
(SELECT REGEXP_SUBSTR(vtablestr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vTableCnt)
and owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)
order by owner, table_name) loop
dbms_output.put_line('<tr><td>' || v.OWNER || '</td><td>' ||
v.table_name || '</td><td>' || v.num_rows ||
'</td><td>' || v.blocks || '</td><td>' ||
v.degree || '</td><td>' || v.last_analyzed ||
'</td><td>' || v.temporary || '</td><td>' ||
v.partitioned || '</td><td>' || v.pct_free ||
'</td><td>' || v.tablespace_name || '</td></tr>');
end loop;
dbms_output.put_line('</table></br>');
----SQL涉及所有表的列信息(Table Column Statistics)
dbms_output.put_line('<b>Table Column Statistics</b>');
dbms_output.put_line('<table><tr>' || '<th>OWNER</th>' ||
'<th>table_name</th>' || '<th>column_name</th>' ||
'<th>data_type</th>' || '<th>nullable</th>' ||
'<th>last_analyzed</th>' || '<th>avg_col_len</th></tr>');
for v in (select owner,
table_name,
column_name,
data_type,
nullable,
to_char(last_analyzed, 'mm/dd/yy hh24:mi:ss') analyzed,
avg_col_len
from dba_tab_cols
where table_name in (SELECT REGEXP_SUBSTR(vTABLESTR, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vTABLECNT)
and owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)
order by owner, table_name, column_name) loop
dbms_output.put_line('<tr><td>' || v.OWNER || '</td><td>' ||
v.table_name || '</td><td>' || v.column_name ||
'</td><td>' || v.data_type || '</td><td>' ||
v.nullable || '</td><td>' || v.analyzed ||
'</td><td>' || v.avg_col_len || '</td></tr>');
end loop;
dbms_output.put_line('</table></br>');
----SQL涉及所有表的触发器信息(Table Triggers)
dbms_output.put_line('<b>Table Triggers</b>');
dbms_output.put_line('<table><tr>' || '<th>table_owner</th>' ||
'<th>table_name</th>' || '<th>base_object_type</th>' ||
'<th>tiggger_owner</th>' || '<th>trigger_name</th>' ||
'<th>trigger_type</th>' || '<th>triggering_event</th></tr>');
for v in (select table_owner,
table_name,
base_object_type,
owner tiggger_owner,
trigger_name,
trigger_type,
triggering_event
from dba_triggers
where table_name in (SELECT REGEXP_SUBSTR(vTABLESTR, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vTABLECNT)
and owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)
order by table_owner, table_name) loop
dbms_output.put_line('<tr><td>' || v.table_owner || '</td><td>' ||
v.table_name || '</td><td>' || v.base_object_type ||
'</td><td>' || v.tiggger_owner || '</td><td>' ||
v.trigger_name || '</td><td>' || v.trigger_type ||
'</td><td>' || v.triggering_event || '</td></tr>');
end loop;
dbms_output.put_line('</table></br>');
----SQL涉及的分区表的相关信息(Partition Statistics)
dbms_output.put_line('<b>Partition Statistics</b>');
dbms_output.put_line('<table><tr>' || '<th>OWNER</th>' ||
'<th>table_name</th>' ||
'<th>partitioning_type</th>' ||
'<th>partition_count</th>');
for v in (select t.owner,
t.table_name,
t.partitioning_type,
t.partition_count
from dba_part_tables t
where table_name in
(SELECT REGEXP_SUBSTR(vtablestr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vtablecnt)
and owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)) loop
dbms_output.put_line('<tr><td>' || v.OWNER || '</td><td>' ||
v.table_name || '</td><td>' ||
v.partitioning_type || '</td><td>' ||
v.partition_count || '</td></tr>');
end loop;
dbms_output.put_line('</table></br>');
----分区表的分区列相关信息(Partition Key Statistics)
dbms_output.put_line('<b>Partition Key Statistics</b>');
dbms_output.put_line('<table><tr>' || '<th>owner</th>' ||
'<th>name</th>' || '<th>object_type</th>' ||
'<th>column_name</th>');
for v in (select owner, name, object_type, column_name
from dba_part_key_columns
where name in (SELECT REGEXP_SUBSTR(vtablestr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vtablecnt)
and owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)) loop
dbms_output.put_line('<tr><td>' || v.OWNER || '</td><td>' || v.name ||
'</td><td>' || v.object_type || '</td><td>' ||
v.column_name || '</td></tr>');
end loop;
dbms_output.put_line('</table>');
----分区表分区范围信息(Partition Range Statistics)
dbms_output.put_line('<b>Partition Range Statistics</b>');
dbms_output.put_line('<table><tr>' || '<th>owner</th>' ||
'<th>table_name</th>' || '<th>partition_name</th>' ||
'<th>high_value</th>' || '<th>tablespace_name</th>');
for v in (SELECT table_owner owner,
table_name,
partition_name,
high_value,
tablespace_name
FROM dba_tab_partitions t
where table_name in
(SELECT REGEXP_SUBSTR(vtablestr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vtablecnt)
and table_owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)
order by table_owner, table_name, t.partition_position) loop
dbms_output.put_line('<tr><td>' || v.OWNER || '</td><td>' ||
v.table_name || '</td><td>' || v.partition_name ||
'</td><td>' || v.high_value || '</td><td>' ||
v.tablespace_name || '</td></tr>');
end loop;
dbms_output.put_line('</table></br>');
----索引的大小(Index Segments)
dbms_output.put_line('<b>Index Segments</b>');
dbms_output.put_line('<table><tr>' || '<th>OWNER</th>' ||
'<th>table_name</th>' || '<th>segment_name</th>' ||
'<th>MB</th></tr>');
for v in (select t1.owner,
t2.table_name,
t1.segment_name,
to_char(sum(t1.bytes) / 1024 / 1024, '9999990.99') MB
from dba_segments t1, dba_indexes t2
where t1.segment_name = t2.index_name
and t1.segment_type like '%INDEX%'
and t1.owner = t2.owner
and t2.table_name in
(SELECT REGEXP_SUBSTR(vtablestr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vtablecnt)
and t1.owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)
and t2.owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)
group by t1.owner, t2.table_name, t1.segment_name
order by owner, table_name) loop
dbms_output.put_line('<tr><td>' || v.owner || '</td><td>' ||
v.table_name || '</td><td>' || v.segment_name ||
'</td><td>' || v.MB || '</td></tr>');
end loop;
dbms_output.put_line('</table></br>');
----索引相关信息(Index Statistics)
dbms_output.put_line('<b>Index Statistics</b>');
dbms_output.put_line('<table><tr>' || '<th>OWNER</th>' ||
'<th>table_name</th>' || '<th>name</th>' ||
'<th>rows</th>' || '<th>type</th>' ||
'<th>status</th>' || '<th>factor</th>' ||
'<th>blevel</th>' || '<th>distinct</th>' ||
'<th>leaf</th>' || '<th>unique</th>' ||
'<th>degree</th>' || '<th>analyzed</th></tr>');
for v in (select t.owner,
t.table_name,
t.index_name "name",
t.num_rows "rows",
t.index_type "type",
t.status,
t.clustering_factor factor,
t.blevel,
t.distinct_keys "distinct",
t.leaf_blocks leaf,
t.uniqueness "unique",
t.degree,
to_char(t.last_analyzed, 'mm/dd/yy hh24:mi:ss') analyzed
from dba_indexes t
where table_name in
(SELECT REGEXP_SUBSTR(vtablestr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vtablecnt)
and owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)
order by owner, table_name) loop
dbms_output.put_line('<tr><td>' || v.owner || '</td><td>' ||
v.table_name || '</td><td>' || v."name" ||
'</td><td>' || v."rows" || '</td><td>' ||
v."type" || '</td><td>' || v.status ||
'</td><td>' || v.factor || '</td><td>' ||
v.blevel || '</td><td>' || v."distinct" ||
'</td><td>' || v.leaf || '</td><td>' ||
v."unique" || '</td><td>' || v.degree ||
'</td><td>' || v.analyzed || '</td><tr>');
end loop;
dbms_output.put_line('</table></br>');
----索引列信息，在哪些列有索引(Index columns)
dbms_output.put_line('<b>Index columns</b>');
dbms_output.put_line('<table><tr>' || '<th>OWNER</th>' ||
'<th>table_name</th>' || '<th>table_name</th>' ||
'<th>column_name</th>' ||
'<th>column_position</th>' ||
'<th>DESCEND</th></tr>');
for v in (select t.index_owner owner,
t.table_name,
t.index_name,
t.column_name,
t.column_position,
t.DESCEND
from dba_ind_columns t
where table_name in
(SELECT REGEXP_SUBSTR(vtablestr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vtablecnt)
and index_owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)
order by owner, table_name, index_name, column_position) loop
dbms_output.put_line('<tr><td>' || v.owner || '</td><td>' ||
v.table_name || '</td><td>' || v.index_name ||
'</td><td>' || v.column_name || '</td><td>' ||
v.column_position || '</td><td>' || v.DESCEND ||
'</td></tr>');
end loop;
dbms_output.put_line('</table></br>');
----分区索引的分区个数(Partition Indexes nums)
dbms_output.put_line('<b>Partition Indexes Summary</b>');
dbms_output.put_line('<table><tr>' || '<th>OWNER</th>' ||
'<th>table_name</th>' || '<th>table_name</th>' ||
'<th>partitioning_type</th>' ||
'<th>partition_count</th></tr>');
for v in (select owner,
table_name,
index_name,
partitioning_type,
partition_count
from dba_part_indexes
where table_name in
(SELECT REGEXP_SUBSTR(vtablestr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vtablecnt)
and owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)
order by owner, table_name, index_name) loop
dbms_output.put_line('<tr><td>' || v.owner || '</td><td>' ||
v.table_name || '</td><td>' || v.index_name ||
'</td><td>' || v.partitioning_type || '</td><td>' ||
v.partition_count || '</td></tr>');
end loop;
dbms_output.put_line('</table></br>');
----分区索引的详细信息(Partition Indexes details)
dbms_output.put_line('<b>Partition Indexes Details</b>');
dbms_output.put_line('<table><tr>' || '<th>OWNER</th>' ||
'<th>index_name</th>' || '<th>partition_name</th>' ||
'<th>status</th>' || '<th>blevel</th>' ||
'<th>leaf_blocks</th>' ||
'<th>tablespace_name</th></tr>');
for v in (select index_owner owner,
index_name,
partition_name,
status,
blevel,
leaf_blocks,
tablespace_name
from dba_ind_partitions
where index_name in
(select index_name
from dba_indexes
where table_name in
(SELECT REGEXP_SUBSTR(vtablestr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vtablecnt)
and owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt))
and index_owner in (SELECT REGEXP_SUBSTR(vOwnerstr, '[^,]+', 1, LEVEL) AS value_str
FROM DUAL
CONNECT BY LEVEL <= vOwnerCnt)
order by owner, index_name, partition_name) loop
dbms_output.put_line('<tr><td>' || v.owner || '</td><td>' ||
v.index_name || '</td><td>' || v.partition_name ||
'</td><td>' || v.status || '</td><td>' ||
'</td><td>' || v.blevel || '</td><td>' ||
'</td><td>' || v.leaf_blocks || '</td><td>' ||
'</td><td>' || v.tablespace_name || '</td></tr>');
end loop;
dbms_output.put_line('</table></br>');
----嵌入awrsqrpt
dbms_output.put_line('<b>Awrsqrpt Contents</b>');
dbms_output.put_line('<div>');
begin
for v in (select max(maxsnap_id) maxsnap_id,
max(maxsnap_id) - 1 as minsnap_id
from (select startup_time,
max(a.snap_id) maxsnap_id,
min(a.snap_id) minsnap_id
from dba_hist_snapshot a, dba_hist_sqlstat b
where a.snap_id = b.snap_id
and b.sql_id = vsqlid
group by startup_time)
where maxsnap_id <> minsnap_id) loop
dbms_output.put_line('<div>');
begin
for v_in in (select output
from table(dbms_workload_repository.awr_sql_report_html((select dbid
from v$database
where rownum = 1),
(select instance_number
from v$instance
where rownum = 1),
v.minsnap_id,
v.maxsnap_id,
vsqlid))) loop
dbms_output.put_line(v_in.output);
end loop;
Exception
when others then
vsqlmsg := sqlerrm;
dbms_output.put_line('在DBA_HIST_XXXX中找不到SQL:' || vsqlid || '的信息.');
dbms_output.put_line(vsqlmsg);
end;
dbms_output.put_line('</div>');
end loop;
Exception
when others then
vsqlmsg := sqlerrm;
dbms_output.put_line(vsqlmsg);
end;
dbms_output.put_line('</div>');
dbms_output.put_line('</body></html>');
rollback;
end;
/
set termout on
spool off;
exit
执行脚本，此脚本需要SQL_ID
[oracle@orasql ~]$ sqlplus / as sysdba
SQL> @spoolsql.sql
"input sqlid:"
Enter value for sql_id: 5fy6m249h2kr5
./5fy6m249h2kr5.html
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
3330
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
