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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Mar 12 2007 - 10:54:12 CDT
