SQLT Diagnostic Tool1.SQLT :SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise - ST CoE. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and similar elements that influence the performance of the SQL being analyzed.2. :SQLT , ; 1.SQLTXPLAIN : SQLT repository,SQLT SQLT SQLTXPLAIN

;SQLTXPLAIN CREATE SESSION,CREATE TABLE ; 2.SQLTXADMIN : SQLT PL/SQL ,SQLTXADMIN

;SQLTXADMIN ADMINISTER SQL MANAGEMENT OBJECT,ADMINISTER SQL TUNING SET,ADVISOR,ALTER SESSION,ANALYZE ANY,SELECT ANY DICTIONARY,SELECT_CATALOG_ROLE ; 3.SQLT_USER_ROLE : SQLT SQLT ;SQLT_USER_ROLE

ADVISOR,SELECT_CATALOG_ROLE ; 4.TIPS:12c SYS SQLT , PL/SQL , SQLTADMIN(GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN);3. : 1. ;( sqlt_instructions.html ) 2. :conn / as sysdba; SQL> @/tools/sqlt/install/sqcreate.sql; : 1. ( , PDB ): ; 2.SQLTXPLAIN ; 3.SQLTXPLAIN : 50MB ; 4.SQLTXPLAIN ; 5. ( ): SQL (EBS APPS,Siebel SIEBEL,PeopleSoftSYSADM), , SQLT_USER_ROLE ; 6. Oracle Pack(T,D,N):T Oracle Tuning,D Oracle Diagnostic, N None; TD,SQLT ; T, N,SQLT ;

3. :conn / as sysdba; SQL> @/path/sqlt/install/sqdrop.sql; 4. : , , ; 5.tips: 1. , :EXEC sqltxadmin.sqlt$a.set_sess_param('connect_identifier', '@network_name'); 2. , CREATE USER sqlt;GRANT sqlt_user_role TO sqlt;4. : 1.XTRACT : 1. SQL SQL_ID HASH_VALUE, , XECUTE; AWR reportSQL_ID, SQL trace HASH_VALUE( SQL , "hv=" ); 2. SQL STATISTICS_LEVEL ALL, (

); SQL CBO :/*+ GATHER_PLAN_STATISTICS */; 11g ,SQL CBO :/*+ GATHER_PLAN_STATISTICS MONITOR */;

3. :SQL> @/path/sqlt/run/sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]; 2.XECUTE : 1. XTRACT , ; XECUTE , SQL,

; 2. SQL , ; , SQL

1 , , XTRACT; 3. XECUTE , SQL ; SQL ,

; sqlt/input/sample/script1.sql ; SQL /* ^^unique_id */; 4. :SQL> @/path/sqlt/run/sqltxecute.sql /path/scriptname [sqltxplain_password]; 3.XTRXEC : 1. XTRACT XECUTE ; ,XTRXEC ; SQL

,XTRACT SQL ; ,XTRXEC XECUTE;

2.SQLT , XTRACT ;;

3. XTRXEC (XTRACT) , (XECUTE); ;

4. :SQL> @/path/sqlt/run/sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]; 4.XTRSBY : 1. DataGuard SQL, ; SQL SQL_IDHASH_VALUE; 2. database link, , DBA

; 3. SQL STATISTICS_LEVEL ALL,( ); SQL CBO :/*+ GATHER_PLAN_STATISTICS */; 11g , SQL CBO :/*+ GATHER_PLAN_STATISTICS MONITOR */; 4. :SQL> @/path/sqlt/run/sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] [DB_LINK]; 5.XPLAIN : 1. EXPLAIN PLAN FOR , SQL ; XTRACTXECUTE ;

2. XPLAIN , SQL ; SQL , : SQL" ", ; sqlt/input/sample/sql1.sql ;

3. :SQL> @/path/sqlt/run/sqltxplain.sql /path/scriptname [sqltxplain_password]; 6.XPREXT : 1. XTRACT SQLT , ;sqlt/run/sqltcommon11.sql ; 2. :SQL> @/path/sqlt/run/sqltxprext.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]; 7.XPREXC : 1. XECUTE SQLT , ;sqlt/run/sqltcommon11.sql ; 2. :SQL> @/path/sqlt/run/sqltxprexc.sql /path/scriptname [sqltxplain_password]; 8.TIPS: 1.XTRACT,XECUTE,XTRXEC,XTRSBY,XPREXT XPREXC bind peeking( ),XPLAIN ; XPLAIN EXPLAIN PLAN FOR , bind peeking; ,

XPLAIN; 2. XPLAIN bind peeking , 7 ,

SQL ; SQL AWR , XTRACT XTRXEC, XECUTE; 3. DataGuard , XTRSBY; 4. , XPLAIN; 5.XPREXT XPREXC XTRACT XECUTE, SQLT SQLT ;5.PROFILE : 1. SQL , 10g PROFILE ; 2. , AWR ; ,, SQL Profile " " ; , SQL

; 3. 11g SQL Plan Management(SPM) ;6. html ;7. :SQLT Diagnostic Tool (Doc ID 215187.1),SQLT (Doc ID 1677588.1(CHN),Doc ID 1614107.1(EN));