Re: What is left of explain plan with ASSM?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 19 Oct 2006 14:26:02 +0100
Message-ID: <014a01c6f382$1f9081e0$0200a8c0@Primary>

See

Cost Based Oracle - Fundamentals
Ch. 5 - Clustering-factor

        pp 95-99 : ASSM
        pp 105 - 107: sys_op_countchg()

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

Original Message ----- From: "Polarski, Bernard" <Bernard.Polarski_at_atosorigin.com> To: "oracle-l" <oracle-l_at_freelists.org> Sent: Thursday, October 19, 2006 12:52 PM Subject: What is left of explain plan with ASSM?

What is left with explain plan value nowadays when ASSM is in house? I got his from explain plan but the real executions show that there is no problems:
The problem seems that the optimiser sees fantastic clustering factor. Note the prediction of 568 table block access in contrast of the reality.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=74 Card=648 Bytes=17496)

1 0 SORT (ORDER BY) (Cost=74 Card=648 Bytes=17496)

   2    1     HASH JOIN (Cost=71 Card=648 Bytes=17496)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'ASSIGNMENTS' (Cost=56
Card=568 Bytes=9656)
   4    3         INDEX (RANGE SCAN) OF 'ASG_002' (NON-UNIQUE) (Cost=3
Card=568)
   5    2       TABLE ACCESS (FULL) OF 'TRAINING_EVENTS' (Cost=14 Card

=23587 Bytes=235870)

Statistics

         14  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        417  bytes sent via SQL*Net to client
        457  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

B. Polarski
Oracle DBA

No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.5/483 - Release Date: 18/10/2006

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 19 2006 - 08:26:02 CDT