Re: Delete based on rowid

From: DBA Deepak <oracle.tutorials_at_gmail.com>
Date: Tue, 6 Oct 2009 20:22:00 +0530
Message-ID: <75aa80160910060752n1c4157ccv6c0dcad15fe6f9a2_at_mail.gmail.com>

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_at_verizonwireless.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 06 2009 - 09:52:00 CDT