Re: please help with stored outlines

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 13 Jun 2003 11:54:40 -0700
Message-ID: <130ba93a.0306131054.8e942ef@posting.google.com>

"Ryan" <rgaffuri_at_cox.net> wrote in message news:<AafGa.13477$Id7.1122_at_news2.east.cox.net>...

> yes. I believe that is in the original post.
No you did not mention you session setting.

> I get the impression that the hash code will be different because the tables
> are in two different tablespaces and there is no way to use the same plan on
> these two tables? Even though they have the same name, but are in different
> tablespaces.

Forget about the schema and tablespace stuff. Outlines are sotred in the tables owned by "outln". You specify you want to use outline, you run a query, ORACLE will dutifully compare your SQL text with those stored in the outline tables. If they match, stored plan is used. It is that simple.

Something was not done right.
*How did you move your outlines data into another database? This is typically done by truncating the destination outline tables and then import from the source database. I used to send a outline patch to my customers. I had no idea what schema name or tablespace names ther were using. They don't matter.
* What is your CURSOR_SHARING setting? "FORCE" will disbale outlines as I recall.

If you still have any doubt...

SQL> create table tyu as select object_id, object_name, object_type from dba_objects
2 where rownum < 100;

Table created.

SQL> alter table tyu add constraint tyu_pk primary key (object_id);

Table altered.

SQL> alter session set create_stored_outlines=upd1;

Session altered.

SQL> update tyu set object_name='For outline' where object_id=9849;

1 row updated.

SQL> alter session set create_stored_outlines=false;

Session altered.

SQL> commit;

Commit complete.

Verify outline has been created. SQL> select sql_text,name,owner, category from dba_outlines where category='UPD1';

SQL_TEXT

NAME                           OWNER
------------------------------ ------------------------------

CATEGORY

update tyu set object_name='For outline' where object_id=9849 SYS_OUTLINE_030613105212120 JYANG
UPD1 SQL> grant select on tyu to jtest;

Grant succeeded.

Change TBS for sotring the test table. SQL> alter user jtest default tablespace example;

User altered.

SQL> alter user jtest quota unlimited on example;

User altered.

SQL> conn jtest/jtest_at_athena
Connected.
SQL> create table tyu as select * from jyang.tyu;

Table created.

SQL> alter table tyu add constraint tyu_pk primary key (object_id);

Table altered.

Notice that OUTLINE_CATEGORY is null SQL> select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%';

SQL_TEXT

OUTLINE_CATEGORY

select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%'

update tyu set object_name='For outline' where object_id=9849

SQL> alter session set use_stored_outlines=upd1;

Session altered.

SQL> update tyu set object_name='For outline' where object_id=9849;

1 row updated.

note that OUTLINE_CATEGORY has been chnaged to UPD1 SQL> select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%';

SQL_TEXT

OUTLINE_CATEGORY

select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%9849%'

update tyu set object_name='For outline' where object_id=9849 UPD1

Jusung Yang

Received on Fri Jun 13 2003 - 13:54:40 CDT