Running tkprofs but missing prof$plan table, what is that ?: Oracle, Sql, TkProfs

by: AndytwPosted on 2009-10-14 at 15:09:21ID: 25575724

The PROF$PLAN_TABLE table is what TKPROF uses to write the execute plan information before it writes to the output file, when TKPROF is run with the EXPLAIN command argument.

If the table doesn't exist then it is created automatically by TKPROF, then dropped at the end. As such the specified user (e.g. EXPLAIN=scott/tiger) must be able to issue CREATE TABLE and DROP TABLE statements. The specified user must be able to issue INSERT, SELECT, and DELETE statements against this table.

Your error suggests that the user doesn't have the privileges above. Can you check the privileges of the specified user (PROF$)?

by: AndytwPosted on 2009-10-14 at 15:10:29ID: 25575730

Sorry, typo there. I meant check the privileges of the specified user (IMEDW)

by: AndytwPosted on 2009-10-14 at 15:19:09ID: 25575786

Issue the following grant:

GRANT CREATE TABLE TO OPS$IMEDW
/

by: sdstuberPosted on 2009-10-14 at 15:36:51ID: 25575888

as a workaround, try NOT using the EXPLAIN option.

It's sort of misleading anyway.
The "real" plan will already be in the tkprof output,

The EXPLAIN option generates a new plan for each statement which may, or may not, be the same as what really ran.

by: williampeckPosted on 2009-10-14 at 15:56:50ID: 25576036

Ok, thanks for the expl. I will confirm on Thurs

by: williampeckPosted on 2009-10-15 at 06:44:52ID: 25580428

sdstuber, you said:
>>
as a workaround, try NOT using the EXPLAIN option. It's sort of misleading anyway.
The "real" plan will already be in the tkprof output, The EXPLAIN option generates a new plan for each statement which may, or may not, be the same as what really ran.
<<

I don't understand -->"the 'real' plan will already be in the tkprof output . . . "
- in the output it says this:

Error in CREATE TABLE of EXPLAIN PLAN table: OPS$IMEDW.prof$plan_table
ORA-01031: insufficient privileges

EXPLAIN PLAN option disabled.

and the output is below in code snippet, hope the formatting sticks.

********************************************************************************
 
UPDATE ODS.MEM_MEMB_SUB_GRP_PRODT SET ODS_STUS_CD = 'L' 
WHERE
 MEMB_SUB_GRP_PRODT_SEQNO = :B1 
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 1420726    395.22     672.30      12727    5708353   11668184     1420726
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   1420727    395.22     672.31      12727    5708353   11668184     1420726
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 1548     (recursive depth: 1)
********************************************************************************

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

Select all Open in new window

by: sdstuberPosted on 2009-10-15 at 07:03:06ID: 25580602

what I meant by "the real plan" is, the trace will contain the plan that was executed when the query was run

here's a simple example I just ran in a test database...

exec dbms_monitor.session_trace_enable
select * from user_tables;
exit

then I tkprof'd the generated trace file

tkprof sds11_ora_3776.trc eetest.out (no explain or other options)

and here's an excerpt of that output
notice the plan (row source operations) is already in the output

SQL ID: dtbhjabjx3v1u
Plan Hash: 2428392710
select *
from
 user_tables
 
 
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        4      0.12       0.09          0       2458          0          37
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.12       0.09          0       2458          0          37
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 81
 
Rows     Row Source Operation
-------  ---------------------------------------------------
     37  HASH JOIN  (cr=2458 pr=0 pw=0 time=0 us cost=826 size=18011754 card=7757)
   2044   FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=0 us cost=0 size=201500 card=100)
     37   MERGE JOIN CARTESIAN (cr=2458 pr=0 pw=0 time=0 us cost=826 size=2381399 card=7757)
     37    HASH JOIN RIGHT OUTER (cr=2458 pr=0 pw=0 time=0 us cost=696 size=1954764 card=7757)
   8231     TABLE ACCESS FULL SEG$ (cr=246 pr=0 pw=0 time=0 us cost=67 size=370395 card=8231)
     37     HASH JOIN RIGHT OUTER (cr=2212 pr=0 pw=0 time=0 us cost=628 size=460161 card=2223)
     99      TABLE ACCESS FULL USER$ (cr=7 pr=0 pw=0 time=0 us cost=3 size=1584 card=99)
     37      HASH JOIN OUTER (cr=2205 pr=0 pw=0 time=0 us cost=624 size=424593 card=2223)
     37       HASH JOIN OUTER (cr=1931 pr=0 pw=0 time=0 us cost=553 size=406809 card=2223)
     37        HASH JOIN  (cr=1146 pr=0 pw=0 time=0 us cost=354 size=340119 card=2223)
      9         TABLE ACCESS FULL TS$ (cr=15 pr=0 pw=0 time=0 us cost=5 size=171 card=9)
     37         NESTED LOOPS  (cr=1131 pr=0 pw=0 time=0 us cost=348 size=297882 card=2223)
    270          TABLE ACCESS FULL OBJ$ (cr=950 pr=0 pw=0 time=20000 us cost=261 size=80028 card=2223)
     37          TABLE ACCESS CLUSTER TAB$ (cr=181 pr=0 pw=0 time=0 us cost=1 size=98 card=1)
    104           INDEX UNIQUE SCAN I_OBJ# (cr=74 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 3)
  71408        INDEX FAST FULL SCAN I_OBJ2 (cr=785 pr=0 pw=0 time=0 us cost=199 size=2142270 card=71409)(object id 37)
  71408       INDEX FAST FULL SCAN I_OBJ1 (cr=274 pr=0 pw=0 time=0 us cost=70 size=571272 card=71409)(object id 36)
     37    BUFFER SORT (cr=0 pr=0 pw=0 time=0 us cost=759 size=55 card=1)
      1     FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=0 us cost=0 size=55 card=1)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  SQL*Net message from client                     4        1.76          4.79
********************************************************************************

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:

Select all Open in new window

by: williampeckPosted on 2009-10-15 at 07:32:31ID: 25580905

sdstuber.

so are you saying the EXPLAIN PLAN that I am missing (due to the error in creating the prof$plan table) is just a simple "oh, here's how Oracle WILL handle this", while what your stats are showing is "here's how Oracle REALLY handled this" ?

if so, then I should skip all of my "options" ?, which are:
sort='(prsela,exeela,fchela)'

btw, that's a nasty plan explanation for a simple query, no ?

good stuff, thanks

by: sdstuberPosted on 2009-10-15 at 07:59:28ID: 25581231

the sort options are handy, saves you from having to search for the query with the most executions or fetches.

It's only the explain option that requires reconnecting to the db. Your explanation is correct, with some caveats. It's how Oracle will handle it IF it ran right now as the explain plan user with the db settings as they are now for the database you are connecting to - the db, the user and the settings could be different from what the environment as it was when the trace file was generating.

user_tables is actually a view, and the plan reveals stuff under the view required to support it.

by: williampeckPosted on 2009-10-15 at 08:57:55ID: 25581893

Ok, sounds good, thanks

by: williampeckPosted on 2009-10-16 at 07:27:58ID: 31641345

big help from both of you.

as a lone oracle developer, EE makes me look VERY SMART to management, he he

Question

Running tkprofs but missing prof$plan table, what is that ?

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Oracle

Sql

TkProfs

Oracle Database

Oracle 10.x

SQL Query Syntax

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press