RE: Bad exectution plans due to bind variable peeking

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Fri, 23 Jun 2006 16:44:58 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45059E193D@NT15.oneneck.corp>

Deleting the histograms did the trick! Thanks Alex!

More detail in case anyone is interested:

Here is the troublesome explain plan with the histograms in place (notice the CARTESIAN MERGE JOIN & BUFFER SORT - this seems to be the usual recipe for disaster with bind variable peeking from what I've experienced):

SQL> select operation, options, object_name from v$sql_plan where sql_id
= '72xtqyuumpb0k';

OPERATION                      OPTIONS

OBJECT_NAME

------------------------------ ------------------------------
------------------------------
SELECT STATEMENT
SORT                           ORDER BY

FILTER
FILTER
FILTER

NESTED LOOPS                   OUTER

NESTED LOOPS
NESTED LOOPS

MERGE JOIN                     CARTESIAN
TABLE ACCESS                   BY INDEX ROWID
TTDSLS041107
INDEX                          SKIP SCAN
TTDSLS041107$IDX4
BUFFER                         SORT
TABLE ACCESS                   BY INDEX ROWID
TTCCOM010105
INDEX                          RANGE SCAN
TTCCOM010105$IDX1
TABLE ACCESS                   BY INDEX ROWID
TTDSLS045107
INDEX                          RANGE SCAN
TTDSLS045107$IDX1
TABLE ACCESS                   BY INDEX ROWID
TTDSLS040107
INDEX                          UNIQUE SCAN
TTDSLS040107$IDX1
TABLE ACCESS                   BY INDEX ROWID
TTCMCS042107
INDEX                          UNIQUE SCAN
TTCMCS042107$IDX1
TABLE ACCESS                   BY INDEX ROWID
TTDSLS041107
INDEX                          RANGE SCAN

TTDSLS041107$IDX1 And then after getting rid of the histograms on just one of the tables:

begin

dbms_stats.gather_table_stats(
ownname=> 'BAAN',
tabname=> 'TTCCOM010105' ,
estimate_percent=> 25,
cascade=> TRUE,
degree=> null,
no_invalidate=> FALSE,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE 1');

end;
/

SQL> select operation, options, object_name from v$sql_plan where sql_id
= '72xtqyuumpb0k';

OPERATION                      OPTIONS

OBJECT_NAME

------------------------------ ------------------------------
----------------------------
SELECT STATEMENT
SORT                           ORDER BY

FILTER
FILTER
FILTER

NESTED LOOPS                   OUTER

NESTED LOOPS
NESTED LOOPS
NESTED LOOPS

TABLE ACCESS                   BY INDEX ROWID
TTDSLS045107
INDEX                          RANGE SCAN
TTDSLS045107$IDX1
TABLE ACCESS                   BY INDEX ROWID
TTCCOM010105
INDEX                          UNIQUE SCAN
TTCCOM010105$IDX1
TABLE ACCESS                   BY INDEX ROWID
TTDSLS041107
INDEX                          UNIQUE SCAN
TTDSLS041107$IDX1
TABLE ACCESS                   BY INDEX ROWID
TTDSLS040107
INDEX                          UNIQUE SCAN
TTDSLS040107$IDX1
TABLE ACCESS                   BY INDEX ROWID
TTCMCS042107
INDEX                          UNIQUE SCAN
TTCMCS042107$IDX1
TABLE ACCESS                   BY INDEX ROWID
TTDSLS041107
INDEX                          RANGE SCAN

TTDSLS041107$IDX1 This new plan works well for both of the queries with vastly different bind variables.

-----Original Message-----
From: Alex Gorbachev [mailto:gorbyx_at_gmail.com]

Do not collect histograms on tables. I.e. use 1 backet only.

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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

Received on Fri Jun 23 2006 - 18:44:58 CDT