This discussion is archived
1 2 Previous Next 25 Replies Latest reply: May 27, 2013 3:34 PM by Jonathan Lewis Go to original post RSS
  • 15. Re: db block gets while delete
    sb92075 Guru
    Currently Being Moderated
    kamilp wrote:
    My question is - is there any tool to find out more details about the query execution ? I would like to know what objects is database scanning causing db block gets both in select and delete.
    if you SQL_TRACE=TRUE, then the content of the trace file will contain the details regarding which file(s) & which blocks are actually being read.

    You can then reverse engineer the actual DB Object the reads are accessing.
  • 16. Re: db block gets while delete
    kamilp Newbie
    Currently Being Moderated
    SYS_C0014687 - is different database server, same structure and same problem
  • 17. Re: db block gets while delete
    kamilp Newbie
    Currently Being Moderated
    sb92075 wrote:

    if you SQL_TRACE=TRUE, then the content of the trace file will contain the details regarding which file(s) & which blocks are actually being read.

    You can then reverse engineer the actual DB Object the reads are accessing.
    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
    ORACLE_HOME = /opt/oracle/database
    System name:    Linux
    Node name:      ims
    Release:        2.6.32.12-0.7-default
    Version:        #1 SMP 2010-05-20 11:14:20 +0200
    Machine:        x86_64
    Instance name: orcl
    Redo thread mounted by this instance: 1
    Oracle process number: 65
    Unix process pid: 28469, image: oracle@ims (TNS V1-V3)
    
    
    *** 2013-05-27 10:51:08.251
    *** SESSION ID:(578.41368) 2013-05-27 10:51:08.251
    *** CLIENT ID:() 2013-05-27 10:51:08.251
    *** SERVICE NAME:(SYS$USERS) 2013-05-27 10:51:08.251
    *** MODULE NAME:(SQL*Plus) 2013-05-27 10:51:08.251
    *** ACTION NAME:() 2013-05-27 10:51:08.251
    
    =====================
    PARSING IN CURSOR #1 len=34 dep=0 uid=88 oct=42 lid=88 tim=1369644668250917 hv=3913151867 ad='7fa03696fe40' sqlid='14ys3d7nmvxbv'
    ALTER SESSION SET SQL_TRACE = TRUE
    END OF STMT
    EXEC #1:c=0,e=58,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1369644668250914
    
    *** 2013-05-27 10:52:01.462
    CLOSE #1:c=0,e=8,dep=0,type=0,tim=1369644721462308
    =====================
    PARSING IN CURSOR #2 len=45 dep=0 uid=88 oct=7 lid=88 tim=1369644721464238 hv=2789037062 ad='73ca406b0' sqlid='997rusum3up06'
    DELETE FROM MESSAGES2 WHERE ID = 138065763035
    END OF STMT
    PARSE #2:c=4000,e=1842,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1420548849,tim=1369644721464237
    
    *** 2013-05-27 10:56:29.885
    EXEC #2:c=9868617,e=268421318,p=186717,cr=3,cu=186717,mis=0,r=1,dep=0,og=1,plh=1420548849,tim=1369644989885616
    STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  MESSAGES2 (cr=3 pr=186717 pw=0 time=0 us)'
    STAT #2 id=2 cnt=1 pid=1 pos=1 obj=81659 op='INDEX UNIQUE SCAN SYS_C0014687 (cr=3 pr=3 pw=0 time=0 us cost=2 size=17 card=1)'
    
    *** 2013-05-27 10:57:06.934
    CLOSE #2:c=0,e=37,dep=0,type=0,tim=1369645026934336
    =====================
    PARSING IN CURSOR #1 len=35 dep=0 uid=88 oct=42 lid=88 tim=1369645026934843 hv=4067503723 ad='7fa03696fe40' sqlid='0pgs023t72bmb'
    ALTER SESSION SET SQL_TRACE = FALSE
    END OF STMT
    PARSE #1:c=0,e=422,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1369645026934842
    EXEC #1:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1369645026934948
    Above is my trace file, but I dont find any details regarding which file(s) & which blocks are actually being read. I have statistics_level setting 'ALL'.
  • 18. Re: db block gets while delete
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    EXEC #2:c=9868617,e=268421318,p=186717,cr=3,cu=186717
    It shows CPU time of 9.868seconds, elapsed time of 268seconds and Current Gets count of 186,717 blocks.

    What is the structure of the table ?


    Hemant K Chitale
  • 19. Re: db block gets while delete
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    kamilp wrote:
    after enabling/disabling constraints I have found exactly 1 constraint that causes high db block gets while my delete
    It would have been helpful to give us the definition of that constraint.

    However, based on the comments and numbers so far, it looks as if you have a referential integrity constraint on the folders2 table that references the messages2 table, and that the processing done by your system manages to insert and delete an enormous number of rows into the folders2 table for a message in such a way that a very large number of leaf blocks in the folders2 index are empty after holding the index entries for that message.

    When you attempt to delete the message from the messages2 table Oracle does an index range scan through all the empty leaf blocks for the folders2 index for that message, doing current block gets to check for existing (including existing and uncommitted) rows. You need to examine the processing that causes such a catastrophic state in the folders2 index.


    Regards
    Jonathan Lewis
  • 20. Re: db block gets while delete
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Jonathan Lewis wrote:
    However, based on the comments and numbers so far, it looks as if you have a referential integrity constraint on the folders2 table that references the messages2 table, and that the processing done by your system manages to insert and delete an enormous number of rows into the folders2 table for a message in such a way that a very large number of leaf blocks in the folders2 index are empty after holding the index entries for that message.
    Just created a little test do demonstrate the point: http://jonathanlewis.wordpress.com/2013/05/27/cu-gets/

    Regards
    Jonathan Lewis
  • 21. Re: db block gets while delete
    rp0428 Guru
    Currently Being Moderated
    >
    Just created a little test do demonstrate the point
    >
    Titled 'CU Gets' (not familiar with that acronym) which I think has a typo.
    Statistics
    ----------------------------------------------------------
               0  recursive calls
           107  db block gets
    . . .
    Note the db block gets statistics – 1007, compared to the 103 leaf blocks in the index – that’s a pretty good indication by itself that the workload that the OP is seeing is related to a massive delete from the child table with a large scan (possibly including a high volume of delayed block cleanout in his case).
    The stats show 107 gets but the comment says 1007.

    Also your comment would have been more helpful to many if it explained what your plan is showing and why it is 'related to a massive delete from the child table. . '. And since your delete is from the parent table, not the child table and you have ON DELETE CASCADE commented out won't your delete fail?
  • 22. Re: db block gets while delete
    kamilp Newbie
    Currently Being Moderated
    >
    However, based on the comments and numbers so far, it looks as if you have a referential integrity constraint on the folders2 table that references the messages2 table, and that the processing done by your system manages to insert and delete an enormous number of rows into the folders2 table for a message in such a way that a very large number of leaf blocks in the folders2 index are empty after holding the index entries for that message.

    When you attempt to delete the message from the messages2 table Oracle does an index range scan through all the empty leaf blocks for the folders2 index for that message, doing current block gets to check for existing (including existing and uncommitted) rows. You need to examine the processing that causes such a catastrophic state in the folders2 index.


    Regards
    Jonathan Lewis
    I think there is something about this. I try toi enable / disable foreign key constraint on table folders2 and indeed the number of db block gets depends on that constraint.
    After disabling this constraint the number of db block gets lowers to 88701 and after disabling the foreign key constraint on another table received2 the number of db block gets lowers to 14.
    SQL> analyze index FOLDERS2_IDX_MI validate structure;
    
    Index analyzed.
    
    Elapsed: 00:00:06.01
    SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;
    
            LF_ROWS         LF_BLKS     DEL_LF_ROWS
    --------------- --------------- ---------------
           29311258          520599         1839693
    
    Elapsed: 00:00:00.06
    Edited by: kamilp on May 27, 2013 2:16 PM
  • 23. Re: db block gets while delete
    rp0428 Guru
    Currently Being Moderated
    >
    I try toi enable / disable foreign key constraint on table folders2 and indeed the number of db block gets depends on that constraint.
    After disabling this constraint the number of db block gets lowers to 88701 and after disabling the foreign key constraint on another table received2 the number of db block gets lowers to 14.
    >
    Post the DDL for those constraints as ask for by J. Lewis.

    Also, was there at some point a large delete on the 'FOLDERS2' table, perhaps prior to deleting from the MESSAGES2 table?
  • 24. Re: db block gets while delete
    kamilp Newbie
    Currently Being Moderated
    >
    Post the DDL for those constraints as ask for by J. Lewis.
    >
      CREATE TABLE FOLDERS2 (
      ID NUMBER,
      PHEADER VARCHAR2(64), 
      CFNUMBER NUMBER, 
      TIME NUMBER, 
      MESSAGE_ID NUMBER, 
      METADATA_ID NUMBER, 
      PRIMARY KEY (ID) VALIDATE , 
      FOREIGN KEY (MESSAGE_ID) REFERENCES MESSAGES (ID) VALIDATE,
      FOREIGN KEY (METADATA_ID) REFERENCES METADATA2 (ID) VALIDATE )
    
      CREATE INDEX FOLDERS2_IDX_MI ON FOLDERS2 (MESSAGE_ID);
    >
    Also, was there at some point a large delete on the 'FOLDERS2' table, perhaps prior to deleting from the MESSAGES2 table?
    >
    Yes, there was large delete, according to application logs probably more than 50 percent of table. The application firstly deletes data from folders2 table and then from messages2
  • 25. Re: db block gets while delete
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    rp0428 wrote:
    Titled 'CU Gets' (not familiar with that acronym)
    I've changed it to CUR, which is a little more common, but CU is the shorthand used in trace files.
    Statistics
    ----------------------------------------------------------
    0  recursive calls
    107  db block gets
    . . .
    Note the db block gets statistics – 1007, compared to the 103 leaf blocks in the index – that’s a pretty good indication by itself that the workload that the OP is seeing is related to a massive delete from the child table with a large scan (possibly including a high volume of delayed block cleanout in his case).
    The stats show 107 gets but the comment says 1007.
    Thanks - now fixed.

    >
    Also your comment would have been more helpful to many if it explained what your plan is showing and why it is 'related to a massive delete from the child table. . '.
    Made a couple of enhancements to the description.
    And since your delete is from the parent table, not the child table and you have ON DELETE CASCADE commented out won't your delete fail?
    I think you missed the lines "delete from child; commit;"

    Regards
    Jonathan Lewis
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points