Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, niranjan.

Asked: August 22, 2017 - 2:36 pm UTC

Last updated: August 26, 2017 - 3:22 am UTC

Version: Oracle 12 c

Viewed 1000+ times

You Asked


I'm getting performance issues while running the below query. Its taking 7 minutes to give results

Table - V_DM_test_VISIT contains 25 million records
table - V_DM_PLANNED_VISIT contains 60 K records

I'm using analytical function to find out the particular visit


SELECT A.test_VISIT_EID, ROW_NUMBER() OVER (PARTITION BY testNUMBER, A.VISIT_PROJECTED_DT, A.STUDY_CD_ALIAS, A.COUNTRY_DESC, A.STUDY_SITE_ID ORDER BY SEQUENCE_NUMBER ASC)
AS Telephonic
FROM V_DM_test_VISIT A,
V_DM_PLANNED_VISIT B
WHERE B.STUDY_NO = A.STUDY_NO
AND A.VISIT_NAME = B.VISIT_NAME
AND A.VISIT_PROJECTED_DT IS NOT NULL

Could you please help me in improving the performance of query

and Chris said...

If you want help with SQL performance, we need to see the execution plan for the query!

Get this by:

- Running "set serveroutput off"
- Adding the /*+ gather_plan_statistics */ hint to your query
- Run it
- Get the plan by running:

select * 
from  table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


And post the output of it here. Ensure it includes the A(ctual)-rows as well as the (E)estimated-rows columns!

You can read more generating plans.

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

Rating

  (4 ratings)

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

Comments

niranjan deshpande, August 22, 2017 - 7:06 pm UTC

Here is the updated query 

explain plan for SELECT A.SUBJECT_VISIT_EID, ROW_NUMBER() OVER (PARTITION BY SUBJECTNUMBER, A.VISIT_PROJECTED_DT, A.STUDY_CD_ALIAS, A.COUNTRY_DESC, A.STUDY_SITE_ID ORDER BY SEQUENCE_NUMBER ASC)
 AS Telephonic
FROM DM_SUBJECT_VISIT A,
     DM_PLANNED_VISIT B
WHERE B.STUDY_EREF = A.STUDY_EREF
AND   A.VISIT_NAME = B.VISIT_NAME
AND   A.VISIT_PROJECTED_DT IS NOT NULL



Explain Plan 

 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |   529K|    40M|       |   148K  (1)| 00:00:06 |
|   1 |  WINDOW SORT                    |                          |   529K|    40M|    53M|   148K  (1)| 00:00:06 |
|*  2 |   HASH JOIN                     |                          |   529K|    40M|       |   138K  (1)| 00:00:06 |
|   3 |    NESTED LOOPS                 |                          |   529K|    40M|       |   138K  (1)| 00:00:06 |
|   4 |     NESTED LOOPS                |                          |   529K|    40M|       |   138K  (1)| 00:00:06 |
|   5 |      STATISTICS COLLECTOR       |                          |       |       |       |            |          |
|   6 |       TABLE ACCESS FULL         | DM_PLANNED_VISIT         | 67479 |  1186K|       |   478   (1)| 00:00:01 |
|   7 |      BITMAP CONVERSION TO ROWIDS|                          |       |       |       |            |          |
|*  8 |       BITMAP INDEX RANGE SCAN   | IDX_SUB_VISIT_EREX_VISNM |       |       |       |            |          |
|   9 |     TABLE ACCESS BY INDEX ROWID | DM_SUBJECT_VISIT         |     8 |   504 |       |   138K  (1)| 00:00:06 |
|* 10 |    TABLE ACCESS FULL            | DM_SUBJECT_VISIT         |     8 |   504 |       |   138K  (1)| 00:00:06 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("B"."STUDY_EREF"="A"."STUDY_EREF" AND "A"."VISIT_NAME"="B"."VISIT_NAME")
   8 - access("B"."STUDY_EREF"="A"."STUDY_EREF" AND "A"."VISIT_NAME"="B"."VISIT_NAME")
       filter("A"."VISIT_PROJECTED_DT" IS NOT NULL AND "B"."STUDY_EREF"="A"."STUDY_EREF" AND 
              "A"."VISIT_NAME"="B"."VISIT_NAME")
  10 - filter("A"."VISIT_PROJECTED_DT" IS NOT NULL)

Connor McDonald
August 23, 2017 - 2:05 am UTC

Hmmm.... we asked for "X", and even gave you instructions on how to do it....

... and you gave us "Y".


niranjan deshpande, August 23, 2017 - 3:23 am UTC

SQL_ID 7squa6twczm8y, child number 2
-------------------------------------
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS
LAST'))


--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.02 | 44 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 1 | 8168 | 9 |00:00:00.02 | 44 |
--------------------------------------------------------------------------------------------------------------

Chris Saxon
August 24, 2017 - 12:59 pm UTC

Well, we've got E & A rows. But I don't see the name of any of your tables in that plan!

Did you run:

set serveroutput off


Before your query and the dbms_xplan select?

niranjan deshpande, August 25, 2017 - 3:09 am UTC

Getting below msg

select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'))

SQL_ID 55a2gx1n7b6qx, child number 0

BEGIN SYS.DBMS_OUTPUT.get_line (line => :line, status => :status);
END;

NOTE: cannot fetch plan for SQL_ID: 55a2gx1n7b6qx, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)


Chris Saxon
August 25, 2017 - 10:11 am UTC

What is your complete script? Show us everything you ran before this query.

Lots of rows joined to lots of rows = lots of results

Andrew Sayer, August 25, 2017 - 12:16 pm UTC

There doesn't seem to be any real filter in this query other than the join between the tables, if you join 25 million rows to 60K rows, you're likely to get a lot of results. There's not a lot of options for executing the query
-either nested loop from a small set of results and hope that the join doesn't actual return that many rows
-or read both tables and do a hash join, which will take a while for large tables.

After getting all those rows, it then needs to assign a row_number to each row before it can return any to you. Again, this could be quite a lot of work to sort your huge (exected) result set.

The key question would be, what would you plan on doing with all these expected results? Or is there a filter that's actually reducing the rows required?

As for the lack of decent results from your dbms_xplan calls, when you specify the sql_id argument as NULL it will use the most recently executed statement

SQL_ID 7squa6twczm8y, child number 2
-------------------------------------
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS
LAST'))


--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.02 | 44 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 1 | 8168 | 9 |00:00:00.02 | 44 |

Here, we can see that the most recently executed statement was a call to dbms_xplan, not your statement.

elect *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'))

SQL_ID 55a2gx1n7b6qx, child number 0

BEGIN SYS.DBMS_OUTPUT.get_line (line => :line, status => :status);
END;

NOTE: cannot fetch plan for SQL_ID: 55a2gx1n7b6qx, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Here, we can see that the most recent statement was a call to dbms_output. This would be the case if you have serveroutput on in SQL*Plus.

Repeating Chris's answer in the first post:
set serverout off
<run your query with row source statistics gathered, either through alter session set statistics_level=all or by adding the gather_plan_statistics hint>
IMMEDIATELY AFTER
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

Sanity check that this is for the query you executed, if it is not then you have done something wrong.
Connor McDonald
August 26, 2017 - 3:22 am UTC

nice input

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database