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  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
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  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
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 |
 |
LNossov
Messages: 318
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.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 09:38:16 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
