Databases
StackOverflow2013
Postsdbo
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?
 

Note that there are some explanatory texts on larger screens.

plurals
  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?
    primarykey
    data
    text
    <p>Consider Table:</p> <pre><code>Table Name:ORDER Columns: (ID (PK), ORDER_NUM, ORDER_STATUS, etc...) Index(ORDER_IDX) exists on (ORDER_NUM, ORDER_STATUS) together. There are various FKs too, on which Indexes exist as well. There are about 2 million rows in the table. </code></pre> <p>Consider SQL Query:</p> <pre><code>DELETE from ORDER where ORDER_NUM=234234; </code></pre> <p>For a particular ORDER_NUM value, the DELETE Query runs very slow first time (almost 5 seconds to delete 200 rows). <br></p> <p>But if I rollback and run DELETE Query again for <strong>same</strong> ORDER_NUM, the DELETE QUERY now runs in 200 milliseconds.</p> <p>Therefore, for ANY <strong>new</strong> ORDER_NUM supplied to this query - the query runs very slow.</p> <p><strong>What can I do to fasten the query first time itself? Do I have to rebuild indexes? Or anything else?</strong></p> <p><em>I am testing this from a Oracle SQL Client Tool (like TOAD/SQL-Developer) - after seeing this slow behavior within the web application where it is actually used.</em></p> <p><strong>EDIT>>></strong><br> <strong><em>Results of SET AUTOTRACE ON</em></strong></p> <p><strong>FIRST TIME when QUERY is RUN</strong></p> <pre><code> 3 user calls 0 physical read total multi block requests 4915200 physical read total bytes 4915200 cell physical IO interconnect bytes 0 commit cleanout failures: block lost 0 IMU commits 1 IMU Flushes 0 IMU contention 0 IMU bind flushes 0 IMU mbu flush </code></pre> <p><strong>SECOND TIME When Query is RUN</strong></p> <pre><code> 3 user calls 0 physical read total multi block requests 0 physical read total bytes 0 cell physical IO interconnect bytes 0 commit cleanout failures: block lost 0 IMU commits 1 IMU Flushes 0 IMU contention 0 IMU bind flushes 0 IMU mbu flush </code></pre> <p><strong>The EXPLAIN Plans</strong> - in both FIRST and SECOND RUN is exactly same - shown below:</p> <pre><code> ID OPERATION NAME ROWS Bytes Cost(%CPU) Time&lt;br&gt; ======================================================================================= 0 DELETE Statement 49 2891 41 (0) 00:00:01 1 DELETE ORDER 2 INDEX RANGE SCAN ORDER_IDX 49 2891 3 (0) 00:00:01 </code></pre> <p>You can see Very High Physical Reads, during the First Time.<br> Can I do anything at all to help with this situation?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USJasper
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      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?
      1. USJasper
      1. VTBountyStart
    2. VO
      singulars
      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?
      1. USChris Saxon
      1. VTFavorite
    3. VO
      singulars
      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?
      1. USJon Heller
      1. VTFavorite
    1. COcacheable data ? when you redo - they are still there - in memory the other problem is to many splits in index cluster - needs to be reorganised ....
      singulars
      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?
      1. USMike
    2. CO@jasper:insert 200 rows commit and then run this command `ANALYZE TABLE <TABLE_NAME> COMPUTE STATISTICS;` and then delete the records .Now check whats the time it take to delete
      singulars
      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?
      1. USGaurav Soni
    3. CO@GauravSoni That did not help. We are using Oracle 11g by the way.
      singulars
      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?
      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