OraFAQ Forum: Performance Tuning » checking the delete performance in the package

Home » RDBMS Server » Performance Tuning » checking the delete performance in the package (10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

checking the delete performance in the package [message #581974]

Fri, 12 April 2013 05:27

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Hi,

I need to check the package performance and need to improve the package performance.

1. how to check the package performance(each and every statment in the package)?
2. In the pacakge using the delete statement to delete all records and observed that delete is taking long time to delete all the records in the table(Table records 7000000).This table is like staging table.Daily need to clean the data before inserting the data into it. what can I use instead of Delete.

Please advice.

Report message to a moderator

Re: checking the delete performance in the package [message #581976 is a reply to message #581974]

Fri, 12 April 2013 05:35

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

1) tkprof would be a place to start.
2) if you want to empty the table use truncate.

Report message to a moderator

Re: checking the delete performance in the package [message #582253 is a reply to message #581976]

Tue, 16 April 2013 07:08

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

TKPROF is good tool. But if you don't know what TRUNCATE is then maybe TKPROF is not the first place to start. Also TKPROF requires access to the HOST machine running the database and since SOX, many companies restrict such access making TKPROF unavailable, or requiring cooperation with an overworked SYSDBA.

So for this particular problem, assuming you can modify the code, the easiest way to get timings is to simply put some instrumenation into your code. Here is an exampe:

begin
   dbms_output.put_line('START TIME 1 = '||TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS'):
   -- DO SQL#1 HERE
   dbms_output.put_linE('END TIME = 1 '||to_char(sysdate,'dd-mon-rrrr hh24:mi:ss');

   dbms_output.put_line('START TIME 2 = '||TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS'):
   -- DO SQL#2 HERE
   dbms_output.put_linE('END TIME = 2 '||to_char(sysdate,'dd-mon-rrrr hh24:mi:ss');
end;
/

As you see, very primitive but it is easy, anyone with access to the code can do it, works in every Oracle environment currently supported.

Once you graduate from doing your own simple instrumentation, you can move on to using other people's stuff.

If you have TOAD then you can get your SYSDBA to install and make available DBMS_PROFILER. TOAD makes it easy to use (not an endorsement, just an observation).

There is always Tom Kyte's RUNSTATS utility.

And of course Tanel Poder's Snapper.

Good luck.

Report message to a moderator

Re: checking the delete performance in the package [message #582324 is a reply to message #582253]

Wed, 17 April 2013 03:39

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Thank you very much.

suppose tracing is not enabled and didn't keep the dbms_output in my package/procedure.How can I check the previous days performance(that is yesterday/day before yesterday etc) of the package/procedure?

Please advice.

Report message to a moderator

Re: checking the delete performance in the package [message #582326 is a reply to message #582324]

Wed, 17 April 2013 03:57

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you have statspack or awr installed and running you can probably get some information from them.
Otherwise you can't get anything.

Report message to a moderator

Re: checking the delete performance in the package [message #582542 is a reply to message #581974]

Fri, 19 April 2013 05:24

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Hi,

I have package and trace file with me. Please let me know how to analyze the trace file.

Attaching the package and trace file as below.


TKPROF: Release 10.2.0.4.0 - Production on Fri Apr 19 09:47:35 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: pobom2_ora_32634.trc
Sort options: prsela  fchela  exeela
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

BEGIN tidypackage.processTidyUp8DayForPlant(:1); END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1   6717.64    6797.86     917817  836433488   40323599           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1   6717.64    6797.86     917817  836433488   40323599           1

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

DELETE ORDER_DIFFERENCE_COMMODITY
WHERE
 ORDER_DIFFERENCE_COMMODITY.PLANT_CODE = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1   4111.42    4023.65      23145  540425503     752289      122723
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1   4111.42    4023.65      23145  540425503     752289      122723

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 73     (recursive depth: 1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                      2418        0.29          0.49
  db file scattered read                         99        0.00          0.02
  log file switch completion                      3        0.09          0.18



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1   6717.64    6797.86     917817  836433488   40323599           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1   6717.64    6797.86     917817  836433488   40323599           1

Misses in library cache during parse: 0
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                      2421        0.29          0.55
  db file scattered read                         99        0.00          0.02
  log file switch completion                      3        0.09          0.18
  SQL*Net message to client                       2        0.00          0.00
  log file sync                                   2        0.00          0.01
  SQL*Net message from client                     2        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       37      0.04       0.04          0          0          2           0
Execute  42083   4115.09    4027.19      23148  540425510     752299      122725
Fetch    42318   2072.72    2025.62       8059  277568818          0       44922
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    84438   6187.86    6052.86      31207  817994328     752301      167647

Misses in library cache during parse: 23
Misses in library cache during execute: 24

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                      8059        0.07          1.87
  latch: shared pool                              2        0.00          0.00
  latch: cache buffers chains                     1        0.00          0.00

    2  user  SQL statements in session.
  424  internal SQL statements in session.
  426  SQL statements in session.
********************************************************************************
Trace file: pobom2_ora_32634.trc
Trace file compatibility: 10.01.00
Sort options: prsela  fchela  exeela
       1  session in tracefile.
       2  user  SQL statements in trace file.
     424  internal SQL statements in trace file.
     426  SQL statements in trace file.
      28  unique SQL statements in trace file.
   98072  lines in trace file.
    2049  elapsed seconds in trace file.

Attachment: Package.txt
(Size: 13.14KB, Downloaded 2040 times)

Report message to a moderator

Re: checking the delete performance in the package [message #582551 is a reply to message #582542]

Fri, 19 April 2013 06:34

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

Oracle spent 4111.42 seconds doing the delete

Report message to a moderator

Re: checking the delete performance in the package [message #582556 is a reply to message #582551]

Fri, 19 April 2013 07:26

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Yes.You are right. Is there any way to reduce that time in the package.

Report message to a moderator

Re: checking the delete performance in the package [message #582565 is a reply to message #582556]

Fri, 19 April 2013 07:52

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

Considering it's a single delete statement, with no noticable wait events and no other code running, I suspect you're going to have to buy better hardware, or look at options like partitioning.
Others may have better ideas though.
I suggest you post the explain plan for the delete, along with the table structure and details of any indexes.

EDIT: typo

[Updated on: Fri, 19 April 2013 07:52]

Report message to a moderator

Re: checking the delete performance in the package [message #582578 is a reply to message #582565]

Fri, 19 April 2013 09:36

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

We donot have any index other than primary key index. Please we have only table structure and dont have the explain plan.

 CREATE TABLE ORDER_DIFFERENCE_COMMODITY
   (COMMODITY_ID VARCHAR2(20 BYTE), 
    ORDER_NUMBER VARCHAR2(6 BYTE), 
    ORDER_OFFLINE_DATE DATE NOT NULL , 
    DIFFERENCE_TYPE NUMBER, 
    PLANT_CODE VARCHAR2(1 BYTE)NOT NULL, 
    K_SPEC_BATCH_NO VARCHAR2(8 BYTE),
    primary key(COMMODITY_ID, ORDER_NUMBER)
   );

  ALTER TABLE ORDER_DIFFERENCE_COMMODITY ADD FOREIGN KEY (COMMODITY_ID)
	  REFERENCES COMMODITY (COMMODITY_ID) ENABLE;
 
  ALTER TABLE ORDER_DIFFERENCE_COMMODITY ADD FOREIGN KEY (PLANT_CODE)
	  REFERENCES SCS_PLANT (PLANT_CODE) ENABLE;

Please suggest.

Report message to a moderator

Re: checking the delete performance in the package [message #582731 is a reply to message #582578]

Mon, 22 April 2013 05:47

nlnkapardi
Messages: 26
Registered: April 2013
Location: India

Junior Member

hello GRPatwari

Hope you are doing good !

Can you please try with Hints. Also When you are Executing the Package I Have found DML Statements are there. So make Sure When you are Running the Package There is no Any locks on the following tables like.,

1.) SCS_HISTORY_ORDER
2.)SCS_HISTORY_ORDER_COMMODITY
3.)SCS_HISTORY_ORDER_CMDTY_PART
4.)OBOM_ORDER_COMMODITY
5.)ORD_DIFF_PARTS_WITH_DETAILS

And Comming to locks you can Query from v$lock Table., Where you can get Object level locks and the Table level locks even.

Please Feel Free to contact me

Regards
kapardi

Report message to a moderator

Re: checking the delete performance in the package [message #582736 is a reply to message #582731]

Mon, 22 April 2013 06:09

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Hi,

I hope delete statement is taking the time by checking the trace file.I have observed two following scenarios.I need to test the below scenarios once.

Observation:

Delete statement is doing full table scan (because at any cost need to delete full data from the table may be million of data)

Recommendations:
-----------------

1. If we create the bitmap index on plant_code then it is taking ROWID index scan and then use the index hint on delete statement.
2. If we create the normal index on plant_code then it is taking range scan then use the index hint on delete statement.

Please advice if I am going wrong way.

Report message to a moderator