Re: db_file_multiblock_read

From: Vlad Sadilovskiy <vlovsky_at_gmail.com>
Date: Wed, 7 Nov 2007 15:01:30 -0500
Message-ID: <df9f25d50711071201q2841539dmf522e8d11eee6970@mail.gmail.com>

Amir,

Reducing guesswork, if the hint is taken, then in 10053 trace (10.2.0.3, 11.1.0.6) you'd see it in the section like below:

PARAMETERS IN OPT_PARAM HINT

  optimizer_index_caching             = 10
  star_transformation_enabled         = 'true'

And also in the target profile:

Content of other_xml column

  db_version     : 11.1.0.6
  parse_schema   : TEST
  plan_hash      : 3128673074
  plan_hash_2    : 2108540337

  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
      DB_VERSION('11.1.0.6')
      OPT_PARAM('star_transformation_enabled' 'true')
      OPT_PARAM('optimizer_index_caching' 10)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T3"@"SEL$1")

END_OUTLINE_DATA
*/

Vlad Sadilovskiy
Oracle Database Tools
Web site: http://www.fourthelephant.com
Blog: http://vsadilovskiy.wordpress.com

On 11/7/07, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:
>
> Alan and Vlad,
> You guys seem to be right that this hint only supports a few parameters
> and MRBC is not one of them. At least I am not able to force the optimizer
> to scan a table with larger MRBC with this hint.
>
> ------------------------------
> *From:* Vlad Sadilovskiy [mailto:vlovsky_at_gmail.com]
> *Sent:* Wednesday, November 07, 2007 1:24 PM
> *To:* Brandon.Allen_at_oneneck.com
> *Cc:* Hameed, Amir; oracle-l
> *Subject:* Re: db_file_multiblock_read_count
>
>
> Brandon is right it needs to be tested. I've just gotten from the 11g and
> this didn't work for the MBRC, exactly as mentioned in the Docs:
>
> *"The OPT_PARAM hint lets you set an initialization parameter for the
> duration of the current query only. This hint is valid only for the
> following parameters: OPTIMIZER_DYNAMIC_SAMPLING , OPTIMIZER_INDEX_CACHING,
> OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING, and
> STAR_TRANSFORMATION_ENABLED. "*
>
> Vlad Sadilovskiy
> Oracle Database Tools
> Web site: http://www.fourthelephant.com
> Blog: http://vsadilovskiy.wordpress.com
>
>
>
> On 11/7/07, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
> >
> > I haven't tested it but I think you can use the opt_param hint. It's
> > available in 10.2, but not documented - however it is covered in the 11g
> >
> > documentation:
> >
> > http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_eleme
> > nts006.htm#BABEBAID
> >
> > Regards,
> > Brandon
> >
> > Privileged/Confidential Information may be contained in this message or
> > attachments hereto. Please advise immediately if you or your employer do not
> > consent to Internet email for messages of this kind. Opinions, conclusions
> > and other information in this message that do not relate to the official
> > business of this company shall be understood as neither given nor endorsed
> > by it.
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 07 2007 - 14:01:30 CST