Re: Influencing execution plan via SQL Profiles

  • From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
  • To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Sat, 19 Nov 2016 09:24:57 +0100

Here's below an example with the corresponding SQL script attached


SQL> select /*+ full(t1) */ count(1) from t1 where owner ='C##MHOURI';


COUNT(1)

----------

45155


SQL> start xpsimp


SQL_ID 70qkj0rf0m9s0, child number 0

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

select /*+ full(t1) */ count(1) from t1 where owner ='C##MHOURI'


Plan hash value: 3724264953

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

| Id | Operation | Name | Rows | Bytes |

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

| 0 | SELECT STATEMENT | | | |

| 1 | SORT AGGREGATE | | 1 | 6 |

|* 2 | TABLE ACCESS FULL| T1 | 192K| 1126K|

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

Predicate Information (identified by operation id):

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

2 - filter("OWNER"='C##MHOURI')


SQL> select /*+ index(t1) */ count(1) from t1 where owner ='C##MHOURI';


COUNT(1)

----------

45155


SQL> start xpsimp


SQL_ID 6argzx87r89rv, child number 0

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

select /*+ index(t1) */ count(1) from t1 where owner ='C##MHOURI'


Plan hash value: 383751810

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

| Id | Operation | Name | Rows | Bytes |

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

| 0 | SELECT STATEMENT | | | |

| 1 | SORT AGGREGATE | | 1 | 6 |

|* 2 | INDEX RANGE SCAN| T1_IDX | 192K| 1126K|

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='C##MHOURI')


SQL> @FixProfileFromXmlFromCache

Enter value for sql_id_from: 6argzx87r89rv

Enter value for child_no_from: 0

Enter value for sql_id_to: 70qkj0rf0m9s0

Enter value for sql_id_to: 70qkj0rf0m9s0

Enter value for sql_id_to: 70qkj0rf0m9s0


PL/SQL procedure successfully completed.


SQL> select /*+ full(t1) */ count(1) from t1 where owner ='C##MHOURI';


COUNT(1)

----------

45155


SQL> start xpsimp

SQL_ID 70qkj0rf0m9s0, child number 0

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

select /*+ full(t1) */ count(1) from t1 where owner ='C##MHOURI'

Plan hash value: 383751810

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

| Id | Operation | Name | Rows | Bytes |

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

| 0 | SELECT STATEMENT | | | |

| 1 | SORT AGGREGATE | | 1 | 6 |

|* 2 | INDEX RANGE SCAN| T1_IDX | 192K| 1126K|

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='C##MHOURI')


Note

-----

- SQL profile profile_70qkj0rf0m9s0_attach used for this statement


Best regards

Mohamed Houri

2016-11-18 19:56 GMT+01:00 Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>:


One option would be to use the dbms_sqldiag_internal "sql_patch" procedure
described at: https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a

Regards
Jonathan Lewis

________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Hameed, Amir <Amir.Hameed@xxxxxxxxx>
Sent: 18 November 2016 18:01:19
To: oracle-l@xxxxxxxxxxxxx
Subject: Influencing execution plan via SQL Profiles

Hi,
I am trying to figure out how to remove/nullify existing hints of SQL
statement and insert different hints without modifying the code:

Original Statement:
UPDATE
    SELECT /*+ leading(aeh,xet,xte) use_hash(xet,xec,xeca) use_nl(xte)
     swap_join_inputs(xet) swap_join_inputs(xec) swap_join_inputs(xeca) */
     aeh.program_update_date -- added hint per performance change 7259699
           ,aeh.program_id
           ,aeh.request_id
           ,aeh.gl_transfer_date
           ,aeh.gl_transfer_status_code
           ,aeh.group_id


TO

Modified statement:
UPDATE
    SELECT /*+ full(xte) parallel(xte,8) */
     aeh.program_update_date -- added hint per performance change 7259699
           ,aeh.program_id
           ,aeh.request_id
           ,aeh.gl_transfer_date
           ,aeh.gl_transfer_status_code
           ,aeh.group_id

This is a packaged application and therefore, it is not possible for us to
change the code at the moment.

Thanks,
Amir


--
//www.freelists.org/webpage/oracle-l




-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>
rem
rem     Script:     FixProfileXmlFromCache.sql
rem     Author:     Original taken somewhere (Kerry Osborne or Carlos Sierra Or 
Aziz Cherrabi)
rem                 and updated by Mohamed Houri to consider hints > 500 bytes
rem                 and avoid ORA-06502
rem     Dated:      September 2016
rem     Purpose:    Transfer an execution plan of (hinted_sql_id) to a packaged 
query
rem                 
rem
rem     Last tested
rem             12.1.0.2
rem
rem     Usage:
rem     SQL> @fixProfilefromCache
rem                Enter value for sql_id_from: 2w9a295mxcjgx
rem                Enter value for child_no_from: 0
rem                Enter value for sql_id_to: addzft9frsckw
rem                                Enter value for sql_id_to: addzft9frsckw
rem                Enter value for sql_id_to: addzft9frsckw
rem    Notes : any feedback will be highly appreciated
rem 
declare
   ar_profile_xml clob;
   cl_sql_text clob;
begin
-- get sql_id_from information
-- 
   select
      regexp_replace(other_xml,'.*(<outline_data>.*</outline_data>).*','\1') 
         into ar_profile_xml
   from
      gv$sql_plan
   where
      sql_id = '&sql_id_from'
   and child_number = &child_no_from
   and other_xml is not null
        and rownum =1;
 
-- get information of sql_id_to 
-- use gv$sql instead of g$sqlstats
-- to avoid query text being truncated when it is very big
 begin
   select
      sql_fulltext into cl_sql_text
   from
      gv$sql
   where
      sql_id = '&sql_id_to';
exception
    when NO_DATA_FOUND then
          select
             sql_text into cl_sql_text
          from
             dba_hist_sqltext
          where
             sql_id = '&sql_id_to'
          and dbid  = (select dbid from v$database);
end;
-- fix Profile
 dbms_sqltune.import_sql_profile(
      sql_text => cl_sql_text ,
      profile_xml => ar_profile_xml ,
      name => 'profile_'||'&&sql_id_to'||'_attach' ,
      category => 'DEFAULT' ,
      replace => true ,
      force_match => TRUE
   );
 end;
/

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2016