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)
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 |
--------------------------------------------------------------------------------------------------------------
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)
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.
August 26, 2017 - 3:22 am UTC
nice input