OraFAQ Forum: SQL & PL/SQL » OUTLINE and CURSOR

Home » SQL & PL/SQL » SQL & PL/SQL » OUTLINE and CURSOR_SHARING = SIMILAR

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

OUTLINE and CURSOR_SHARING = SIMILAR [message #177265]

Tue, 13 June 2006 14:30

pka1976
Messages: 3
Registered: June 2006
Location: Germany

Junior Member

Hi,

we've problems with stored outlines in combination with cursor_sharing = similar at Oracle 9.2.0.6. Maybe somebody has an idea what's wrong or missing.

We created the Stored Outline as describe in the documentation:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET CURSOR_SHARING = SIMILAR;
ALTER SESSION SET OPTIMIZER_MODE = CHOOSE;
ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE;

SELECT ....;

ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

After execution the outline was created, the SQL Text includes system bind variables as expected. The USED - Flag of the outline is FALSE.

Now we try to use the outline.

ALTER SESSION SET OPTIMIZER_MODE = RULE;
ALTER SESSION SET CURSOR_SHARING = SIMILAR;
ALTER SESSION SET USE_STORED_OUTLINES = TRUE;

SELECT ....;

Unexpected result: The execution plan is based on RBO mode and doesn't match the plan of the stored outline. The USED - Flag of the outline is TRUE.

It's very mystic. We tried a lot of combinations but nothing works. What's wrong?

Report message to a moderator

Re: OUTLINE and CURSOR_SHARING = SIMILAR [message #177268 is a reply to message #177265]

Tue, 13 June 2006 14:47

pka1976
Messages: 3
Registered: June 2006
Location: Germany

Junior Member

Additional info: The same works fine for CURSOR_SHARING = EXACT. If anybody asks why to use it in RBO mode. Answer: The statements are part of an invoicing modul which is running in this mode.

Report message to a moderator

Re: OUTLINE and CURSOR_SHARING = SIMILAR [message #177616 is a reply to message #177268]

Thu, 15 June 2006 15:28

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

I believe outlines are only used with CBO, not RBO. Here is a link to a section of online documentation regarding how to smoothly transition from RBO to CBO, without losing your existing RBO functionality, by using outlines based on the old RBO:

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm#i12916

Report message to a moderator

Re: OUTLINE and CURSOR_SHARING = SIMILAR [message #177706 is a reply to message #177616]

Fri, 16 June 2006 02:49

pka1976
Messages: 3
Registered: June 2006
Location: Germany

Junior Member

That's not correct. As I wrote stored outlines in this scenario works fine if we use CURSOR_SHARING = EXACT. The RBO will choose the execution plan of the stored outline.

Meanwhile I think there is a bug in Oracle 9.2.0.6. If we use CURSOR_SHARING = SIMILAR the stored outline will be used (execution plan in V$SQL_PLAN = execution plan of the outline) but showed execution plan by "set autotrace on explain" or "explain plan for" is another. This is the reason why we were confused the whole time.

Report message to a moderator

Re: OUTLINE and CURSOR_SHARING = SIMILAR [message #177829 is a reply to message #177706]

Fri, 16 June 2006 14:28

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

I think you are right. It looks like the outline may be used, but the results obtained by selecting from v$sql in 9i, as recommended in the 10g documentation, are misleading. Everything else seems to indicate that the outline is being used. I ran complete tests of an outline created under RBO used under CBO and an outline created under CBO used under RBO. I did each once with cursor_sharing set to exact and once set to similar and ran the whole test once in 9i and once in 10g. The usage of the outline is clearer in 10g. I will post the 9i results below and the 10g results in another response, for easier readability.

-- testing environment:

scott@ORA92> SELECT banner FROM v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE	9.2.0.1.0	Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

scott@ORA92> CREATE TABLE test_tab (col1 NUMBER, col2 NUMBER)
  2  /

Table created.

scott@ORA92> INSERT INTO test_tab VALUES (1, 2)
  2  /

1 row created.

scott@ORA92> CREATE INDEX test_idx ON test_tab (col1)
  2  /

Index created.

scott@ORA92> EXEC DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'TEST_TAB')

PL/SQL procedure successfully completed.

scott@ORA92> ALTER SESSION SET CURSOR_SHARING = EXACT
  2  /

Session altered.

-- create stored outline based on RBO:

scott@ORA92> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

scott@ORA92> ALTER SESSION SET OPTIMIZER_MODE = RULE
  2  /

Session altered.

scott@ORA92> ALTER SESSION SET USE_STORED_OUTLINES = FALSE
  2  /

Session altered.

scott@ORA92> ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE
  2  /

Session altered.

scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_TAB'
   2    1     INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE)



scott@ORA92> SET AUTOTRACE OFF

-- show that CBO uses different plan:

scott@ORA92> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

scott@ORA92> ALTER SESSION SET OPTIMIZER_MODE = CHOOSE
  2  /

Session altered.

scott@ORA92> ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE
  2  /

Session altered.

scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6)
   1    0   TABLE ACCESS (FULL) OF 'TEST_TAB' (Cost=2 Card=1 Bytes=6)



scott@ORA92> SET AUTOTRACE OFF

-- Is stored outline used by CBO with cursor_sharing = SIMILAR?:

scott@ORA92> ALTER SESSION SET USE_STORED_OUTLINES = TRUE
  2  /

Session altered.

scott@ORA92> ALTER SESSION SET CURSOR_SHARING = SIMILAR
  2  /

Session altered.

scott@ORA92> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=825 Card=1 Bytes=6)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_TAB' (Cost=825 Card=1 Bytes=6)
   2    1     INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=25 Card=1)



scott@ORA92> SET AUTOTRACE OFF
scott@ORA92> SELECT OUTLINE_CATEGORY, OUTLINE_SID
  2  FROM   V$SQL
  3  WHERE  SQL_TEXT LIKE 'SELECT * FROM test_tab WHERE col1 = 1%'
  4  /

no rows selected

scott@ORA92> SELECT used, sql_text FROM user_outlines
  2  /

USED      SQL_TEXT
--------- --------------------------------------------------------------------------------
...
USED      SELECT * FROM test_tab WHERE col1 = 1
...

-- show that stored outline is used by CBO with cursor_sharing = EXACT:

scott@ORA92> ALTER SESSION SET CURSOR_SHARING = EXACT
  2  /

Session altered.

scott@ORA92> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=825 Card=1 Bytes=6)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_TAB' (Cost=825 Card=1 Bytes=6)
   2    1     INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=25 Card=1)



scott@ORA92> SET AUTOTRACE OFF
scott@ORA92> SELECT OUTLINE_CATEGORY, OUTLINE_SID
  2  FROM   V$SQL
  3  WHERE  SQL_TEXT LIKE 'SELECT * FROM test_tab WHERE col1 = 1%'
  4  /

OUTLINE_CATEGORY                                                 OUTLINE_SID
---------------------------------------------------------------- -----------
DEFAULT                                                                    0

scott@ORA92> SELECT used, sql_text FROM user_outlines
  2  /

USED      SQL_TEXT
--------- --------------------------------------------------------------------------------
...
USED      SELECT * FROM test_tab WHERE col1 = 1
...

-- create stored outline based on CBO:

scott@ORA92> BEGIN
  2    FOR r IN  (SELECT name FROM user_outlines) LOOP
  3  	 EXECUTE IMMEDIATE 'DROP OUTLINE ' || r.name;
  4    END LOOP;
  5  END;
  6  /

PL/SQL procedure successfully completed.

scott@ORA92> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

scott@ORA92> ALTER SESSION SET USE_STORED_OUTLINES = FALSE
  2  /

Session altered.

scott@ORA92> ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE
  2  /

Session altered.

scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6)
   1    0   TABLE ACCESS (FULL) OF 'TEST_TAB' (Cost=2 Card=1 Bytes=6)



scott@ORA92> SET AUTOTRACE OFF

-- Is stored outline used by RBO with cursor_sharing = SIMILAR?:

scott@ORA92> ALTER SESSION SET OPTIMIZER_MODE = RULE
  2  /

Session altered.

scott@ORA92> ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE
  2  /

Session altered.

scott@ORA92> ALTER SESSION SET USE_STORED_OUTLINES = TRUE
  2  /

Session altered.

scott@ORA92> ALTER SESSION SET CURSOR_SHARING = SIMILAR
  2  /

Session altered.

scott@ORA92> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=2 Card=1 Bytes=6)
   1    0   TABLE ACCESS (FULL) OF 'TEST_TAB' (Cost=2 Card=1 Bytes=6)



scott@ORA92> SET AUTOTRACE OFF
scott@ORA92> SELECT OUTLINE_CATEGORY, OUTLINE_SID
  2  FROM   V$SQL
  3  WHERE  SQL_TEXT LIKE 'SELECT * FROM test_tab WHERE col1 = 1%'
  4  /

no rows selected

scott@ORA92> SELECT used, sql_text FROM user_outlines
  2  /

USED      SQL_TEXT
--------- --------------------------------------------------------------------------------
...
USED      SELECT * FROM test_tab WHERE col1 = 1
...

-- show that stored outline is used by RBO with cursor_sharing = EXACT:

scott@ORA92> ALTER SESSION SET CURSOR_SHARING = EXACT
  2  /

Session altered.

scott@ORA92> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=2 Card=1 Bytes=6)
   1    0   TABLE ACCESS (FULL) OF 'TEST_TAB' (Cost=2 Card=1 Bytes=6)



scott@ORA92> SET AUTOTRACE OFF
scott@ORA92> SELECT OUTLINE_CATEGORY, OUTLINE_SID
  2  FROM   V$SQL
  3  WHERE  SQL_TEXT LIKE 'SELECT * FROM test_tab WHERE col1 = 1%'
  4  /

OUTLINE_CATEGORY                                                 OUTLINE_SID
---------------------------------------------------------------- -----------
DEFAULT                                                                    0

scott@ORA92> SELECT used, sql_text FROM user_outlines
  2  /

USED      SQL_TEXT
--------- --------------------------------------------------------------------------------
...
USED      SELECT * FROM test_tab WHERE col1 = 1
...

scott@ORA92>

Report message to a moderator

Re: OUTLINE and CURSOR_SHARING = SIMILAR [message #177830 is a reply to message #177706]

Fri, 16 June 2006 14:35

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

And here is the 10g test:

-- testing environment:

SCOTT@10gXE> SELECT banner FROM v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SCOTT@10gXE> CREATE TABLE test_tab (col1 NUMBER, col2 NUMBER)
  2  /

Table created.

SCOTT@10gXE> INSERT INTO test_tab VALUES (1, 2)
  2  /

1 row created.

SCOTT@10gXE> CREATE INDEX test_idx ON test_tab (col1)
  2  /

Index created.

SCOTT@10gXE> EXEC DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'TEST_TAB')

PL/SQL procedure successfully completed.

SCOTT@10gXE> ALTER SESSION SET CURSOR_SHARING = EXACT
  2  /

Session altered.

-- create stored outline based on RBO:

SCOTT@10gXE> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SCOTT@10gXE> ALTER SESSION SET OPTIMIZER_MODE = RULE
  2  /

Session altered.

SCOTT@10gXE> ALTER SESSION SET USE_STORED_OUTLINES = FALSE
  2  /

Session altered.

SCOTT@10gXE> ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE
  2  /

Session altered.

SCOTT@10gXE> SET AUTOTRACE ON EXPLAIN
SCOTT@10gXE> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
Plan hash value: 976671675

------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL1"=1)

Note
-----
   - rule based optimizer used (consider using cbo)

SCOTT@10gXE> SET AUTOTRACE OFF

-- show that CBO uses different plan:

SCOTT@10gXE> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SCOTT@10gXE> ALTER SESSION SET OPTIMIZER_MODE = CHOOSE
  2  /

Session altered.

SCOTT@10gXE> ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE
  2  /

Session altered.

SCOTT@10gXE> SET AUTOTRACE ON EXPLAIN
SCOTT@10gXE> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     6 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |     1 |     6 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"=1)

SCOTT@10gXE> SET AUTOTRACE OFF

-- Is stored outline used by CBO with cursor_sharing = SIMILAR?:

SCOTT@10gXE> ALTER SESSION SET USE_STORED_OUTLINES = TRUE
  2  /

Session altered.

SCOTT@10gXE> ALTER SESSION SET CURSOR_SHARING = SIMILAR
  2  /

Session altered.

SCOTT@10gXE> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SCOTT@10gXE> SET AUTOTRACE ON EXPLAIN
SCOTT@10gXE> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
Plan hash value: 976671675

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB |     1 |     6 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL1"=1)

Note
-----
   - outline "SYS_OUTLINE_06061612113905519" used for this statement

SCOTT@10gXE> SET AUTOTRACE OFF
SCOTT@10gXE> SELECT OUTLINE_CATEGORY, OUTLINE_SID
  2  FROM   V$SQL
  3  WHERE  SQL_TEXT LIKE 'SELECT * FROM test_tab WHERE col1 = 1%'
  4  /

OUTLINE_CATEGORY                                                 OUTLINE_SID
---------------------------------------------------------------- -----------
DEFAULT

SCOTT@10gXE> SELECT used, sql_text FROM user_outlines
  2  /

USED   SQL_TEXT
------ --------------------------------------------------------------------------------
...
USED   SELECT * FROM test_tab WHERE col1 = 1
...
8 rows selected.

-- show that stored outline is used by CBO with cursor_sharing = EXACT:

SCOTT@10gXE> ALTER SESSION SET CURSOR_SHARING = EXACT
  2  /

Session altered.

SCOTT@10gXE> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SCOTT@10gXE> SET AUTOTRACE ON EXPLAIN
SCOTT@10gXE> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
Plan hash value: 976671675

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB |     1 |     6 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL1"=1)

Note
-----
   - outline "SYS_OUTLINE_06061612113905519" used for this statement

SCOTT@10gXE> SET AUTOTRACE OFF
SCOTT@10gXE> SELECT OUTLINE_CATEGORY, OUTLINE_SID
  2  FROM   V$SQL
  3  WHERE  SQL_TEXT LIKE 'SELECT * FROM test_tab WHERE col1 = 1%'
  4  /

OUTLINE_CATEGORY                                                 OUTLINE_SID
---------------------------------------------------------------- -----------
DEFAULT

SCOTT@10gXE> SELECT used, sql_text FROM user_outlines
  2  /

USED   SQL_TEXT
------ --------------------------------------------------------------------------------
...
USED   SELECT * FROM test_tab WHERE col1 = 1
...

8 rows selected.

-- create stored outline based on CBO:

SCOTT@10gXE> BEGIN
  2    FOR r IN  (SELECT name FROM user_outlines) LOOP
  3  	 EXECUTE IMMEDIATE 'DROP OUTLINE ' || r.name;
  4    END LOOP;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SCOTT@10gXE> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SCOTT@10gXE> ALTER SESSION SET USE_STORED_OUTLINES = FALSE
  2  /

Session altered.

SCOTT@10gXE> ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE
  2  /

Session altered.

SCOTT@10gXE> SET AUTOTRACE ON EXPLAIN
SCOTT@10gXE> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     6 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |     1 |     6 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"=1)

SCOTT@10gXE> SET AUTOTRACE OFF

-- Is stored outline used by RBO with cursor_sharing = SIMILAR?:

SCOTT@10gXE> ALTER SESSION SET OPTIMIZER_MODE = RULE
  2  /

Session altered.

SCOTT@10gXE> ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE
  2  /

Session altered.

SCOTT@10gXE> ALTER SESSION SET USE_STORED_OUTLINES = TRUE
  2  /

Session altered.

SCOTT@10gXE> ALTER SESSION SET CURSOR_SHARING = SIMILAR
  2  /

Session altered.

SCOTT@10gXE> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SCOTT@10gXE> SET AUTOTRACE ON EXPLAIN
SCOTT@10gXE> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     6 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |     1 |     6 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"=1)

Note
-----
   - outline "SYS_OUTLINE_06061612114532427" used for this statement

SCOTT@10gXE> SET AUTOTRACE OFF
SCOTT@10gXE> SELECT OUTLINE_CATEGORY, OUTLINE_SID
  2  FROM   V$SQL
  3  WHERE  SQL_TEXT LIKE 'SELECT * FROM test_tab WHERE col1 = 1%'
  4  /

OUTLINE_CATEGORY                                                 OUTLINE_SID
---------------------------------------------------------------- -----------
DEFAULT

SCOTT@10gXE> SELECT used, sql_text FROM user_outlines
  2  /

USED   SQL_TEXT
------ --------------------------------------------------------------------------------
...
USED   SELECT * FROM test_tab WHERE col1 = 1
...

8 rows selected.

-- show that stored outline is used by RBO with cursor_sharing = EXACT:

SCOTT@10gXE> ALTER SESSION SET CURSOR_SHARING = EXACT
  2  /

Session altered.

SCOTT@10gXE> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SCOTT@10gXE> SET AUTOTRACE ON EXPLAIN
SCOTT@10gXE> SELECT * FROM test_tab WHERE col1 = 1
  2  /

      COL1       COL2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
Plan hash value: 3962208483

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     6 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |     1 |     6 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"=1)

Note
-----
   - outline "SYS_OUTLINE_06061612114532427" used for this statement

SCOTT@10gXE> SET AUTOTRACE OFF
SCOTT@10gXE> SELECT OUTLINE_CATEGORY, OUTLINE_SID
  2  FROM   V$SQL
  3  WHERE  SQL_TEXT LIKE 'SELECT * FROM test_tab WHERE col1 = 1%'
  4  /

OUTLINE_CATEGORY                                                 OUTLINE_SID
---------------------------------------------------------------- -----------
DEFAULT

SCOTT@10gXE> SELECT used, sql_text FROM user_outlines
  2  /

USED   SQL_TEXT
------ --------------------------------------------------------------------------------
...
USED   SELECT * FROM test_tab WHERE col1 = 1
...

8 rows selected.

SCOTT@10gXE>

Report message to a moderator

Previous Topic:	making the selected value as a column name for another table
Next Topic:	load excel

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Aug 05 13:21:03 CDT 2025