Re: CBO & different execution plans

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 11 Mar 2007 18:24:09 -0700
Message-ID: <1173662649.596363.179780@t69g2000cwt.googlegroups.com>

On Mar 11, 4:45 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Charles Hooper wrote:
> > I experimented a bit some time ago with dbms_advanced_rewrite. I had
> > a SQL statement in a packaged application that was taking excessively
> > long to execute - roughly 3.4 seconds per execution and the packaged
> > application was trying to execute the statement thousands of times.
> > The report that should have required 15 seconds to complete required
> > 12+ minutes. The problem in this case had to do with Oracle picking
> > the wrong index for a table access. Providing a hint to Oracle to use
> > the correct index dropped the execution time down to roughly 0.04
> > seconds per execution, allowing the report to again complete in 15
> > seconds (bad news is that, if the application were specifically coded
> > for Oracle, it could have built the report in less than 2 seconds).
> > During my experimentation, I could not make dbms_advanced_rewrite
> > recognize the presence of the hint and to have Oracle act on the hint
> > that was embedded.
>
> Every time I have seen this behaviour it came down to one of two
> issues:
>
> 1. query_rewrite not set to trusted
> 2. a database character set not supported
>
> If anyone has an example of this not working I would appreciate
> receiving it off-line.
>
> Thank you.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

I threw together a quick demonstration of the problem that I was encountering, just adding a hint and leaving the rest of the SQL statement alone.

The set up:
CREATE TABLE T1 (
  C1 NUMBER(12),
  C2 NUMBER(12)); CREATE INDEX IND_JT1 ON T1(C1);
CREATE INDEX IND_JT2 ON T1(C2,C1); Insert 10,000 rows, with the second column cycling between 0 and 49: INSERT INTO
  T1
SELECT
  ROWNUM,
  MOD(ROWNUM,50)
FROM
  DBA_OBJECTS
WHERE
  ROWNUM<=10000;

Gather the statistics for the table and indexes: EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER_HERE',TABNAME=>'T1',CASCADE=>TRUE); Make certain that the environment is set up to handle rewrite equivalence:
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE; ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED; Let DBMS Xplan report additional information: ALTER SESSION SET STATISTICS_LEVEL=ALL; The first test, just a plain query:
SELECT
  *
FROM
  T1
WHERE
  C2=5;

Let's see the plan:
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); SELECT * FROM T1 WHERE C2=5 Plan hash value: 3586113557

|* 1 | INDEX RANGE SCAN| IND_JT2 | 1 | 200 | 200 | 00:00:00.01 | 16 |

Predicate Information (identified by operation id):

1 - access("C2"=5)

Oracle used the IND_JT2 index to satisfy the query, which was expected.

Now, let's try a hint just to make certain that the hint syntax is correct:
SELECT /*+ FULL(T1) */
  *
FROM
  T1
WHERE
  C2=5;

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); SQL_ID 3hatv8v3nfu05, child number 0

SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5 Plan hash value: 3617692013

|* 1 | TABLE ACCESS FULL| T1 | 1 | 200 | 200 | 00:00:00.01 |37 |

Predicate Information (identified by operation id):

1 - filter("C2"=5)

Oracle now performed a full table scan to satisfy the query, as requested.

Now, let's try to create an equivalence to automatically insert the hint into the SQL statement:
BEGIN
SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE

  ( NAME             => 'T1_EQ',
    SOURCE_STMT      => 'SELECT * FROM T1 WHERE C2=5',
    DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5',
    VALIDATE         => FALSE,
    REWRITE_MODE     => 'TEXT_MATCH' );

END;
/

ERROR at line 1:

ORA-30394: source statement identical to the destination statement
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2

According to Oracle, the two SQL statements are already the same, one just has a comment, according to the above. Maybe there is something wrong with the syntax that I used.

Let's try again, this time make certain that the two SQL statements do not differ by just a comment (or white space): BEGIN
SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE

  ( NAME             => 'T1_EQ',
    SOURCE_STMT      => 'SELECT * FROM T1 WHERE C2=5',
    DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=6',
    VALIDATE         => FALSE,
    REWRITE_MODE     => 'TEXT_MATCH' );

END;
/

PL/SQL procedure successfully completed.

The above executed, so I just had to change the meaning of the SQL statement. Now, let's see if it works:
SELECT
  *
FROM
  T1
WHERE
  C2=5;

C1 C2
---------- ----------

      1856          6
      1906          6
      1956          6
      2006          6

...

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); SELECT * FROM T1 WHERE C2=5 Plan hash value: 3617692013

|* 1 | TABLE ACCESS FULL| T1 | 1 | 200 | 200 | 00:00:00.01 |37 |

Predicate Information (identified by operation id):

1 - filter("C2"=6)

It looks like Oracle will recognize the hint if we change the meaning of the SQL statement.

If you see something that I did wrong, please let me know. This would be a very useful feature for me if I did not have to change the meaning of the SQL statement in order for query rewrite to accept a hint.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Mar 11 2007 - 20:24:09 CDT