10g)과거에 사용된 바인드변수 추적 / SQL이 실행된 옵티마이저 환경

16:25:06  > !cat sqlbind.sql

set lines 150 pages 50 verif off trimspool on
col VAR_NAME form A18
col TYPE_VALUE form A30
col sess_param  form A80

-- 20080506  jailee

accept sql_id      prompt 'Please enter the value for Sql_id : '

 

select *
from (select decode(no,1,'VAR ','exec :')||replace(name,':','') VAR_NAME,decode(no,1,datatype_string, ' := '||''''||value_string||''';') TYPE_VALUE
      from v$sql_bind_capture b
          ,(select 1 no from dual union all select 2 from dual) t
      where sql_id = '&sql_id'
      order by 1,2
     )
union all
select '--DBA_HIST_SQLBIND','' from dual
union all
select *
from (select decode(no,1,'VAR ','exec :')||replace(name,':',''),decode(no,1,datatype_string, ' := '||''''||value_string||''';')
      from DBA_HIST_SQLBIND b
          ,(select 1 no from dual union all select 2 from dual) t
      where  dbid = (select dbid from v$database)
      and sql_id = '&&sql_id'
      and (snap_id , dbid) = (select max(snap_id) , max(b.dbid)
                              from DBA_HIST_SQLBIND, (select dbid from v$database )  b
                              where sql_id =  '&&sql_id')
      order by b.snap_id,1,2
     )
;

select --SQL_ID,
       CHILD_ADDRESS,isdefault,'"'||NAME||'" = '||value||' ;' sess_param
from v$sql_optimizer_env b
where sql_id = '&&sql_id'
--and isdefault = 'NO'
order by isdefault,name
;

 

16:26:10 > @sqlbind
Please enter the value for Sql_id : 55ynxfrzdhzhw

VAR_NAME           TYPE_VALUE
------------------ ------------------------------
VAR B1             CHAR(32)
VAR B2             CHAR(32)
VAR B3             CHAR(32)
VAR B4             CHAR(32)
exec :B1            := '001';
exec :B2            := '2008-05-01';
exec :B3            := '2008-05-02';
exec :B4            := '107983481   ';
--DBA_HIST_SQLBIND
VAR B1             CHAR(32)
VAR B2             CHAR(32)
VAR B3             CHAR(32)
VAR B4             CHAR(32)
exec :B1            := '001';
exec :B2            := '2008-05-01';
exec :B3            := '2008-05-02';
exec :B4            := '107981134   ';

19 rows selected.

Elapsed: 00:00:00.55

CHILD_ADDRESS    ISD SESS_PARAM
---------------- --- --------------------------------------------------------------------------------
C00000076A201F58 NO  "_b_tree_bitmap_plans" = false ;
C00000076A201F58 NO  "_bloom_filter_enabled" = false ;
C00000076A201F58 NO  "_db_file_optimizer_read_count" = 128 ;
C00000076A201F58 NO  "_gby_hash_aggregation_enabled" = false ;
C00000076A201F58 NO  "_index_join_enabled" = false ;
C00000076A201F58 NO  "_optimizer_compute_index_stats" = false ;
C00000076A201F58 NO  "_optimizer_sortmerge_join_enabled" = false ;
C00000076A201F58 NO  "_pga_max_size" = 2097152 KB ;
C00000076A201F58 NO  "optimizer_secure_view_merging" = false ;
C00000076A201F58 NO  "sort_area_size" = 2147483647 ;
C00000076A201F58 YES "active_instance_count" = 3 ;
C00000076A201F58 YES "bitmap_merge_area_size" = 1048576 ;
C00000076A201F58 YES "cpu_count" = 14 ;
C00000076A201F58 YES "cursor_sharing" = exact ;
C00000076A201F58 YES "hash_area_size" = -2 ;
C00000076A201F58 YES "optimizer_dynamic_sampling" = 2 ;
C00000076A201F58 YES "optimizer_features_enable" = 10.2.0.3 ;
C00000076A201F58 YES "optimizer_index_caching" = 0 ;
C00000076A201F58 YES "optimizer_index_cost_adj" = 100 ;
C00000076A201F58 YES "optimizer_mode" = all_rows ;
C00000076A201F58 YES "parallel_ddl_mode" = enabled ;
C00000076A201F58 YES "parallel_dml_mode" = disabled ;
C00000076A201F58 YES "parallel_execution_enabled" = true ;
C00000076A201F58 YES "parallel_query_mode" = enabled ;
C00000076A201F58 YES "parallel_threads_per_cpu" = 2 ;
C00000076A201F58 YES "pga_aggregate_target" = 51200000 KB ;
C00000076A201F58 YES "query_rewrite_enabled" = true ;
C00000076A201F58 YES "query_rewrite_integrity" = enforced ;
C00000076A201F58 YES "skip_unusable_indexes" = true ;
C00000076A201F58 YES "sort_area_retained_size" = 0 ;
C00000076A201F58 YES "star_transformation_enabled" = false ;
C00000076A201F58 YES "statistics_level" = typical ;
C00000076A201F58 YES "workarea_size_policy" = auto ;

[출처] 10g)과거에 사용된 바인드변수 추적 / SQL이 실행된 옵티마이저 환경|작성자 푸름조아

select * from table(dbms_xplan.display_awr('803b7z0t84sp7', NULL, NULL, 'basic rows bytes cost'));