Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The key to understand your problem is to understand how statements are executed. DELETE is a relatively expensive operation and often leads to performance problems. So here is how Oracle executes a DML statement:</p> <ol> <li>The first step in executing DML is to find the required blocks in the database buffer cache (if they are already there) or copy them into the buffer cache from the datafiles (slow). In addition to that, an empty block of an undo segment is also copied into the buffer cache.</li> <li>Then, locks are placed on the affected rows and indices.</li> <li>After that, redo is generated: Change vectors describing all the changes done to the data block and undo block are generated. For a DELETE, the change vector to be written to the undo block is the entire row.</li> <li>Then, the DELETE is carried out. The whole row is copied from the data block to the undo block and the row in the data block is deleted. DELETE generates much more undo data than an INSERT for example, because the contents of the whole row are copied (so other sessions can read the original data or the deletion can be rolled back).</li> </ol> <p>Your query almost certainly runs faster the second time <strong>because all the relevant blocks are already in the database buffer cache</strong>. Of course, the more blocks can be held in the database buffer cache, the less I/O is needed. <strong>Make sure your SGA is sized appropriately.</strong></p> <p>So for your problem, we have to look at the following points:</p> <ul> <li>Most importantly, <strong>does your query use your index?</strong> Is the index VALID? Run the EXPLAIN PLAN for your DELETE query and check if the index for ORDER_NUM is used and how your data is accessed.</li> <li>Are there any <strong>CONSTRAINTS</strong> on the table? If there are CONSTRAINTS with "ON DELETE CASCADE", there might be other tables affected by your DELETE.</li> </ul> <p>So for your problem, looking at the Execution Plan (EXPLAIN PLAN) might be your best bet.</p>
    singulars
    1. This table or related slice is empty.
    1. PODELETE QUERY Runs Slow First Time, but second time onwards (for same condition) runs fast - How to make the query fast at first time running?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USSimon K.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSimon> How can i check if my SGA is sized properly? Anything i need to look at Table Level? The EXPLAIN PLAN results, and CONSTRAINT are the things i looked at first - they are fine. My main question is: First time DELETE takes almost 5 seconds, and next time onwards same delete (as long as condition remains same) takes just 200 milliseconds. What can i do to improve this situation. Thanks
      singulars
      1. PO
      1. USJasper
    2. COFirst, find out how big your database is. How big are your most accessed tables? Since you have current statistics, it's easiest to use `select bytes/1024/1024 mb from dba_segments where owner='SIMON';` or similar. Then, look at `v$sga` to check how big your SGA is. In the best case, the "Database Buffers" are bigger than your data, but that is not always applicable. As mentioned above, the second time you execute that query, all affected blocks are already in memory (the database buffer cache) and can therefore be deleted almost instantly. That is the reason the second query executes so fast.
      singulars
      1. PO
      1. USSimon K.
    3. COThanks - i have added SET AUTOTRACEON Results.
      singulars
      1. PO
      1. USJasper
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload