In last week’s post I described how to use SQL Monitor to determine what is happening during the execution of long-running SQL statements. Shortly after the post went up, I got some requests on both social media and via the blog comments asking, “If it is possible to get the same information from a traditional text-based execution plan, as not everyone has access to SQL Monitor?”.
The answer is yes, it is possible to see a lot of the information shown in SQL Monitor by viewing the execution plan via the DBMS_XPLAN.DISPLAY_CURSOR function. In order to call this function, you will need SELECT or READ privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL, and V$SQL_PLAN, otherwise, you’ll get an error message.
The DBMS_XPLAN.DISPLAY_CURSOR function takes three parameters:
- SQL ID – default null, means the last SQL statement executed in this session
- CURSOR_CHILD_NO – default 0
- FORMAT – Controls the level of details that will be displayed in the execution plan, default TYPICAL.
The video below demonstrates how you can use the FORMAT parameter within the DBMS_XPLAN.DISPLAY_CURSOR function to show you information about what’s happened during an execution plan including the bind variable values used, the actual number of rows returned by each step, and how much time was spent on each step.
Under the video you will find all of the commands used, so you can cut and paste them easily.
How do I see the actual number of rows and elapse time for each step in the plan?
You will need to do two things in order to see the actual number of rows:
- Add the GATHER_PLAN_STATISTICS hint to the SQL statement
- Setting the FORMAT parameter of DBMS_XPLAN.DISPLAY_CURSOR to ‘ALLSTATS LAST’
SELECT /*+ GATHER_PLAN_STATISTICS */ t2.owner, SUM(b.object_id) FROM big_table b, t2 ,t1 WHERE b.object_id = t2.object_id AND b.data_object_id = t1.data_object_id AND t1.object_type='TABLE' AND t2.owner ='SSB' GROUP BY t2.owner; SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- SQL_ID d3z7q78jtgxm2, child NUMBER 1 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ t2.owner, SUM(b.object_id) FROM big_table b, t2 ,t1 WHERE b.object_id = t2.object_id AND b.data_object_id = t1.data_object_id AND t1.object_type='TABLE' AND t2.owner ='SSB' GROUP BY t2.owner Plan hash VALUE: 1122440390 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-TIME | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.25 | 50110 | | | | | 1 | SORT GROUP BY NOSORT| | 1 | 256 | 1 |00:00:00.25 | 50110 | | | | |* 2 | HASH JOIN | | 1 | 19 | 256 |00:00:00.91 | 50110 | 2440K| 2440K| 1474K (0)| |* 3 | TABLE ACCESS FULL | T1 | 1 | 592 | 592 |00:00:00.01 | 146 | | | | |* 4 | HASH JOIN | | 1 | 1599 | 3072 |00:00:06.31 | 49964 | 2293K| 2293K| 1590K (0)| |* 5 | TABLE ACCESS FULL| T2 | 1 | 102 | 102 |00:00:00.01 | 9 | | | | |* 6 | TABLE ACCESS FULL| BIG_TABLE | 1 | 298K| 298K|00:00:00.61 | 49955 | | | | ----------------------------------------------------------------------------------------------------------------------- Predicate Information (identified BY operation id): --------------------------------------------------- 2 - access("B"."DATA_OBJECT_ID"="T1"."DATA_OBJECT_ID") 3 - filter(("T1"."DATA_OBJECT_ID" IS NOT NULL AND "T1"."OBJECT_TYPE"='TABLE')) 4 - access("B"."OBJECT_ID"="T2"."OBJECT_ID") 5 - filter("T2"."OWNER"='SSB') 6 - filter("B"."DATA_OBJECT_ID" IS NOT NULL)
NOTE: If you can’t modify the SQL statement to add the hint, it is possible to add the hint via a SQL Profile as demonstrated by Kerry Osborne.
How do I get the COST of the plan to be displayed when I specify ALLSTATS LAST for the FORMAT parameter?
When you use ‘ALLSTATS LAST’ for the FORMAT parameter, the estimated number of bytes (BYTES) and the estimated cost for the plan (COST) are not displayed by default. You can easily have these columns displayed by adding additional predicates to the FORMAT parameter. Each additional predicate is proceeded with ‘+’ sign.
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'d3z7q78jtgxm2', format=>'ALLSTATS LAST +cost +bytes')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID d3z7q78jtgxm2, child NUMBER 1 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ t2.owner, SUM(b.object_id) FROM big_table b, t2 ,t1 WHERE b.object_id = t2.object_id AND b.data_object_id = t1.data_object_id AND t1.object_type='TABLE' AND t2.owner ='SSB' GROUP BY t2.owner Plan hash VALUE: 1122440390 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-TIME | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 15855 (100)| 1 |00:00:00.23 | 50110 | | | | | 1 | SORT GROUP BY NOSORT| | 1 | 19 | 475 | 15855 (1)| 1 |00:00:00.23 | 50110 | | | | |* 2 | HASH JOIN | | 1 | 19 | 475 | 15855 (1)| 256 |00:00:00.91 | 50110 | 2440K| 2440K| 1474K (0)| |* 3 | TABLE ACCESS FULL | T1 | 1 | 113 | 1017 | 2222 (1)| 592 |00:00:00.01 | 146 | | | | |* 4 | HASH JOIN | | 1 | 1599 | 25584 | 13634 (1)| 3072 |00:00:05.65 | 49964 | 2293K| 2293K| 1567K (0)| |* 5 | TABLE ACCESS FULL| T2 | 1 | 102 | 918 | 13 (0)| 102 |00:00:00.01 | 9 | | | | |* 6 | TABLE ACCESS FULL| BIG_TABLE | 1 | 298K| 2037K| 13620 (1)| 298K|00:00:00.73 | 49955 | | | | -------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified BY operation id): --------------------------------------------------- 2 - access("B"."DATA_OBJECT_ID"="T1"."DATA_OBJECT_ID") 3 - filter(("T1"."DATA_OBJECT_ID" IS NOT NULL AND "T1"."OBJECT_TYPE"='TABLE')) 4 - access("B"."OBJECT_ID"="T2"."OBJECT_ID") 5 - filter("T2"."OWNER"='SSB') 6 - filter("B"."DATA_OBJECT_ID" IS NOT NULL)
NOTE: You can find a list of additional predicates that can be added in the Oracle Database PL/SQL Packages and Types Reference guide.
It’s also possible to remove columns from the plan table or other information from the output by adding additional predicates to the FORMAT parameter proceeded with a ‘-‘ sign. For example, the command below removes the E-Rows column and predicate information from below the plan.
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'d3z7q78jtgxm2', format=>'ALLSTATS LAST -rows –predicate')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID d3z7q78jtgxm2, child NUMBER 1 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ t2.owner, SUM(b.object_id) FROM big_table b, t2 ,t1 WHERE b.object_id = t2.object_id AND b.data_object_id = t1.data_object_id AND t1.object_type='TABLE' AND t2.owner ='SSB' GROUP BY t2.owner Plan hash VALUE: 1122440390 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | A-TIME | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.25 | 50110 | | | | | 1 | SORT GROUP BY NOSORT| | 1 | 1 |00:00:00.25 | 50110 | | | | |* 2 | HASH JOIN | | 1 | 256 |00:00:00.91 | 50110 | 2440K| 2440K| 1474K (0)| |* 3 | TABLE ACCESS FULL | T1 | 1 | 592 |00:00:00.01 | 146 | | | | |* 4 | HASH JOIN | | 1 | 3072 |00:00:06.31 | 49964 | 2293K| 2293K| 1590K (0)| |* 5 | TABLE ACCESS FULL| T2 | 1 | 102 |00:00:00.01 | 9 | | | | |* 6 | TABLE ACCESS FULL| BIG_TABLE | 1 | 298K|00:00:00.61 | 49955 | | | | --------------------------------------------------------------------------------------------------------------
How do I determine the join order if I can’t see the plan tree with DBMS_XPLAN.DISPLAY_CURSOR?
In the SQL Monitor video I showed you how I use the plan tree to determine the join order but with DBMS_XPLAN.DISPLAY_CURSOR the execution plan is displayed only as a table. Although it is possible to determine the join order by looking at the indentation of the tables in the operation column, I find it far easier to use the FORMAT parameter of DBMS_XPLAN.DISPLAY_CURSOR to display the outline information for the plan, which will contain the join order.
Adding the ‘+outline’ predicate to the FORMAT parameter will return the outline (full set of hints to reproduce the statement) for the SQL statement.
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'d3z7q78jtgxm2', format=>'ALLSTATS LAST +outline')); ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-TIME | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.23 | 50110 | | | | | 1 | SORT GROUP BY NOSORT| | 1 | 19 | 1 |00:00:00.23 | 50110 | | | | |* 2 | HASH JOIN | | 1 | 19 | 256 |00:00:00.91 | 50110 | 2440K| 2440K| 1474K (0)| |* 3 | TABLE ACCESS FULL | T1 | 1 | 113 | 592 |00:00:00.01 | 146 | | | | |* 4 | HASH JOIN | | 1 | 1599 | 3072 |00:00:05.65 | 49964 | 2293K| 2293K| 1567K (0)| |* 5 | TABLE ACCESS FULL| T2 | 1 | 102 | 102 |00:00:00.01 | 9 | | | | |* 6 | TABLE ACCESS FULL| BIG_TABLE | 1 | 298K| 298K|00:00:00.73 | 49955 | | | | ----------------------------------------------------------------------------------------------------------------------- Outline Data ----------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1""T2"@"SEL$1") FULL(@"SEL$1""B"@"SEL$1") FULL(@"SEL$1""T1"@"SEL$1") LEADING(@"SEL$1" "T2"@"SEL$1" "B"@"SEL$1" "T1"@"SEL$1") USE_HASH(@"SEL$1""B"@"SEL$1") USE_HASH(@"SEL$1""T1"@"SEL$1") SWAP_JOIN_INPUTS(@"SEL$1""T1"@"SEL$1") END_OUTLINE_DATA */
In the outline information, look for the line that begins with the word LEADING. This line shows the join order for this query. In this example you see “T2”, then “B”, then “T1” referenced on this line; these are the aliases for each of the tables used in our query T2, BIG_TABLE, and T1.
How do I see what bind variable values were used for this execution?
In SQL Monitor the bind variable values used to generate an execution plan are shown via a link in the upper right-hand corner of the screen.
To show the same information with DBMS_XPLAN.DISPLAY_CURSOR you simply add a ‘+peeked_binds’ predicate to the FORMAT parameter.
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'+PEEKED_BINDS')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 1427awr1f8qkf, child NUMBER 0 ------------------------------------- SELECT t2.owner, SUM(b.object_id) FROM big_table b, t2 ,t1 WHERE b.object_id = t2.object_id AND b.data_object_id = t1.data_object_id AND t1.object_type= :obj_type AND t2.owner = :own GROUP BY t2.owner Plan hash VALUE: 1122440390 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TIME | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 15855 (100)| | | 1 | SORT GROUP BY NOSORT| | 19 | 475 | 15855 (1)| 00:00:01 | |* 2 | HASH JOIN | | 19 | 475 | 15855 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | T1 | 113 | 1017 | 2222 (1)| 00:00:01 | |* 4 | HASH JOIN | | 1599 | 25584 | 13634 (1)| 00:00:01 | |* 5 | TABLE ACCESS FULL| T2 | 102 | 918 | 13 (0)| 00:00:01 | |* 6 | TABLE ACCESS FULL| BIG_TABLE | 298K| 2037K| 13620 (1)| 00:00:01 | ---------------------------------------------------------------------------------- Peeked Binds (identified BY position): -------------------------------------- 1 - :OBJ_TYPE (VARCHAR2(30), CSID=873): 'TABLE' 2 - :OWN (VARCHAR2(30), CSID=873): 'SSB'
Hi Maria,
Thanks for the article, I have a long running SQL which has complete execution and my statisics_level is set to typical. Is there any way I can get information about the actual rows returned.
Thanks,
Bala
Hi Bala,
If the statement is still in the SQL Monitor buffer, than you will be able to see the actual number of rows on the report as SQL Monitor always collects that information and stores it.
However, if you are using DBMS_XPLAN.DISPLAY_CURSOR, then the only way to have Oracle persist the execution statistics is by supplying the GATHER_PLAN_STATISTICS hint or by setting the statistics_level to ALL.
Thanks,
Maria
Hi Maria,
if GATHER_PLAN_STATISTICS hint / statistics_level to ALL is set, will different consecutive executions of DBMS_XPLAN.DISPLAY_CURSOR display the evolution of statistics ? I have noticed that consecutive errorstack dumps (to get the execution plan and metrics) do not show metrics changing over time even if the statement is still running.
Thanks
Olivier.
Hi Olivier,
Yes, the DBMS_XPLAN.DISPLAY_CURSOR function will show the evolution of statistics during the execution of a long running query assuming you know the SQL_ID and the child cursor number.
It’s not as simple or as elegant as using a SQL Monitor report but it is possible, assuming you are willing to do a little math to make this work.
In a separate session, you will need to execute select * from table(dbms_xplan.display_cursor(sql_id=>’YOUR_SQL_ID’, cursor_child_no=>YOUR_CHILD_NO, format=>’ALLSTATS ALL’));
Make note of all of the statistics you are interested in.
By rerunning this command throughout the execution you will be able to tell which step of the plan is currently being executed by watching which A-Rows entry is currently increasing. The difference between the original run of the command and the current value will tell you the actual number of rows for this execution.
Thanks,
Maria
Hi Maria, Nice blog !!!
If I have a sqlid with 5 child cursors how can I know which one is my cursor running via Display Cursor function
Hi William,
By default, if you do not supply any parameter values to the DBMS_XPLAN.DISPLAY_CURSOR function, it will display the child cursor used by the previously executed SQL statement in your session. You will be able to tell which child cursor it is, by looking for the SQL_ID and CHILD NUMBER just above the plan table.
If however, you supply just the SQL_ID then you typically child cursor 0.
If you know the child number of the plan you want to display, you can supply it as the second parameter to the DBMS_XPLAN.DISPLAY_CURSOR function.
Thanks,
Marua
Hi Maria,
Thanks for the useful information.
Is there a way where I could hide (using the minus sign) the whole SQL Query Text that gets displayed at the top of every EXECUTION PLAN if I have provided exactly the SQL_ID and CURSOR_CHILD_NO parameter values?
It would help save the screen real-estate because I know exactly which SQL query text it is, so no need to show it again.
I have the following:-
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => ‘8t6w2p8snazn9’, cursor_child_no => 0, FORMAT => ‘TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE’));
I wish there was an option like “-SQLTEXT” or “-QUERY” so that I start seeing the results starting from the line that says “Plan hash value: ……”
Hi Maria,
Would you please let me know how to see the SQL execution plan of the current SQLs with info such as the “+PEEKED_BINDS +cost +bytes +outline'” using SQL_IDs? As you know, we can see the Explain plan details of the current SQLs running in the database using Toad and Grid Control SQL Monitoring, but I’d like see the same or similar plans from SQL*Plus. From time to time, some SQLs started from application are running long time so, I’d like to see the plans of them from SQL*Plus.
Thanks,
Chris
Hi Chris,
You can use the DBMS_XPLAN.DISPLAY_CURSOR procedure to view the execution plan of any SQL statement in the cursor cache from SQL*Plus if you have the SQL_ID.
The following command should do it:
select plan_table_output
from table(dbms_xplan.display_cursor(sql_id=>’6phkrurssuyjf’,cursor_child_no=>0, format=>’basic +PEEKED_BINDS +cost +bytes +outlines’));
More details on the format parameter options can be found on the optimizer blog:
https://blogs.oracle.com/optimizer/how-do-i-display-and-read-the-execution-plans-for-a-sql-statement
Thanks,
Maria
Hi Maria,
Thanks for the information. Appreciated it. As you mentioned, I also use the command to see some useful information as long as SQL statements are not that complicated or extremely long with with bind variables, and it works fine mostly.
However, from time to time in critical PROD databases, I see very complicated and in-efficiently written SQLs which have hundreds of lines with multiple bind variables and run for several hours with no sign of completion.
In this case, I’m not quite sure if the “dbms_xplan.display_cursor” can still show us all the useful information when the “EXPLAIN PLAN FOR” cannot be done properly due to multiple bind variables that are not identified, and the actual SQL statement has never been completed successfully and cannot be executed for test in the PROD DB.
So, I also try to generate SQL Tuning Advisor reports to see the plan along with outline data on the fly, but very often the SQL Tuning Advisor does not show much information for that kind of long and complicated SQLs even after long processing time to generate the report.
In this case, what can be the best way to see the plan and outline data from SQL Plus so that I can check if I can create a SQL profile to push a hint using object alias or query block names for example?
I’ve reviewed the article listed on your comment, but I wasn’t able to get some idea for my case.
Thanks,
Chris
Hi Chris,
If the complex SQL statement with the binds is still in the Shared Pool than dbms_xplan.display_cursor can show you everything include the outline and the actual bind variable values used when you set the format parameter to ‘TYPICAL +outline +peeked_binds’.
Thanks,
Maria
Hi Maria,
First of all, thank you very much for your contribution to the oracle community and us. You do amazing job. I have a question related to FORMAT parameter. As known, ALLSTATS LAST give the statistics regarding to the last execution. On the other hande when ALLSTATS ALL is used, the cumulative data of the all execution related to the sql shows. Also, more columns appearin the plan. Is there any chance to get these columns for ALLSTATS LAST?
In other words, what if we use ALLSTATS LAST ALL together? Can we reach all columns related to the only last execution statistics?
select * from table(dbms_xplan.display_cursor(sql_id=>’YOUR_SQL_ID’, cursor_child_no=>YOUR_CHILD_NO, format=>’ALLSTATS ALL’));
Thanks in advance
Hi Cagler,
Sorry if I mislead you in the video or this post by accidentally implying that you only get the extra columns in the execution plan if you use ALLSTATS ALL.
You actually get the extra columns with ALLSTATS LAST too.
There is no need to try and get creative by using ALLSTATS LAST ALL.
SQL> SELECT /*+ ORDERED USE_NL(s) */ c.cust_last_Name, sum(s.amount_sold)2 FROM customers c, sales s
3 WHERE c.cust_id = s.cust_id
4 AND c.cust_city = 'Los Angeles'
5 AND CUST_STATE_PROVINCE = 'CA'
6 AND s.time_id = '09-SEP-17'
7 GROUP BY c.cust_last_Name;
no rows selected
SQL>
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3mubwz7z707j2, child number 0
-------------------------------------
SELECT /*+ ORDERED USE_NL(s) */ c.cust_last_Name, sum(s.amount_sold)
FROM customers c, sales s WHERE c.cust_id = s.cust_id AND
c.cust_city = 'Los Angeles' AND CUST_STATE_PROVINCE = 'CA' AND
s.time_id = '09-SEP-17' GROUP BY c.cust_last_Name
Plan hash value: 2265433329
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.11 | 10974 | 9589 | | | |
| 1 | HASH GROUP BY | | 1 | 2 | 0 |00:00:00.11 | 10974 | 9589 | 948K| 948K| |
|* 2 | HASH JOIN | | 1 | 2 | 0 |00:00:00.11 | 10974 | 9589 | 1335K| 1335K| 397K (0)|
| 3 | VIEW | VW_GBC_5 | 1 | 624 | 0 |00:00:00.11 | 10974 | 9589 | | | |
| 4 | HASH GROUP BY | | 1 | 624 | 0 |00:00:00.11 | 10974 | 9589 | 1230K| 1230K| |
|* 5 | TABLE ACCESS FULL| SALES | 1 | 2966 | 0 |00:00:00.11 | 10974 | 9589 | | | |
|* 6 | TABLE ACCESS FULL | CUSTOMERS | 0 | 2 | 0 |00:00:00.01 | 0 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."CUST_ID"="ITEM_1")
5 - filter("S"."TIME_ID"='09-SEP-17')
6 - filter(("C"."CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA'))
I have a complex query running 20 hours, returning 60M many rows. Oracle drops the run stats before the query finishes.
With
alter session set statistics_level=’ALL’;
I believe I loose the stats before the query finishes because Oracle sees the status as INACTIVE. Apparently, my host client can incur peak activity and not request more records from the sorted output soon enough. And, Oracle drops the stats from V$SQL_PLAN_STATISTICS_ALL.
How do I convince Oracle my query should really be shown as ACTIVE and keep the PLAN STATISTICS?
I could probably run display_cursor every 5 minutes and keep saving results, but, could there be a more elegant solution to keeping the stats available until the query actually finishes?
I tried spooling to /dev/null on the host, but, I still get INACTIVE status which I’m thinking triggers cleanup.
The same type of thing happens in production where Oracle believes a status should be INACTIVE and it TRUNCATES rollback causing a snapshot too old.
How do I convince Oracle my session is still ACTIVE? Or, how do I keep the PLAN Statistics around long enough to run DBS_XPLAN.DISPLAY_CURSOR when the query finishes?
Note: If I SET AUTOTRACE TRACEONLY, my display_cursor(‘LAST’) provides the results of a query related to autotrace. Spooling the result to disk or /dev/null still allows my cursor to show as INACTIVE. So, now I’m attempting to insert the result into a table and hoping the stats will be available when the query finishes. I’m going to hedge my bets and run display_cursor every 5 minutes.
Thank you so much for this great site.
Hi Maria,
It’s a really nice blog, and I learned a lot.
I am using 19c Sandard edition 2 and after setting systemlevel statistics_level=ALL, still i am not able to see actual rows in “SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(‘$SQL_ID’,0,’ALLSTATS LAST +cost +bytes’)); “. Please let me know if this is limitation in standard edition.
Hi Harisai,
There is no limitation on generating execution statistics in Stanard Edition.
Here are two examples I just ran in Standard Edition that worked as expected.
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> alter session set statistics_level=ALL;
Session altered.
SQL> select count(*) from employees;
COUNT(*)
———-
107
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(format=>’ALLSTATS LAST’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 7c1rnh08dp922, child number 0
————————————-
select count(*) from employees
Plan hash value: 1426549735
——————————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| EMP_EMP_ID_PK | 1 | 107 | 107 |00:00:00.01 | 1 |
——————————————————————————————–
SQL> select count(*) from employees;
COUNT(*)
———-
107
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(format=>’ALLSTATS LAST +cost’));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 7c1rnh08dp922, child number 0
————————————-
select count(*) from employees
Plan hash value: 1426549735
——————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows| A-Time | Buffers |
———————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 (100)| 1|00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1|00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN | EMP_EMP_ID_PK | 1 | 107 | 1 (0)| 107|00:00:00.01 | 1 |
———————————————————————————————————
Thanks Maria. I didn’t get at the beginning, but now I am able to see e-rows and a-rows.
Hi Maria,
My A-Rows are always 200. I assume that is b/c the query only displays the first 200 rows. Is that a silly assumption? If that is correct how can I get it to show the actual A-Rows?
-Another Maria 🙂
Hi Maria,
I suspect you are correct. The reason you are seeing 200 rows in the A-rows column, has something to do with how you are running your queries (e.g. limiting the rows returned). But I can’t say for sure until I know how you are running/viewing your queries?
Thanks,
Maria
Dear Maria,
Thanks for the nice blog.
I m facing a issue where in v$sql_plan there is an entry as ‘TABLE ACCESS FULL’ against the last ID and ‘TABLE ACCESS BY INDEX ROWID’ against last before one for a sql_id ,but for the same sql_id while checking in display_cursor it doesn’t show the ‘TABLE ACCESS FULL’ only ‘TABLE ACCESS BY INDEX ROWID’ .I m referring same object in both the places. Is that normal or I am missing some basic thing?
Thanks,
Rajkamal.R
Hi Rajkamal,
It sounds like you encountered an adaptive plan, and I’m guessing you are running Oracle Database 12.1.
With an adaptive plan, Oracle postpones making the final decision on a join type and the access method on the right-hand side of the join.
After the statement has completed, DBMS_XPLAN.DISPLAY_CURSOR displays only the final plan selected by default.
To get DBMS_XPLAN.DISPLAY_CURSOR to display the entire adaptive plan, you need to add the format parameter, ‘+all_dyn_plan’.
Thanks,
Maria
Thank you for the reply.
Hi Maria,
I learned a lot from your blog. Thank you for that!
I have a question whether DBMS_XPLAN.DISPLAY_CURSOR could be relied upon in the RAC environment. We’re running exadata 19c database, and very often the DISPLAY_CURSOR doesn’t find the needed SQL_ID because it’s executed on another instance.
Hi Ola,
DBMS_XPLAN.DISPLAY_CURSOR works just as well on RAC as on a single instance. However, it would be best to remember that each RAC node has its own cursor cache within its own SHARED_POOL. Plus, Oracle does not ship cursors across the interconnect on RAC, as it does with data blocks within the BUFFER CACHE. Therefore, DBMS_XPLAN.DISPLAY_CURSOR can only display cursor information about the cursors found on the node the command is executed on.
If a SQL statement is executed on node 1 (and doesn’t execute in parallel across all nodes), then you can only display the execution plan via DBMS_XPLAN.DISPLAY_CURSOR on node1.
The only exceptions to this rule are SQL statements that execute in parallel across nodes. In this case, a cursor will be created on each RAC node participating in the query.
Thanks,
Maria