| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: CBO & different execution plans
On Mar 12, 11:54 am, DA Morgan <damor..._at_psoug.org> wrote:
> Charles Hooper wrote:
> > 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
>
> > --------------------------------------------------------------------------------------
> > | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> > Time | Buffers |
> > --------------------------------------------------------------------------------------
> > |* 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
>
> > ------------------------------------------------------------------------------------
> > | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> > Time | Buffers |
> > ------------------------------------------------------------------------------------
> > |* 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
> > ------------------------------------------------------------------------------------
> > | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> > Time | Buffers |
> > ------------------------------------------------------------------------------------
> > |* 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.
>
> Retry your statement with VALIDATE=>TRUE and you will see why.
>
> Oracle will likely send you the following personal note.
>
> 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
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
You result was identical to the result that I originally posted - the two statements only differ by a comment - the comment just happens to be a hint:
With validate at FALSE:
SQL> BEGIN
2 SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE
3 ( NAME => 'T1_EQ', 4 SOURCE_STMT => 'SELECT * FROM T1 WHERE C2=5', 5 DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5', 6 VALIDATE => FALSE, 7 REWRITE_MODE => 'TEXT_MATCH' );8 END;
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
With validate at TRUE:
SQL> BEGIN
2 SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE
3 ( NAME => 'T1_EQ', 4 SOURCE_STMT => 'SELECT * FROM T1 WHERE C2=5', 5 DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5', 6 VALIDATE => TRUE, 7 REWRITE_MODE => 'TEXT_MATCH' );8 END;
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
It would be nice to use DBMS_ADVANCED_REWRITE to add a hint, such as
(for the OP):
/*+ LEADING (HTS_ASSAY HTS_COMPOUND_LOT HTS_ASSAY_RESULT) */
And leave the rest of the SQL statement alone.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Mar 12 2007 - 15:45:17 CDT
 |
 |