按SQLID一键获取执行计划索引分区等信息的脚本
以下内容放spoolsql.sql中，在sqlplus中执行，相关信息会自动生成html文件set term off
set termout off
set heading on
set feedback off
set verify off
set echo off
/*
功能说明：
输入SQL_ID、spool路径。
输出与SQL相关的信息，spool到指定文件。
*/
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>' || v.blevel ||
'</td><td>' || v.leaf_blocks ||
'</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('SQL:' || vsqlid || ', cannot be found in DBA_HIST_XXXX.');
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
刘雅君
欲事之无繁，则必劳于始而逸于终
No results found
文章目录
- 常用Linux命令和脚本
- Docker学习笔记_15 docker应用 - 部署Goldengate
- Docker学习笔记_14 docker应用 - 部署ORACLE 11g单实例数据库.md
- Oracle主库添加datafile备库空间不足错误处理
- crfclust.bdb文件过大处理
- 在Centos和Ubuntu中强制yum和Apt-get使用IPv4或IPv6
- Oracle SQL Tuning_03 CBO算法
- Docker学习笔记_13 docker应用 - 部署MYSQL服务
- Oracle SQL Tuning_02 SQL优化方法论
- redis入门到精通 - 04 link链表
- redis入门到精通 - 03 Redis字符串类型的操作
- redis入门到精通 - 02 通用key操作命令
- redis入门到精通 - 01 redis特点及安装
- Docker学习笔记_12 docker应用 - 部署REDIS服务
- Docker学习笔记_11 docker应用 - 部署GITLAB服务
- Python3自动化运维 - 05 函数
- Docker学习笔记_10 docker应用 - 部署TOMCAT服务
- 注册美区Apple ID（无需信用卡）
- Docker学习笔记_09 docker应用 - 部署DNS服务
- Python3自动化运维 - 04 分支循环与深浅拷贝
- Python3自动化运维 - 02 数据类型及引用计数
- Python3自动化运维 - 03 运算表达式
- Python3自动化运维 - 01 环境配置
- ORA-01157 ORA-01110 错误
- Oracle SQL Tuning_01 SQL优化方法论
- Oracle特殊恢复原理与实战_12 Oracle坏块处理
- Oracle特殊恢复原理与实战_11 ORA-8102 Index Corruption修复
- Oracle特殊恢复原理与实战_10 恢复ora-600[4193][4194]的错误
- Oracle特殊恢复原理与实战_09 Undo深入内部解析
- Oracle特殊恢复原理与实战_08 Redo Architecture and Configuration
- Oracle特殊恢复原理与实战_07 归档模式下缺失Redo Log后的恢复
- oracle 11g修改自动收集统计信息计划任务的执行时间
- Oracle特殊恢复原理与实战_06 使用BBED手工修复block数据
- Oracle特殊恢复原理与实战_05 使用BBED跳过归档的恢复
- Oracle特殊恢复原理与实战_04 SYSTEM文件头损坏的恢复
- Oracle特殊恢复原理与实战_03 Control file深入内部解析
- 一台电脑同时使用GitHub和GitLab仓库
- Docker学习笔记_08使用Rancher pipeline搭建基于容器的CICD
- Oracle特殊恢复原理与实战_02 Control file丢失的恢复
- Oracle特殊恢复原理与实战_01 Oracle特殊恢复入门
- Docker学习笔记_07 搭建harbor企业级私有registry
- Docker学习笔记_06 使用rancher搭建gitlab服务并启用https
- Docker学习笔记_05 Rancher的访问控制
- Docker学习笔记_04 Rancher的部署安装(编排选用K8S)
- Docker学习笔记_03 Docker的部署安装(Ubuntu)
- Ubuntu Server 16.04.3安装及常用配置图解
- Docker学习笔记_02 Docker的部署安装(CentOS)
- Docker学习笔记_01 Docker的相关概念
- Docker学习笔记_00 Docker常用命令
- CentOS7下MySQL5.7.20的安装-源码方式
- MySQL性能优化最佳实践 - 12 MySQL性能优化的最佳20+条经验
- Oracle Linux网卡参数默认设置导致ORA-603
- MySQL性能优化最佳实践 - 10 MySQL写出高效SQL
- MySQL性能优化最佳实践 - 11 MySQL锁优化分析
- MySQL性能优化最佳实践 - 09 MySQL优化器算法解析
- flashback常用命令整理
- RMAN (Recovery Manager) 常用命令整理
- MySQL性能优化最佳实践 - 08 SQL EXPLAIN解析
- Oracle RMAN异机恢复归档RMAN-07518（供日志挖掘使用）及修改dbid步骤
- MySQL性能优化最佳实践 - 07 MySQL索引设计
- ORACLE审计
- MySQL性能优化最佳实践 - 06 MySQL设计与开发最佳实践
- MySQL性能优化最佳实践 - 05 MySQL核心参数优化
- MySQL性能优化最佳实践 - 04 MySQL优化之Linux系统层面调优
- MySQL性能优化最佳实践 - 03 MySQL服务器优化
- CentOS7下MySQL5.7的安装-RPM方式
- MySQL性能优化最佳实践 - 02 MySQL数据库性能衡量
- MySQL性能优化最佳实践 - 01 MySQL优化方法论
- 一键获取数据库整体信息脚本
- 按SQLID一键获取执行计划索引分区等信息的脚本
- 收获不止SQL优化读书笔记 - 第三章 循规蹈矩--如何读懂SQL执行计划
- 收获不止SQL优化读书笔记 - 第一章 全局在胸--用工具对SQL整体优化
- 收获不止SQL优化读书笔记 - 第二章 风驰电掣--SQL优化过程
- Oracle 12c特性解读-容器数据库和灾备-11 灾备维护经验总结
- Oracle 12c特性解读-容器数据库和灾备-10 容灾切换和故障演练实践总结
- Oracle 12c特性解读-容器数据库和灾备-09 12c 灾备环境搭建
- SQL优化--dbms_sqltune应用实例
- Oracle 12c特性解读-容器数据库和灾备-08 容灾简介和环境搭建
- Oracle 12c特性解读-容器数据库和灾备-07 升级数据库至12c
- Oracle 12c特性解读-容器数据库和灾备-06 PDB备份与恢复
- Oracle 12c特性解读-容器数据库和灾备-05 PDB迁移和克隆
- Oracle 12c特性解读-容器数据库和灾备-04 CDB PDB对象管理
- 在DELL R730物理机上安装配置Oracle Linux 7.3图解
- 使用Dell iDRAC服务器远程控制安装操作系统简要图解
- Oracle 12c特性解读-容器数据库和灾备-03 创建与管理CDB和PDB
- Oracle 12c特性解读-容器数据库和灾备-02 数据库环境初始化
- Hugepage计算脚本
- Oracle 12c特性解读-容器数据库和灾备-01 Oracle 12c体系结构
- MySQL-DBA从小白到大神实战-15 MySQL源码初探
- MySQL-DBA从小白到大神实战-14 运维MySQL过程中线上故障分析与排查
- MySQL DBA从小白到大神实战-13 深入分析Online DDL原理
- 那些12c中惹祸的特性关闭列表
- ORA-27086 unable to lock file - already in use
- MySQL DBA从小白到大神实战-12 MySQL构架设计与容量规划
- MySQL DBA从小白到大神实战-11 构建高可用MySQL系统
- Hexo文章的密码访问
- GoldenGate 12.1.2 新特性参数部分解释
- 数据库GI PSU，SPU(CPU)，Bundle Patches 和 Patchsets 补丁号码快速参考 (文档 ID 1922396.1)
- Oracle Database 11g工作中常用的命令和脚本整理
- Oracle expdp中INCLUDE参数限制4000个字符的解决办法
- MySQL DBA从小白到大神实战-10 深入理解MySQL主从复制
- VMWare CentOS 虚拟机根分区磁盘扩容(基于LVM)
- MySQL DBA从小白到大神实战-09 MySQL性能优化的关键点
- MySQL DBA从小白到大神实战-08 MySQL监控系统之Zabbix
- CentOS7.3安装图解
- MySQL DBA从小白到大神实战-07 MySQL锁机制与事务机制实现
- MySQL DBA从小白到大神实战-06 深入浅出MySQL备份与恢复
- hexo解决代码块空行自动删除问题
- MySQL DBA从小白到大神实战-05 MySQL DBA日常操作
- MySQL DBA从小白到大神实战-04 揭密MySQL databock and binlog的格式
- 那些在11gR2中可能惹祸的新特性
- hexo博客添加常用插件
- hexo更换主题并个性化定制
- GIT常用命令整理
- hexo+github搭建个人博客
- Sublime Text 常用快捷键
- MySQL DBA从小白到大神实战-03 深入MySQL体系结构
- VIM常用命令
- MySQL DBA从小白到大神实战-02 MySQL标准化、自动化部署
- MySQL DBA从小白到大神实战-01 MySQL 高级DBA职业规划
- Oracle职业直通车-07 重做日志、日志挖掘和回滚段
- Oracle职业直通车-06 Oracle的内存结构与后台进程
- Oracle职业直通车-05 数据字典&数据库的备份和恢复
- Oracle职业直通车-04 复杂一点的SQL/PLSQL
- Oracle职业直通车-03 Oracle数据库的安装和配置
- Oracle职业直通车-02 从最简单的SQL语句开始
- Oracle职业直通车-01 轻松带你走进Oracle数据库的世界