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<br>ID</th>'||CHR(10)||
'<th>Column Name</th>'||CHR(10)||
'<th>Data<br>Type</th>'||CHR(10)||
'<th>Num<br>Rows</th>'||CHR(10)||
'<th>Num<br>Nulls</th>'||CHR(10)||
'<th>Sample<br>Size</th>'||CHR(10)||
'<th>Perc</th>'||CHR(10)||
'<th>Num<br>Distinct</th>'||CHR(10)||
'<th>Low Value</th>'||CHR(10)||
'<th>High Value</th>'||CHR(10)||
'<th>Last Analyzed</th>'||CHR(10)||
'<th>Avg<br>Col<br>Len</th>'||CHR(10)||
'<th>Density</th>'||CHR(10)||
'<th>Num<br>Buckets</th>'||CHR(10)||
'<th>Histogram</th>'||CHR(10)||
'<th>Global<br>Stats</th>'||CHR(10)||
'<th>User<br>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<br>ID</th>'||CHR(10)||
'<th>Column Name</th>'||CHR(10)||
'<th>Data<br>Type</th>'||CHR(10)||
'<th>Num<br>Rows</th>'||CHR(10)||
'<th>Num<br>Nulls</th>'||CHR(10)||
'<th>Sample<br>Size</th>'||CHR(10)||
'<th>Perc</th>'||CHR(10)||
'<th>Num<br>Distinct</th>'||CHR(10)||
'<th>Low Value</th>'||CHR(10)||
'<th>High Value</th>'||CHR(10)||
'<th>Last Analyzed</th>'||CHR(10)||
'<th>Avg<br>Col<br>Len</th>'||CHR(10)||
'<th>Density</th>'||CHR(10)||
'<th>Num<br>Buckets</th>'||CHR(10)||
'<th>Histogram</th>'||CHR(10)||
'<th>Global<br>Stats</th>'||CHR(10)||
'<th>User<br>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;
