performance tuning tool09-sql profiles

SQL Profiles1. SQL Profiles :

1. SQL Profiles 10g , DBMS_SQLTUNE EM , AST(Automatic SQL Tuning) ; 10g ,11g SPM;

2. Automatic SQL Tuning:1. , sql ( ),

; hints , ,sql , ;

2. Automatic SQL Tuning SQL Profile ;Automatic Tuning Optimizersql ( ) SQL Profile;

cardinality/selectivity/cost , ;3. SQL Profile ,Automatic Tuning Optimizer SQL

, optimizer_mode all_rows first_rows;3. SQL Profile:

1. SQL Profile ( AST), sql ;

2. SQL Profile , / // SQL Profile ;

3. ,SQL Profile , AST SQL Profile;4. tips:SQL Profile Stored Outlines SQL ; SQL

Profile, , ;4. SQL Profile :

1. SQL Profile category , profile;2. dba_sql_profiles (SELECT NAME, category FROM dba_sql_profiles);3. , profiles DEFAULT ,

sqltune_category=DEFAULT( ) profile;4. sqltune_category profile , ;

5. SQL Profile :1. SELECT ;2. UDPATE ;3. INSERT INTO SELECT ;4. DELETE ;5. CTAS ;6. MERGE ( UPDATE INSERT );

6. Sql Profiles OUTLINES , :1. SQL Profiles , ;2. SQL Profiles SQL ;

7. SQL Profiles :1. " "SQL ;2. SQL , SQL ;

2. SQL Profiles:1. CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, ALTER ANY SQL_PROFILE ;2. STA Profile:DBMS_SQLTUNE.ACCEPT_SQL_PROFILE

(task_name,object_id,name,description,category,task_owner,replace,force_match,profile_type), << SQL Tuning Advisor SQL Profile, SQL Profile >>;

3. Profile:DBMS_SQLTUNE.ALTER_SQL_PROFILE (name,attribute_name,value);4. Profile:DBMS_SQLTUNE.DROP_SQL_PROFILE (name,ignore); 5. SQL Profile , <<How to Move SQL Profiles from One Database to Another>>;6. DBMS_SQLTUNE.IMPORT_SQL_PROFILE SQL Profiles, SQL Profiles

Outlines , SQL , ;1. SQL ;2. SQL SQL , SQL hints;

---------------------- SQL Tuning Advisor SQL Profile, SQL Profile ------------------------ 1. ;SQL> SELECT * FROM v$version;

BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production

-- 2. ;conn / as sysdbaCREATE TABLE t1 AS SELECT object_id, object_name FROM dba_objects WHERE rownum <= 50000;CREATE TABLE t2 AS SELECT * FROM dba_objects;CREATE INDEX idx_t2_oid ON t2(object_id);

BEGIN dbms_stats.gather_table_stats(USER, 'T1', cascade => TRUE, method_opt => 'for all columns size 1'); dbms_stats.gather_table_stats(USER, 'T2', cascade => TRUE, method_opt => 'for all columns size 1');END;/

-- 3. SQL , ;1. SQL ; 1. : sql , T1 like , ; T2;

2. ID=2 ,CBO T1 LIKE '%T1%' 2500 , T1 5%( LIKE5%); INDEX RANGE SCAN + NESTED LOOPS ,CBO FULL TABLE

SCAN + HASH JOIN;

SQL> SET AUTOT ON;SQL> SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1%' AND t1.object_id = t2.object_id;

......26 rows selected.

Execution Plan----------------------------------------------------------Plan hash value: 1838229974

---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2500 | 97K| 212 (3)| 00:00:03 ||* 1 | HASH JOIN | | 2500 | 97K| 212 (3)| 00:00:03 ||* 2 | TABLE ACCESS FULL| T1 | 2500 | 72500 | 55 (4)| 00:00:01 || 3 | TABLE ACCESS FULL| T2 | 50350 | 540K| 155 (2)| 00:00:02 |---------------------------------------------------------------------------

Predicate Information (identified by operation id):---------------------------------------------------

1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')

Statistics---------------------------------------------------------- 304 recursive calls 0 db block gets 974 consistent gets 0 physical reads 0 redo size 1402 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 26 rows processed

2. hints sql ; 1. :CBO 5061, 212; 2. : hint 288, 974; 3. ORACLE T1 , NEST LOOP ,;

4.tips: EXPLAIN PLAN FOR SELECT * FROM TABLE(DBMS_XPLAN.display());

SQL> SET AUTO ON;SQL> SELECT /*+ use_nl(t1 t2) index(t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1%' AND t1.object_id = t2.object_id;

26 rows selected.


------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2500 | 97K| 5061 (1)|00:01:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)|00:00:01 |

| 2 | NESTED LOOPS | | 2500 | 97K| 5061 (1)|00:01:01 |

|* 3 | TABLE ACCESS FULL | T1 | 2500 | 72500 | 55 (4)|00:00:01 |

|* 4 | INDEX RANGE SCAN | IDX_T2_OID | 1 | | 1 (0)|00:00:01 |

------------------------------------------------------------------------------------------


3 - filter("T1"."OBJECT_NAME" LIKE '%T1%') 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")


-- 4. STA sql;1. sql sql_id 'cy6x06rhrt54x';

v$sql dba_hist_sqltext sql_id;2. sqltrpt.sql ;SQL> @?/rdbms/admin/sqltrpt.sqlTune the sql~~~~~~~~~~~~

GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name : TASK_62Tuning Task Owner : SYSScope : COMPREHENSIVETime Limit(seconds) : 1800Completion Status : COMPLETEDStarted at : 07/26/2014 10:45:03Completed at : 07/26/2014 10:45:03Number of SQL Profile Findings : 1

-------------------------------------------------------------------------------Schema Name: SYS

SQL ID : cy6x06rhrt54xSQL Text : SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1%' AND t1.object_id = t2.object_id

-------------------------------------------------------------------------------FINDINGS SECTION (1 finding)-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)-------------------------------------------------------- A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 49.32%)

------------------------------------------ - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_62', replace => TRUE);

-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------

1- Original With Adjusted Cost------------------------------Plan hash value: 1838229974

---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 26 | 1040 | 212 (3)| 00:00:03 ||* 1 | HASH JOIN | | 26 | 1040 | 212 (3)| 00:00:03 ||* 2 | TABLE ACCESS FULL| T1 | 26 | 754 | 55 (4)| 00:00:01 || 3 | TABLE ACCESS FULL| T2 | 50350 | 540K| 155 (2)| 00:00:02 |---------------------------------------------------------------------------


1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')

2- Using SQL Profile--------------------Plan hash value: 1164754026

------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 26 | 1040 | 107 (2)|

00:00:02 || 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)|00:00:01 || 2 | NESTED LOOPS | | 26 | 1040 | 107 (2)|00:00:02 ||* 3 | TABLE ACCESS FULL | T1 | 26 | 754 | 55 (4)|00:00:01 ||* 4 | INDEX RANGE SCAN | IDX_T2_OID | 1 | | 1 (0)|00:00:01 |------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("T1"."OBJECT_NAME" LIKE '%T1%') 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

3. STA ,T1 26, , SQL Profile;SQL> EXECUTE dbms_sqltune.accept_sql_profile(task_name => 'TASK_62', REPLACE=> TRUE);

PL/SQL procedure successfully completed.

-- 5. SQL Profile ;1. SQL Profile ;SQL> SELECT NAME, CATEGORY, signature, sql_text FROM dba_sql_profiles;

NAME CATEGORY SIGNATURE------------------------------ ------------------------------ ----------SQL_TEXT--------------------------------------------------------------------------------SYS_SQLPROF_0151dc24f6338000 DEFAULT 8.9755E+18SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1%' AND t1.object_id = t2.object_id

2. sys.sqlprof$attr (11g SQLOBJ$,SQLOBJ$AUXDATA,SQLOBJ$DATA ) , signature attr_val;

1. 10g v$sql exact_matching_signature,force_matching_signature , sql, sql ;

2. dbms_sqltune.accept_sql_profile , force_match=>true dba_sql_profilesignature sql force_matching_signature , exact_matching_signature; 3. SQL Profile outlines , SQL; 4.attr_val hints, outline , SQL Profile hints sql

; , , / ; 5. 0.0104*2500=26, CBO,T1 0.0104 ; 6. SQL Profile SQL , , CBO ;

SQL> SELECT * FROM sys.sqlprof$attr;

SIGNATURE CATEGORY ATTR# ATTR_VAL----------- -------- ---------- --------------------------------------------------------------------------------8.9755E+18 DEFAULT 1 OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0104)

3. ? sql:SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T2%' AND t1.object_id = t2.object_id;1. 10.2.0.1 : SQL Profile ;2. SQL Profile, T1 LIKE '%T1%' , SQL Profile

;

4. , ; 1. T1 500W, Oracle 500W*5%*0.0104=2600 ; FULL TABLE SCAN + HASH JOIN; 2. SQL Profile , ;

SQL> EXEC dbms_stats.set_table_stats('SYS', 'T1', numrows=>5000000);

SQL> EXPLAIN PLAN FOR SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1%' AND t1.object_id = t2.object_id;

Explained.

SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 1838229974

---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2600 | 101K| 401 (49)| 00:00:05 ||* 1 | HASH JOIN | | 2600 | 101K| 401 (49)| 00:00:05 ||* 2 | TABLE ACCESS FULL| T1 | 2600 | 75400 | 245 (79)| 00:00:03 || 3 | TABLE ACCESS FULL| T2 | 50350 | 540K| 155 (2)| 00:00:02 |---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------


Note----- - SQL profile "SYS_SQLPROF_0151dc24f6338000" used for this statement---------------------- SQL Tuning Advisor SQL Profile, SQL Profile ----------------------

---------------------- How to Move SQL Profiles from One Database to Another ------------------------ 1. ;1. ;SQL> SELECT * FROM v$version;


2. ;SQL> SELECT * FROM v$version;

BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production

-- 2. , , ;SQL> conn / as sysdbaSQL> ALTER USER hr IDENTIFIED BY oracle ACCOUNT UNLOCK;SQL> conn hr/oracleSQL> CREATE TABLE emp AS SELECT * FROM employees;SQL> ALTER TABLE emp ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (EMPLOYEE_ID) USING INDEX;SQL> SET AUTOT ONSQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;

EMPLOYEE_ID FIRST_NAME----------- -------------------- 198 Donald


--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | 100 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| EMP | 4 | 100 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------


1 - filter("EMPLOYEE_ID"=198)


-- 3. STA SQL , SQL Profile;DECLARE my_sta_name VARCHAR2(30); my_sqltext CLOB;BEGIN my_sqltext := 'SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198'; my_sta_name := dbms_sqltune.create_tuning_task(sql_text => my_sqltext, user_name => 'HR', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'my_sql_tuning_task'); dbms_sqltune.execute_tuning_task(task_name => my_sta_name); dbms_sqltune.accept_sql_profile(task_name => my_sta_name, NAME => 'my_sql_profile');END;/

-- 4. ;1. profile ;SELECT * FROM dba_sql_profiles WHERE name = 'my_sql_profile';2. sql ;SQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;



-----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 9 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP_ID | 1 | | 0 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------


2 - access("EMPLOYEE_ID"=198)

Note----- - SQL profile "my_sql_profile" used for this statement


-- 5. SQL Profiles ;1. SQL Profiles( schema );BEGIN dbms_sqltune.create_stgtab_sqlprof(table_name => 'TB_SQLP_STAGE', schema_name => 'HR'); dbms_sqltune.pack_stgtab_sqlprof(profile_name => 'my_sql_profile', staging_table_name => 'TB_SQLP_STAGE', staging_schema_owner => 'HR');END;/2. stage table;[oracle@singleton10g ~]$ expdp hr/oracle directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE;

Export: Release 10.2.0.1.0 - Production on Friday, 25 July, 2014 11:49:29

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsStarting "HR"."SYS_EXPORT_TABLE_01": hr/******** directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGEEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 320 KBProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "HR"."TB_SQLP_STAGE" 9.257 KB 1 rowsMaster table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for HR.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/admin/ORCL/dpdump/sqlp.dmpJob "HR"."SYS_EXPORT_TABLE_01" successfully completed at 11:49:333. dump ;

-- 6. SQL Profiles;1. stage table;[oracle@singleton11g ~]$ impdp hr/oracle directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE

Import: Release 11.2.0.3.0 - Production on Fri Jul 25 11:52:37 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "HR"."SYS_IMPORT_TABLE_01" successfully loaded/unloadedStarting "HR"."SYS_IMPORT_TABLE_01": hr/******** directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGEProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "HR"."TB_SQLP_STAGE" 9.257 KB 1 rowsJob "HR"."SYS_IMPORT_TABLE_01" successfully completed at 11:53:052. SQL Profiles( SQL Profile , dba_sql_profiles );BEGIN dbms_sqltune.unpack_stgtab_sqlprof(profile_name => 'my_sql_profile', REPLACE => TRUE, staging_table_name => 'TB_SQLP_STAGE', staging_schema_owner => 'HR');END;/

-- 7. ;SQL> SELECT /*+ NO_INDEX(e pk_emp_id) */ employee_id, first_name FROM emp e WHERE employee_id=198;



-----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 9 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP_ID | 1 | | 0 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------


2 - access("EMPLOYEE_ID"=198)

Note----- - SQL profile "my_sql_profile" used for this statement

Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 497 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed---------------------- How to Move SQL Profiles from One Database to Another ----------------------

---------------------- DBMS_SQLTUNE.IMPORT_SQL_PROFILE SQL Profiles ------------------------ 1. ;SQL> SELECT * FROM v$version;


-- 2. ;1. : , ;SQL> desc dbms_sqltunePROCEDURE IMPORT_SQL_PROFILEArgument Name Type In/Out Default?------------------------------ ----------------------- ------ --------SQL_TEXT CLOB INPROFILE SQLPROF_ATTR INNAME VARCHAR2 IN DEFAULTDESCRIPTION VARCHAR2 IN DEFAULTCATEGORY VARCHAR2 IN DEFAULTVALIDATE BOOLEAN IN DEFAULTREPLACE BOOLEAN IN DEFAULTFORCE_MATCH BOOLEAN IN DEFAULT

2.SYS.SQLPROF_ATTR VARCHAR2 ;SELECT * FROM dba_source WHERE owner = 'SYS' AND NAME = 'SQLPROF_ATTR';TYPE sqlprof_attr AS VARRAY(2000) of VARCHAR2(500)

-- 3. ;BEGIN dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0151dc24f6338000');END;/BEGIN dbms_stats.set_table_stats('SYS', 'T1', numrows => 5000);END;/

-- 4. SQL ;1. sql ;SQL>SELECT sql_id, plan_hash_value, sql_text FROM v$sql;

SQL_ID PLAN_HASH_VALUE------------- ---------------SQL_TEXT--------------------------------------------------------------------------------cy6x06rhrt54x 1838229974SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1%' AND t1.object_id = t2.object_id

2.10g ,v$sql_plan SQL outlines , hints;SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('cy6x06rhrt54x',NULL,'outline'));

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID cy6x06rhrt54x, child number 0-------------------------------------SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1%' ANDt1.object_id = t2.object_id

Plan hash value: 1838229974

---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 212 (100)| |

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------|* 1 | HASH JOIN | | 2500 | 97K| 212 (3)| 00:00:03 ||* 2 | TABLE ACCESS FULL| T1 | 2500 | 72500 | 55 (4)| 00:00:01 || 3 | TABLE ACCESS FULL| T2 | 50350 | 540K| 155 (2)| 00:00:02 |---------------------------------------------------------------------------

Outline Data-------------

/*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") FULL(@"SEL$1" "T2"@"SEL$1") LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1") USE_HASH(@"SEL$1" "T2"@"SEL$1") END_OUTLINE_DATA */

Predicate Information (identified by operation id):


3.SQLT coe_xfr_sql_profile.sql : shared pool,awr SQL ID Outline Data SQL Profile; 1. sql sql_id plan_hash_value,coe_xfr_sql_profile_${sql_id}_${plan_hash_value}.sql ; 2. force_match FALSE, TRUE, ;

SQL>@/home/oracle/coe_xfr_sql_profile.sql cy6x06rhrt54x 1838229974

Parameter 1:SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS--------------- ----------- 1838229974 .087 1164754026

Parameter 2:PLAN_HASH_VALUE (required)

Values passed to coe_xfr_sql_profile:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SQL_ID : "cy6x06rhrt54x"PLAN_HASH_VALUE: "1838229974"

SQL>BEGIN 2 IF :sql_text IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).'); 4 END IF; 5 END; 6 /SQL>SET TERM OFF;SQL>BEGIN 2 IF :other_xml IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).'); 4 END IF; 5 END; 6 /SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_cy6x06rhrt54x_1838229974.sqlon TARGET system in order to create a custom SQL Profilewith plan 1838229974 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL>@coe_xfr_sql_profile_cy6x06rhrt54x_1838229974.sqlSQL>DECLARE 2 sql_txt CLOB; 3 h SYS.SQLPROF_ATTR; 4 PROCEDURE wa (p_line IN VARCHAR2) IS 5 BEGIN 6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line); 7 END wa; 8 BEGIN 9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);11 -- SQL Text pieces below do not have to be of same length.12 -- So if you edit SQL Text (i.e. removing temporary Hints),13 -- there is no need to edit or re-align unmodified pieces.14 wa(q'[SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1]');15 wa(q'[%' AND t1.object_id = t2.object_id16 ]');17 DBMS_LOB.CLOSE(sql_txt);18 h := SYS.SQLPROF_ATTR(19 q'[BEGIN_OUTLINE_DATA]',20 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',21 q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.1')]',22 q'[ALL_ROWS]',23 q'[OUTLINE_LEAF(@"SEL$1")]',24 q'[FULL(@"SEL$1" "T1"@"SEL$1")]',25 q'[FULL(@"SEL$1" "T2"@"SEL$1")]',26 q'[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")]',27 q'[USE_HASH(@"SEL$1" "T2"@"SEL$1")]',28 q'[END_OUTLINE_DATA]');29 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);30 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);31 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (32 sql_text => sql_txt,33 profile => h,34 name => 'coe_cy6x06rhrt54x_1838229974',35 description => 'coe cy6x06rhrt54x 1838229974 '||:signature||' '||:signaturef||'',36 category => 'DEFAULT',37 validate => TRUE,38 replace => TRUE,39 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );40 DBMS_LOB.FREETEMPORARY(sql_txt);41 END;42 /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUESQL>SET ECHO OFF;

SIGNATURE--------------------- 8975541025552400288

SIGNATUREF--------------------- 3960696072677096522

... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_cy6x06rhrt54x_1838229974 completed

-- 5. SQL SQL , SQL hints;1. sql , sql hints sql ; 1. : , , ; 2. outline: ; 3. SQL Profiles: OUTLINE ;2. ; 1. SQL , sql_id; 2. SQL , SQL( , sql , schema,

); 3. SQL, Outline Data; 4. SQL SQL Outline Data SQL Profile;3. SQL; 1.FULL TABLE SCAN + HASH JOIN :SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1%' AND t1.object_id = t2.object_id; 2.INDEX RANGE SCAN + NESTED LOOP :SELECT /*+ sql_profile index(t1) use_nl(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1%' AND t1.object_id = t2.object_id; 3. sql_id plan_hash_value;SQL>SELECT sql_id, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE '%sql_profile%' AND sql_id IN ('8cukn1u7dxssh','6csfwp8k7qu49') 2 ;

SQL_ID PLAN_HASH_VALUE------------- ---------------SQL_TEXT--------------------------------------------------------------------------------8cukn1u7dxssh 1838229974SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROMt1, t2 WHERE t1.object_name LIKE '%T1%' AND t1.object_id = t2.object_id

6csfwp8k7qu49 1164754026SELECT /*+ sql_profile index(t1) use_nl(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1%' AND t1.object_id = t2.object_id

4. coe_xfr_sql_profile.sql sql sql_profile ;SQL>@/home/oracle/coe_xfr_sql_profile.sql 8cukn1u7dxssh 1838229974 -- coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sqlSQL>@/home/oracle/coe_xfr_sql_profile.sql 6csfwp8k7qu49 1164754026 -- coe_xfr_sql_profile_6csfwp8k7qu49_1164754026.sql

5. coe_xfr_sql_profile_6csfwp8k7qu49_1164754026.sql SYS.SQLPROF_ATTR force_match => TRUE coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql ;h := SYS.SQLPROF_ATTR(q'[BEGIN_OUTLINE_DATA]',q'[IGNORE_OPTIM_EMBEDDED_HINTS]',q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.1')]',q'[ALL_ROWS]',q'[OUTLINE_LEAF(@"SEL$1")]',q'[FULL(@"SEL$1" "T1"@"SEL$1")]',q'[INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))]',q'[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")]',q'[USE_NL(@"SEL$1" "T2"@"SEL$1")]',q'[END_OUTLINE_DATA]');

6. , ;SQL>@/home/oracle/coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql

SQL>EXPLAIN PLAN FOR SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T1%' AND t1.object_id = t2.object_id;

Explained.

SQL>SELECT * FROM TABLE(dbms_xplan.display());


------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 250 | 10000 | 554 (1)|00:00:07 |


| 2 | NESTED LOOPS | | 250 | 10000 | 554 (1)|00:00:07 |


PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------


------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- 3 - filter("T1"."OBJECT_NAME" LIKE '%T1%') 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note----- - SQL profile "coe_8cukn1u7dxssh_1838229974" used for this statement

-- 7. , , SQL ;SQL>EXPLAIN PLAN FOR SELECT /*+ sql_profile full(t1) full(t2) use_hash(t1 t2) */ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE '%T2%' AND t1.object_id = t2.object_id;

Explained.

SQL>SELECT * FROM TABLE(dbms_xplan.display());


------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 250 | 10000 | 554 (1)|00:00:07 |


| 2 | NESTED LOOPS | | 250 | 10000 | 554 (1)|00:00:07 |


PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------


------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- 3 - filter("T1"."OBJECT_NAME" LIKE '%T2%') 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note----- - SQL profile "coe_8cukn1u7dxssh_1838229974" used for this statement

---------------------- DBMS_SQLTUNE.IMPORT_SQL_PROFILE SQL Profiles ----------------------

mailto:@%3F/rdbms/admin/sqltrpt.sql

mailto:SQL%26gt;@/home/oracle/coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql

performance tuning tool09-sql profiles

Technology