Re: What do these accomplish?

<dbaxchange.dba_at_gmail.com> wrote in message news:1133197510.195169.222060_at_z14g2000cwz.googlegroups.com...
> Hello everyone,
>
> I've searched metalink and on the web and could find very little
> documentation on this and so thought I would post it here......
>
> While collecting database stats using the dbms_stats package and
> including the option for histograms on indexed columns (FOR ALL INDEXED
> COLUMNS SIZE AUTO), I've seen oracle submit SQL statements with the
> following hints:
>
> no_monitoring
> cursor_sharing_exact
>
> What do these accomplish? They both are features within the database
> but how do they translate to be directives to the optimizer? Does
> cursor_sharing_exact hint mean that "do not share this statement if
> already in the library cache unless the cursor's exact"? Not really
> sure....
>
> Also, I've seen the following functions being used by oracle for
> collecting histograms:
>
> sys_op_descend
> sys_op_lbid
>
> The only documentation I could find on sys_op_descend on the web was
> this : "Used to generate the values stored for indexes with descending
> columns". What values are exactly being generated? Not really sure
> here......
>
> Any information on this would be appreciated......
>
> Thanks!
>
> http://www.dbaxchange.com
>

cursor_sharing_exact

    If cursor_sharing is set to force or similar, then     ignore the parameter for this statement. i.e. Do     NOT change literals to bind variables.

no_monitoring

    Do not update the col_usage$ table with any     column usage information for this statement,     as it is running for internal statistical purposes     only, not as part of the end-user application.

sys_op_descend

    An internal function that takes a value and     returns the form that would be stored for     that value in a descending index. Essentially     doing a one's complement on the bytes and     appending an 0xFF byte

sys_op_lbid

    Has many functional variations, but the point     is to scan through index leaf blocks and calculate     a measure of the quality of the index. lbid may be     short for Leaf Block ID. The way it is counted,     and the thing being counted, depends on the type     of index (viz: bitmap, simple b-tree, local/global     index, IOT, secondary on IOT, cluster).

-- 
Regards

Jonathan Lewis

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

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005