HomeНаука и техникаRelated VideosMore From: The Magic of SQL

Query Tuning 101 How to Run Autotrace in SQL Developer

42 ratings | 18063 views
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. ============================ 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.
Html code for embedding videos on your blog
Text Comments (27)
Oleksandr Gavenko (10 months ago)
https://asktom.oracle.com/pls/asktom/f?p=100:11:::NO:RP:P11_QUESTION_ID:9535421800346826314 GRANT SELECT ON SYS.V_$SQL_PLAN_STATISTICS_ALL TO ...;
Emanuel Oliveira (1 year ago)
I added the 7 grants and autotrace still not work.. the Operation appears empty(and I reconnected session etc): GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE,GRANTABLE,HIERARCHY,COMMON,TYPE PLUSTRACE,SYS,V_$SESSION,SYS,SELECT,NO,NO,NO,VIEW PLUSTRACE,SYS,V_$SESSTAT,SYS,SELECT,NO,NO,NO,VIEW PLUSTRACE,SYS,V_$MYSTAT,SYS,SELECT,NO,NO,NO,VIEW PLUSTRACE,SYS,V_$STATNAME,SYS,SELECT,NO,NO,NO,VIEW PLUSTRACE,SYS,V_$SQL,SYS,SELECT,NO,NO,NO,VIEW PLUSTRACE,SYS,V_$SQL_PLAN,SYS,SELECT,NO,NO,NO,VIEW PLUSTRACE,SYS,V_$SQL_PLAN_STATISTICS,SYS,SELECT,NO,NO,NO,VIEW Oracle12 - sql developer 4.1.5.21 (build MAIN-21.78) Can you please suggest? Ask me if you need me to share anything to help you ? ------------------------------------------------------------------------- --autotrace - enable listing execution plan inside sql developer.sql ------------------------------------------------------------------------- --src: https://www.youtube.com/watch?v=GeCanC7u8cs --run as SYS user drop role plustrace; create role plustrace; --sqlplus permissions 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; --sqldeveloper needs extra permissions grant select on v_$session to plustrace; grant select on v_$sql_plan to plustrace; grant select on v_$sql_plan_statistics to plustrace; grant select on v_$sql to plustrace; --step 2 reconnect session and now you can autotrace/execute a sql inside sql developer, and it will display the execution plan as well grant plustrace to myuser;
The Magic of SQL (1 year ago)
Glad you got it sorted
Emanuel Oliveira (1 year ago)
It started working on itself after some hours.. so good news, thanks so much for the info. FYI: I just tested and works in sql developer 4.1.5 as well as sql developer 17.2 and this is the script we used.. which is basically the same as you show in your video exactly: ------------------------------------------------------------------------- --autotrace - enable listing execution plan inside sql developer.sql ------------------------------------------------------------------------- --src: https://www.youtube.com/watch?v=GeCanC7u8cs --run as SYS user drop role plustrace; create role plustrace; --sqlplus permissions 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; --sqldeveloper needs extra permissions grant select on v_$session to plustrace; grant select on v_$sql_plan to plustrace; grant select on v_$sql_plan_statistics to plustrace; grant select on v_$sql to plustrace; --step 2 reconnect session and now you can autotrace/execute a sql inside sql developer, and it will display the execution plan as well grant plustrace to INSTALL;
The Magic of SQL (1 year ago)
Any chance you can record a short video of the issue and send us the link? Also, have you tried upgrading? 17.2 is the latest release.
Emanuel Oliveira (1 year ago)
yes its script, but DBA exchanged myuser to INSTALL user (as it was just an exmaple). So i click autotrace in a sql statement and I still dont see explain plan coming.. I see top "Operation" empty.. Is there anything else needed to configure in sql developer ?
The Magic of SQL (1 year ago)
Are you connected as myuser? And is that the exact script you've run?
manju kris (1 year ago)
I have posted my question on https://asktom.oracle.com ..please review and let me know...thank you
manju kris (1 year ago)
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
manju kris (1 year ago)
with the livesql link ...i am unable to post the question
manju kris (1 year ago)
I get that error with the above mentioned AUTOTRACE step up in SQL Developer Version 4.2.0.17.089 - i get that error when I use the autotrace
manju kris (1 year ago)
sure will try again
The Magic of SQL (1 year ago)
Livelink issue? You don't have to submit a LiveSQL link, we just give priority to those that do. Please submit your question on https://asktom.oracle.com stating exactly what you pressed to get that error. Or make a video showing us what you did and post a link to that.
manju kris (1 year ago)
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
manju kris (1 year ago)
please help
manju kris (1 year ago)
Hi Chris, Please let me know if you need more info.
manju kris (1 year ago)
Thank you so much I shall post the questions on the link mentioned.
The Magic of SQL (1 year ago)
What precisely are you doing when you get the error? If you submit a question at https://asktom.oracle.com with full details of what you're doing and the error you're getting we'll do our best to help.
manju kris (1 year ago)
GRANT SELECT ON SYS.V_$MYSTAT TO PLUSTRACE; GRANT SELECT ON SYS.V_$SESSION TO PLUSTRACE; GRANT SELECT ON SYS.V_$SESSTAT TO PLUSTRACE; GRANT SELECT ON SYS.V_$SQL TO PLUSTRACE; GRANT SELECT ON SYS.V_$SQL_PLAN TO PLUSTRACE; GRANT SELECT ON SYS.V_$SQL_PLAN_STATISTICS TO PLUSTRACE; GRANT SELECT ON SYS.V_$STATNAME TO PLUSTRACE; GRANT PLUSTRACE TO KAVITHAV WITH ADMIN OPTION;
manju kris (1 year ago)
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;
manju kris (1 year ago)
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
abbeydudeuk (1 year ago)
I am still getting an error
The Magic of SQL (1 year ago)
:)
abbeydudeuk (1 year ago)
just kidding your video says still getting an error that made me smile.
The Magic of SQL (1 year ago)
Doing what?
@?/sqlplus/admin/plustrce.sql grant select on v_$session to plustrace; grant select on v_$sql_plan to plustrace; grant select on v_$sql_plan_statistics to plustrace; grant select on v_$sql to plustrace;
Angel Quintero (3 years ago)
hahahaha, I really enjoyed it your explanation.  That was exactly the steps a normal user will follow in order to reproduce the error messages.  We still are getting an error......What is going on here!!!!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.