当前位置：首页 > article >正文

执行计划统计信息相关 SQL_MONITOR display_cursor

article 2025/3/10 11:55:45

Select sql_plan_hash_value,sql_plan_line_id,count(*) from
dba_hist_active_sess_history where sql_id='XXXx' and sample_time>sysdate-1
Group by sql_plan_hash_value,sql_plan_line_id order by 3 desc;

和activity 对应的上

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
SQL_ID => 'XXXX',
TYPE => 'TEXT',
REPORT_LEVEL => 'ALL') AS REPORT
FROM dual;

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
SQL_ID => 'XXXX',
TYPE => 'HTML',
REPORT_LEVEL => 'ALL') AS REPORT
FROM dual;

箭头的方向是在做的join和表

select * from table(dbms_xplan.display_cursor('xxx',null,'advanced'));

select * from table(gv$(cursor(select * from table(dbms_xplan.display_awr('xxx',null)))))

select * from table(gv$(cursor(select * from table(dbms_xplan.display_cursor('xxx',null)))))

查找SQL中相关表统计信息

SELECT /* ^^script..sql Table Columns */
 v2.line_text
FROM (
WITH object AS (
SELECT /*+ MATERIALIZE */
 object_owner owner, object_name name
FROM gv$sql_plan
WHERE inst_id IN (SELECT inst_id FROM gv$instance)
 AND sql_id = '2dx6gfdkfhza1'
 AND object_owner IS NOT NULL
 AND object_name IS NOT NULL
UNION
SELECT object_owner owner, object_name name
FROM dba_hist_sql_plan
WHERE
sql_id = '2dx6gfdkfhza1'
 AND object_owner IS NOT NULL
 AND object_name IS NOT NULL
), plan_tables AS (
SELECT /*+ MATERIALIZE */
 'TABLE' object_type, t.owner object_owner, t.table_name object_name
 FROM dba_tab_statistics t, -- include fixed objects
 object o
WHERE t.owner = o.owner
 AND t.table_name = o.name
UNION
SELECT 'TABLE' object_type, i.table_owner object_owner, i.table_name object_name
 FROM dba_indexes i,
 object o
WHERE i.owner = o.owner
 AND i.index_name = o.name
)
SELECT object_name table_name,
 object_owner owner,
 1 line_type,
 1 row_num,
 '<a name="c_'||LOWER(object_name||'_'||object_owner)||'"></a><h3>Table Columns: '||object_name||' ('||object_owner||')</h3>'||CHR(10)||CHR(10)||
 'CBO Statistics and relevant attributes.'||CHR(10)||CHR(10)||
 '<table>'||CHR(10)||CHR(10)||
 '<tr>'||CHR(10)||
 '<th>#</th>'||CHR(10)||
 --'<th>Table Name</th>'||CHR(10)||
 --'<th>Owner</th>'||CHR(10)||
 '<th>Indexes</th>'||CHR(10)||
 '<th>Col ID</th>'||CHR(10)||
 '<th>Column Name</th>'||CHR(10)||
 '<th>Data Type</th>'||CHR(10)||
 '<th>Num Rows</th>'||CHR(10)||
 '<th>Num Nulls</th>'||CHR(10)||
 '<th>Sample Size</th>'||CHR(10)||
 '<th>Perc</th>'||CHR(10)||
 '<th>Num Distinct</th>'||CHR(10)||
 '<th>Low Value</th>'||CHR(10)||
 '<th>High Value</th>'||CHR(10)||
 '<th>Last Analyzed</th>'||CHR(10)||
 '<th>Avg Col Len</th>'||CHR(10)||
 '<th>Density</th>'||CHR(10)||
 '<th>Num Buckets</th>'||CHR(10)||
 '<th>Histogram</th>'||CHR(10)||
 '<th>Global Stats</th>'||CHR(10)||
 '<th>User Stats</th>'||CHR(10)||
 '</tr>'||CHR(10) line_text
FROM plan_tables
WHERE object_type = 'TABLE'
UNION ALL
SELECT v.table_name,
 v.owner,
 2 line_type,
 ROWNUM row_num,
 CHR(10)||'<tr>'||CHR(10)||
 '<td class="r">'||ROWNUM||'</td>'||CHR(10)||
 --'<td>'||v.table_name||'</td>'||CHR(10)||
 --'<td>'||v.owner||'</td>'||CHR(10)||
 '<td class="c">'||v.indexes||'</td>'||CHR(10)||
 '<td class="c">'||v.column_id||'</td>'||CHR(10)||
 '<td>'||v.column_name||'</td>'||CHR(10)||
 '<td>'||v.data_type||'</td>'||CHR(10)||
 '<td class="r">'||v.num_rows||'</td>'||CHR(10)||
 '<td class="r">'||v.num_nulls||'</td>'||CHR(10)||
 '<td class="r">'||v.sample_size||'</td>'||CHR(10)||
 '<td class="r">'||v.sample_size_perc||'</td>'||CHR(10)||
 '<td class="r">'||v.num_distinct||'</td>'||CHR(10)||
 '<td nowrap>'||v.low_value||'</td>'||CHR(10)||
 '<td nowrap>'||v.high_value||'</td>'||CHR(10)||
 '<td nowrap>'||v.last_analyzed||'</td>'||CHR(10)||
 '<td class="r">'||v.avg_col_len||'</td>'||CHR(10)||
 '<td class="r">'||v.density||'</td>'||CHR(10)||
 '<td class="r">'||v.num_buckets||'</td>'||CHR(10)||
 '<td>'||v.histogram||'</td>'||CHR(10)||
 '<td class="c">'||v.global_stats||'</td>'||CHR(10)||
 '<td class="c">'||v.user_stats||'</td>'||CHR(10)||
 '</tr>'||CHR(10) line_text
FROM (
SELECT /*+ NO_MERGE LEADING(pt t c) */
 t.table_name,
 t.owner,
 NVL(ic.index_count,0) indexes,
 c.column_id,
 c.column_name,
 c.data_type,
 c.data_default,
 t.num_rows,
 c.num_nulls,
 c.sample_size,
 CASE
 WHEN t.num_rows > c.num_nulls THEN TO_CHAR(LEAST(100, ROUND(c.sample_size * 100 / (t.num_rows - c.num_nulls), 1)), '99999990D0')
 WHEN t.num_rows = c.num_nulls THEN TO_CHAR(100, '99999990D0')
 END sample_size_perc,
 c.num_distinct,
 c.low_value,
 c.high_value high_value,
 TO_CHAR(c.last_analyzed, 'YYYY-MM-DD/HH24:MI:SS') last_analyzed,
 c.avg_col_len,
 LOWER(TO_CHAR(c.density, '0D000000EEEE')) density,
 c.num_buckets,
 c.histogram,
 c.global_stats,
 c.user_stats
FROM plan_tables pt,
 dba_tables t,
 dba_tab_cols c,
 (SELECT i.table_owner,
 i.table_name,
 i.column_name,
 COUNT(*) index_count
 FROM dba_ind_columns i
 GROUP BY
 i.table_owner,
 i.table_name,
 i.column_name ) ic
WHERE pt.object_type = 'TABLE'
 AND pt.object_owner = t.owner
 AND pt.object_name = t.table_name
 AND t.owner = c.owner
 AND t.table_name = c.table_name
 AND ic.table_owner (+) = c.owner
 AND ic.table_name (+) = c.table_name
 AND ic.column_name (+) = c.column_name
ORDER BY
 t.table_name,
 t.owner,
 NVL(ic.index_count,0) DESC,
 c.column_id NULLS LAST,
 c.column_name) v
UNION ALL
SELECT object_name table_name,
 object_owner owner,
 3 line_type,
 1 row_num,
 '<tr>'||CHR(10)||
 '<th>#</th>'||CHR(10)||
 --'<th>Table Name</th>'||CHR(10)||
 --'<th>Owner</th>'||CHR(10)||
 '<th>Indexes</th>'||CHR(10)||
 '<th>Col ID</th>'||CHR(10)||
 '<th>Column Name</th>'||CHR(10)||
 '<th>Data Type</th>'||CHR(10)||
 '<th>Num Rows</th>'||CHR(10)||
 '<th>Num Nulls</th>'||CHR(10)||
 '<th>Sample Size</th>'||CHR(10)||
 '<th>Perc</th>'||CHR(10)||
 '<th>Num Distinct</th>'||CHR(10)||
 '<th>Low Value</th>'||CHR(10)||
 '<th>High Value</th>'||CHR(10)||
 '<th>Last Analyzed</th>'||CHR(10)||
 '<th>Avg Col Len</th>'||CHR(10)||
 '<th>Density</th>'||CHR(10)||
 '<th>Num Buckets</th>'||CHR(10)||
 '<th>Histogram</th>'||CHR(10)||
 '<th>Global Stats</th>'||CHR(10)||
 '<th>User Stats</th>'||CHR(10)||
 '</tr>'||CHR(10) line_text
FROM plan_tables
WHERE object_type = 'TABLE'
UNION ALL
SELECT object_name table_name,
 object_owner owner,
 4 line_type,
 1 row_num,
 CHR(10)||'</table>'||CHR(10)||CHR(10) line_text
FROM plan_tables
WHERE object_type = 'TABLE') v2
ORDER BY
 v2.table_name,
 v2.owner,
 v2.line_type,
 v2.row_num;