outline not working on 1 instance of RAC cluster

From: Wayne Adams <work_at_wayneadams.com>
Date: Fri, 13 Apr 2007 12:54:50 -0700
Message-Id: <34193.1176494090@wayneadams.com>

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

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

Received on Fri Apr 13 2007 - 14:54:50 CDT