通过这个SQL得到问题SQL的SQLID:
select sql_id,program,username,last_call_et from v$session where type !='BACKGROUND' and status='ACTIVE';
查看表的每个列的数据分布情况：
select column_name,t.num_rows,c.NUM_NULLS,c.num_distinct
from dba_tables t,dba_tab_columns c
where t.owner=c.owner and t.table_name=c.table_name and t.table_name='SIE_BOE_HEADER'
order by num_distinct;
找到执行计划的中哪一步是瓶颈：
select count(*),sql_plan_line_id from v$active_session_history
where sql_id='6pkd06hdx99xk'
group by sql_plan_line_id
order by 2;
select * from table(dbms_xplan.DISPLAY_CURSOR('6pkd06hdx99xk',null,'ALLSTATS'));
select * from table(dbms_xplan.display_awr('6pkd06hdx99xk'));
select * from table(dbms_xplan.display_cursor('6pkd06hdx99xk',null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
SELECT t.VALUE_STRING FROM v$sql_bind_capture t WHERE sql_id = '6pkd06hdx99xk';
SELECT snap_id,NAME,position,value_string,last_captured,WAS_CAPTURED
FROM dba_hist_sqlbind
WHERE sql_id = '6pkd06hdx99xk';
select count(*),event,count(distinct session_id)
from v$active_session_history
where sql_id='cxsajwa1td6vc'
group by event;
select sql_id,sql_plan_line_id,sql_plan_hash_value,count(*)
from v$active_session_history
where sql_id='6pkd06hdx99xk'
group by sql_id,sql_plan_line_id,sql_plan_hash_value;
SELECT * FROM
(SELECT PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10;
SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM<=5;
col hevent format a40
col wevent format a40
select s.sid blocker,s.event hevent,w.event wevent,w.sid blocked
from v$session s,v$session w
where w.blocking_session = s.sid
and w.blocking_session_status='VALID';
SELECT a.VALUE + b.VALUE logical_reads,c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),4) hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c
WHERE a.NAME='db block gets' AND b.NAME='consistent gets' AND c.NAME='physical reads';
set pages 80
set lines 120
col event for a40
select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait
where event not like 'SQL%' and event not like 'rdbms%';
