AWR 딕셔너리 VIEW을 이용한 SQL Tuning 정보 추출

위의 뷰를 사용하기 위해서는 SQL_ID를 알아야 합니다.

SQL_ID를 토대로 여러 정보를 딕셔너리뷰에서 조회할 수 있습니다.

 

1.해당 SQL의 바인드변수 찾기(DBA_HIST_SQLBIND)

select sql_id,name,value_string from DBA_HIST_SQLBIND
where sql_id='9yc6t10u847kx';


------------------------------------
9yc6t10u847kx	:1	
9yc6t10u847kx	:2	
9yc6t10u847kx	:3	
9yc6t10u847kx	:4	
9yc6t10u847kx	:5	000000000000
9yc6t10u847kx	:6	999999999999
9yc6t10u847kx	:7	750
9yc6t10u847kx	:8	774
9yc6t10u847kx	:9	20220522
9yc6t10u847kx	:10	20220522
9yc6t10u847kx	:11	20220522
9yc6t10u847kx	:12	20220522
9yc6t10u847kx	:13	20220522

 

2.해당 SQL의 PLAN 찾기(DBA_HIST_SQL_PLAN)

select sql_id,lpad(' ',depth*4,'  ')||' '||operation|| ' ' ||options|| ' '
 ||object_name|| ' (' ||cost||')'plan
   from DBA_HIST_SQL_PLAN 
where sql_id = ('9yc6t10u847kx') 
order by timestamp,sql_id,plan_hash_value, id;

--------------------------------------------------------------------------------
9yc6t10u847kx	 SELECT STATEMENT   (8016185)
9yc6t10u847kx	     COUNT STOPKEY  ()
9yc6t10u847kx	         NESTED LOOPS   (6)
9yc6t10u847kx	             NESTED LOOPS   (6)
9yc6t10u847kx	                 TABLE ACCESS BY INDEX ROWID BATCHED OBJ_MAIN (3)
9yc6t10u847kx	                     INDEX RANGE SCAN XIE3OBJ_MAIN (2)
9yc6t10u847kx	                 INDEX RANGE SCAN XPKOBJ_DETAIL (2)
9yc6t10u847kx	             TABLE ACCESS BY INDEX ROWID OBJ_DETAIL (3)
9yc6t10u847kx	     FILTER   ()
9yc6t10u847kx	         NESTED LOOPS OUTER  (8016185)
9yc6t10u847kx	             VIEW   (8016181)
9yc6t10u847kx	                 HASH GROUP BY  (8016181)
9yc6t10u847kx	                     FILTER   ()
9yc6t10u847kx	                         HASH JOIN OUTER  (8016174)
9yc6t10u847kx	                             HASH JOIN   (6048420)
9yc6t10u847kx	                                 TABLE ACCESS FULL TB_CTB2002 (4656370)
9yc6t10u847kx	                                 TABLE ACCESS FULL TB_CTB2001 (1392050)
9yc6t10u847kx	                             TABLE ACCESS FULL TB_UDB3111 (1967753)
9yc6t10u847kx	             TABLE ACCESS BY INDEX ROWID BATCHED TB_UDB3111 (4)
9yc6t10u847kx	                 INDEX RANGE SCAN IX_UDB3111_02 (3)