As I understand it, Oracle does not have many things to which I am used to working with MS SQL Server and are out of the box ... = (

How can you visualize query execution plans, which would be just as convenient as in MS SQL Management Studio?

enter image description here

The priority is free, and then paid solutions.

  • four
    You better tell me how you understood these pictures, in the same place, in order to see something important, you need to mouse over each icon, and then remember. And this stupid picture forever on the screen does not fit. you have to move to the side to see at least something. And in the text at Oracle everything is just fine, everything is immediately visible. So get used to it :) - Mike

2 answers 2

In addition to the answer from @MaxU. The actual execution plan for a request may differ from the one provided by explain plan . Usually, if I want to see the plan of the executed query, then first I get the sql id (looks something like ds9njzundv6k0 ) of the query I need.

 select s.sql_id, s.* from v$session s

Further, depending on the mood, there are several ways to see the plan:

  • 1 - In text form:

     SELECT * FROM TABLE (dbms_xplan.display_cursor('ds9njzundv6k0'));

    The result will be presented in text form:

     SQL_ID 9ya87b6a04kc3, child number 0 ------------------------------------- select s.sql_id, s.* from v$session s Plan hash value: 644658511 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | 1 | NESTED LOOPS | | 1 | 1379 | 0 (0)| | 2 | NESTED LOOPS | | 1 | 1170 | 0 (0)| |* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 1009 | 0 (0)| |* 4 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 161 | 0 (0)| |* 5 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 209 | 0 (0)| ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)) 4 - filter("S"."INDX"="W"."KSLWTSID")

  • 2 - In the form of HTML (option 1):

     select dbms_sqltune.report_sql_monitor( sql_id => '9ya87b6a04kc3', type => 'HTML', report_level => 'ALL + PLAN') as report from dual;

    As a result, clob will return a field containing an HTML file that can be opened in the browser. enter image description here

  • 3 - in the form of HTML (option 2 using Flash Player):

     select dbms_sqltune.report_sql_detail( sql_id => '9ya87b6a04kc3', type => 'ACTIVE', report_level => 'ALL+XPLAN' ) as report from dual;

    As a result, clob containing an HTML page and using a flash player will return, which gives a slightly more interactive display. In this version, on 1 of the buttons you can see the graphic plan on which you can pull the mouse. enter image description here or more Oracle style enter image description here

PS

Methods 2 and 3 are used to build the interface in Enterprise Manager, perhaps there are some subtleties with licensing. By playing around with the parameter report_level you can get various results.

  • I will vent tomorrow at work about (PS) about the license. In the remaining +1. - 0xdb
  • @ 0xdb by the ear heard that the Orakla license prohibits the use of what is not paid, even if it is in the standard package. And here EM is a separate option, but what about the packets for generating it is not clear - Viktorov
  • I understood that and will clarify it. I myself have not thought about it, because yusil only point 1. - 0xdb
  • In my opinion dbms_sqltune is an interface to the SQL Tuning Advisor and in order to use it you need to have an Oracle Tuning Pack license - MaxU
  • This is where the questions begin, if you pull it out of the package, is it the same as ticking it in EM? - Viktorov

Oracle's sqlplus provides an excellent opportunity to see a detailed query execution plan in tabular form (as text):

 set linesize 200 pages 3000 explain plan for <your SQL query>; select * from table(dbms_xplan.display);

You can also view it in free Oracle SQL Developer by pressing F10 or the "Explain Plan" button

  • Excellent answer @ iluxa1810 will finally leave the mouse alone. - 0xdb
  • one
    In fairness, it should be noted that there are situations when the explain shows one plan, and the fact of execution uses another - Viktorov
  • @ 0xdb, thanks! :) - MaxU
  • 2
    @Viktorov, in this case, you can see the plan for plan_hash_value using DBMS_XPLAN.DISPLAY_AWR (sql_id, plan_hash_value) - MaxU