Performance Degrade After analyze table

Quote:

---

Originally posted by gnagesh
Explain Plan:
LEVEL ID OPERATION OPTIONS OBJECT POSITION
---------- --- ------------------------------ ------------------ -------------------- ----------
1 0 SELECT STATEMENT cost=9232
2 1 COUNT STOPKEY
3 2 TABLE ACCESS FULL MST_USER

---

that is not full table scan, oracle is clever enoguh to stop after read first row

Quote:

---

Originally posted by pando
that is not full table scan, oracle is clever enoguh to stop after read first row

---

Thanks to all. Can you elaborate it Please.

elaborate what???

Quote:

---

Originally posted by pando
elaborate what???

---

Is it because of COUNT STOP KEY, you are saying it is not FTS? Please clear me If I am wrong?

Thanks

Quote:

---

Originally posted by pando
that is not full table scan, oracle is clever enoguh to stop after read first row

---

Hi Pando,
Why it is not a FTS ? The COUNT STOPKEY is I think because of the ROWNUM condition in the query?

Sanjay

well try it with a 100 million rows table and see how long it takes, and set autotrace on and see how much it reads

if you say it´s FTS you would expect lengthy time and tons of reads, but try it

You could play too with this two parameters:

optimizer_index_caching
optimizer_index_cost_adj

Their default values are not very goods for index accesing.

Check it at www.evdbt.com.

Cheers

Angel

Nagesh,

May be you can do this test.
Can you post the 'explain plan', 'time elapsed' and 'rows returned' with and without rownum condition in your where clause?

Sanjay

Code:

---

SQL> select * from cmsapmdw.estadisticas_midd partition (ESTADISTICAS_MIDD200201) ;

570201 rows selected.

Elapsed: 00:00:26.81

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=520 Card=570201 Bytes=18816633)

  1    0  TABLE ACCESS (FULL) OF 'ESTADISTICAS_MIDD' (Cost=520 Card=570201 Bytes=18816633)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
      41207  consistent gets
      3186  physical reads
          0  redo size
  71096799  bytes sent via SQL*Net to client
    6348720  bytes received via SQL*Net from client
      38015  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    570201  rows processed


SQL> select * from cmsapmdw.estadisticas_midd partition (ESTADISTICAS_MIDD200201)
  2  where rownum < 2;

1 row selected.

Elapsed: 00:00:00.43

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=520 Card=570201 Bytes=18816633)

  1    0  COUNT (STOPKEY)
  2    1    TABLE ACCESS (FULL) OF 'ESTADISTICAS_MIDD' (Cost=520 Card=570201 Bytes=18816633)

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

---

Quote:

---

Originally posted by pando

Code:

---

SQL> select * from cmsapmdw.estadisticas_midd partition (ESTADISTICAS_MIDD200201) ;

570201 rows selected.

Elapsed: 00:00:26.81

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=520 Card=570201 Bytes=18816633)

  1    0  TABLE ACCESS (FULL) OF 'ESTADISTICAS_MIDD' (Cost=520 Card=570201 Bytes=18816633)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
      41207  consistent gets
      3186  physical reads
          0  redo size
  71096799  bytes sent via SQL*Net to client
    6348720  bytes received via SQL*Net from client
      38015  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    570201  rows processed


SQL> select * from cmsapmdw.estadisticas_midd partition (ESTADISTICAS_MIDD200201)
  2  where rownum < 2;

1 row selected.

Elapsed: 00:00:00.43

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=520 Card=570201 Bytes=18816633)

  1    0  COUNT (STOPKEY)
  2    1    TABLE ACCESS (FULL) OF 'ESTADISTICAS_MIDD' (Cost=520 Card=570201 Bytes=18816633)

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

---

---

But ain't the cost and cardinality for FTS is same for both plans?

Sanjay