Home » RDBMS Server » Performance Tuning » Help - Creating Stored Outlines
Help - Creating Stored Outlines [message #267515] Thu, 13 September 2007 10:43 Go to next message
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
Re: Help - Creating Stored Outlines [message #268415 is a reply to message #267515] Tue, 18 September 2007 08:46 Go to previous message
michael_bialik
Messages: 611
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
Previous Topic: outer join problem
Next Topic: Query Tuning Problem (tkprof o/p,query & explain plan attached)
Goto Forum:
  


Current Time: Wed Dec 07 16:13:51 CST 2016

Total time taken to generate the page: 0.10665 seconds