Results 1 to 12 of 12

Thread: SQL statement

  1. #1
    Join Date
    Dec 2003
    Location
    Buenos Aires, Argentina
    Posts
    86

    Unanswered: SQL statement

    Hi,
    I have table X with no more than 370 rows, lets say
    Table X
    cod number (30)
    field1 varchar2
    field2 varchar2

    It's referenced by table Y (that contains almost 7million rows), with a FK (field1 and field2).
    When I perform
    SQL> delete from X where cod = any_number;

    It last more than 5min (cause I think it performs a full scan on tab Y)... This is a test environment, so it has low or no activity.
    Im newbie about SQL performance, so Im not sure how to analize the query (or the tbs an idx) to improve performance... so I only create an Index on table Y with field1 and field2... but, performance is still low...
    Can anybody give a hand on this. Specially on how to monitorice the SQL statement.
    Thanks in advance,

    Manf

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    alter session set sql_trace=true;
    delete from X where cod = any_number;
    alter session set sql_trace=false;

    The run the *trc file thru TKPROF with EXPLAIN=USERNAME/PASSWORD
    and the post the results here
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by Manf
    Hi,
    ... so I only create an Index on table Y with field1 and field2... but, performance is still low...
    Manf
    Dont know why you created an Index on table Y when you are actually trying to delete on table X.. ? .. as your example shows:

    Code:
    SQL> delete from X where cod = any_number;
    The index should be created on table X column cod.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    disable the FK and it should run fine.

    Currently when you delete from X, Oracle is searching through Y
    to determine if any values exist that are dependent.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by The_Duck
    Currently when you delete from X, Oracle is searching through Y
    to determine if any values exist that are dependent.
    Thought this was in the case you were deleting from Y, not from X ?

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    The Duck is right ... If, however you have a cascading delete fkey, then
    you better have the foreign key indexed ... What version of Oracle ???
    If you are using cost based optimizer, are the statistics up to date ???

    Again, as stated earlier, we can help you better if you post the explain plan
    here ...

    Gregg

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    let's do a benchmark:
    PHP Code:
    topicadm@Topic_Devcreate table temp_x as select distinct object_type from dba_objects;

    Table created.

    Elapsed00:00:00.04
    topicadm
    @Topic_Devcreate table temp_y as select from dba_objects;

    Table created.

    Elapsed00:00:00.02
    topicadm
    @Topic_Dev> declare 
      
    2  begin 
      3  
    for i in 1..1000 loop
      4  insert into temp_y select 
    from dba_objects;
      
    5  end loop;
      
    6  end;
      
    7  /

    PL/SQL procedure successfully completed.

    Elapsed00:00:18.09

    topicadm
    @Topic_Devselect count(*) from temp_y;

      
    COUNT(*)
    ----------
       
    5095200

    Elapsed
    00:00:00.03

    topicadm
    @Topic_Devinsert into temp_x (object_typevalues (1);

    1 row created.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devinsert into temp_x (object_typevalues (2);

    1 row created.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devinsert into temp_x (object_typevalues (3);

    1 row created.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devcommit;

    Commit complete.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devdelete temp_x where object_type '1';

    1 row deleted.

    Elapsed00:00:00.01
    topicadm
    @Topic_Devdelete temp_x where object_type '2';

    1 row deleted.

    rollback;

    topicadm@Topic_Devalter table temp_y add (
      
    2    constraint fk_test foreign key (object_type
      
    3      references temp_x (object_type));

    Table altered.

    topicadm@Topic_Devdelete temp_x  where object_type '2';

    1 row deleted.

    Elapsed00:00:03.01
    topicadm
    @Topic_Devrollback;

    Rollback complete.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devdelete temp_x where object_type '1';

    1 row deleted.

    Elapsed00:00:03.00 

    as you see, with the FK it takes 3 seconds to delete a record.
    without the FK it takes almost no time.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    actually, to mimic in greater detail what the original post was doing:

    PHP Code:
    topicadm@Topic_Dev> declare 
      
    2  
      3  cursor c1 is
      4  select 
    from temp_x for update;
      
    5  vcount  number;
      
    6  
      7  begin 
      8  vcount 
    := 1;
      
    9  
     10  
    for i in c1 loop
     11  
     12  update temp_x set object_id 
    vcount
     13  where current of c1
    ;
     
    14  
     15  vcount
    := vcount+1;
     
    16  end loop;
     
    17  end;
     
    18  /

    PL/SQL procedure successfully completed.

    Elapsed00:00:00.00
    topicadm
    @Topic_Dev>  commit;

      
    1select object_idobject_type from temp_x where object_type in ('1','2','3')
    topicadm@Topic_Dev> /

     
    OBJECT_ID OBJECT_TYPE
    ---------- ------------------
            
    27 3
            26 2
            25 1

    Elapsed
    00:00:00.00
    topicadm
    @Topic_Devdelete temp_x where object_id 25;

    1 row deleted.

    Elapsed00:00:03.00
    topicadm
    @Topic_Devset autotrace traceonly explain statistics
    topicadm
    @Topic_Devdelete temp_x where object_id 26;

    1 row deleted.

    Elapsed00:00:04.03

    Execution Plan
    ----------------------------------------------------------
       
    0      DELETE STATEMENT Optimizer=CHOOSE
       1    0   DELETE OF 
    'TEMP_X'
       
    2    1     TABLE ACCESS (FULLOF 'TEMP_X'




    Statistics
    ----------------------------------------------------------
              
    6  recursive calls
              7  db block gets
          64271  consistent gets
          20817  physical reads
            448  redo size
            457  bytes sent via SQL
    *Net to client
            449  bytes received via SQL
    *Net from client
              4  SQL
    *Net roundtrips to/from client
              1  sorts 
    (memory)
              
    0  sorts (disk)
              
    1  rows processed 

    see the FULL table scan there?
    I guess I need an index on object_id

    In general I would say the poster needs to normalize his db a little.
    maybe "COD" should be the FK (pass COD over to the larger table
    instead of two fields) which then the FK represents FIELD1 and FIELD2
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I have had tables that went down 20 layers of FK's where all the tables were tied together with Foreign Keys that had cascade delete and there is no delay when I delete a row from the top table, the cascade flows all the way to the bottom and deletes all the associated records (about 5000 rows total in 20 tables). The secret was to have every column that was referenced by a foreign key or had a foreign key constraint indexed. No Index = Full table scan.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Dec 2003
    Location
    Buenos Aires, Argentina
    Posts
    86
    Thanks guys for all your comments, tips and explanation. It helped me to understand a few things.
    I disable the FK that point from Y to X, and the delete was really faster.
    Next time I'll will try the SQL trace in order to go deeper in SQL tunning.
    Thanks again!!!
    Regards,

    Manf

  11. #11
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Were there any child rows left without parent rows ??? Can you re-enable
    the foreign key or was it unnecessary to have the foreign key (data integrity) ... (Was the foreign key a "delete cascade" ?) ... Were there triggers (delete) on the child table that may have been doing something else ??? These were the reasons we were looking for an explain plan to
    see what was actually going on ...

    Gregg

  12. #12
    Join Date
    Dec 2003
    Location
    Buenos Aires, Argentina
    Posts
    86
    Gregg,
    no child rows were left without parent record. After the delete were performed, I could enabled the FK. I need to delete those records cause programmer are performing tests on news functionalities.
    Thanks again!

    Manf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •