Query Tuning 101 How to Run Autotrace in SQL Developer

This video shows how to run autotrace reports using Oracle SQL Developer to analyze query performance. It also discusses the privileges you need to enable database users to run autotrace.

Plustrace role script:

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;

grant plustrace to [your user];

============================
The Magic of SQL with Chris Saxon

Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.

[tiagorsacxs]

You are fantastic ...
Thanks for sharing your knowledge!

[mankavi]

Thank you for the presentation. I have issue though the setup works for Sql developer 4.1 and it does not work for sql developer 4.2 ... it says ora 00972 : table or view does not exist - (no data in the v$sql plan table) ..please advice ....thanks in advance...-Kavitha

[nada]

Thanks for the great video. Could you please share the code?

[michaeliceslash]

sir could you pls share your script..

[mankavi]

Hello I was unable to post my question on the link mentioned due to livelink issue ..Please review my question ..thank you

unable to use AUTOTRACE in SQL Developer Version 4.2.0.17.089

it works fine in sqldeveloper-4.1.0.19.07 with the below enclosed setup

GRANT SELECT ON SYS.V_$MYSTAT TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SESSION TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SESSTAT TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SQL TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SQL_PLAN TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SQL_PLAN_STATISTICS TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$STATNAME TO RL_AUTOTRACE;

GRANT RL_AUTOTRACE TO TEST_KAVI;


The same set up does not work for SQL Developer Version 4.2.0.17.089

error msg - ORA-00942: table or view does not exist (no data in V$sql_plan)

Best REgards,
KAvitha

[mankavi]

Hello I was unable to post my question on the link mentioned due livelink issue ..Please review and question in steps which follows after this comment ..thank you

unable to use AUTOTRACE in SQL Developer Version 4.2.0.17.089

it works fine in sqldeveloper-4.1.0.19.07 with the below enclosed setup

GRANT SELECT ON SYS.V_$MYSTAT TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SESSION TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SESSTAT TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SQL TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SQL_PLAN TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SQL_PLAN_STATISTICS TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$STATNAME TO RL_AUTOTRACE;

GRANT RL_AUTOTRACE TO TEST_KAVI;


The same set up does not work for SQL Developer Version 4.2.0.17.089

error msg - ORA-00942: table or view does not exist (no data in V$sql_plan)

Best REgards,
KAvitha

[mankavi]

more details I have this role created DROP ROLE RL_AUTOTRACE;

CREATE ROLE RL_AUTOTRACE NOT IDENTIFIED;

-- Object privileges granted to RL_AUTOTRACE
GRANT SELECT ON SYS.V_$MYSTAT TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SESSION TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SESSTAT TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SQL TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SQL_PLAN TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$SQL_PLAN_STATISTICS TO RL_AUTOTRACE;
GRANT SELECT ON SYS.V_$STATNAME TO RL_AUTOTRACE;

-- Roles granted to RL_AUTOTRACE
GRANT PLUSTRACE TO RL_AUTOTRACE;