bind_mismatch causes cursor with different execution plans

We have experienced performance problem in one Peoplesoft database (HR8.3, PT2.0). The online users complain application not responsive sporadically in the early morning (6AM to 8AM). Here are some basic information about the database and it's settings:

--Oracle 9.2.0.6
--Solaris 8
--dbms_stats.gather_schema_stats "gather stale" at 4:00PM every day. no
histogram.
--database shutdown for 5 minutes at 8:00PM M-F for backup (EMC BCV splits)
--optimizer_index_caching integer 80
--optimizer_index_cost_adj integer 50
--db_file_multiblock_read_count integer 16

When I looked into the database, I discovered, during the period of application slow, that some cursors have two versions, each version comes with its own execution plan. One of the execution plan is always trying to use FULL TABLE SCAN, FULL INDEX SCAN OR HASH JOIN. All of cursors with different execution plans are using "bind variable". Here is one of those trouble cursors:

SQL statement:
SELECT EMPLID, EMPL_RCD FROM PS_EMP_VW A WHERE EMPLID=:1 ORDER BY EMPLID, EMPL_RCD Two different execution plans from v$sql_plan

  cn ID cost card operation
--- ------ ------- ------------ ----------------------------------- ----

  0    0         7              SELECT STATEMENT
       1         7            7  VIEW
       2         7            7   SORT UNIQUE NOSORT
       3A        3           11    INDEX RANGE SCAN PS_AR_JOB

  1    0     3,886              SELECT STATEMENT
       1     3,886       73,741  VIEW
       2     3,886       73,741   SORT UNIQUE
       3F    3,635       73,741    INDEX FAST FULL SCAN PS_AR_JOB

v$sql_shared_cursor shows "bind_mismatch" is the cause of multiple versions.

SQL>select * from v$sql_shared_cursor where kglhdpar='85CC5850' ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
-------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
8B4D3E00 85CC5850 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
8F3B16C4 85CC5850 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N

v$sql_bind_metadata doesn't show the differences of datatype, max_length or array_len.

SQL>select * from v$sql_bind_metadata where address in ('8B4D3E00','8F3B16C4');
ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME
-------- ---------- ---------- ---------- ----------

8B4D3E00          1          1       2000          0 1
8F3B16C4          1          1       2000          0 1

setting "_optim_peek_user_binds=false" did not seem to have any impact on it.

Again, the problem is intermittent, it could happen once a week or once every two weeks. No pattern to follow.

I opened a TAR with Oracle support, I was recommended to try and see the histograms make any difference, from "all columns size 1" to "all columns size 200" or "all columns auto". I, however, hesitate to try it because the tables and indexes used by cursors with different execution plans did not get analyzed at time when slowness occurs or the day before. So I doubt they are related.

The database only has one schema, all cursors are parsed through this schema id.

Any helps would be highly appreciated.

--

Regards,

Limin Guo.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 20 2006 - 12:13:54 CDT