Solved

Issue with a DELETE sentence that running slowly

Posted on 2014-12-22
9
141 Views
Last Modified: 2014-12-29
Hi experts,
I'm trying to play a test environment, the behavior of a "DELETE" massive, that is taking much more than expected.
The production environment has RAC and ASM, but not the test environment.

I modified the lines of the package that performs the delete and making the trace of the session and then using the tkprof, I see:

The deletes are executed whith:
BEGIN HISR_RETEN.p_hisr_retention_all; END;

Open in new window

Running the package without modifications:
DELETE FROM A_5MIN_002 
WHERE
 :past_date > UTCTIME AND rownum < 300000 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     11    116.57     481.75      47230     170437   20818013     2922360
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12    116.57     481.75      47230     170437   20818013     2922360

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 135     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  A_5MIN_002 (cr=11514 pr=11413 pw=0 time=77607604 us)
    299999     299999     299999   COUNT STOPKEY (cr=3273 pr=1637 pw=0 time=833298 us)
    299999     299999     299999    INDEX FULL SCAN SYS_C0033585 (cr=3273 pr=1637 pw=0 time=384103 us cost=2259 size=321336 card=26778)(object id 653635)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     47230        0.10        364.47
  log buffer space                               16        0.05          0.84
  reliable message                               62        0.00          0.01
  log file switch (checkpoint incomplete)         7        0.00          0.03
  log file switch completion                     14        0.01          0.08
  log file switch (private strand flush incomplete)
                                                  1        0.00          0.00
  buffer busy waits                               2        0.00          0.00
  latch: redo allocation                          1        0.00          0.00
  latch: cache buffers chains                     1        0.00          0.00

Open in new window

The "DELETE" takes:
Elapsed: 00:14:56.43

With the same conditions I run the pkg with modifications:
DELETE FROM A_5MIN_002 WHERE :past_date > UTCTIME AND rownum < 300000 
                              AND pointnumber = :r_point 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       30      0.00       0.00          0          0          0           0
Execute     60    156.89     578.57      54507      36077   29670425     2921790
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       90    156.90     578.57      54507      36077   29670425     2921790

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 135     (recursive depth: 1)
Number of plan statistics captured: 4

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  A_5MIN_002 (cr=612 pr=3653 pw=0 time=28506764 us)
     97393      97393      97393   COUNT STOPKEY (cr=472 pr=0 pw=0 time=232112 us)
     97393      97393      97393    INDEX RANGE SCAN SYS_C0033585 (cr=472 pr=0 pw=0 time=96012 us cost=3 size=13395 card=893)(object id 653635)

Open in new window

Elapsed: 00:12:44.48

As can be seen the modification adds a " AND pointnumber =" adding a filter to "select" original.

In both cases "DELETE" is made on a range of tables, deleting 5.8 Million records.
 
My inquiry is about:

Getting the execution plan: Why change the item "INDEX FULL SCAN" (original) by the "INDEX RANGE SCAN" (modified) ?

And in both cases, I see the item "db file sequential read" with a large number of waits,
Can I understand the slow "DELETE", being that this is high?

Should I improve it?
How I can improve?
Attached generic scripts, tables and their indexes.
Also, tkprof outputs.
If necessary, I can attach the script package, too.
Thankyou in advanced.
Regards
A-5MIN-001.SQL
tkprof-pkg-original
tkprof-pkg-modified
0
Comment
Question by:carlino70
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 167 total points
Comment Utility
How many rows are left after the delete of the 6 million rows?

It might be quicker to create a new table with the remaining rows then drop the original and rename the new one.

Deletes take time.  The also generate redo.  If you are in archive redo mode, a LOT or archived redo as well.

You might delete them in smaller chunks.  Instead of 300,000 or 200,000 per chunk, maybe 100,000 of 50,000?
0
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 166 total points
Comment Utility
I totally agree with slightwv upon deleting such a huge number of rows in one chunk being slow almost all the time.
Apart from that, I just want to add a few things:
I suppose you did this on a TEST system?! If so, are the statistics up to date and -in case you want to compare the 2 executions- you should do a "alter system flush buffer_cache;" and "alter system flush shared_pool;" between the 2 comparisons.
Regarding the "db seq reads" here are some useful links:
http://logicalread.solarwinds.com/oracle-db-file-sequential-read-wait-event-part2-mc01/#.VJkj9bAWC3A
http://logicalread.solarwinds.com/oracle-db-file-sequential-read-wait-event-part1-mc01/#.VJkj9LAWC3A
http://www.dba-oracle.com/art_builder_io_speed.htm

If your system is configured properly, I suppose there is little you can tune to reduce the overall time of the deletion of 6 million rows, unless you want to exchange some hardware (e.g. more RAM, SSD disks ...) in order to increase the I/O.

Btw.: What about triggers and/or foreign key constraints upon that table?!
0
 

Author Comment

by:carlino70
Comment Utility
Very clear!

I want to clarify: the production system sweeps around 400 tables, doing "delete" in each particular case, according to configración. This is a scheduled task, and does not create a temporary table with the desired records.

Perhaps the stage in "TEST" is an exaggeration, but I looked it to be so.

As the "DELETE" is triggered every 1 hour, only deletes records surpluses, and let those who are in a retention period.

I made the "flush" suggested between the execution of each test.
alter system flush buffer_cache;
alter system flush shared_pool;

Open in new window

I'll try to take smaller segments, according to the suggestion and I will read the suggested documentation.

Do you see the need to create a new index?

Thanks again.

Regards
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Usually no, I would not suggest creating an index to support a "delete" statement.  It is possible that another index could make this delete statement faster, but this additional index will make inserts slower.

You had asked earlier about why your "explain plan" changed from "INDEX FULL SCAN" (original) to:  "INDEX RANGE SCAN" (modified).  This is likely because the filter that you added (AND pointnumber = :r_point) matches a column in this index (SYS_C0033585).  We don't know which columns of this table are indexed in your database, or how many columns are included in this/these index(es).  Without that information, it is difficult for us to tell you exactly what your best option is for this
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:carlino70
Comment Utility
markgeer:
"AND pointnumber = :r_point"
r_point is a list of pointnumbers from each table A_5MIN_XXX
The indexes in each table are:
CREATE UNIQUE INDEX A_5MIN_001_PK ON A_5MIN_001
(POINTNUMBER, UTCTIME)
LOGGING
TABLESPACE XA_HISR_HIST_INDEX_TS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

Open in new window

and:
CREATE INDEX I_A_5MIN_001 ON A_5MIN_001
(POINTNUMBER)
LOGGING
TABLESPACE XA_HISR_HIST_INDEX_TS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

Open in new window

describe a_5min_001
TABLE a_5min_001
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 UTCTIME                                   NOT NULL DATE                        
 POINTNUMBER                               NOT NULL NUMBER                      
 SITEID                                             NUMBER                      
 VALOR_INST                                         FLOAT(126)                  
 TLQ_INST                                           NUMBER                      
 VALOR_PROM                                         FLOAT(126)                  
 TLQ_PROM                                           NUMBER                      
 VALOR_MAX                                          FLOAT(126)                  
 TLQ_MAX                                            NUMBER                      
 UTCTIME_MAX                                        DATE                        
 VALOR_MIN                                          FLOAT(126)                  
 TLQ_MIN                                            NUMBER                      
 UTCTIME_MIN                                        DATE

Open in new window

0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 167 total points
Comment Utility
That information helps us understand why the "explain plan" changed when you added the filter on  pointnumber.  This column is the first column in the PK index, so with that included as a filter, Oracle is able to use a "range scan" of that index.  Without that value in the "where" clause, Oracle was forced to do the "INDEX FULL SCAN" since the other column you supplied a value for (the UTCTIME column) is the second column in that PK index.

It looks like you now have a non-unique index on just the pointnumber column, plus that is the first column in the PK index.  I don't see a reason for the non-unique index on pointnumber by itself.  That looks to me like just a waste of space and a performance penalty.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
With it currently taking 12 minutes, what is expected the performance goal?  If you are looking to get it to 1 minute, give up now and buy a LOT of new hardware.

>> That looks to me like just a waste of space and a performance penalty.

I agree but I doubt just removing that index would be a huge performance increase.

The other thing to take into consideration that I don't think has been discussed is the hardware and its configuration.  If you are on RAID5 then you might look to get it on a faster RAID configuration.
0
 

Author Comment

by:carlino70
Comment Utility
ok, first get in conclusion that the elimination of the index on "pointnumber" help improve performance.

Furthermore, searches in hardware (storage is on a RAID 6 array) were performed. On the storage I have not found errors.

Do you advise me to implement the use of a "profile" applied to delete? I've never used

Thanks in advance.

Regards
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
No, I do not believe that adding a "profile" to this delete will make it faster.  For some SQL queries that involve multiple tables, adding a "profile" can help.  But, for a single-table SQL statement (like this delete) that won't help.

Remember that Oracle has to do almost as much work for delete commands as for insert commands.  The best way in Oracle do big deletes is to avoid the deletes.  You can usually do this by partitioning the table, then do an "alter table ... drop partition" command when the data is old enough to delete.  This command can be 1000 to 1000000 (or more) times faster for Oracle than doing a delete of each row.

If you can't (or don't want to) partition this table for some reason, another option to avoid the delete is to do a multi-step data copy with "no logging" like this:
1. create table [new_table] as select * from [old_table] where rownum < 1;
2. alter table [new_table] no logging;
3. insert /*+APPEND */ into [new_table] select * from [old_table]
where [some_date] > [the oldest date you need to keep];
4. drop the indexes on [old_table]
5. create the same indexes on [new_table]
6. check the grants and constraints on the [old_table] table, and copy them to the [new_table]
7. drop the [old_table]
8.rename the [new_table] to the name of the [old_table]
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now