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 ;
select * from table(dbms_xplan.display_awr('803b7z0t84sp7', NULL, NULL, 'basic rows bytes cost'));
'ORACLE > TUNING' 카테고리의 다른 글
| Chapter 9 Tuning Buffer Cache (0) | 2012.05.02 |
|---|---|
| AWR report 생성 및 분석 (0) | 2012.03.08 |
| SQL 튜닝 대상선정(10g)하기 (0) | 2012.03.08 |
| V$SQL BIND CAPTURE (0) | 2012.03.08 |
| SQL 실행 계획 확인방법 (0) | 2012.01.10 |
sql_tuning_script_etc1.txt
