Home » RDBMS Server » Performance Tuning » How to run a query with an different plan_hash value then in GV$SQL_PLAN (Oracle 10.2.0.4.0 RAC on Linux)
How to run a query with an different plan_hash value then in GV$SQL_PLAN [message #571586] Tue, 27 November 2012 06:34 Go to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi all,

A change(running a statistics collection package) was made,
and now a query is running impossibly slow, disabling use of an application function.

This is cause by an sql , which's sql_id is know to me: 4zty77fkf907j
when I run

select * from GV$SQL_PLAN_STATISTICS where SQL_ID = '4zty77fkf907j'

I get that the plan hash value is 3405747833.

I ran
SELECT Q.PARSING_SCHEMA_NAME
       ,Q.SQL_ID,ROUND(Q.ELAPSED_TIME / Q.EXECUTIONS)/1000000 AS AVG_USECS
       ,Q.CHILD_NUMBER
       ,Q.HASH_VALUE
       ,Q.PLAN_HASH_VALUE
       ,'''' || REPLACE(Q.SQL_FULLTEXT
                       ,''''
                       ,'''''') || ''';'
       ,DBMS_XPLAN.DISPLAY_CURSOR(Q.SQL_ID
                                 ,Q.CHILD_NUMBER
                                 ,'all allstats advanced')
         FROM GV$SQL Q WHERE '4zty77fkf907j' IN (Q.SQL_ID, TO_CHAR(Q.HASH_VALUE
                                      ,'FM9999999999'),
        TO_CHAR(Q.OLD_HASH_VALUE
                            ,'FM9999999999')) AND Q.EXECUTIONS > 0;


and seen that the result was 2 rows(for 2 RAC nodes), with a figure of hundreds of seconds of execution, meaning - bad plan.


I know yesterday problem did not exist, and when I run

select * from dba_hist_sql_plan where sql_id='4zty77fkf907j' 

I get plan hash value of 3390684693. I assume this might be a "good plan"

For a start, how can I hint my query to use that "good plan" before I use stored outlines to implement it?

(sorry I could not post from sql_plus, I have some copy-paste issues in a certain client I use to connect,
because the customer has some irritating vpn....)


Thanks in advance,
Andrey

[Updated on: Tue, 27 November 2012 07:45]

Report message to a moderator

Re: How to run a query with an different plan_hash value then in GV$SQL_PLAN [message #572192 is a reply to message #571586] Fri, 07 December 2012 03:57 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi All,
I did not find an answer of how to sort of "hint" to use a particular plan identified by a PHV,
But what I do know now is that to check how a query performs with another plan, you can:

1. Run the query with a hint.
2. Create an outline with a category enabled only in your session(alter session set use_stored_outlines = 'testcategory')

Hope it helps somebody.

Regards,
Andrey
Re: How to run a query with an different plan_hash value then in GV$SQL_PLAN [message #572813 is a reply to message #571586] Mon, 17 December 2012 17:14 Go to previous message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
For the cursor in sql area you can display the execution plan with dbms_xplan.display_cursor (for sql in awr with dbms_xplan.display_awr). If you use the parameter format=>'advanced' in the both function calls, then you get the outlines in the output. These outlines fix your execution plan.

For ex.

SQL_ID  4jap5bz7vmbn9, child number 0
-------------------------------------
select count(*) from dba_tab_columns

Plan hash value: 3822578803

----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |       |       |   928 (100)|          |
|   1 |  SORT AGGREGATE                      |                   |     1 |   101 |            |          |
|*  2 |   FILTER                             |                   |       |       |            |          |
|*  3 |    HASH JOIN                         |                   |   967 | 97667 |   928   (2)| 00:00:12 |
|   4 |     INDEX FULL SCAN                  | I_USER2           |    98 |   392 |     1   (0)| 00:00:01 |
|*  5 |     HASH JOIN                        |                   |   967 | 93799 |   927   (2)| 00:00:12 |
|   6 |      INDEX FULL SCAN                 | I_USER2           |    98 |  2156 |     1   (0)| 00:00:01 |
|*  7 |      HASH JOIN                       |                   |   967 | 72525 |   925   (2)| 00:00:12 |
|*  8 |       HASH JOIN OUTER                |                   |   967 | 58020 |   716   (2)| 00:00:09 |
|*  9 |        HASH JOIN RIGHT OUTER         |                   |   967 | 49317 |   677   (2)| 00:00:09 |
|  10 |         INDEX FULL SCAN              | I_USER2           |    98 |   392 |     1   (0)| 00:00:01 |
|* 11 |         HASH JOIN OUTER              |                   |   967 | 45449 |   676   (2)| 00:00:09 |
|  12 |          NESTED LOOPS OUTER          |                   |   967 | 36746 |   419   (2)| 00:00:06 |
|* 13 |           TABLE ACCESS FULL          | COL$              |   967 | 11604 |   400   (2)| 00:00:05 |
|  14 |           TABLE ACCESS BY INDEX ROWID| COLTYPE$          |     1 |    26 |     1   (0)| 00:00:01 |
|* 15 |            INDEX UNIQUE SCAN         | I_COLTYPE2        |     1 |       |     0   (0)|          |
|* 16 |          TABLE ACCESS FULL           | OBJ$              |  2998 | 26982 |   257   (1)| 00:00:04 |
|  17 |        INDEX FAST FULL SCAN          | I_HH_OBJ#_INTCOL# | 33907 |   298K|    38   (0)| 00:00:01 |
|  18 |       INDEX FAST FULL SCAN           | I_OBJ2            | 75649 |  1108K|   208   (0)| 00:00:03 |
|* 19 |    TABLE ACCESS CLUSTER              | TAB$              |     1 |    13 |     2   (0)| 00:00:01 |
|* 20 |     INDEX UNIQUE SCAN                | I_OBJ#            |     1 |       |     1   (0)| 00:00:01 |
|  21 |    NESTED LOOPS                      |                   |     1 |    29 |     2   (0)| 00:00:01 |
|* 22 |     INDEX SKIP SCAN                  | I_USER2           |     1 |    20 |     1   (0)| 00:00:01 |
|* 23 |     INDEX RANGE SCAN                 | I_OBJ4            |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$0F07BF54
   4 - SEL$0F07BF54 / U@SEL$3
   6 - SEL$0F07BF54 / U@SEL$4
  10 - SEL$0F07BF54 / U@SEL$6
  13 - SEL$0F07BF54 / C@SEL$3
  14 - SEL$0F07BF54 / AC@SEL$3
  15 - SEL$0F07BF54 / AC@SEL$3
  16 - SEL$0F07BF54 / OT@SEL$3
  17 - SEL$0F07BF54 / H@SEL$3
  18 - SEL$0F07BF54 / O@SEL$4
  19 - SEL$7        / T@SEL$7
  20 - SEL$7        / T@SEL$7
  21 - SEL$5
  22 - SEL$5        / U2@SEL$5
  23 - SEL$5        / O2@SEL$5

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$0F07BF54")
      MERGE(@"SEL$83990AE1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$83990AE1")
      MERGE(@"SEL$0BBAD545")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$0BBAD545")
      MERGE(@"SEL$4")
      MERGE(@"SEL$6")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$6")
      FULL(@"SEL$0F07BF54" "C"@"SEL$3")
      INDEX_RS_ASC(@"SEL$0F07BF54" "AC"@"SEL$3" ("COLTYPE$"."OBJ#" "COLTYPE$"."INTCOL#"))
      FULL(@"SEL$0F07BF54" "OT"@"SEL$3")
      INDEX(@"SEL$0F07BF54" "U"@"SEL$6" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1"
              "USER$"."SPARE2"))
      INDEX_FFS(@"SEL$0F07BF54" "H"@"SEL$3" ("HIST_HEAD$"."OBJ#" "HIST_HEAD$"."INTCOL#"))
      INDEX_FFS(@"SEL$0F07BF54" "O"@"SEL$4" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE"
              "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME" "OBJ$"."TYPE#" "OBJ$"."SPARE3"
              "OBJ$"."OBJ#"))
      INDEX(@"SEL$0F07BF54" "U"@"SEL$4" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1"
              "USER$"."SPARE2"))
      INDEX(@"SEL$0F07BF54" "U"@"SEL$3" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1"
              "USER$"."SPARE2"))
      LEADING(@"SEL$0F07BF54" "C"@"SEL$3" "AC"@"SEL$3" "OT"@"SEL$3" "U"@"SEL$6" "H"@"SEL$3" "O"@"SEL$4"
              "U"@"SEL$4" "U"@"SEL$3")
      USE_NL(@"SEL$0F07BF54" "AC"@"SEL$3")
      USE_HASH(@"SEL$0F07BF54" "OT"@"SEL$3")
      USE_HASH(@"SEL$0F07BF54" "U"@"SEL$6")
      USE_HASH(@"SEL$0F07BF54" "H"@"SEL$3")
      USE_HASH(@"SEL$0F07BF54" "O"@"SEL$4")
      USE_HASH(@"SEL$0F07BF54" "U"@"SEL$4")
      USE_HASH(@"SEL$0F07BF54" "U"@"SEL$3")
      SWAP_JOIN_INPUTS(@"SEL$0F07BF54" "U"@"SEL$6")
      SWAP_JOIN_INPUTS(@"SEL$0F07BF54" "U"@"SEL$4")
      SWAP_JOIN_INPUTS(@"SEL$0F07BF54" "U"@"SEL$3")
      INDEX_SS(@"SEL$5" "U2"@"SEL$5" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1"
              "USER$"."SPARE2"))
      INDEX(@"SEL$5" "O2"@"SEL$5" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))
      LEADING(@"SEL$5" "U2"@"SEL$5" "O2"@"SEL$5")
      USE_NL(@"SEL$5" "O2"@"SEL$5")
      INDEX(@"SEL$7" "T"@"SEL$7" "I_OBJ#")
      END_OUTLINE_DATA
  */

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

   2 - filter(((INTERNAL_FUNCTION("O"."TYPE#") OR ("O"."TYPE#"=2 AND  IS NULL)) AND
              (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND
              "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14
              AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR
              (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE'
              AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_ed
              ition_id'))) OR  IS NOT NULL)))))
   3 - access("O"."SPARE3"="U"."USER#")
   5 - access("O"."OWNER#"="U"."USER#")
   7 - access("O"."OBJ#"="C"."OBJ#")
   8 - access("C"."OBJ#"="H"."OBJ#" AND "C"."INTCOL#"="H"."INTCOL#")
   9 - access("OT"."OWNER#"="USER#")
  11 - access("AC"."TOID"="OT"."OID$")
  13 - filter(DECODE("C"."PROPERTY",0,'NO',DECODE(BITAND("C"."PROPERTY",32),32,'YES','NO'))='NO')
  15 - access("C"."OBJ#"="AC"."OBJ#" AND "C"."INTCOL#"="AC"."INTCOL#")
  16 - filter("OT"."TYPE#"=13)
  19 - filter((BITAND("T"."PROPERTY",8192)=8192 OR BITAND("T"."PROPERTY",512)=512))
  20 - access("T"."OBJ#"=:B1)
  22 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id
              ')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_i
              d'))))
  23 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   3 - (#keys=1) "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
       "O"."OBJ#"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
   4 - "U"."USER#"[NUMBER,22]
   5 - (#keys=1) "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
       "O"."OBJ#"[NUMBER,22], "O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
   6 - "U"."USER#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
       "U"."SPARE2"[NUMBER,22]
   7 - (#keys=1) "O"."OBJ#"[NUMBER,22], "O"."SPARE3"[NUMBER,22], "O"."OWNER#"[NUMBER,22],
       "O"."TYPE#"[NUMBER,22]
   8 - (#keys=2) "C"."OBJ#"[NUMBER,22]
   9 - (#keys=1) "C"."OBJ#"[NUMBER,22], "C"."INTCOL#"[NUMBER,22]
  10 - "USER#"[NUMBER,22]
  11 - (#keys=1) "C"."OBJ#"[NUMBER,22], "C"."INTCOL#"[NUMBER,22], "OT"."OWNER#"[NUMBER,22]
  12 - "C"."OBJ#"[NUMBER,22], "C"."INTCOL#"[NUMBER,22], "AC"."TOID"[RAW,16]
  13 - "C"."OBJ#"[NUMBER,22], "C"."INTCOL#"[NUMBER,22]
  14 - "AC"."TOID"[RAW,16]
  15 - "AC".ROWID[ROWID,10]
  16 - "OT"."OWNER#"[NUMBER,22], "OT"."OID$"[RAW,16]
  17 - "H"."OBJ#"[NUMBER,22], "H"."INTCOL#"[NUMBER,22]
  18 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22],
       "O"."SPARE3"[NUMBER,22]
  19 - "T".ROWID[ROWID,10], "T"."OBJ#"[NUMBER,22], "T"."PROPERTY"[NUMBER,22]
  20 - "T".ROWID[ROWID,10]
  22 - "U2"."USER#"[NUMBER,22]


You can use these outlines in sql for fixing the execution plan with phv 3822578803

select 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$0F07BF54")
      MERGE(@"SEL$83990AE1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$83990AE1")
      MERGE(@"SEL$0BBAD545")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$0BBAD545")
      MERGE(@"SEL$4")
      MERGE(@"SEL$6")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$6")
      FULL(@"SEL$0F07BF54" "C"@"SEL$3")
      INDEX_RS_ASC(@"SEL$0F07BF54" "AC"@"SEL$3" ("COLTYPE$"."OBJ#" "COLTYPE$"."INTCOL#"))
      FULL(@"SEL$0F07BF54" "OT"@"SEL$3")
      INDEX(@"SEL$0F07BF54" "U"@"SEL$6" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1"
              "USER$"."SPARE2"))
      INDEX_FFS(@"SEL$0F07BF54" "H"@"SEL$3" ("HIST_HEAD$"."OBJ#" "HIST_HEAD$"."INTCOL#"))
      INDEX_FFS(@"SEL$0F07BF54" "O"@"SEL$4" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE"
              "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME" "OBJ$"."TYPE#" "OBJ$"."SPARE3"
              "OBJ$"."OBJ#"))
      INDEX(@"SEL$0F07BF54" "U"@"SEL$4" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1"
              "USER$"."SPARE2"))
      INDEX(@"SEL$0F07BF54" "U"@"SEL$3" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1"
              "USER$"."SPARE2"))
      LEADING(@"SEL$0F07BF54" "C"@"SEL$3" "AC"@"SEL$3" "OT"@"SEL$3" "U"@"SEL$6" "H"@"SEL$3" "O"@"SEL$4"
              "U"@"SEL$4" "U"@"SEL$3")
      USE_NL(@"SEL$0F07BF54" "AC"@"SEL$3")
      USE_HASH(@"SEL$0F07BF54" "OT"@"SEL$3")
      USE_HASH(@"SEL$0F07BF54" "U"@"SEL$6")
      USE_HASH(@"SEL$0F07BF54" "H"@"SEL$3")
      USE_HASH(@"SEL$0F07BF54" "O"@"SEL$4")
      USE_HASH(@"SEL$0F07BF54" "U"@"SEL$4")
      USE_HASH(@"SEL$0F07BF54" "U"@"SEL$3")
      SWAP_JOIN_INPUTS(@"SEL$0F07BF54" "U"@"SEL$6")
      SWAP_JOIN_INPUTS(@"SEL$0F07BF54" "U"@"SEL$4")
      SWAP_JOIN_INPUTS(@"SEL$0F07BF54" "U"@"SEL$3")
      INDEX_SS(@"SEL$5" "U2"@"SEL$5" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1"
              "USER$"."SPARE2"))
      INDEX(@"SEL$5" "O2"@"SEL$5" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))
      LEADING(@"SEL$5" "U2"@"SEL$5" "O2"@"SEL$5")
      USE_NL(@"SEL$5" "O2"@"SEL$5")
      INDEX(@"SEL$7" "T"@"SEL$7" "I_OBJ#")
      END_OUTLINE_DATA
  */
count(*) from dba_tab_columns


or you can store they in sql profile or in stored outlines.
Previous Topic: SQL Tuning...?
Next Topic: Adaptive cursor sharing Doubt!!
Goto Forum:
  


Current Time: Sat Jul 26 12:50:35 CDT 2014

Total time taken to generate the page: 0.09995 seconds