OraFAQ Forum: Performance Tuning » Help - Creating Stored Outlines

Home » RDBMS Server » Performance Tuning » Help - Creating Stored Outlines

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

Help - Creating Stored Outlines [message #267515]

Thu, 13 September 2007 10:43

kumarsz
Messages: 180
Registered: March 2005
Location: canada

Senior Member

Hi,

Can i use following syntax to create stored outline for existing query in a stored procedure in Oracle 9i R2
(and the statement in v$sql - i can see the hash_value in statspack)

DBMS_OUTLN.create_outline(
hash_value => 3909283366,
child_number => 0,
);

Also please let me know why the Row Source Generation and Explain Plan are showing different paths in the following test i carried on 9i database.


Session -1
***********

create table obs5 as select * from all_objects;

create index obs5x on obs5(object_id);

exec dbms_stats.gather_table_stats('PRATAP','OBS5',CASCADE=>TRUE);


confirmed that following statement is using index path
SELECT * FROM OBS5 WHERE OBJECT_ID=:b;

Executed in another session and traced with 10046
Session -2
***********


SELECT *
FROM
 OBS5 WHERE OBJECT_ID=:b


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          6          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.00          0          6          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 128

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID OBS5
      0   INDEX RANGE SCAN OBS5X (object id 396809)

Session -1
***********

created stored outline now

ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE;
SELECT * FROM OBS5 WHERE OBJECT_ID=:b;
ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

UPDATE OBS5 SET OBJECT_ID=6; (all rows with same value for forcing FTS)

exec dbms_stats.gather_table_stats('PRATAP','OBS5',CASCADE=>TRUE);

confirmed that following statement is doing FTS now
SELECT * FROM OBS5 WHERE OBJECT_ID=:b;


Session -2 
***********

SELECT *
FROM
 OBS5 WHERE OBJECT_ID=:b


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     2600      0.19       1.21        528       3094          0       38983
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2602      0.19       1.21        528       3094          0       38983

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 128  (PRATAP)

Rows     Row Source Operation
-------  ---------------------------------------------------
  38983  TABLE ACCESS FULL OBS5


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
  38983   TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'OBS5'


Session -2 with following alter session now
***********

ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE;

SELECT *
FROM
 OBS5 WHERE OBJECT_ID=:b


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     2600      0.14       0.77        134       5808          0       38983
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2602      0.14       0.77        134       5808          0       38983

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 128  (PRATAP)

Rows     Row Source Operation
-------  ---------------------------------------------------
  38983  TABLE ACCESS BY INDEX ROWID OBS5
  38983   INDEX RANGE SCAN OBS5X (object id 396809)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
  38983   TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'OBS5'

Please suggest

Thanks and Regards,
Pratap

Report message to a moderator

Re: Help - Creating Stored Outlines [message #268415 is a reply to message #267515]

Tue, 18 September 2007 08:46

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

What is the datatype of OBJECT_ID column in OBS5 table?
What is the datatype of your parameter(it's probably coming from some PL/SQL proc/function)?

If OBJECT_ID datatype is VARCHAR2 and parameters datatype is NUMBER then try:

SELECT * FROM OBS5 WHERE OBJECT_ID=TO_CHAR(:b);

Michael

Report message to a moderator

Previous Topic:	outer join problem
Next Topic:	Query Tuning Problem (tkprof o/p,query & explain plan attached)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 08:44:26 CDT 2024