Home » SQL & PL/SQL » SQL & PL/SQL » Help with auto trace output.
Help with auto trace output. [message #242334] Fri, 01 June 2007 10:58 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member



SQL> select count(*) from interim_index_su
  2  /

  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FULL SCAN) OF 'PK_INTERIM_INDEX_SU' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      46083  consistent gets
      46079  physical reads
          0  redo size
        221  bytes sent via SQL*Net to client
        274  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



TABLE HAS 0 records. first time it took around 5 minutes to give a record count of 0. next time it gives me the count of 0 in 23 seconds. I did an analyze this morning just before doing a query. All other tables gives me the count(*) instantly. I am wondering why this table takes long.

Re: Help with auto trace output. [message #242337 is a reply to message #242334] Fri, 01 June 2007 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because it likely was big and was emptied with deletes.
Execute a truncate on the table and the query will return fast.

Regards
Michel
Re: Help with auto trace output. [message #242346 is a reply to message #242334] Fri, 01 June 2007 11:11 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

Thank you michel.

yes. this table had records which are deleted thru the stored procedure.


dELETE FROM INTERIM_INDEX_SU
WHERE INDEX_RUN_IRM = <MONTH>

<MONTH> IS PASSED THRU THE PARAMETER IN THE STORED PROC.

truncate can not be done. there is a where clause. based on that it needs to delete.

Could you please let me know what i can do in this case.

Re: Help with auto trace output. [message #242350 is a reply to message #242346] Fri, 01 June 2007 11:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why would you need a count(*)?
Re: Help with auto trace output. [message #242351 is a reply to message #242334] Fri, 01 June 2007 11:28 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member


This interim_index_su has 500000 records. out of which 100000 records are deleted thru

delete from interim_index_su
where index_run_irm = <month>;

this deletion took about 20 minutes.


but this morning i simply wanted to see the number of records in this table. so i did a count(*). and it took 23 seconds to give me the count of 0. Since i did the analyze, should it give me the count faster???

Re: Help with auto trace output. [message #242359 is a reply to message #242351] Fri, 01 June 2007 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To reset the HWM use truncate.
You have 0 row so it does not hurt.

Regards
Michel
Re: Help with auto trace output. [message #242423 is a reply to message #242359] Fri, 01 June 2007 22:40 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Although your table will probably be suffering for HWM (the problem discussed above) I don't believe that's the issue in this case because you are performing an index scan rather than a Full Table Scan.

Much more likely is that the deletes and subsequent inserts caused massive fragmentation of the index. If you drop and rebuild the index, you will probably notice significant improvement in the query above (it won't fix the HWM problem which probably affects other queries though).

Ross Leishman
Re: Help with auto trace output. [message #242427 is a reply to message #242423] Sat, 02 June 2007 00:36 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TRUNCATE also fixes the issue on indexes, this is why I suggested it but you're right my first idea was to rebuild the index.

Regards
Michel
Previous Topic: SQL Performance question
Next Topic: USER_TAB_COLUMNS and Other Schemas
Goto Forum:
  


Current Time: Sun Dec 04 20:58:38 CST 2016

Total time taken to generate the page: 0.03981 seconds