Thank you

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

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,
  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,
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

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.

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.

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
 
 
 

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.

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.

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.

Peace reigns in the land of the sun.

Thanks.

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.  

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,

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

I am up and running. Thank you.

Arun

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 

Thanks Tom..

Very GOD (Good)

Regards

HGUL

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 

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

 

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. 

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!

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,

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

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 ?  

What should I do to enable this role?

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 ?  

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. 

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

if os_authent_prefix = "" then how to use

alter user ops$<un> identified by foobar;

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

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?

if you have os_authent_prefix, yes.

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


else, no. 

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? 

I found the answer on Metalink:

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

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

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,

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?

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 ???  

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.

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.

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
 

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) 

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 

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

 

Hi Tom,

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

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.

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)

 

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,

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,

Why is there no help documented w.r.t these kind of errors in oracle documentation.

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,

Tell me the process of generating trace files
i mean the steps of generating trace files

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--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-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)

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)

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

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

And what about
utl_file_dir=*

It could really also hurt.

Regards
Michel

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,

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

with the wording about "set role autotrace".

Thanks

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.

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.

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

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

The URL from the original response is not valid

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

is there an updated URL?

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


 

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

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?

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?

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

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:

Can you please share the Trick for 9i?

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3083286970877#977351300346962326

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.

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

Hi Tom,

Thanks alot.

If you any doc for installation/setup tkprof can you please share.

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.

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

Dear Tom,

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

Regards

the like not work !
Many thanks

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

THIS ANS WAS CORRECT.