outline not working on 1 instance of RAC cluster
- From: Wayne Adams <work@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 13 Apr 2007 12:54:50 -0700
We created an outline for a SQL statement that was using the incorrect index.
We
tested it in QA where it worked fine and then we put it in production across a
couple of RAC databases that run identical applications. Everything is fine for
2 weeks. Our performance issue goes away and we can see the outline being used.
Then, last night, one of the instances of one of our 2 instance RAC databases
starts having problems and it turns out to be the SQL that we outlined. Looking
at the plan, we can see that it is no longer using the outline. We can't figure
out why.
Can anybody give me any suggestions as to what to look for? We've compared the
SQL_TEXT between the 2 instances, and it's identical (it should be given that
it's the same application load balanced to the 2 instances). Also, the SQL_ID
and HASH_VALUEs are the same. We checked the CHILD_NUMBER on the off chance
that
changed, but it all seems to match.
SQL> exec print_table('select inst_id, sql_id, loads, first_load_time,
invalidations, parse_calls, optimizer_env_hash_value, hash_value,
old_hash_value,
plan_hash_value, child_number, outline_category, exact_matching_signature,
force_matching_signature from gv$sql where sql_id = ''58661pk09k8ax'' order by
inst_id');
INST_ID : 1
SQL_ID : 58661pk09k8ax
LOADS : 1
FIRST_LOAD_TIME : 2007-04-13/09:42:10
INVALIDATIONS : 0
PARSE_CALLS : 11
OPTIMIZER_ENV_HASH_VALUE : 1348537871
HASH_VALUE : 2157519197
OLD_HASH_VALUE : 557217828
PLAN_HASH_VALUE : 3207762864
CHILD_NUMBER : 0
OUTLINE_CATEGORY :
EXACT_MATCHING_SIGNATURE : 10005701153072284098
FORCE_MATCHING_SIGNATURE : 10005701153072284098
-----------------
INST_ID : 3
SQL_ID : 58661pk09k8ax
LOADS : 4
FIRST_LOAD_TIME : 2007-04-12/15:08:05
INVALIDATIONS : 1
PARSE_CALLS : 3
OPTIMIZER_ENV_HASH_VALUE : 3850634304
HASH_VALUE : 2157519197
OLD_HASH_VALUE : 557217828
PLAN_HASH_VALUE : 908374216
CHILD_NUMBER : 0
OUTLINE_CATEGORY : STEVE_OUTL
EXACT_MATCHING_SIGNATURE : 0
FORCE_MATCHING_SIGNATURE : 0
-----------------
# INST_ID 1 ran the old bad plan, INST_ID 3 runs the good plan with the OUTLINE
SQL> exec print_table('select * from dba_outlines');
NAME : SYS_OUTLINE_07032116495626001
OWNER : FORUMSAPP
CATEGORY : STEVE_OUTL
USED : USED
TIMESTAMP : 12-apr-2007 21:39:07
VERSION : 10.2.0.2.0
SQL_TEXT : SELECT jiveMessage.messageID,
jiveMessage.modificationDate FROM jiveMessage WHERE jiveMessage.threadID=:1 AND
jiveMessage.modificationDate >= :2 AND jiveMessage.modValu
e >= :3 ORDER BY jiveMessage.modificationDate DESC
SIGNATURE : FCE7F7A2597BFF41B5ABCA9389C7128A
COMPATIBLE : COMPATIBLE
ENABLED : ENABLED
FORMAT : NORMAL
Thanks
Wayne Adams
www.wayneadamsconsulting.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
