Home » RDBMS Server » Performance Tuning » delete statement taking forever (10.2.0.3)
delete statement taking forever [message #516780] Wed, 20 July 2011 16:31 Go to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Hi Experts,
One of my delete statement is taking forever
to delete the record.The execution plan is
same which we was being used before when it was fine.
When I look at v@session and v$session_wait,session is showing active.
Can you please tell me where can i concentrate
looking at database to know the reason behind it.
I would like to know how to pursue on this to interpret the
reason behind it.Is there any way I can figure out what exactly
is happening on the background when it is trying to delete.
Thanks,
Varun



CM: removed [code] tags - they're for code not text.

[Updated on: Wed, 20 July 2011 17:55] by Moderator

Report message to a moderator

Re: delete statement taking forever [message #516782 is a reply to message #516780] Wed, 20 July 2011 17:55 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
trace the session to see where the time is being spent.
Re: delete statement taking forever [message #516783 is a reply to message #516782] Wed, 20 July 2011 18:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
does it utilize bind variables?

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR('<sql_id>') FROM DUAL;


http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/instance_tune.htm#CACGEEIF

You can monitor the statistics for SQL statement execution using the V$SQL_MONITOR and V$SQL_PLAN_MONITOR views. You can use these views in conjunction with the following views to get additional information about the execution being monitored:
V$ACTIVE_SESSION_HISTORY
V$SESSION
V$SESSION_LONGOPS
V$SQL
V$SQL_PLAN
Re: delete statement taking forever [message #516798 is a reply to message #516780] Wed, 20 July 2011 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68500
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First have a look at alert.log for any message like "checkpoint nor complete" or "cannot switch log"...

Regards
Michel

Re: delete statement taking forever [message #516956 is a reply to message #516798] Thu, 21 July 2011 08:43 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Hi Michael,
I dont see any messages like "checkpoint nor complete" or "cannot switch log" in the alert log file.
Thanks,
Varun

[Updated on: Thu, 21 July 2011 08:44]

Report message to a moderator

Re: delete statement taking forever [message #516959 is a reply to message #516956] Thu, 21 July 2011 08:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: delete statement taking forever [message #516967 is a reply to message #516959] Thu, 21 July 2011 09:16 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
BlackSwun,
I have only read only access to production database .
So i am not able to generate explain plan and
trace files.
Thanks for your help though.

Varun
Re: delete statement taking forever [message #516969 is a reply to message #516967] Thu, 21 July 2011 09:18 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you can't generate trace files then you need to get someone else to do so for you or you will not be able to get to the bottom of this.
Re: delete statement taking forever [message #516972 is a reply to message #516969] Thu, 21 July 2011 09:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> set autotrace on explain
SQL> select sysdate from dual;

SYSDATE
-------------------
2011-07-21 07:20:25


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation	 | Name | Rows	| Cost (%CPU)| Time	|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |	|     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL	 |	|     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

SQL> 



when all else fails Read The Fine Manual

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_9010.htm#i2061796

EXPLAIN PLAN is just another SQL statement that you could issue!
Re: delete statement taking forever [message #516987 is a reply to message #516969] Thu, 21 July 2011 10:59 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Experts,
I am attaching TKPROF ouput for the session.
Please guide me how to interpret this output properly.
Thanks,
Varun
Re: delete statement taking forever [message #516991 is a reply to message #516987] Thu, 21 July 2011 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68500
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have attached nothing.
Please post in the same way than BlackSwan did.

Regards
Michel
Re: delete statement taking forever [message #516995 is a reply to message #516991] Thu, 21 July 2011 12:01 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Hi Experts,
I am not able to attach file because of the size of file.
I am putting some part of it.Please let me know how could
i attach 5.49 mb file
********************************************************************************

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      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                        133        0.37          4.21
  db file sequential read                         1        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     3110      0.04       0.11          1          2         20           0
Execute   3110    872.93    1444.55    1984225   43191547      22326           0
Fetch     2491      0.98       1.27         15     182635          0        2491
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     8711    873.96    1445.94    1984241   43374184      22346        2491

Misses in library cache during parse: 6
Misses in library cache during execute: 6

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                      26132        0.56        511.40
  db file sequential read                     11411        0.36         60.29
  latch free                                      1        0.00          0.00
  latch: cache buffers chains                    19        0.00          0.00

  619  user  SQL statements in session.
 2491  internal SQL statements in session.
 3110  SQL statements in session.
********************************************************************************
Trace file: pismnwrk_ora_4664.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
     619  user  SQL statements in trace file.
    2491  internal SQL statements in trace file.
    3110  SQL statements in trace file.
       6  unique SQL statements in trace file.
   80053  lines in trace file.
 2097469  elapsed seconds in trace file.
Re: delete statement taking forever [message #516996 is a reply to message #516995] Thu, 21 July 2011 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>One of my delete statement is taking forever
we still have NO idea what is actual SQL.
We asked for EXPLAIN PLAN for problem SQL.
You have a mystery & we still have no clues.
unless & until you provide requested & meaningful details, You're On Your Own (YOYO)!
Re: delete statement taking forever [message #517024 is a reply to message #516996] Thu, 21 July 2011 19:10 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
Does the delete statement call any functions?
Does the table being deleted from have any delete triggers?
Re: delete statement taking forever [message #517117 is a reply to message #517024] Fri, 22 July 2011 13:18 Go to previous message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Hi Experts,
It was one the weird error.After going through all the steps,
I tried to delete the row manually and realized that child
record is still in the database.There were two refernce keys
in the table referring to two different columns of child table.
Deveoper put only one key in the join condition in the proc and as a
result it was getting stuck on parent table as other key was missing in join condition.
Thanks for you help though.

-Varun

[Updated on: Fri, 22 July 2011 13:19]

Report message to a moderator

Previous Topic: Sql Query comsuming time
Next Topic: big table partitioning
Goto Forum:
  


Current Time: Fri Sep 22 12:50:50 CDT 2023