玉面飞龙的BLOG

set verify off echo off feed off
set linesize 300
set pagesize 3000
col hv head ‘hv’ noprint
col “cn” for 90 print
col “card” for 999,999,990
col “rows” for 999,999,990
col “ELAPSED” for 999,999.999
col “CPU” for 999,999.999
col “LOGICAL_READS” for 999,999,990
col “CR_GETS” for 999,999,990
col “CU_GETS” for 999,999,990
col “GETS” for 999,999,990
col “READS” for 999,999,990
col operation   format a30
col id format a4
col cost for 999,999,999

break on hv skip 0 on “cn” skip 0

select p.hash_value hv  ,
p.child_number “cn” ,
to_char(p.id,’990′)||decode(access_predicates,null,null,’A') || decode(filter_predicates,null,null,’F') id,
p.cost “cost”, p.cardinality “card”,
lpad(’ ‘,depth)||p.operation||’ ‘||p.options||’ ‘||
p.object_name||decode(p.partition_start,null,’ ‘,’:')||translate(p.partition_start,’(NUMBER’,'(NR’)||
decode(p.partition_stop,null,’ ‘,’-')||translate(p.partition_stop,’(NUMBE’,'(NR’) “operation”
,p.position “pos”,
(select s.last_output_rows  from v$sql_plan_statistics s where
s.address=p.address and s.hashvalue=p.hash_value and s.child_number=p.child_number
and s.operation_id=p.id) “ROWS”,
(select round(s.last_elapsed_time/1000000,2)  from v$sql_plan_statistics s where
s.address=p.address and s.hashvalue=p.hash_value and s.child_number=p.child_number
and s.operation_id=p.id) “ELAPSED”,
(select s.last_cu_buffer_gets+s.last_cr_buffer_gets  from v$sql_plan_statistics s where
s.address=p.address and s.hashvalue=p.hash_value and s.child_number=p.child_number
and s.operation_id=p.id) “LOGICAL_READS”
from v$sql_plan p
where p.hashvalue=**************************
order by p.child_number,p.id;

tuning by cardinality feedback，真是不错。当CBO无法根据统计信息选择我们理想的执行计划的时候，根据cardinality feedback和v$sql_plan_statistics里面的真实statistics找到“修理”统计信息的入口。
Very good,这个方法似曾相识，实际上在日常tuning中时常使用.

http://www.centrexcc.com/papers.html