Re: Delete based on rowid

  • From: DBA Deepak <oracle.tutorials@xxxxxxxxx>
  • To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 6 Oct 2009 20:22:00 +0530

Thanks Govind, for sharing your experience.

In our case the statistics are up to date. Am still trying to find out the
possible reason. Any help is appreciated.

On Tue, Oct 6, 2009 at 6:39 PM, Arumugam, Govind <
Govind.Arumugam@xxxxxxxxxxxxxxxxxxx> wrote:

>  Deepak,
>
> I don't have posting privileges yet on the oracle-l list.  But I think I
> have an answer to your question.  Yes, Accessing a row based on ROWID is the
> fastest way in Oracle.  I ran into a similar issue with a delete on AQ
> tables.  After collecting statistics at the table level, the data access
> changed from INDEX FULL SCAN to TABLE ACCESS BY USER ROWID.
>
> SQL Text: delete from "VZW_SCM"."QT_VM_AUDITEVENT_DATA" where rowid = :1
>
> SQL Profile:
>
> Stat Name                                Statement   Per Execution % Snap
> ---------------------------------------- ---------- -------------- -------
> Elapsed Time (ms)                         4,580,561           21.5     9.0
> CPU Time (ms)                             4,255,953           20.0    11.9
> Executions                                  212,673            N/A     N/A
> Buffer Gets                              ##########        1,850.2    23.4
> Disk Reads                                        3            0.0     0.0
> Parse Calls                                     271            0.0     0.0
> Rows                                        212,104            1.0     N/A
>
> SQL Execution Plan
>
> -------------------------------------------------------------------------------
> | Id  | Operation        | Name                  | Rows  | Bytes | Cost
> (%CPU)|
>
> -------------------------------------------------------------------------------
> |   0 | DELETE STATEMENT |                       |       |       |     1
> (100)|
> |   1 |  DELETE          | QT_VM_AUDITEVENT_DATA |       |
> |            |
> |   2 |   INDEX FULL SCAN| SYS_C0012284          |     1 |   117 |     0
> (0)|
>
> After collecting stats:
>
>
> -----------------------------------------------------------------------------------------------------
> | Id  | Operation                   | Name                  | Rows  | Bytes
> | Cost (%CPU)| Time     |
>
> -----------------------------------------------------------------------------------------------------
> |   0 | DELETE STATEMENT            |                       |     1 |    72
> |     1   (0)| 00:00:01 |
> |   1 |  DELETE                     | QT_VM_AUDITEVENT_DATA |       |
> |            |          |
> |*  2 |   TABLE ACCESS BY USER ROWID| QT_VM_AUDITEVENT_DATA |     1 |    72
> |     1   (0)| 00:00:01 |
>
> -----------------------------------------------------------------------------------------------------
>
> Hope this helps.
>
> Take care.
>
> Govind
>
>
> The information contained in this message and any attachment may be
> proprietary, confidential, and privileged or subject to the work
> product doctrine and thus protected from disclosure.  If the reader
> of this message is not the intended recipient, or an employee or
> agent responsible for delivering this message to the intended
> recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is strictly prohibited.
> If you have received this communication in error, please notify me
> immediately by replying to this message and deleting it and all
> copies and backups thereof.  Thank you.
>
>
>


-- 
Regards,

Deepak
Oracle DBA

Other related posts: