Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, khalid.

Asked: September 28, 2002 - 11:56 am UTC

Last updated: December 14, 2017 - 8:29 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom
When I enter the statement set autotrace on
i get the following error.
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

How can solve it?

and we said...

Rating

  (73 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Tom

khalid, September 28, 2002 - 1:21 pm UTC

Thank you

correct your script

A reader, February 08, 2003 - 4:56 pm UTC

cd $oracle_home/rdbms/admin 
log into sqlplus as system 
run SQL> @utlxplan 
run SQL> create public public synonym plan_table for plan_table 
run SQL> grant all on plan_table to public 
exit sqlplus and cd $oracle_home/sqlplus/admin 
log into sqlplus as SYS 
run SQL> @plustrce 
run SQL> grant plustrace to public 


Tom
 Please correct create public public synonym plan_table for plan_table 
Remove extra public

Secondly why cant I run plustrce connect as system, and why should I log out and login again...

Lastly why cant I run all the above scripts connected as scott..

thanks 

Tom Kyte
February 09, 2003 - 3:11 pm UTC

removed the extra public....


you need to "log out and login" again because I want you to create the table as system -- but the grants need to be run as "sys" or "sysdba"

because

a) scott cannot create a public synonym
b) scott cannot run plustrce.sql successfully.

Why 'SYS' why not 'SYSTEM'

Nasser, February 09, 2003 - 9:48 pm UTC

Tom

I understand that SCOTT does not have necessary privileges to create public synonyms and create roles.

But system does have those abilities, so then why connect as SYS why not as system and create the public synonym, and run the plustrce.sql script.



Tom Kyte
February 09, 2003 - 9:54 pm UTC

Confusion on my part here. system does the public synonym:

You ask " so then why connect as SYS why not as
system and create the public synonym,"

well, SYSTEM created the public synonym in my example. system created the table and hence created the public synonym and granted on it.

sys runs plustrce.sql because at the top of it (the script) it says:

-- DESCRIPTION
-- Creates a role with access to Dynamic Performance Tables
-- for the SQL*Plus SET AUTOTRACE ... STATISTICS command.
-- After this script has been run, each user requiring access to
-- the AUTOTRACE feature should be granted the PLUSTRACE role by
-- the DBA.
--
-- USAGE
-- sqlplus "/ as sysdba" @plustrce
--


in order to run this, you must have been granted select on v_$ views with the ADMIN option or own the v_$ views. SYS owns them, SYSTEM doesn't have the admin option.... hence, SYSTEM cannot run this script.




All the Statistics are not showing

Arun, March 21, 2003 - 6:04 am UTC

Tom,
  I Asked my DBA to give PLUSTRACE role to me. He also enabled that. But  i find this.
SQL> select count(*) from dual;

  COUNT(*)
----------
         1


Statistics
----------------------------------------------------------
          0  recursive calls
          1  rows processed

SQL> 

Only  a part of info is shown.  He said, on checking he got all the other information also. 
What would have gone wrong.

Thanks,
Arun 

Tom Kyte
March 21, 2003 - 8:56 am UTC

I have never before seen that in my life.

if you trace autotrace, we run a query:


SELECT STATISTIC# S, NAME
FROM
SYS.V_$STATNAME WHERE NAME IN ('recursive calls','db block gets','consistent
gets','physical reads','redo size','bytes sent via SQL*Net to client',
'bytes received via SQL*Net from client','SQL*Net roundtrips to/from
client','sorts (memory)','sorts (disk)') ORDER BY S


that gets the statistic numbers for each statistic of interest. That is used to drive this report.

I cannot imagine what caused your output -- you'll have to contact support if it is happening like that.

I think this is the reason

Arun, March 21, 2003 - 10:06 am UTC

Tom,
When i log into oracle, on sqlplus from my home directory only i get this problem. When i tried from some other directory it works fine.
My DBA would be cursing me for all the trouble i gave him.
Still i donno whats the reason...

Regards,
Arun

Similar behaviour for TKPROF

Matt, March 25, 2003 - 7:40 pm UTC

I have been receiving some trace files from a customer site. My understanding was that these trace files are standalone - there is sufficient information in them to be tkprof'ed on any machine.

However, when I use for example 'tkprof ora_40714.trc outputfile=out.prf' and look at the resulting file, I do not see any 'Row Source Operation' output. eg:

Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE DAEMON
5 TABLE ACCESS FULL DAEMON

Everything else in the file seemed fine - no errors.

So, can you please clarify whether I should see this output?

BTW, I just received a tkprof'ed file from the same customer. Same problem.

Does the tkprof utility have any reason to use the PLAN_TABLE (for the parameters I've described)? I can't find anything described in the doc to this effect. I couldn't find a description of any required setup either.

Is this a known problem, or is this again, a good reason to have a chat with support?

Thanks for any help.

Tom Kyte
March 26, 2003 - 7:18 am UTC

they did not close their session before grabbing the trace file.

The STAT records are recorded only when the cursor is closed, most cursors are wisely not closed until the session disconnects.


You need to have them

o enable tkprof
o run
o END THE SESSION

then grab the trace file.

It is normal, expected behavior. You can observe it yourself with sqlplus -- trace, run a query, host out and run tkprof. Then EXIT from sqlplus and run tkprof on that same trace file.

The session should have been closed.

Matt, March 26, 2003 - 6:49 pm UTC

The trace was generated by a software module that, when started, runs in it's own session. The first thing that the module does is an alter sesssion to switch on SQL_TRACE. So, when the module is completed (this is viewable via the UI) the trace file is then retrieved.

I have tested this (described above) many times in our development environments and have never seen this before.

I will double check with the customer that the task had indeed completed before the retreival of the trace, but as teh job was scheduled to run overnight and the trace was retreived in the morning, I am not hopefull.

There is definately no interface between PLAN_TABLE and tkprof for the previous description? It may be possible the there is an eariler pre-upgrade version of the PLAN_TABLE in the site test environment.


Tom Kyte
March 26, 2003 - 7:07 pm UTC

there is no connection to the PLAN table.

that information comes from the STAT records placed into the trace file.

Dear Tom, what am I mising here?

Sami, March 11, 2004 - 1:35 pm UTC

SQL> grant connect,resource to a identified  by a;
 
Grant succeeded.
 
SQL> grant plustrace to a;
 
Grant succeeded.
 
SQL> show user
USER is "SYS"
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.3.0 - Production
 
njd05dsmdbc01 oracle idb11 /opt/oracle
$ sqlplus a/a
 
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Mar 11 13:33:31 2004
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.3.0 - Production
 
SQL> @?/rdbms/admin/utlxplan.sql
 
Table created.
 
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report




SQL> select * from user_role_privs;
 
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
A                              CONNECT                        NO  YES NO
A                              PLUSTRACE                      NO  YES NO
A                              RESOURCE                       NO  YES NO
PUBLIC                         PLUSTRACE                      NO  YES NO
 
 
 

Tom Kyte
March 11, 2004 - 2:14 pm UTC

what is

select * from dba_tab_privs where grantee = 'PLUSTRACE';



SELECT on v_$session should be granted to plustrace

rsergio, March 11, 2004 - 2:54 pm UTC

I´ve got a similar problem (as the last post) and found that SELECT on V_$SESSION is necessary to plustrace to function properly. It is not in the plstrce.sql script.

Tom Kyte
March 11, 2004 - 3:08 pm UTC

in 8i, it should look like this:

set echo on

drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$session to plustrace;
grant plustrace to dba with admin option;

set echo off


in 9i, like this:


drop role plustrace;
create role plustrace;

grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

set echo off


what could have happened is someone used the 9i plustrace in 8i to set it up. thats why I wanted the dba_tab_privs.

Working on 91

rsergio, March 11, 2004 - 3:35 pm UTC

Dear Tom,

I'm working on 9i (9.2.0.4) and I have executed plstrce.sql as SYS (the script with grants on v_$statname, v_$mystat and v_$sesstat) but the only way I could get plustrace doing its job (granting access to set autotrace on) was granting also select on v_$session. Well, something must have been very wrong! Oh, God! Time for worries have begun!

Thanks for your time and excuse my bad english.

Tom Kyte
March 11, 2004 - 3:43 pm UTC

did you use v8i sqlplus? if so, that is the issue, it (sqlplus) would be looking for different v$ views.

SQL*Plus 9.2.0.1.0

rsergio, March 11, 2004 - 4:04 pm UTC

Nope. Version of SQL*Plus is as the title says. Well, but it's not a big deal (I hope) since autotrace is accessible to developers and things are going OK. I just get a little worried because it is not a normal behavior. I've had a hope this was a documented bug or something. I would appreciate if you could shed more ligth on the subject. Anyway, life goes on. Thanks again.

Tom Kyte
March 11, 2004 - 4:16 pm UTC

that's it

9201 -> 9204

9203 and up use v$mystat
9202 and before use v$session

9201 client and lower queries v$session
9203 client and higher queries v$mystat

Great!

rsergio, March 11, 2004 - 4:26 pm UTC

Peace reigns in the land of the sun.

Thanks.

Varad, March 12, 2004 - 5:50 am UTC

Hi arun,

SQL> set autotrace on 
Unable to verify PLAN_TABLE format or existence
Error enabling EXPLAIN report

when i tried this autotrace, it gave me this error and subsequently when i run any query, it shows me this.

Statistics
----------------------------------------------------------
        680  recursive calls
         20  rows processed

This could be one of the problems you are facing.  

Tom Kyte
March 12, 2004 - 9:46 am UTC

you didn't create a plan table, thats all


@?/rdbms/admin/utlxplan

Philip Tao, March 30, 2004 - 11:24 pm UTC

Hi Tom,

I have followed the given steps to enable autotrace and granted the plustrace role to a database user called dbo.
but for some reason when i enable autotrace for the user
it still complains :

SQL> set autotrace traceonly;
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report 


When i granted the plustrace role to user HR , the autotrace worked for HR.


  

Tom Kyte
March 31, 2004 - 8:30 am UTC

look up a couple of reviews.

I believe you have slightly mismatched versions of SQLPlus and database.


9203 and up use v$mystat
9202 and before use v$session

9201 client and lower queries v$session
9203 client and higher queries v$mystat


make sure plustrace has select on v$mystat and v$session or use the same version of sqlplus as database.

Interesting scenario

Arun Mathur, April 19, 2004 - 3:06 pm UTC

Tom,

I'm running 10g on my local machine, and use it connect to other 9i instances. Interestingly enough, when I connect to a 9i instance using my 10g client, I get an error when trying to set the autotrace

(SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report)

However, when I connect to the instance using the 9i client, I am able to set the autotrace without issues, as I've always have been.

Regards,
Arun



Tom Kyte
April 19, 2004 - 3:13 pm UTC

look up one comment -- make sure you have the right grants in place.

You hit the nail on the head

Arun Mathur, April 19, 2004 - 3:30 pm UTC

I am up and running. Thank you.

Arun

Table or view not exists (v_$sesstat)

Hector Gabriel Ulloa Ligarius, April 28, 2004 - 5:53 pm UTC

Dear Tom...

  My problem is when running the file plustrce.sql :

 See :

17:38:54 SQL> @plustrce.sql
17:38:56 SQL> 
17:38:56 SQL> drop role plustrace;

Role dropped.

 real: 600
17:38:56 SQL> create role plustrace;

Role created.

 real: 60
17:38:57 SQL> 
17:38:57 SQL> grant select on v_$sesstat to plustrace;
grant select on v_$sesstat to plustrace
                *
ERROR at line 1:
ORA-00942: table or view does not exist

 real: 60
17:38:57 SQL> grant select on v_$statname to plustrace;
grant select on v_$statname to plustrace
                *
ERROR at line 1:
ORA-00942: table or view does not exist


 real: 50
17:38:57 SQL> grant select on v_$session to plustrace;
grant select on v_$session to plustrace
                *
ERROR at line 1:
ORA-00942: table or view does not exist


 real: 110
17:38:57 SQL> grant plustrace to dba with admin option;

Grant succeeded.

 real: 60
17:38:57 SQL> 
17:38:57 SQL> set echo off
17:38:57 SQL> 


 Why the views not exists? 

 I have a running another SQLs files ?


 Thanks Tom

Regards

 HGUL

PD : Sorry my english 

Tom Kyte
April 28, 2004 - 7:46 pm UTC

you are not logged in as SYS, or "as sysdba" as the script says you must be.

Table or view not exists (v_$sesstat)

Hector Gabriel Ulloa Ligarius, April 29, 2004 - 4:06 pm UTC

Thanks Tom..

Very GOD (Good)

Regards

HGUL

A reader, May 05, 2004 - 7:26 am UTC

Tom,

Please help me.
wanted to install your runstats script. i am gettingt his error

SQL>conn sys/change_on_install@test3 as sysdba
Connected.
SQL>grant select on v$mystat to scott;
grant select on v$mystat to scott
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

thanks 

Tom Kyte
May 05, 2004 - 7:55 am UTC

v_$mystat

</code> http://asktom.oracle.com/~tkyte/runstats.html <code>
see second bullet point in requirements.

SP2-0575

reader, June 09, 2004 - 10:32 am UTC

Oracle v.9.2.0.4

Have set up autotrace with no problems.
when I try to designate the execution plan report
I get the following error:
SQL> set autotrace on explain
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.

the documentation explains/action:
SP2-0575 Use of Oracle SQL feature not in SQL92 Entry |Intermediate|Full Level

Cause: A SQL statement was attempted that is not FIPS compliant. This error may also occur
if a SQL*Plus feature, for example SET AUTOTRACE, that uses SQL specific to Oracle was turned on when using FIPS flagging.

Action: Use SET FLAGGER, and turn FIPS compliance checking OFF, or rewrite the statement

can you explain what this is and how to correct.

thanks

 

Tom Kyte
June 09, 2004 - 11:28 am UTC

ops$tkyte@ORA9IR2> show flagger
flagger OFF
ops$tkyte@ORA9IR2> set autotrace on
ops$tkyte@ORA9IR2> set autotrace off

ops$tkyte@ORA9IR2> set flagger 
Usage: SET FLAGGER { OFF | ENTRY | INTERMEDIATE | FULL }

ops$tkyte@ORA9IR2> set flagger full
ops$tkyte@ORA9IR2> set autotrace on
SP2-0575: Use of Oracle SQL feature not in SQL92 Full Level.
ops$tkyte@ORA9IR2>


it is the "fips flagger", something ANSI compliant databases are supposed to have in order to be compliant.  it disallows "non standard" commands. 

Don't forget default roles

Chris, June 17, 2004 - 8:16 pm UTC

I've been pulling my hair out for the past hour reading and re-reading this post trying to figure out what I am missing. As it turns out, a vender application issued an ALTER USER statement that set DEFAULT ROLES. Since the statement specifically enumuerated each default role, all subsequent roles granted to the user, including PLUSTRACE, are non-default. As a result, these users were encountering the SP2-0618 error while others were not. Just something else to keep in mind for those of you having trouble with this feature!

Sql*plus error

Yogesh, November 17, 2004 - 1:07 pm UTC

I was executing one of the queries. I just wanted to know row count of output, so I used set autotrace traceonly and executed the query.


I got following output, with sql*plus error,

Statistics
----------------------------------------------------------
19 recursive calls
337 db block gets
62158007 consistent gets
999584 physical reads
462264 redo size
SQL*Plus internal error state 1075, context 1:5:0
Unsafe to proceed
51364157 bytes received via SQL*Net from client
462737 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
6941000 rows processed

Elapsed: 01:54:26.05

Is it due to set autotrace traceonly? or some other reason? I searched metalink but couldn't find any document.

Tom Kyte
November 17, 2004 - 2:05 pm UTC

contact support please.

A reader, November 23, 2004 - 1:10 pm UTC

Tom,

Why wouldn't plustrace work in my environment even after the dba set it (grant)? How is it working for some other user?

Tom Kyte
November 23, 2004 - 2:25 pm UTC

it is an internal (hence unexpected) error. please contact support.

Autotrace not working in 8.0.4

Yogesh, January 31, 2005 - 12:10 pm UTC

whenever I execute following I get 

SQL> set autotrace on
Cannot find the Session Identifier.  Check PLUSTRACE role is enabled Error enabling STATISTICS report


SELECT * FROM dba_tab_privs WHERE grantee = 'PLUSTRACE';

GRANTEE    OWNER    TABLE_NAME    GRANTOR    PRIVILEGE    GRANTABLE
PLUSTRACE    SYS    V_$SESSION    SYS    SELECT    NO
PLUSTRACE    SYS    V_$SESSTAT    SYS    SELECT    NO
PLUSTRACE    SYS    V_$STATNAME    SYS    SELECT    NO

what could be the problem ?  

Tom Kyte
January 31, 2005 - 12:23 pm UTC

doesn't mean that the role is actually granted to you OR enabled....

Yogesh, January 31, 2005 - 12:36 pm UTC

What should I do to enable this role?

Tom Kyte
January 31, 2005 - 12:42 pm UTC

make sure it is granted to you and if it is not "default" as they normally are, use the "set role" command to enable it.

Yogesh, January 31, 2005 - 12:51 pm UTC

I used following command to enable it 

SQL> set role all
  2  /

Role set.

SQL> set autotrace on
Error while connecting
Error enabling STATISTICS report
SQL>

I'm using OS auth for connecting. Is this cauing problem ?  

Tom Kyte
January 31, 2005 - 12:53 pm UTC

yes, won't work in that release with OS identified accounts.

alter user ops$your_name identified by foobar;

will let you 

connect /
and
connect ops$your_name/foobar

second one will work with autotrace. 

can't use ops$

Yogesh, January 31, 2005 - 1:03 pm UTC

alter user ops$tc1 identified by foobar
*
ERROR at line 1:
ORA-01918: user 'OPS$TC1' does not exist



Tom Kyte
January 31, 2005 - 1:22 pm UTC

well, whatever you are using then

Yogesh, January 31, 2005 - 1:19 pm UTC

if os_authent_prefix = "" then how to use

alter user ops$<un> identified by foobar;

Tom Kyte
January 31, 2005 - 1:42 pm UTC

you don't since you don't have ops$ 

Yogesh, February 01, 2005 - 5:59 am UTC

Is there any way we can use OS and password auth at same time?

I mean if I want to use sqlplus / or sqlplus user/pass for same user, is it possible?

Tom Kyte
February 01, 2005 - 9:00 am UTC

if you have os_authent_prefix, yes.

ops$tkyte@ORA9IR2> connect /
Connected.
ops$tkyte@ORA9IR2> connect ops$tkyte/foobar;
Connected.


else, no. 

getting another error

Jim, February 01, 2005 - 1:43 pm UTC

IN 8.1.7.4, I get this as an app owner, but not as user system:

alter session set sort_area_size=2000000;

Session altered.

SQL> set autotrace traceonly statistics;
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report

Any idea what is wrong here? 

Tom Kyte
February 01, 2005 - 3:40 pm UTC

how did you log on.
and did you change your password after logging in?

found it

Jim, February 01, 2005 - 2:19 pm UTC

I found the answer on Metalink:

I needed to set o7_dictionary_acessibility=true in my 8174 database and the problem went away

Upgrading from 9.2.0.1 to 9.2.0.6 does not upgrade PLUSTRACE

Lefteris Vakalis, March 09, 2005 - 8:00 am UTC

I have just upgraded my local database
from 9.2.0.1 to 9.2.0.6 and seems that
the "plustrace" role has not been updated.

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Mar 9 11:52:39 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

evakalis@LV920> set autot trace
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
...
system@LV920> select * from dba_tab_privs where grantee = 'PLUSTRACE';
PLUSTRACE SYS V_$SESSION SYS SELECT
PLUSTRACE SYS V_$SESSTAT SYS SELECT
PLUSTRACE SYS V_$STATNAME SYS SELECT
...
sys@LV920> grant select on V_$MYSTAT to PLUSTRACE;

Grant succeeded.
...
evakalis@LV920> set autotr trace
evakalis@LV920>

So, it must be updated manually with the correct
grants.
And of course, no one can reach to the solution
of the problem just reading the error message.
That's why "Ask Tom" is the first place to go
in research of answers to my problems with Oracle.

Regards


plan table,

A reader, October 10, 2005 - 3:27 pm UTC

I don't have access to log in as sys or system.
However, using the plan table definition, can I create the plan_table on my schema and somehow use that to generate the trace for the SQL's I run?

Thanks,


Tom Kyte
October 10, 2005 - 3:35 pm UTC

you can create the plan table in your own schema, yes.

plan table

A reader, October 11, 2005 - 7:48 am UTC

I tried creating the plan_table in my own schema and it worked. Can the plan_table in my schema be in a different name?



Tom Kyte
October 11, 2005 - 3:19 pm UTC

if you use explain plan directly - yes.
explain plan .... INTO TABLENAME ..

for autotrace, I believe it wants "plan_table" - I know of no way to have it go elsewhere.

Unable to configure autotrace on 9.2.0.5

DELABY, October 12, 2005 - 1:04 pm UTC

Hi Tom. 
First, thanks for all the time you share with us ! :)
Next, my config and my problem
I'm working on 9.2.0.5. I setup the plan as discribed in "Effective Oracle by Design" (great !) with the utils schema.

Now, I want to setup autotrace. I do as I read everywhere :

SQL> connect SYS AS SYSDBA
SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to public

No problem when I try this following as sys :

SQL> set autotrace traceonly

But when I try on an other acount

service@MEGA> set autotrace traceonly
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

I can add this as comment 
service@MEGA> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
PUBLIC                         PLUSTRACE                      NO  YES NO
SERVICE                        CONNECT                        NO  YES NO
SERVICE                        RESOURCE                       NO  YES NO

sys@MEGA> select * from dba_tab_privs where grantee = 'PLUSTRACE';

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE                                GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
PLUSTRACE                      SYS                            V_$SESSTAT                     SYS                            SELECT                                   NO  NO
PLUSTRACE                      SYS                            V_$MYSTAT                      SYS                            SELECT                                   NO  NO
PLUSTRACE                      SYS                            V_$STATNAME                    SYS                            SELECT                                   NO  NO


...
any idea please ???  

Tom Kyte
October 12, 2005 - 2:24 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5671636641855#16337894844643 <code>

read through that.


what version of SQLPLUS do you have - 8i or 9i??

Unable to configure autotrace on 9.2.0.5

DELABY, October 13, 2005 - 5:03 am UTC

Hi,

First, I used a 10g client. When I read the previous posts, I only try to setup autotrace on the server with this version
SQL*Plus: Release 9.2.0.5.0 - Production on Je Oct 13 10:22:54 2005

I tried to grant manually the v_$session with no effects. (I know, it's for 8i...)

Note that the problem is only with statistics. No problem with set autotrace trace exp.




Able to configure autotrace on 9.2.0.5 !

DELABY, October 14, 2005 - 10:38 am UTC

No answer from Tom... OK, I surely have all that I need.
Glups, ...
May be...

alter user service default role all;

Yes, it runs !

OK, sorry for time wasting...

Regards.

Same problem to 10g Client

Raj, May 07, 2006 - 7:58 am UTC

Hi Tom,

I read the above reviews for solving issues with setting the AUTOTRACE when connected as O/S authenticated users and found that the answer suggested was: 
set o7_dictionary_acessibility=true 

Is this the correct way to get round the issue?

When I connect as Scott it works fine but when I connect as O/S authenticated user it fails. I even tried using the password for the user but even that doesn't work.

Here is what I tried:

**************************
C:\Documents and Settings\Sanju>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 7 12:52:04 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set autotrace on
SQL> set autotrace off
SQL> connect /
Connected.
SQL> sho user
USER is "OPS$ARCHU\SANJU"
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> connect "OPS$ARCHU\SANJU"/sanju
Connected.
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL>
*************************************************

Please help!

Thanks

Raj
 

Tom Kyte
May 07, 2006 - 11:39 am UTC

i don't get it - once you connect scott/tiger@REMOTE_DATABASE

then you connect two times locally?  You are using fundementally different databases perhaps.


C:\oracle\product\10.1.0\db_1\sqlplus\admin>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Sun May 7 11:36:01 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

scott@ORA10GR1> set autotrace on
scott@ORA10GR1> connect /
Connected.
ops$xp10gr1\tkyte@ORA10GR1> set autotrace on
ops$xp10gr1\tkyte@ORA10GR1> show user
USER is "OPS$XP10GR1\TKYTE"
ops$xp10gr1\tkyte@ORA10GR1> connect "OPS$XP10GR1\TKYTE"/foobar;
Connected.
ops$xp10gr1\tkyte@ORA10GR1> set autotrace on
ops$xp10gr1\tkyte@ORA10GR1>



check to see that you actually have plustrace the role enabled (session_roles) and that you are using consistent sqlplus versions with database versions (older releases of sqlplus used different v$ tables then newer ones and that mismatch can do this as well) 

Autotrace problem

Raj, May 07, 2006 - 3:31 pm UTC

Hi Tom,

All the time I am connecting to the same database and there is only 1 version of SQL*Plus client on my machine.

I tried setting the PLUSTRACE role and it now works but I am not sure why the O/S authenticated user doesn't have this role set by default.

OK ..here is what I tried.
*********************
C:\Documents and Settings\Sanju>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 7 20:22:08 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> sho user
USER is "SCOTT"
SQL> select * from session_roles;

ROLE
------------------------------
PLUSTRACE
CONNECT
RESOURCE

SQL> set autotrace on
SQL> set autotrace off
SQL> connect /@orcl
Connected.
SQL> sho user
USER is "OPS$ARCHU\SANJU"
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE

SQL> set role plustrace;

Role set.

SQL> set autotrace on
SQL> set autotrace off
*********************************

I even trid granting the role explicity to the user:
********************************************
SQL> connect sys@orcl as sysdba
Enter password:
Connected.
SQL> grant plustrace to "OPS$ARCHU\SANJU";

Grant succeeded.

SQL> connect /@orcl
Connected.
SQL> sho user
USER is "OPS$ARCHU\SANJU"
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
*********************************

but even that doesn't seem to work.

Please can you explain why this is so?

Thanks

Raj 

Tom Kyte
May 07, 2006 - 4:19 pm UTC

when granted to them, or via some alter user - it was not made one of their default roles.


they must have had "alter this user and make these their default roles" at some point, just alter them again and let that role be default too



ops$tkyte@ORA10GR2> create user a identified by a;

User created.

ops$tkyte@ORA10GR2> grant connect to a;

Grant succeeded.

ops$tkyte@ORA10GR2> alter user a default role connect;

User altered.

ops$tkyte@ORA10GR2> grant plustrace to a;

Grant succeeded.

ops$tkyte@ORA10GR2> connect a/a
Connected.
a@ORA10GR2> select * from session_roles;

ROLE
------------------------------
CONNECT

a@ORA10GR2> connect /
Connected.
ops$tkyte@ORA10GR2> alter user a default role ALL;

User altered.

ops$tkyte@ORA10GR2> connect a/a
Connected.
a@ORA10GR2> select * from session_roles;

ROLE
------------------------------
CONNECT
PLUSTRACE

 

Thanks

Raj, May 07, 2006 - 4:41 pm UTC

Hi Tom,

I understand it now. Thanks for the comments, very much appreciated!

clarification on autotrace

A reader, August 24, 2006 - 1:29 am UTC

Our problem using autotrace solved by altering the user to default role all (again),
just as your above proved solution.

That I want to clarify here are :

1. You create user a with connect as the default role (in example followup).
Here is the creation script of my user :

CREATE USER RK
IDENTIFIED BY VALUES '00538F1742769E8C'
DEFAULT TABLESPACE RK
TEMPORARY TABLESPACE RK_TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for RK
GRANT RESOURCE TO RK;
GRANT CONNECT TO RK;
ALTER USER RK DEFAULT ROLE ALL;
-- 1 System Privilege for RK
GRANT UNLIMITED TABLESPACE TO RK;
-- 1 Tablespace Quota for RK
ALTER USER RK QUOTA UNLIMITED ON RK_IDX;

In time of creation, that user has all default role. (Plustrace role not yet created)

2. Long after the user was created, I create plustrace role. And when I try
to enable it (set autotrace on), the error occured.

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

We are using SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 23 11:27:35 2006

And when we try to query

RK@orcl3> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE

The problem here, we already have all default role (from user creation script above),
so why we must altering the role again.

SYSTEM@orcl3> alter user rk default role all;


Please explain, Tom.
Thanks.

Tom Kyte
August 27, 2006 - 7:36 pm UTC

1) user RK already had unlimited tablespace - granting either resource or dba does that.

and that blows away the need for quota unlimited.


2) I cannot reproduce that set of steps:

ops$tkyte%ORA10GR2> drop user rk;

User dropped.

ops$tkyte%ORA10GR2> create user rk identified by rk;

User created.

ops$tkyte%ORA10GR2> grant connect, resource to rk;

Grant succeeded.

ops$tkyte%ORA10GR2> alter user rk default role all;

User altered.

ops$tkyte%ORA10GR2> grant plustrace to rk;

Grant succeeded.

ops$tkyte%ORA10GR2> connect rk/rk
Connected.
rk%ORA10GR2> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
PLUSTRACE

so, if you run that script, what do you see? (use something other than RK if RK is important to you)

 

What’s the actual problem?

Arindam Mukherjee, September 18, 2006 - 9:27 am UTC

If I issue autotrace on, I could not get the value of parameter. Could you kindly tell me why? Please have a loot at below.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

5 rows selected.


SQL> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      9.2.0

SQL> set autotrace traceonly explain

SQL> show parameter optimizer_features_enable

SQL> show parameter optimizer_mode

SQL> set autotrace off

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      RULE

set autotrace traceonly explain

SQL> select * from v$version;

Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view

Ultimately, during autotrace on, I could not get parameter value and also contents of v$version.

It's my question - Why?
 

Tom Kyte
September 18, 2006 - 1:38 pm UTC

you are not allowed to do an explain plan on v$version - that is all that is saying.

see the error message - it says why.

Error enabling EXPLAIN report

A reader, March 23, 2007 - 6:15 am UTC

tom,

when i do this

SQL> set autot on exp

i am getting this
ERROR:
ORA-00904: "BYTES": invalid identifier

SP2-0611: Error enabling EXPLAIN report


what could be wrong
Tom Kyte
March 23, 2007 - 9:52 am UTC

sounds like you have the wrong plan_table installed.

utlxplan.sql in $ORACLE_HOME/rdbms/admin - make sure to use that structure

Why no help available

A reader, April 03, 2007 - 2:19 am UTC

Tom,

Why is there no help documented w.r.t these kind of errors in oracle documentation.
Tom Kyte
April 03, 2007 - 11:08 pm UTC

I have no idea what you mean by that.

?

Ora-904 - documented

error message, well, it actually seemed quite "self explanatory" to me?

SQL> set autot on exp

i am getting this
ERROR:
ORA-00904: "BYTES": invalid identifier


seems like maybe (just maybe) the explain plan table was - not right? you wanted explain plan on - and it said "bytes, not there..."

trc files

A reader, April 03, 2007 - 2:27 am UTC

Tom,

to get the trace files

i did

alter session set sql_trace=trueand
set autot on exp

even after doing this i am unable to find the .trc file.

please tell me the process of generating the trace files

hope my query is clear


Tom Kyte
April 03, 2007 - 11:11 pm UTC

trace files will be

a) in the user dump destination (init.ora parameter) directory if you use a dedicated server

b) in the background dump destination if you use a shared server

but, the cause is "obvious"?

A reader, April 04, 2007 - 12:06 am UTC

Tom,

Tell me the process of generating trace files
i mean the steps of generating trace files
Tom Kyte
April 04, 2007 - 10:06 am UTC

sorry, don't know what you mean - unless you are talking about setting sql_trace = true?

SQL92 levels

Cristian, June 01, 2007 - 3:57 am UTC

Tom,
i've not undertand a thing: by the error above reported SP-0575 it seems that SQL92 have three levels: Entry, Intermediate and Full. Also in a draft of SQL92 that i've found on internet there are specified three levels, but on your book "Expert Oracle Database Architecture", on page 30 you add a fourth level (transitional), why?
Compliments for your book and for your answers.
Thanks
Cristian
Tom Kyte
June 01, 2007 - 11:59 am UTC

it is like the letter Y in the English language - it is sometimes considered a vowel and sometimes not.


http://www.google.com/search?q=%22sql92+transitional%22&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a



Reader, July 10, 2007 - 4:06 pm UTC

Tom--Nice to post a question after long time.As usual I know I will get my answers here...
I have two databases(DB1 & DB2) on the same server using 9207 binaries.DB1 has 4K blocks and DB2 has 8K blocks.But I see high consistent gets for DB2 for the below query.Also it takes 16 secs on DB1 and 1.6 mins on DB2.

Below are the stats on both the databases:
<b>-------------DB1-------------</b>
select code, stream_category,name,STREAM_TYPE,
avg(GRS_gas_VOL_MSCF) as "Daily Ave", 
sum(GRS_GAS_VOL_MTD_MSCF) as "MTD Cum",
sum(GRS_GAS_VOL_YTD_MSCF) as "YTD Cum",
sum(GRS_GAS_VOL_MTD_MSCF)/ TO_CHAR ((production_day),'dd') "MTD Ave",
sum(GRS_GAS_VOL_YTD_MSCF)/TO_CHAR ((production_day),'ddd') as "YTD Ave",
sum(AVG_PRESS_PSIG) as Press_PSIG,
sum(AVG_TEMP_F) as "Temp F",
sum(EcBp_Stream_Fluid.calcGrsStdVolTotalToDay(object_id,daytime)) as FLTD
  from RV_CT_STRM_DAY_STREAM 
  where production_day='21-apr-2007' 
  and code not like 'RS_%' 
  and op_area_code ='A_BBLT'
  and stream_category like 'GAS_%'
  group by name,stream_category,stream_type,code,production_day;

10 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=230)
   1    0   SORT (GROUP BY) (Cost=11 Card=1 Bytes=230)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'STRM_VERSION' (Cost=2
          Card=1 Bytes=48)

   3    2       NESTED LOOPS (Cost=8 Card=1 Bytes=230)
   4    3         NESTED LOOPS (Cost=6 Card=1 Bytes=182)
   5    4           NESTED LOOPS (Cost=5 Card=1 Bytes=130)
   6    5             NESTED LOOPS (Cost=5 Card=1 Bytes=97)
   7    6               INDEX (UNIQUE SCAN) OF 'PK_SYSTEM_DAYS' (UNIQU
          E) (Cost=1 Card=1 Bytes=8)

   8    6               TABLE ACCESS (BY INDEX ROWID) OF 'STRM_VERSION
          ' (Cost=4 Card=1 Bytes=89)

   9    8                 INDEX (RANGE SCAN) OF 'I_STRM_VERSION_1' (NO
          N-UNIQUE) (Cost=3 Card=1)

  10    5             INDEX (UNIQUE SCAN) OF 'PK_STREAM' (UNIQUE)
  11    4           TABLE ACCESS (BY INDEX ROWID) OF 'STREAM' (Cost=1
          Card=1 Bytes=52)

  12   11             INDEX (UNIQUE SCAN) OF 'PK_STREAM' (UNIQUE)
  13    3         INDEX (RANGE SCAN) OF 'PK_STRM_VERSION' (UNIQUE) (Co
          st=1 Card=1)





Statistics
----------------------------------------------------------
     149472  recursive calls
          0  db block gets
     669363  consistent gets
          0  physical reads
          0  redo size
       2441  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
       4099  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> 


<b>---------------DB2----------------</b> 
11 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=8 Card=1 Bytes=2
          17)

   1    0   SORT (GROUP BY) (Cost=8 Card=1 Bytes=217)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'STRM_VERSION' (Cost=2
          Card=1 Bytes=45)

   3    2       NESTED LOOPS (Cost=6 Card=1 Bytes=217)
   4    3         NESTED LOOPS (Cost=4 Card=1 Bytes=172)
   5    4           NESTED LOOPS (Cost=3 Card=1 Bytes=122)
   6    5             NESTED LOOPS (Cost=3 Card=1 Bytes=90)
   7    6               INDEX (UNIQUE SCAN) OF 'PK_SYSTEM_DAYS' (UNIQU
          E) (Cost=1 Card=1 Bytes=7)

   8    6               TABLE ACCESS (BY INDEX ROWID) OF 'STRM_VERSION
          ' (Cost=2 Card=1 Bytes=83)

   9    8                 INDEX (RANGE SCAN) OF 'I_STRM_VERSION_1' (NO
          N-UNIQUE) (Cost=1 Card=1)

  10    5             INDEX (UNIQUE SCAN) OF 'PK_STREAM' (UNIQUE)
  11    4           TABLE ACCESS (BY INDEX ROWID) OF 'STREAM' (Cost=1
          Card=1 Bytes=50)

  12   11             INDEX (UNIQUE SCAN) OF 'PK_STREAM' (UNIQUE)
  13    3         INDEX (RANGE SCAN) OF 'PK_STRM_VERSION' (UNIQUE) (Co
          st=1 Card=1)





Statistics
----------------------------------------------------------
     173562  recursive calls
          0  db block gets
    2620881  consistent gets
          7  physical reads
        120  redo size
       2566  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
       5045  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL> 


Query accesses three base tables which have almost same amount of data.The execution plan is exactly the same on both databases.I also generated a 10046 trace with level 8 and I see absolutely no wait events on both.I also monitored the locks using v$segment_statistics and see no
locks.

On DB2,thinking DUAL was costing more and hence I created a IOT of DUAL following the metalink doc:363988.1.
Still I see no gain in elapsed time.Though the costs look small in 10046 file.

I wonder why the consistent gets are so high on DB2?
Is there anything I am doing wrong or missing any part?
Or is there any way to know where exactly the time is being spent?(like DBMS_PROFILER for pl/sql)

Thanks

Tom Kyte
July 10, 2007 - 8:13 pm UTC

tkprof it, it can show consistent gets by step in the plan, that'll be interesting to compare.

but based on the large number of recursive call differences - I'd say that plsql function is called "a lot less" in one case - run it without that thing and see what you see as well.

Reader, July 11, 2007 - 10:36 am UTC

Tom-here is a portion from 10046 file.Interesting is the CR values are too low compared to other database.
You are right there is a difference in recursive calls because the query has 3 additional columns in DB2(so 3 additional function calls).

Do you think just 3 additional function calls can cause this huge difference(16 secs to 1.6 mins) in elapsed time?
One more point to notice is, I see the fetch query mode blocks for DUAL table going up and down ...this number is high compared to other database.

--------On DB2 Where the query takes 1.6 mins----------
select code, stream_category,name,STREAM_TYPE, 
avg(GRS_gas_VOL_MSCF) as "Daily Ave", 
sum(GRS_GAS_VOL_MTD_MSCF) as "MTD Cum", 
sum(GRS_GAS_VOL_YTD_MSCF) as "YTD Cum", 
sum(GRS_GAS_VOL_MTD_MSCF)/ TO_CHAR ((production_day),'dd') "MTD Ave", 
sum(GRS_GAS_VOL_YTD_MSCF)/TO_CHAR ((production_day),'ddd') as "YTD Ave", 
sum(AVG_PRESS_PSIG) as Press_PSIG, 
sum(AVG_TEMP_F) as "Temp F", 
sum(EcBp_Stream_Fluid.calcGrsStdVolTotalToDay(object_id,daytime)) as FLTD 
  from RV_CT_STRM_DAY_STREAM 
  where production_day='21-apr-2007' 
  and code not like 'RS_%' 
  and op_area_code ='A_BBLT' 
  and stream_category like 'GAS_%' 
  group by name,stream_category,stream_type,code,production_day

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        1     93.75     179.95         23    2620872          0          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     93.75     179.95         23    2620872          0          11

Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 25  

Rows     Row Source Operation
-------  ---------------------------------------------------
     11  SORT GROUP BY (cr=2620872 r=0 w=0 time=183662116 us)
     11   TABLE ACCESS BY INDEX ROWID STRM_VERSION (cr=99 r=0 w=0 time=3062 us)
     35    NESTED LOOPS  (cr=79 r=0 w=0 time=2639 us)
     11     NESTED LOOPS  (cr=77 r=0 w=0 time=2152 us)
     17      NESTED LOOPS  (cr=58 r=0 w=0 time=1724 us)
     17       NESTED LOOPS  (cr=56 r=0 w=0 time=1428 us)
      1        INDEX UNIQUE SCAN PK_SYSTEM_DAYS (cr=2 r=0 w=0 time=25 us)(object id 40195)
     17        TABLE ACCESS BY INDEX ROWID STRM_VERSION (cr=54 r=0 w=0 time=1325 us)
    179         INDEX RANGE SCAN I_STRM_VERSION_1 (cr=1 r=0 w=0 time=493 us)(object id 40183)
     17       INDEX UNIQUE SCAN PK_STREAM (cr=2 r=0 w=0 time=113 us)(object id 40022)
     11      TABLE ACCESS BY INDEX ROWID STREAM (cr=19 r=0 w=0 time=297 us)
     17       INDEX UNIQUE SCAN PK_STREAM (cr=2 r=0 w=0 time=84 us)(object id 40022)
     23     INDEX RANGE SCAN PK_STRM_VERSION (cr=2 r=0 w=0 time=186 us)(object id 40182)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        7.06          7.07
********************************************************************************

select  ecbp_stream_formula.evaluatemethod('FCTY_CLASS_1',
  '0756655FAF0E0F50E0440003BA5D246D','TOTAL_THEOR_WELL_GAS_PROD',
  to_date('31.12.2005 00:00:00','dd.mm.yyyy hh24:mi:ss'),to_date('21.04.2007 
  00:00:00','dd.mm.yyyy hh24:mi:ss'))  
from
 dual


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        1     20.93      39.54          4     469731          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     20.93      39.54          4     469731          0           1

Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 25     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  INDEX FULL SCAN SYS_IOT_TOP_50480 (cr=1 r=0 w=0 time=22 us)(object id 50481)

********************************************************************************

select  ecbp_stream_formula.evaluatemethod('FCTY_CLASS_1',
  '0756655FAF0E0F50E0440003BA5D246D','TOTAL_THEOR_WELL_GAS_PROD',
  to_date('01.01.2007 00:00:00','dd.mm.yyyy hh24:mi:ss'),to_date('21.04.2007 
  00:00:00','dd.mm.yyyy hh24:mi:ss'))  
from
 dual


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        1      7.65      14.25          0     131884          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      7.65      14.25          0     131884          0           1

Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 25     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  INDEX FULL SCAN SYS_IOT_TOP_50480 (cr=1 r=0 w=0 time=20 us)(object id 50481)

********************************************************************************

select  ecbp_stream_formula.evaluatemethod('FCTY_CLASS_1',
  '0756655FAF0E0F50E0440003BA5D246D','TOTAL_THEOR_WELL_GAS_PROD',
  to_date('01.04.2007 00:00:00','dd.mm.yyyy hh24:mi:ss'),to_date('21.04.2007 
  00:00:00','dd.mm.yyyy hh24:mi:ss'))  
from
 dual


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        1      1.44       2.63          0      25014          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      1.44       2.63          0      25014          0           1

Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 25     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  INDEX FULL SCAN SYS_IOT_TOP_50480 (cr=1 r=0 w=0 time=20 us)(object id 50481)

********************************************************************************

select  ecbp_stream_formula.evaluatemethod('FCTY_CLASS_1',
  '0756655FAF0E0F50E0440003BA5D246D','TOTAL_THEOR_WELL_GAS_PROD',
  to_date('21.04.2007 00:00:00','dd.mm.yyyy hh24:mi:ss'),to_date('21.04.2007 
  00:00:00','dd.mm.yyyy hh24:mi:ss'))  
from
 dual


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        1      0.07       0.12          0       1194          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.07       0.12          0       1194          0           1

Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 25     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  INDEX FULL SCAN SYS_IOT_TOP_50480 (cr=1 r=0 w=0 time=19 us)(object id 50481)

Tom Kyte
July 11, 2007 - 1:03 pm UTC

could three additional function calls do that? Absolutely, it is your code - what does it do??

most all of the consistent gets are coming FROM these functions!!

they are the culprit, the query is fine itself.

Autotrace in 10g ...

Zahir M, April 01, 2008 - 2:52 pm UTC

The autotrace output wrongfully states that RBO is being used , when no RULE hint is being applied to it / nor the optimizer mode is set to RULE .

Please advise.

The following is from 10.2.0.3.0 version of the database .

SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
CLAIM_TYPE VARCHAR2(2)
ASSIGNMENT_TYPE VARCHAR2(2)
BENEFIT_TYPE NOT NULL VARCHAR2(2)
NETWORK_ID NUMBER(10)


SQL> select BENEFIT_TYPE from t;


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T |
----------------------------------

Note
-----
- rule based optimizer used (consider using cbo)
Tom Kyte
April 01, 2008 - 8:59 pm UTC

it is not wrong, why do you think it is?

you did not show the optimizer mode. I think you might be mistaken.

do this for us:
ops$tkyte%ORA10GR2> column p format a72
ops$tkyte%ORA10GR2> select name || '=' || value p from v$parameter where isdefault <> 'TRUE';
....


show the output.

Autotrace - Rule

Zahir M, April 02, 2008 - 9:17 am UTC

Tom ,

Here is the output .

processes=150
sga_max_size=629145600
sga_target=264241152
control_files=E:\ORACLE\PRODUCT\10.2.0\ORADATA\10.2.0\PRD\CONTROLPRD01.CTL,
E:\ORACLE\PRODUCT\10.2.0\ORADATA\10.2.0\PRD\CONTROL
PRD02.CTL, E:\ORACLE\PRODUCT\10.2.0\ORADATA\10.2.0\PRD\CONTROL
PRD03.CTL

db_block_size=8192
compatible=10.2.0.1.0
log_buffer=2854912
db_file_multiblock_read_count=16
db_recovery_file_dest=\\vsg01\RMAN\ORCL1
db_recovery_file_dest_size=214748364800
undo_management=AUTO
undo_tablespace=UNDOTBS1
remote_login_passwordfile=EXCLUSIVE
db_domain=
dispatchers=(PROTOCOL=TCP) (SERVICE=PRDXDB)
utl_file_dir=*
job_queue_processes=10
cursor_sharing=SIMILAR
audit_file_dest=E:\ORACLE\PRODUCT\10.2.0\ADMIN\PRD\ADUMP
background_dump_dest=E:\ORACLE\PRODUCT\10.2.0\ADMIN\PRD\BDUMP
user_dump_dest=E:\ORACLE\PRODUCT\10.2.0\ADMIN\PRD\UDUMP
core_dump_dest=E:\ORACLE\PRODUCT\10.2.0\ADMIN\PRD\CDUMP
db_name=PRD
open_cursors=3000
optimizer_mode=CHOOSE
_optimizer_cost_model=CHOOSE

optimizer_index_cost_adj=25
optimizer_index_caching=0
pga_aggregate_target=67108864
_gby_hash_aggregation_enabled=FALSE
Tom Kyte
April 02, 2008 - 10:11 am UTC

so, you have no statistics, optimizer is set to choose

therefore, rule based optimizer will be used. If you access objects with statistics, the cbo will be used.

what a mess that init.ora is. that you have set cursor_sharing indicates NO BINDS (huge security risk on your part there, sql injection).

setting _ parameters :(

setting optimizer_index_*

setting the db file multiblock read count :(




Ouch!

Michel Cadot, April 02, 2008 - 10:19 am UTC


And what about
utl_file_dir=*

It could really also hurt.

Regards
Michel

Tom Kyte
April 02, 2008 - 10:25 am UTC

thank you - i missed that one, the others annoyed me right off.


utl_file.fopen( '/home/oracle/oradata/yoursid', 'system.dbf', 'w' );

bamm, your dead in the water.

Dynamic Sampling

Zahir M, April 02, 2008 - 10:44 am UTC

1. If the statistics are not available ; Should not it use dynamic sampling?

2. Agree ... I raised the same concerns for the following parameters and few other packages
( like UTL_TCP , UTL_SMTP , UTL_FILE... ) . Apparently , this is the standard set by the DB architect .
Even I showed them the slide from one of your presentation about binds - not to set cursor_sharing=SIMILAR at the system level.
Still ... no luck


cursor_sharing=SIMILAR
optimizer_index_cost_adj=25
optimizer_index_caching=0
utl_file_dir=*
sql92_security =FALSE


Tom Kyte
April 02, 2008 - 12:00 pm UTC

1) no, you said CHOOSE, not ALL_ROWS (the default), not FIRST_ROWS - you said "choose" and choose defaults to RBO if no stats are present on the segments (and presuming the object can be processed by the RBO - eg: it is not partitioned for example)

2) Your DB architect needs training. Period. If an architect of buildings used the same level of thought - we would *never* enter any of their buildings (the ones still standing somehow). Sorry to sound harsh - but hey - that person is no architect)

please update autotrace article

Nicholas Sushkin, March 17, 2009 - 1:24 pm UTC

Tom,

Could you update http://asktom.oracle.com/tkyte/article1/autotrace.html

with the wording about "set role autotrace".

Thanks
Tom Kyte
March 18, 2009 - 7:52 am UTC

what about it - other than it isn't valid?

ops$tkyte%ORA10GR2> set role autotrace
  2  ;
set role autotrace
         *
ERROR at line 1:
ORA-01919: role 'AUTOTRACE' does not exist


ops$tkyte%ORA10GR2> set role connect;

Role set.


Rows in Explain Plan Vs Rows in Statistics

Rajeshwaran, Jeyabal, September 03, 2009 - 1:47 pm UTC

CREATE TABLE T1(x NUMBER CONSTRAINT T1_PK PRIMARY KEY,v VARCHAR2(100));
CREATE TABLE T2(x NUMBER CONSTRAINT T2_PK PRIMARY KEY,v VARCHAR2(100));
CREATE TABLE MAP(id NUMBER CONSTRAINT MAP_PK PRIMARY KEY,T1_x NUMBER, T2_X NUMBER);

CREATE UNIQUE INDEX MAP_IX ON MAP(T1_X,T2_X);
ALTER TABLE MAP ADD CONSTRAINT MAP_FK1 FOREIGN KEY(t1_x) references T1(X);
ALTER TABLE MAP ADD CONSTRAINT MAP_FK2 FOREIGN KEY(T2_X) REFERENCES T2(X);


INSERT /*+ APPEND */ INTO T1
SELECT ROWNUM,rpad('*',100,'*')
FROM all_objects;

INSERT /*+ APPEND */ INTO T2
SELECT ROWNUM,rpad('*',100,'*')
FROM all_objects;

INSERT /*+ APPEND */ INTO MAP
SELECT ROWNUM,ROWNUM,ROWNUM
FROM all_objects;

BEGIN
 dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T1',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,method_opt=>'for all columns size AUTO');
 dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T2',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,method_opt=>'for all columns size AUTO');
 dbms_stats.gather_table_stats(ownname=>USER,tabname=>'MAP',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,method_opt=>'for all columns size AUTO');
 dbms_stats.gather_index_stats(ownname=>USER,indname=>'T1_PK',estimate_percent=>dbms_stats.auto_sample_size);
 dbms_stats.gather_index_stats(ownname=>USER,indname=>'T2_PK',estimate_percent=>dbms_stats.auto_sample_size);
 dbms_stats.gather_index_stats(ownname=>USER,indname=>'MAP_PK',estimate_percent=>dbms_stats.auto_sample_size);
 dbms_stats.gather_index_stats(ownname=>USER,indname=>'MAP_IX',estimate_percent=>dbms_stats.auto_sample_size);
END; 


Rajesh@IRADSDB> SELECT *
  2  FROM T1, T2, MAP
  3  WHERE T1.X = MAP.T1_X
  4  AND   T2.X = MAP.T2_X
  5  AND   T1.v = 'x'
  6  AND   T2.v ='y'
  7  /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2174958959

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     1 |   249 |     3   (0)| 00:00:01 |       
|   1 |  NESTED LOOPS                 |                  |     1 |   249 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                  |     1 |   144 |     3   (0)| 00:00:01 |
|   3 |    VIEW                       | index$_join$_003 |     1 |    39 |     3   (0)| 00:00:01 |
|*  4 |     HASH JOIN                 |                  |       |       |            |          |
|   5 |      INDEX FAST FULL SCAN     | MAP_IX           |     1 |    39 |     1   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN     | MAP_PK           |     1 |    39 |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID| T1               |     1 |   105 |     0   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN         | T1_PK            |     1 |       |     0   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS BY INDEX ROWID | T2               |     1 |   105 |     0   (0)| 00:00:01 |
|* 10 |    INDEX UNIQUE SCAN          | T2_PK            |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   4 - access(ROWID=ROWID)
   7 - filter("T1"."V"='x')
   8 - access("T1"."X"="MAP"."T1_X")
   9 - filter("T2"."V"='y')
  10 - access("T2"."X"="MAP"."T2_X")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        776  consistent gets
          0  physical reads
       8748  redo size
        583  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed               

Tom,

I am really confused with this. Can you please help me.
The query returns no records. But looking at the number of rows in Execution Plan it is 1 while in the Statistics it is 0.
why this is happening even my query is using CBO. I am working with Oracle 10g R2.
Am i interpreting Thing wrong here. If yes please correct me.
Tom Kyte
September 04, 2009 - 3:36 pm UTC

cardinalities in an explain plan are ESTIMATES

we *never* estimate zero, we always estimate at least one.


it is normal, expected, correct

Cardinality in Tkprof.

Rajeshwaran, Jeyabal, September 06, 2009 - 9:40 am UTC

Thanks Tom, The cardinality in TKPROF seems to be correct.
SELECT *
FROM T1,
  T2,
  MAP
WHERE T1.X = MAP.T1_X
AND T2.X   = MAP.T2_X
AND T1.v   = 'x'
AND T2.v   ='y'

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        1      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 70  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  NESTED LOOPS  (cr=3 pr=0 pw=0 time=39 us)
      0   NESTED LOOPS  (cr=3 pr=0 pw=0 time=35 us)
      0    TABLE ACCESS BY INDEX ROWID T1 (cr=3 pr=0 pw=0 time=31 us)
      0     INDEX RANGE SCAN T_IND (cr=3 pr=0 pw=0 time=27 us)(object id 89149)
      0    TABLE ACCESS BY INDEX ROWID MAP (cr=0 pr=0 pw=0 time=0 us)
      0     INDEX RANGE SCAN MAP_IX (cr=0 pr=0 pw=0 time=0 us)(object id 89147)
      0   TABLE ACCESS BY INDEX ROWID T2 (cr=0 pr=0 pw=0 time=0 us)
      0    INDEX UNIQUE SCAN T2_PK (cr=0 pr=0 pw=0 time=0 us)(object id 89144)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.01          0.01

Tom Kyte
September 06, 2009 - 9:58 am UTC

that is not the estimated cardinality.

that is the ACTUAL OBSERVED RESULTS from that execution. they are 100% correct, they are what happened.

Now, you compare that to the plan - to see if the estimate (plan) is near actual (row source operation)

Don Seiler, September 30, 2009 - 2:50 pm UTC

Seems like the page at http://asktom.oracle.com/tkyte/article1/autotrace.html has been removed. Is there a new home for this?

URL not working

Benny, November 05, 2009 - 4:17 pm UTC

The URL from the original response is not valid

http://asktom.oracle.com/~tkyte/article1/autotrace.html

is there an updated URL?
Tom Kyte
November 11, 2009 - 1:16 pm UTC

goto homepage, i moved things there - but autotrace is documented in the documentation well enough now so I did not replicate that old page


http://docs.oracle.com/docs/cd/B19306_01/server.102/b14357/ch8.htm#sthref1499

A reader, December 13, 2009 - 5:18 pm UTC

Sir, whenever I start autotrace I always see:
SQL> set autotrace on;
ERROR:
ORA-00904: "BYTES": invalid identifier

Oracle version is :9.2.0.6.0

I also recreated autotrace like below:


SQL> @?/rdbms/admin/utlxplan
create table PLAN_TABLE (
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


$ORACLE_HOME/sqlplus/admin/plustrce

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce
SQL>
SQL> drop role plustrace;

Role dropped.

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off
SQL>

SQL> set autotrace on;
ERROR:
ORA-00904: "BYTES": invalid identifier


SP2-0611: Error enabling EXPLAIN report
SQL> select sysdate from dual;

SYSDATE
--------------
20091214121248


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        495  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


 

Tom Kyte
December 14, 2009 - 9:11 am UTC

ops$tkyte%ORA9IR2> @?/rdbms/admin/utlxplan

Table created.

ops$tkyte%ORA9IR2> set autotrace on
ops$tkyte%ORA9IR2> set autotrace off
ops$tkyte%ORA9IR2> alter table plan_table drop column bytes;

Table altered.

ops$tkyte%ORA9IR2> set autotrace on
ops$tkyte%ORA9IR2> select * from dual;

D
-
X


Execution Plan
----------------------------------------------------------
ERROR:
ORA-00904: "BYTES": invalid identifier






Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        402  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed




looks like your plan_table is botched.

You did see the "name is already used" error right?

try recreating your plan_table - which would involve dropping it (make sure it is really a plan table and not a table a developer is using to store, say, project plans or something in)

A reader, December 23, 2009 - 4:28 pm UTC

Respected Sir;

...that is not true.

sir assume DMT.

Assume table a has extents 1m,1.1m,1.2m,..
Table b has extents 10m,11m,12m,..

Assume I drop table a. As far as I know coalesce doesnt automatically happen in DMT.
Conside I didnt issue coalesce statement.

so when I table b needs an extent of 13mb, the free extents of table a would never be used.
Therefore they will be wasted.
am I wrong?
Tom Kyte
December 31, 2009 - 2:19 pm UTC

reader - I don't know what "that is not true" refers to.

... As far as I know coalesce doesnt automatically happen in
DMT.
...

automatically at some fixed point in time, in the future, by a background process - manually - later.


... so when I table b needs an extent of 13mb, the free extents of table a would
never be used.
...

You cannot say that, you have a series there - 1, 1.1, 1.2, ... (dot dot dot, and so on and so on).

If the first 10 extents of table A are contiguous, when you drop A, that would be 14.5mb - it would fit.


But in short, stop using dictionary managed, or if you do either

a) use initial=next and pctincrease = 0 (emulates a locally managed tablespace with uniform extents)

b) use initial =64k, pctincrease = 100 (sort of like system allocated extents)


and stop worrying about it.



... Therefore they will be wasted. ...

that in general would not be true, think about it please, there would be other segments in that tablespace - they would likely be used for something at some point.

A reader, January 04, 2010 - 2:21 pm UTC

Respected sir;
You have been really helpfull.
My final question :)

1-)
...automatically at some fixed point in time, in the future, by a background process - manually - later.

In DMT, How does coalesce happen automatically?


2-)
...You cannot say that, you have a series there - 1, 1.1, 1.2, ... (dot dot dot, and so on and so on).

...If the first 10 extents of table A are contiguous, when you drop A, that would be 14.5mb - it would fit.

If oracle combines the contiguous extents in DMT automatically, there is no need to manually execute "coalesce" statement.
Am I right?

Tom Kyte
January 04, 2010 - 4:23 pm UTC

1) smon does it.

2) in versions created in this century and near the end of last century - yes. In really old (7.ish) versions, the alter coalesce could be used to force it to happen "right then and there", you might have done this in the olden days before creating a new table immediately after dropping an old one.

link not working

Sasidhar, April 07, 2010 - 2:15 pm UTC

Hi tom,
The url http://asktom.oracle.com/~tkyte/article1/autotrace.html provided is not working please provide a correct one.

whem i am trying to do

SQL> set autot on exp
ERROR:
ORA-00904: "BYTES": invalid identifier


SP2-0611: Error enabling EXPLAIN report
SQL>


why is this error
Tom Kyte
April 13, 2010 - 7:34 am UTC

drop your plan table and create the one for the current release - recreate your plan table, it is in a very old format.

run @?/rdbms/admin/utlxplan

to create it.

ROWNUM and EXISTS

Rajeshwaran, Jeyabal, October 23, 2010 - 4:05 am UTC

test@IRDST1> select index_name, column_name, column_position
  2  from user_ind_columns
  3  where index_name ='IX_IR_TEST_04'
  4  order by 3;

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
IX_IR_TEST_04             NEW_MAX_DT                                   1
IX_IR_TEST_04             NEW_MIN_DT                                   2
IX_IR_TEST_04             ENC_KEY                                    3
IX_IR_TEST_04             MEMBER_ID                                    4
IX_IR_TEST_04             MEMBER_SRC_CD                                5
IX_IR_TEST_04             ENC_NBR                                    6
IX_IR_TEST_04             DATA_SRC_KEY                                 7
IX_IR_TEST_04             DENIAL_CD                                    8
IX_IR_TEST_04             CONTRACT_ID                                  9
IX_IR_TEST_04             LOB_KEY                                     10
IX_IR_TEST_04             ENC_STATUS_KEY                              11

11 rows selected.

Elapsed: 00:00:00.84
test@IRDST1>
test@IRDST1>
test@IRDST1>
test@IRDST1> select index_name, column_name, column_position
  2  from user_ind_columns
  3  where index_name ='IX_IR_TEST_020'
  4  /

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
IX_IR_TEST_020            CONTRACT_ID                                 1

Elapsed: 00:00:00.90
test@IRDST1>


The statistics present in the table and Index are stale so thought of using dynamic sampling hint and Benchmarking results from Test Instance below (Test instance is just Exact copy of production Instance)

Query -1
test@IRDST1> select /*+ dynamic_sampling(ir_test,3) */ 1
  2  from ir_test
  3  where CONTRACT_ID = 4
  4  and rownum = 1
  5  /

Elapsed: 00:00:01.90

Execution Plan
----------------------------------------------------------
Plan hash value: 4145087313

-----------------------------------------------------------------------------------------
| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                     |     1 |     4 |  4207   (2)| 00:00:51 |
|*  1 |  COUNT STOPKEY    |                     |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IX_IR_TEST_020   |  3355K|    12M|  4207   (2)| 00:00:51 |
-----------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   2 - access("CONTRACT_ID"=4)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        219  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Query -2
test@IRDST1> select 1 from dual
  2  where exists (
  3    select /*+ dynamic_sampling(ir_test,3) */ 1
  4    from ir_test
  5    where CONTRACT_ID = 4
  6    and rownum = 1
  7  )
  8  /

Elapsed: 00:00:01.95

Execution Plan
----------------------------------------------------------
Plan hash value: 3363643798

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |       |     3 (-100| 00:00:01 |    |  |        |      |            |
|*  1 |  FILTER                     |                    |       |       |            |          |    |  |        |      |            |
|   2 |   FAST DUAL                 |                    |     1 |       |     2   (0)| 00:00:01 |    |  |        |      |            |
|*  3 |   COUNT STOPKEY             |                    |       |       |            |          |    |  |        |      |            |
|   4 |    PX COORDINATOR           |                    |       |       |            |          |    |  |        |      |            |
|   5 |     PX SEND QC (RANDOM)     | :TQ10000           |     2 |     8 |     1   (0)| 00:00:01 |    |  |  Q1,00 | P->S | QC (RAND)  |
|*  6 |      COUNT STOPKEY          |                    |       |       |            |          |    |  |  Q1,00 | PCWC |            |
|   7 |       PX PARTITION RANGE ALL|                    |     2 |     8 |     1   (0)| 00:00:01 |     1 |    50 |  Q1,00 | PCWC |            |
|*  8 |        INDEX FULL SCAN      | IX_IR_TEST_04   |     2 |     8 |     1   (0)| 00:00:01 |     1 |    50 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "ir_test" "ir_test" WHERE ROWNUM=1 AND "CONTRACT_ID"=4))
   3 - filter(ROWNUM=1)
   6 - filter(ROWNUM=1)
   8 - access("CONTRACT_ID"=4)
       filter("CONTRACT_ID"=4)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         12  recursive calls
          0  db block gets
        657  consistent gets
          1  physical reads
          0  redo size
        218  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         60  sorts (memory)
          0  sorts (disk)
          1  rows processed

test@IRDST1> SELECT partitioned, degree
  2  FROM user_indexes
  3  WHERE index_name ='IX_IR_TEST_04'
  4  /

PAR DEGREE
--- ----------------------------------------
YES 4

test@IRDST1> SELECT partitioned, degree
  2  FROM user_indexes
  3  WHERE index_name ='IX_IR_TEST_020'
  4  /

PAR DEGREE
--- ----------------------------------------
NO  4


IX_IR_TEST_04 is Global Range partitioned on NEW_MAX_DT column.

Questions

1) Why the Optimizer considers Index IX_IR_TEST_04 ( having CONTRACT_ID in non-leading portion) to be better than Index IX_IR_TEST_020 ( having CONTRACT_ID in leading portion) in Query-2?

2) Query-1 INDEX RANGE SCAN (IX_IR_TEST_020) fetches 3355K rows took only 4 Logical IO. Query-2 INDEX FULL SCAN (IX_IR_TEST_04) fetches 2 rows from Index segment by scanning all partitioned segments took 657 Logical IO. Is that 657 IO is due to Scanning all partitioned segments?

3)From the above two query, which would you consider better and why? Is that the Query-1 having 4 logical IO and cost 4207 or Query-2 having 657 logical IO and cost 3

Tom Kyte
October 25, 2010 - 5:34 pm UTC

tell me about statistics in place here - looks like _04 might have wacky stats on it.


3) obviously the one that just does 4 io's

'trick' to use in 9i

Rajeshwaran, Jeyabal, June 18, 2011 - 11:51 am UTC

Tom Kyte
June 20, 2011 - 9:51 am UTC

how often are you going to do that.

And why the heck would you ask this on this question - when it OBVIOUSLY should be asked on the other page???? Seems a big strange to find something you want to do on page 1 and go to a random page 2 to ask about it doesn't it?

A reader, December 30, 2011 - 12:42 am UTC

Hi Tom,

I am getting below error.

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as DEV3HOST

SQL> set autotrace on;
Cannot SET AUTOTRACE

Please help.I am very new to this.

Tom Kyte
December 30, 2011 - 10:58 am UTC

I doubt you are getting just that - is that a direct cut and paste?

Has your DBA made it so you can use autotrace - installed the role and granted it to you?

http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm#sthref884

Tuning

A reader, December 30, 2011 - 11:27 am UTC

Hi Tom,

Yes i got it now.Previously we use simply look at plan by pressing F5 for tuning and try to reduce the cost.so using sqltrace is better option for tuning???

how can i know if we have tkprof utility using 10.2.0 version of oracle ???

Thanks alot
Tom Kyte
December 30, 2011 - 11:43 am UTC

everyone has tkprof - it comes with every release of the database.


sql trace shows you what actually happened, real world information.


explain plan shows you what might or might not happen, and what we guessed would be the amount of data processed, which might or might not be right.

A reader, December 30, 2011 - 12:13 pm UTC

Hi Tom,

Thanks alot.

If you any doc for installation/setup tkprof can you please share.
Tom Kyte
December 30, 2011 - 4:32 pm UTC

Thanks

A reader, January 01, 2012 - 10:00 pm UTC

Thanks alot Tom now i am able to use tkprof.

But i am facing below error for sql trace :

Error: cannot fetch plan for statement_id 'PLUS4294967295'

but statistics is showing but not explain plan.Why ???
Please help.

Tom Kyte
January 02, 2012 - 8:31 am UTC

explain where and when you are getting this error, what 'command' are you running in order to get that error.

A reader, January 02, 2012 - 11:34 pm UTC

Hi Tom,

I am runing command in linux.Oracle Version 10.2.0

sqlplus / as sysdba
SQL > set autotrace trace only ;
sql > select * from temp_test ; 

Execution Plan
----------------------------------------------------------
Error: cannot fetch plan for statement_id 'PLUS4294967295'


Statistics
----------------------------------------------------------
         12  recursive calls
         29  db block gets
        212  consistent gets
          0  physical reads
       2248  redo size
        839  bytes sent via SQL*Net to client
       1773  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

Tom Kyte
January 03, 2012 - 11:32 am UTC

do not do anything as sysdba

stop using sysdba

sysdba is special

sysdba is magic

sysdba is nothing to be used except for things like upgrading a database, backup, recovery. It shouldn't even be used for starting and stopping the database (sysoper is sufficient for that)


stop using sysdba

create your own account and use that.


sysdba works differently from other accounts. Using sysdba is like using root as your own personal account, no one would ever do that (and if they did - well, they deserve what they get - which will be a mess someday)

Auto Trace link is not working

Habib Memon, May 09, 2012 - 7:30 am UTC

Dear Tom,

Link http://asktom.oracle.com/~tkyte/article1/autotrace.html
is not working.

Regards

the like out

Talal, May 15, 2017 - 4:55 pm UTC

the like not work !
Many thanks

Broken Link

Nataraj, December 13, 2017 - 9:56 am UTC

Connor McDonald
December 14, 2017 - 8:29 am UTC

Go to Resources => Presentation Downloads and search for "autotrace"

SQL

SACHIN, March 10, 2018 - 4:06 am UTC

THIS ANS WAS CORRECT.