Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: October 26, 2000 - 5:37 am UTC

Last updated: August 27, 2002 - 9:21 pm UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

Hi, Tom

How can I get permission to user to use autotrace.
I have created user and graned him plustrace role,
select any table, select any sequence, roles connect and select_any_catalog. After set autotrace on came error:

SQL> select count(*) from track;

COUNT(*)
----------
1153129

ERROR:
ORA-01031: insufficient privileges


SP2-0612: Error generating AUTOTRACE EXPLAIN report

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

I want not to grant dba role to user.

Thank you, Oleg


and Tom said...

Sounds like you have a common PLAN_TABLE in your database and this user can SELECT from it but not INSERT.

For example, I have a plan_table created and a public synonym on it. I then created a user a and did the following:

tkyte@TKYTE816> grant connect, resource to a identified by a;
Grant succeeded.

tkyte@TKYTE816> grant plustrace to a;
Grant succeeded.

tkyte@TKYTE816> grant select on plan_table to public;
Grant succeeded.

tkyte@TKYTE816> connect a/a
Connected.

tkyte@TKYTE816> set autotrace on
tkyte@TKYTE816> select count(*) from dual;

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

ERROR:
ORA-01031: insufficient privileges


SP2-0612: Error generating AUTOTRACE EXPLAIN report

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

tkyte@TKYTE816> connect tkyte/tkyte
Connected.

tkyte@TKYTE816> grant all on plan_table to a;
Grant succeeded.

tkyte@TKYTE816> connect a/a
Connected.
tkyte@TKYTE816> select count(*) from dual;

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

tkyte@TKYTE816> set autotrace on
tkyte@TKYTE816> select count(*) from dual;

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


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DUAL'




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

tkyte@TKYTE816>


Rating

  (2 ratings)

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

Comments

Question on redo size in the Statistics

Indrakala Venugopal, August 27, 2002 - 11:50 am UTC

Tom,
Why does redo size appear when you take statistics of queries?
Pasted below are the statistics i got. Can you please tell me the areas i need to look inorder to improve performance.

Statistics
----------------------------------------------------------
96 recursive calls
122 db block gets
760692 consistent gets
76553 physical reads
232656 redo size
3914384 bytes sent via SQL*Net to client
615652 bytes received via SQL*Net from client
9311 SQL*Net roundtrips to/from client
325 sorts (memory)
0 sorts (disk)
139649 rows processed

regards
IK

Tom Kyte
August 27, 2002 - 1:34 pm UTC

The redo can appear for many reasons -- delayed block cleanout, space management (dictionary updates)

Caution, smart aleck answer ahead, read at your own caution:

Given the amount of information you supply above in regards to "how to improve performance" the only answer I can give given the amount of information you provide is:

Answer) only query DUAL. DUAL is very fast.


Seriously, read the designing and tuning for performance guide that comes with the database. That is a good start.

Tianhua Wu, August 27, 2002 - 4:57 pm UTC

set autotrace on will open another session that will insert and delete from plan table. That is probably why you see the redo.

Tom Kyte
August 27, 2002 - 9:21 pm UTC

Nope -- that other session would have its redo tracked against ITS session, not the one it is peeking at.

delayed block cleanout
space managment

those are the two likely culprits

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.