bind_mismatch causes cursor with different execution plans

From: "limin guo" <lguo.oracle@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Thu, 20 Apr 2006 13:13:54 -0400
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.
bind_mismatch causes cursor with different execution plans

Other related posts:
