tuning the SQL..Reduce the response time

Hi All,
I am using oracle 8.1.7,solaris 2.8.
I have this query which is taking 3 min for execution.

SELECT COUNT(*) FROM CART_STATS WHERE COMPANY_ID = 8170926 AND ( ((SHIPMENT_STATUS = 'N' or PAYMENT_CLEARED = 'N')
and ARCHIVE != 'D' and STATUS != 'D' and STATUS != 'X' and NUM_TRANSACTIONS > 0) OR ((((SHIPMENT_STATUS = 'Y'
and PAYMENT_CLEARED = 'Y' and STATUS != 'D') OR (STATUS = 'X')) and ARCHIVE != 'D')
and NUM_TRANSACTIONS > 0 and DATE_MODIFIED >= (sysdate - 60)) )

and the execution plan is

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=283 Card=1 Bytes=25)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CART_STATS' (Cost=283 Card=1
29 Bytes=3225)

3 2 INDEX (RANGE SCAN) OF 'CART_PS_IDX' (NON-UNIQUE) (Cost
=5 Card=129)

Its using the index which is built on the following columns(composite index)

CART_PS_IDX COMPANY_ID
CART_PS_IDX PAYMENT_CLEARED
CART_PS_IDX SHIPMENT_STATUS
CART_PS_IDX STATUS
CART_PS_IDX ARCHIVE
CART_PS_IDX CART_ID

No of distinct values in each of these columns are
SQL> select count(distinct company_id) from cart;

COUNT(DISTINCTCOMPANY_ID)
-------------------------
88342


SQL> select count(distinct PAYMENT_CLEARED) from cart;

COUNT(DISTINCTPAYMENT_CLEARED)
------------------------------
2

SQL> select count(distinct shipment_status) from cart;

COUNT(DISTINCTSHIPMENT_STATUS)
------------------------------
2

SQL> select count(distinct ARCHIVE) from cart;

COUNT(DISTINCTARCHIVE)
----------------------
3

SQL> select count(distinct status) from cart;

COUNT(DISTINCTSTATUS)
---------------------
6

How can i reduce the time take by this query, will generation of histograms help on this. Please advice.

regards
anandkl

Hi,
TO tune the query, first of all you can use count(1) INSTEAD of count(*). Secondly create an index on company_id and analyze the table.
I hope this should work. Such heavy composite indexes are usually useless and moreover cause contention than actually help. Try to giving hint /*+INDEX(table_name the index on company_id) */ and try running the query and see if the response time has reduced.
Thanks

Quote:

---

Originally posted by apunhiran
Hi,
TO tune the query, first of all you can use count(1) INSTEAD of count(*).

---

what the...?!?!

Count(1) vs. Count(*) is a urban myth, and I don't really think the complex index will be a significant overhead.

The plan looks OK to me. So, how many rows are selected by:
SELECT COUNT(*) FROM CART_STATS WHERE COMPANY_ID = 8170926;

It could be that you are having to read a vast number of rows in order to do the rest of the logic . . . ?

Total no of records from this query is 54712

and the table has 14.5M records

regards
anandkl

Quote:

---

Originally posted by apunhiran
Hi,
TO tune the query, first of all you can use count(1) INSTEAD of count(*).

---

I read somewhere in Tom's guide
that count(*) and count(1) will work on same method.

So, there are a lot of rows . . . all 54'712 have to be read from the table - that could be enough to explain 3 minutes if most are not in buffers (depends on your hardware - I make that 3ms/row).

(check my logic here) I think you can rewrite the query as

Code:

---

SELECT COUNT(*)
FROM  CART_STATS
WHERE COMPANY_ID = 8170926
and  ARCHIVE != 'D'
and  NUM_TRANSACTIONS > 0
and 
(
 (LOTS OF STUFF)
 OR
 (LOTS OF STUFF)
)

---

in which case an index on (COMPANY_ID, ARCHIVE, NUM_TRANSACTIONS) could be useful if these extra columns are selective.

So how many rows from:
SELECT COUNT(*)
FROM CART_STATS
WHERE COMPANY_ID = 8170926
and ARCHIVE != 'D'
and NUM_TRANSACTIONS > 0

The count is

145591

That must be a typing error - how do you get more than 54,712?

how about changing

NUM_TRANSACTIONS > 0

NOT NUM_TRANSACTIONS = 0

Hi Dapi,
Since i have other condition to be matched in the QUERY, thats why i got 54k in the original query and 1.4 Lakhs in the second query.

Is there an alternative for 'OR' condition so that the query can perform fast


regards
anandkl

try union all

Composite Index will not help for this query.
Just create an index on company_id only. And use hint within the SELECT.

Or Try this:

SELECT /*+ full(CS) parallel(CS,8) */
COUNT(*) FROM CART_STATS CS
Where ....................
....................

Tamil

Hi Tamil,

I don't agree with your comments on the index.

The index with COMPANY_ID in first position is already being used - I would be amazed if using a new index on only that column would have a noticable effect. I'm convinced that it is the retrieval of 54K or 140K rows from the table is taking the time - I would hope to reduce that by improving the selectivity of the the index range scan.

pando's idea looks good, providing the conditions that are OR'ed together really are mutually exclusive - union all keeps duplicates (but anandkl seems to have hidden part of the logic from us). That will divide the query into two pieces which can be tuned individually, each would probably need its own composite index.


. . . now you may be right that a parallel FTS would be better, a good fraction (10% ?) of the table is currently being retrieved - we don't know what fraction of the blocks that is, it could be most of them.

Dapi,
I think UNION ALL may increase Logical IOs.

Anandkl,

Do you have any idea how many blocks are used by the table?

set autot trace statis
run your query and the hint I suggested and post here the statistics.


Tamil

A couple of thoughts while driving home last night:

- if UNION ALL is not applicable then, providing the index selectivity can be improved, UNION would be OK (hopefully only a few thousand PK's to sort).

- if this is the worst case of the query - i.e. if for other values of COMPANY_ID it performs OK (fewer rows to recover) - then FTS is probabaly not the answer. You could reduce the worst case from 3 mins to (say) 30 secs, but increase all the 1 sec response times to 30 secs as well.


Tamil, yes, by itself UNION ALL will only make it worse. But it gives the opportunity to tune the sub-queries independantly.

Code:

---


Select
(
SELECT
  COUNT(*)
FROM
  CART_STATS
WHERE
  COMPANY_ID = 8170926 AND
  SHIPMENT_STATUS = 'N' AND
  STATUS IN ('Rest Of The Values Apart From D & X') AND
  Archive IN ('Rest Of The Values Apart From D') AND
  NUM_TRANSACTIONS > 0
)
+
(
SELECT
  COUNT(*)
FROM
  CART_STATS
WHERE
  COMPANY_ID = 8170926 AND
  PAYMENT_CLEARED = 'N' AND
  STATUS IN ('Rest Of The Values Apart From D & X') AND
  Archive IN ('Rest Of The Values Apart From D') AND
  NUM_TRANSACTIONS > 0
)
+
(
SELECT
  COUNT(*)
FROM
  CART_STATS
WHERE
  COMPANY_ID = 8170926 AND
  SHIPMENT_STATUS = 'Y' AND
  PAYMENT_CLEARED = 'Y' AND
  STATUS IN ('Rest Of The Values Apart From D') AND
  Archive IN ('Rest Of The Values Apart From D') AND
  NUM_TRANSACTIONS > 0 and
  DATE_MODIFIED >= (sysdate - 60)
)
+
(
SELECT
  COUNT(*)
FROM
  CART_STATS
WHERE
  COMPANY_ID = 8170926 AND
  STATUS = 'X' AND
  Archive IN ('Rest Of The Values Apart From D') AND
  NUM_TRANSACTIONS > 0 and
  DATE_MODIFIED >= (sysdate - 60)
)
From
DUAL
;

---

How about this one?

Abhay.

Or Even

Code:

---


 SELECT
  COUNT(*)
 FROM
  CART_STATS
 WHERE
  COMPANY_ID = 8170926 AND
  Archive IN ('Rest Of The Values Apart From D') AND
  NUM_TRANSACTIONS > 0 AND
  EXISTS
  (
  Select
    NULL
  from
    Dual
  Where 
    SHIPMENT_STATUS = 'N' AND
    STATUS IN ('Rest Of The Values Apart From D & X')
  Union All
  Select
    NULL
  from
    Dual
  Where 
    COMPANY_ID = 8170926 AND
    PAYMENT_CLEARED = 'N' AND
    STATUS IN ('Rest Of The Values Apart From D & X')
  Union All
  Select
    NULL
  from
    Dual
  Where 
    SHIPMENT_STATUS = 'Y' AND
    PAYMENT_CLEARED = 'Y' AND
    STATUS IN ('Rest Of The Values Apart From D') AND
    DATE_MODIFIED >= (sysdate - 60)
  Union All
  Select
    NULL
  from
    Dual
  Where 
    STATUS = 'X' AND
    DATE_MODIFIED >= (sysdate - 60)
  )
;

---

Hi Abhay,

That's a move in the right direction*. Your 2nd and 3rd queries can take better advantage of the existing index - HOWEVER the 1st and 4th won't. By itself this change could more than double the execution time! It will need other indexes.

I don't see much point in re-wrting the query in detail, since we aren't quite sure what it actually is!

Quote:

---

Since i have other condition to be matched in the QUERY, thats why i got 54k in the original query and 1.4 Lakhs in the second query.

---

* this refers to the COUNT(*) + COUNT(*) . . . . version.

Quote:

---

Originally posted by DaPi
Hi Abhay,

That's a move in the right direction*. Your 2nd and 3rd queries can take better advantage of the existing index - HOWEVER the 1st and 4th won't. By itself this change could more than double the execution time! It will need other indexes.

---

I dont see any reason why 1st and 4th should not take better advantage of existing composite index. :confused:

Well what ever you said will be correct if COMPANY_ID is not much duplicated over differect combinations of Status, Archive, Shipment_Status and so...But its not the case here (just guessing).

Abhay.

Quote:

---

Originally posted by DaPi


I don't see much point in re-wrting the query in detail, since we aren't quite sure what it actually is!

"Since i have other condition to be matched in the QUERY, thats why i got 54k in the original query and 1.4 Lakhs in the second query."

---

..hmm i quite really didnt see this part in Anand's Later post.;)

Quote:

---

Originally posted by abhaysk
I dont see any reason why 1st and 4th should not take better advantage of existing composite index. :confused:

---

Yes, they should use the index, but only in so far as the index will (I think) find the rowid's of ALL the rows with COMPANY_ID = 8170926 because there is no condition on the second column in the index, PAYMENT_CLEARED (9i might do better, but this is 8.1.7). The 2nd & 3rd can be more selective because PAYMENT_CLEARED (and SHIPMENT_STATUS in the 3rd) are part of the condition.

many times Oracle rewrites OR into UNION ALL

Quote:

---

Originally posted by DaPi
Yes, they should use the index, but only in so far as the index will (I think) find the rowid's of ALL the rows with COMPANY_ID = 8170926 because there is no condition on the second column in the index, PAYMENT_CLEARED (9i might do better, but this is 8.1.7). The 2nd & 3rd can be more selective because PAYMENT_CLEARED (and SHIPMENT_STATUS in the 3rd) are part of the condition.

---

Ok, lemme give u an example of how best you can get from an index...

Code:

---

WW04_PRO> Select Column_Position, Index_Name, Table_Name, Substr(Column_Name, 1, 30) from dba_ind_columns
  2  where table_name = 'YTD_MTD_SHIPMENTS';

    COLUMN_POSITION INDEX_NAME                    TABLE_NAME                    SUBSTR(COLUMN_NAME,1,30)
-------------------- ------------------------------ ------------------------------ ------------------------------
                  1 PK_YTD_MTD_SHIPMENTS          YTD_MTD_SHIPMENTS              FORDATE
                  2 PK_YTD_MTD_SHIPMENTS          YTD_MTD_SHIPMENTS              REGION_CD
                  3 PK_YTD_MTD_SHIPMENTS          YTD_MTD_SHIPMENTS              ORG_LEVEL_NO
                  4 PK_YTD_MTD_SHIPMENTS          YTD_MTD_SHIPMENTS              PH_LEVEL_NO
                  5 PK_YTD_MTD_SHIPMENTS          YTD_MTD_SHIPMENTS              PRODUCT_TYPE
                  6 PK_YTD_MTD_SHIPMENTS          YTD_MTD_SHIPMENTS              PIN
                  7 PK_YTD_MTD_SHIPMENTS          YTD_MTD_SHIPMENTS              ORG_CD
                  8 PK_YTD_MTD_SHIPMENTS          YTD_MTD_SHIPMENTS              COMPANY_CD

8 rows selected.

WW04_PRO> ed
Wrote file afiedt.buf

  1* Select Count(Distinct REGION_CD) from ytd_mtd_shipments
WW04_PRO> /

COUNT(DISTINCTREGION_CD)
------------------------
                      3

Elapsed: 00:00:01.01
WW04_PRO> ed
Wrote file afiedt.buf

  1* Select Count(Distinct Org_Level_No) from ytd_mtd_shipments
WW04_PRO> /

COUNT(DISTINCTORG_LEVEL_NO)
---------------------------
                          5

Elapsed: 00:00:01.01
WW04_PRO> ed
Wrote file afiedt.buf

  1* Select Count(Distinct Ph_Level_No) from ytd_mtd_shipments
WW04_PRO> /

COUNT(DISTINCTPH_LEVEL_NO)
--------------------------
                        9

Elapsed: 00:00:02.00
WW04_PRO> Select Count(Distinct Product_Type) from ytd_mtd_shipments;

COUNT(DISTINCTPRODUCT_TYPE)
---------------------------
                          7

Elapsed: 00:00:01.01
WW04_PRO> Select * from YTD_MTD_SHIPMENTS where FORDATE = '18-DEC-03' and PIN = 'DA236A#ABA';

FORDATE  RE O P PR PIN                ORG_CD         
--------- -- - - -- ------------------ -----------------
18-DEC-03 AM 0 8 CM DA236A#ABA        CORP           
18-DEC-03 AM 1 8 CM DA236A#ABA        AMERICAS       
18-DEC-03 AM 2 8 CM DA236A#ABA        LATIN AMERICA   
18-DEC-03 AM 3 8 CM DA236A#ABA        NMCA           
18-DEC-03 AM 4 8 CM DA236A#ABA        18SP           

Elapsed: 00:00:09.03

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=13 Bytes=1365)
  1    0  TABLE ACCESS (BY INDEX ROWID) OF 'YTD_MTD_SHIPMENTS' (Cost=14 Card=13 Bytes=1365)
  2    1    INDEX (RANGE SCAN) OF 'PK_YTD_MTD_SHIPMENTS' (UNIQUE) (Cost=9 Card=5)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      2173  consistent gets
      1431  physical reads
    6134756  redo size
      2472  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

WW04_PRO> ed
Wrote file afiedt.buf

  1  Select * from
  2  (Select * from YTD_MTD_SHIPMENTS where FORDATE = '18-DEC-03' and rownum > 0)
  3* Where PIN = 'DA236A#ABA'
WW04_PRO> /

FORDATE  RE O P PR PIN                ORG_CD               
--------- -- - - -- ------------------ ----------------------
18-DEC-03 AM 0 8 CM DA236A#ABA        CORP                 
18-DEC-03 AM 1 8 CM DA236A#ABA        AMERICAS             
18-DEC-03 AM 2 8 CM DA236A#ABA        LATIN AMERICA       
18-DEC-03 AM 3 8 CM DA236A#ABA        NMCA                 
18-DEC-03 AM 4 8 CM DA236A#ABA        18SP                 

Elapsed: 00:00:23.04

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=136 Card=1347 Bytes=449898)
  1    0  VIEW (Cost=136 Card=1347 Bytes=449898)
  2    1    COUNT
  3    2      FILTER
  4    3        TABLE ACCESS (BY INDEX ROWID) OF 'YTD_MTD_SHIPMENTS'(Cost=136 Card=1347 Bytes=141435)
  5    4          INDEX (RANGE SCAN) OF 'PK_YTD_MTD_SHIPMENTS' (UNIQUE) (Cost=9 Card=539)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    139182  consistent gets
      2674  physical reads
          0  redo size
      2352  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

WW04_PRO>

---

See above how in the second case Index Range Scan is confined to just FORDATE col, & in first case how is it benifited even when you have PIN at the 6th position & you can expect that Index has to search at the worst 3*5*9*7 keys to find the pin.

I think there is something more than what we have been told to us by DOCS.

Abhay.

Abhay, which version are you running?

Quote:

---

Originally posted by DaPi
Abhay, which version are you running?

---

9.2.0.4, but i dont think 8.x will do any bad....

Abhay.

Version 9i has INDEX SKIP SCAN http://download-west.oracle.com/docs...mops.htm#51553 which did not exist before. I imagine that could be happening (even if the explain plan does not say so).

Quote:

---

Originally posted by DaPi
Version 9i has INDEX SKIP SCAN http://download-west.oracle.com/docs...mops.htm#51553 which did not exist before. I imagine that could be happening (even if the explain plan does not say so).

---

But the above eg is suerly not a skip scan for sure....:rolleyes: :rolleyes:

Ok, Here goes for 8.x

Code:

---

U144> Select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.2.1      Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

Elapsed: 00:00:01.01
U144> select column_position, index_name, table_name, substr(column_name, 1,30) from dba_ind_columns
  2  where table_name = 'RPT_PIT_TRANS_DATA' and index_name like 'PK%';

    COLUMN_POSITION INDEX_NAME                    TABLE_NAME                    SUBSTR(COLUMN_NAME,1,30)
-------------------- ------------------------------ ------------------------------ ------------------------------
                  1 PK_RPT_PIT_TRANS_DATA          RPT_PIT_TRANS_DATA            PLANT_CD
                  2 PK_RPT_PIT_TRANS_DATA          RPT_PIT_TRANS_DATA            SUPPLIER_NO
                  3 PK_RPT_PIT_TRANS_DATA          RPT_PIT_TRANS_DATA            MATERIAL_NO
                  4 PK_RPT_PIT_TRANS_DATA          RPT_PIT_TRANS_DATA            EFFECTIVE_DT

Elapsed: 00:00:02.09
U144> Set autotrace on
U144> Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and MATERIAL_NO = '207074-101');

            COUNT(*)
--------------------
                308

Elapsed: 00:00:15.05

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=254 Card=1 Bytes=15)
  1    0  SORT (AGGREGATE)
  2    1    TABLE ACCESS (BY INDEX ROWID) OF 'RPT_PIT_TRANS_DATA' (Cost=254 Card=2 Bytes=30)
  3    2      INDEX (RANGE SCAN) OF 'AK4_RPT_PIT_TRANS_DATA' (NON-UNIQUE) (Cost=5 Card=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        788  consistent gets
        784  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        390  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

U144> Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and rownum > 0)
  2  Where MATERIAL_NO = '207074-101';

            COUNT(*)
--------------------
                308

Elapsed: 00:01:06.07

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1026 Card=1 Bytes=11)
  1    0  SORT (AGGREGATE)
  2    1    VIEW (Cost=1026 Card=131038 Bytes=1441418)
  3    2      COUNT
  4    3        FILTER
  5    4          INDEX (RANGE SCAN) OF 'PK_RPT_PIT_TRANS_DATA' (UNIQUE) (Cost=1026 Card=131038 Bytes=1965570)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13973  consistent gets
      13931  physical reads
      1404  redo size
        369  bytes sent via SQL*Net to client
        407  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

U144> Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and EFFECTIVE_DT = '20011112');

            COUNT(*)
--------------------
                8597

Elapsed: 00:01:01.05

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1026 Card=1 Bytes=12)
  1    0  SORT (AGGREGATE)
  2    1    INDEX (RANGE SCAN) OF 'PK_RPT_PIT_TRANS_DATA' (UNIQUE) (Cost=1026 Card=66 Bytes=792)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13976  consistent gets
      13932  physical reads
      1456  redo size
        369  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

U144> ed
Wrote file afiedt.buf

  1  Select COUNT(*) from (Select * from RPT_PIT_TRANS_DATA where PLANT_CD = '0040' and rownum > 0)
  2* Where EFFECTIVE_DT = '20011112'
U144> /

            COUNT(*)
--------------------
                8597

Elapsed: 00:01:07.09

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1026 Card=1 Bytes=6)
  1    0  SORT (AGGREGATE)
  2    1    VIEW (Cost=1026 Card=131038 Bytes=786228)
  3    2      COUNT
  4    3        FILTER
  5    4          INDEX (RANGE SCAN) OF 'PK_RPT_PIT_TRANS_DATA' (UNIQUE) (Cost=1026 Card=131038 Bytes=1572456)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13974  consistent gets
      13930  physical reads
      1464  redo size
        369  bytes sent via SQL*Net to client
        406  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

U144>

---

You can see above only in last 2 cases it will be of no importance as it has to scan all the missed out keys obtained from COL postion 2 and 3...But If i see anandkl's composite key, theres only 2 cols that will not be used in sub qry 1 and 4 but again these cols in where clause are not at the end...and more over the card of shipment_status and PAYMENT_CLEARED are just 2 each so at the most you will be scanning 4 times more without including these 2 cols and with including...so i expect range scan will do better that not using.

Abhay.

Hi Friends,
Thank you all for the inputs, i am pasting the stats asked by Tamilselvan


Statistics
----------------------------------------------------------
889 recursive calls
121 db block gets
723827 consistent gets
693231 physical reads
700 redo size
369 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed

Hi Abhay, looks like I got the wrong end of the stick on this one . . . . learn something new evey day :)

I am herby dubbing this as The Thread That Refused To Die

Happy Holiday's!!

:D

MH

Hi Anand,

Statistics
----------------------------------------------------------
889 recursive calls
121 db block gets
723827 consistent gets
693231 physical reads
700 redo size
369 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed

The above statistics was for the full tablescan or Index Range Scan Query.

Tamil

Quote:

---

Originally posted by Mr.Hanky
I am herby dubbing this as The Thread That Refused To Die
MH

---

Why? :D

Quote:

---

Originally posted by DaPi
Why? :D

---

Hurry Hanky, u r right it will never !!!;)