Skip to Main Content
  • Questions

  • Query is taking much time (not using index)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: July 04, 2019 - 9:45 pm UTC

Last updated: July 25, 2019 - 3:08 am UTC

Version: 18.4

Viewed 1000+ times

You Asked

Hello, Ask Tom.

I have a delete statement that involves three tables. All id are pk (indexes are present on each pk by default). There is also an index on t2.columnA but in the plan it says that there is a table access full on table2. The query is taking much time. What is happening here?

--delete statement
delete
from table1 t1
where t1.id in (select t3.id
from table2 t2,
table3 t3
where t2.id = t3.id
and t2.columnA in (
'1234',
'12345',
'123456',
'1234567',
'12345678'
));


Thanks in advanced.

Regards,

and Connor said...

Try this:

delete /*+ GATHER_PLAN_STATISTICS  */ 
from table1 t1
...
...


then run this:

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


and paste that as a review. This will show us how the plan was used and what the actual data was

Rating

  (4 ratings)

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

Comments

Review

Geraldo Peralta, July 05, 2019 - 6:13 pm UTC

Do I have to wait for the delete statement to finish in order to run "select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))"?
Connor McDonald
July 08, 2019 - 3:01 am UTC

Yes.


Review

Geraldo Peralta, July 08, 2019 - 3:19 pm UTC

This is the output meessage:

SQL_ID 2ygnt73ck3jk8, child number 0

begin dbms_utility.expand_sql_text ( input_sql_text => :sql,
output_sql_text => :x ); end;

NOTE: cannot fetch plan for SQL_ID: 2ygnt73ck3jk8, 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)

Review

Geraldo Peralta, July 09, 2019 - 1:06 pm UTC 

I ran the delete statement again. 


PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d161mzg4t17v8, child number 0
-------------------------------------
delete /*+ GATHER_PLAN_STATISTICS  */   from 
efact.detalle_productos_servicios d  where d.encabezado_b2b_id in 
(select eb.encabezado_b2b_id                                 from 
efact.encabezados e,                                      
efact.encabezados_b2b eb                                where 
e.encabezado_id = eb.encabezado_id                                  and 
e.rnc_emisor in ( '131880657', '131880606', '131880681', '131880703', 
'131880711' ))
 

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1491651541
 
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT           |                             |      1 |        |      0 |00:27:00.77 |     366M|       |       |          |
|   1 |  DELETE                    | DETALLE_PRODUCTOS_SERVICIOS |      1 |        |      0 |00:27:00.77 |     366M|       |       |          |
|*  2 |   HASH JOIN RIGHT SEMI     |                             |      1 |  86765 |  85620 |00:00:00.89 |     992 |  2601K|  2601K| 2204K (0)|
|   3 |    VIEW                    | VW_NSO_1                    |      1 |  17353 |  17124 |00:00:00.03 |     529 |       |       |          |
|*  4 |     HASH JOIN SEMI         |                             |      1 |  17353 |  17124 |00:00:00.03 |     529 |  2213K|  1864K| 2185K (0)|
|   5 |      VIEW                  | index$_join$_003            |      1 |  17353 |  17353 |00:00:00.01 |     156 |       |       |          |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  6 |       HASH JOIN            |                             |      1 |        |  17353 |00:00:00.01 |     156 |  2227K|  2096K| 2217K (0)|
|   7 |        INDEX FAST FULL SCAN| PK_ENCABEZADOS_B2B          |      1 |  17353 |  17353 |00:00:00.01 |      78 |       |       |          |
|   8 |        INDEX FAST FULL SCAN| IX_FK_ENC_ENCB2B            |      1 |  17353 |  17353 |00:00:00.01 |      78 |       |       |          |
|*  9 |      TABLE ACCESS FULL     | ENCABEZADOS                 |      1 |  17461 |  17461 |00:00:00.01 |     373 |       |       |          |
|  10 |    INDEX FAST FULL SCAN    | IX_FK_ENCB2B_DETAPRODSERV   |      1 |  86765 |  86765 |00:00:00.27 |     463 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("D"."ENCABEZADO_B2B_ID"="ENCABEZADO_B2B_ID")

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - access("E"."ENCABEZADO_ID"="EB"."ENCABEZADO_ID")
   6 - access(ROWID=ROWID)
   9 - filter(("E"."RNC_EMISOR"='131880606' OR "E"."RNC_EMISOR"='131880657' OR "E"."RNC_EMISOR"='131880681' OR 
              "E"."RNC_EMISOR"='131880703' OR "E"."RNC_EMISOR"='131880711'))
 

38 rows selected.

Connor McDonald
July 25, 2019 - 3:08 am UTC

That plan looks fine to me, in that the estimated rows and actual rows are in close alignment.

But - notice how fast it managed to *get* the rows to delete. It was only when the DELETE starting firing that the time got huge. So things to look at:

- are there triggers on the table that are slowing things down?
- are there dozens of indexes on that table?
- is it being blocked by other sessions?

Your plan looks good - now its time to trace the session and see where the time is being lost.

Run dbms_monitor.session_trace_enable(waits=>true)

and run that delete again. THen look at your tkrof formatted file for answers.

Review

Geraldo Peralta, July 25, 2019 - 12:47 pm UTC

Ok.

There are no triggers. There are not dozens of indexes on the table. I have to check blockings.

I will trace the session as you told me.

Thanks for the answer.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.