FAQ
Hi,

The execution plan for a query in oracle 9i shows "Optimizer= CHOOSE", how can i force the DB engine to use the COST based optimization for a query ?

Oracle version is 9.2.0.4.0

Thanks
Savio Pinto

Search Discussions

  • Baumgartel, Paul at Mar 2, 2006 at 3:31 pm
    If any table mentioned in the query has statistics stored in the data dictionary, Oracle will use the CBO.


    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 10:28 AM
    To: oracle-l_at_freelists.org
    Subject: CBO question ?

    Hi,

    The execution plan for a query in oracle 9i shows "Optimizer= CHOOSE", how can i force the DB engine to use the COST based optimization for a query ?

    Oracle version is 9.2.0.4.0

    Thanks
    Savio Pinto

    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

    --
    http://www.freelists.org/webpage/oracle-l
  • Andem, Venu at Mar 2, 2006 at 3:31 pm
    CHOOSE 'IS' CBO


    pls read at <http://tahiti.oracle.com/> http://tahiti.oracle.com, perf tuning guide.


    -Venu


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Savio Pinto (s)
    Sent: 02 March 2006 15:28
    To: oracle-l_at_freelists.org
    Subject: CBO question ?

    Hi,

    The execution plan for a query in oracle 9i shows "Optimizer= CHOOSE", how can i force the DB engine to use the COST based optimization for a query ?

    Oracle version is 9.2.0.4.0

    Thanks
    Savio Pinto

    PLEASE NOTE: THE ABOVE MESSAGE WAS RECEIVED FROM THE INTERNET.

    On entering the GSi, this email was scanned for viruses by the Government Secure Intranet (GSi) virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs.

    Please see http://www.gsi.gov.uk/main/notices/information/gsi-003-2002.pdf for further details.

    In case of problems, please call your organisational IT helpdesk

    Land Registry at Homebuyer Show, 17-19 March, ExCeL London

    If you have received this e-mail and it was not intended for you, please let us know, and then delete it. Please treat our information in confidence, as you would expect us to treat yours.The Land Registry checks all mail and attachments for known viruses, however, you are advised that you open any attachments at your own risk.

    The original of this email was scanned for viruses by the Government Secure Intranet (GSi) virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs.

    On leaving the GSi this email was certified virus-free
    --
    http://www.freelists.org/webpage/oracle-l
  • Radoulov, Dimitre at Mar 2, 2006 at 3:40 pm

    CHOOSE 'IS' CBO
    pls read at <http://tahiti.oracle.com/> http://tahiti.oracle.com, perf
    tuning guide.
    ?

    Oracle 9i, Database Reference
    Release 2 (9.2)

    choose
    The optimizer chooses between a cost-based approach and a rule-based
    approach based on whether statistics are available.
    If the data dictionary contains statistics for at least one of the accessed
    tables,
    then the optimizer uses a cost-based approach and optimizes with a goal of
    best
    throughput. If the data dictionary contains only some statistics, then the
    cost-based approach is used, and the optimizer must guess the statistics for
    the
    subjects without any statistics. This can result in sub-optimal execution
    plans. If
    the data dictionary contains no statistics for any of the accessed tables,
    then the
    optimizer uses a rule-based approach.

    Regards,
    Dimitre
  • Savio Pinto (s) at Mar 2, 2006 at 4:31 pm
    Thanks, but how can i check/verify that the statistics are stored for a particular table in the data dictionary ?

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Baumgartel, Paul
    Sent: Thursday, March 02, 2006 9:31 AM
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    If any table mentioned in the query has statistics stored in the data dictionary, Oracle will use the CBO.


    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 10:28 AM
    To: oracle-l_at_freelists.org
    Subject: CBO question ?

    Hi,

    The execution plan for a query in oracle 9i shows "Optimizer= CHOOSE", how can i force the DB engine to use the COST based optimization for a query ?

    Oracle version is 9.2.0.4.0

    Thanks
    Savio Pinto

    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

    --
    http://www.freelists.org/webpage/oracle-l
  • Andem, Venu at Mar 2, 2006 at 4:37 pm
    You will have to look into DBA_TABLES (LAST_ANALYZED column), i would want to point you to the exact location where you can read some info inside http://tahiti.oracle.com but somehow strangely the site is down.


    If the site comes up, make sure you check the performance tuning guide, a chapter on CBO, can't remember the exact name (might be just CBO).


    -Venu

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Savio Pinto (s)
    Sent: 02 March 2006 16:32
    To: paul.baumgartel_at_credit-suisse.com; oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    Thanks, but how can i check/verify that the statistics are stored for a particular table in the data dictionary ?

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Baumgartel, Paul
    Sent: Thursday, March 02, 2006 9:31 AM
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    If any table mentioned in the query has statistics stored in the data dictionary, Oracle will use the CBO.


    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 10:28 AM
    To: oracle-l_at_freelists.org
    Subject: CBO question ?

    Hi,

    The execution plan for a query in oracle 9i shows "Optimizer= CHOOSE", how can i force the DB engine to use the COST based optimization for a query ?

    Oracle version is 9.2.0.4.0

    Thanks
    Savio Pinto

    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

    PLEASE NOTE: THE ABOVE MESSAGE WAS RECEIVED FROM THE INTERNET.

    On entering the GSi, this email was scanned for viruses by the Government Secure Intranet (GSi) virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs.

    Please see http://www.gsi.gov.uk/main/notices/information/gsi-003-2002.pdf for further details.

    In case of problems, please call your organisational IT helpdesk

    Land Registry at Homebuyer Show, 17-19 March, ExCeL London

    If you have received this e-mail and it was not intended for you, please let us know, and then delete it. Please treat our information in confidence, as you would expect us to treat yours.The Land Registry checks all mail and attachments for known viruses, however, you are advised that you open any attachments at your own risk.

    The original of this email was scanned for viruses by the Government Secure Intranet (GSi) virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs.

    On leaving the GSi this email was certified virus-free
    --
    http://www.freelists.org/webpage/oracle-l
  • Baumgartel, Paul at Mar 2, 2006 at 4:38 pm
    If user/all/dba_tables.num_rows is not null, statistics for the table exist. The last_analyzed column tells you when they were gathered.


    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 11:32 AM
    To: paul.baumgartel_at_credit-suisse.com; oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    Thanks, but how can i check/verify that the statistics are stored for a particular table in the data dictionary ?

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Baumgartel, Paul
    Sent: Thursday, March 02, 2006 9:31 AM
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    If any table mentioned in the query has statistics stored in the data dictionary, Oracle will use the CBO.


    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 10:28 AM
    To: oracle-l_at_freelists.org
    Subject: CBO question ?

    Hi,

    The execution plan for a query in oracle 9i shows "Optimizer= CHOOSE", how can i force the DB engine to use the COST based optimization for a query ?

    Oracle version is 9.2.0.4.0

    Thanks
    Savio Pinto

    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

    --
    http://www.freelists.org/webpage/oracle-l
  • Jim Silverman at Mar 2, 2006 at 4:39 pm
    Issue the statement

    select table_name, last_analyzed from user_tables;

    If the last_analyzed column is not null, statistics exist (although they
    may not be up to date); if it is null, statistics do not exist. Of
    course, you could also query the all_tables and/or dba_tables views, as
    appropriate.

    Jim Silverman
    Senior Systems Database Administrator
    Solucient, LLC
    Telephone: 734-669-7641

    FAX: 734-930-7611
    E-Mail: jsilverman_at_solucient.com

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 11:32 AM
    To: paul.baumgartel_at_credit-suisse.com;
    oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    Thanks, but how can i check/verify that the statistics
    are stored for a particular table in the data dictionary ?
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Baumgartel, Paul
    Sent: Thursday, March 02, 2006 9:31 AM
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?
    If any table mentioned in the query has statistics

    stored in the data dictionary, Oracle will use the CBO.


    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 10:28 AM
    To: oracle-l_at_freelists.org
    Subject: CBO question ?
    Hi,
    The execution plan for a query in oracle 9i shows

    "Optimizer= CHOOSE", how can i force the DB engine to use the COST based
    optimization for a query ?

    Oracle version is 9.2.0.4.0
    Thanks
    Savio Pinto

    ========================================================================
    ======
    Please access the attached hyperlink for an important

    electronic communications disclaimer:



    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html


    This message is a private communication. It may contain information that is confidential
    and legally protected from disclosure. If you are not an intended recipient, please do
    not read, copy or use this message or any attachments, and do not disclose them to others.
    Please notify the sender of the delivery error by replying to this message, and then
    delete it and any attachments from your system.
    Thank you,
    Solucient LLC
    (eXclaimer 4x)

    --
    http://www.freelists.org/webpage/oracle-l
  • Andem, Venu at Mar 2, 2006 at 4:40 pm
    Paul,


    I think its the other way round, if one of the tables in the query does not contain statistics then Oracle uses RBO. pls correct me if i am wrong.


    Venu

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Baumgartel, Paul
    Sent: 02 March 2006 15:31
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    If any table mentioned in the query has statistics stored in the data dictionary, Oracle will use the CBO.


    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 10:28 AM
    To: oracle-l_at_freelists.org
    Subject: CBO question ?

    Hi,

    The execution plan for a query in oracle 9i shows "Optimizer= CHOOSE", how can i force the DB engine to use the COST based optimization for a query ?

    Oracle version is 9.2.0.4.0

    Thanks
    Savio Pinto

    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

    PLEASE NOTE: THE ABOVE MESSAGE WAS RECEIVED FROM THE INTERNET.

    On entering the GSi, this email was scanned for viruses by the Government Secure Intranet (GSi) virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs.

    Please see http://www.gsi.gov.uk/main/notices/information/gsi-003-2002.pdf for further details.

    In case of problems, please call your organisational IT helpdesk

    Land Registry at Homebuyer Show, 17-19 March, ExCeL London

    If you have received this e-mail and it was not intended for you, please let us know, and then delete it. Please treat our information in confidence, as you would expect us to treat yours.The Land Registry checks all mail and attachments for known viruses, however, you are advised that you open any attachments at your own risk.

    The original of this email was scanned for viruses by the Government Secure Intranet (GSi) virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs.

    On leaving the GSi this email was certified virus-free

    --
    http://www.freelists.org/webpage/oracle-l
  • Gints Plivna at Mar 2, 2006 at 4:56 pm
    One of the previous posters already has posted an excerpt from Oracle
    docs that clearly stated you are wrong. Have you read it?

    Gints

    2006/3/2, Andem, Venu :
    Paul,

    I think its the other way round, if one of the tables in the query does not
    contain statistics then Oracle uses RBO. pls correct me if i am wrong.

    Venu
    --
    http://www.freelists.org/webpage/oracle-l
  • Ric Van Dyke at Mar 2, 2006 at 4:43 pm
    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opti
    mops.htm#PFGRF001



    This is a good place to start. Tahiti appears to be up now.



    Ric Van Dyke

    Hotsos Enterprises

    Hotsos Symposium, be there:

    http://www.hotsos.com/portal/events/SYM06

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Andem, Venu
    Sent: Thursday, March 02, 2006 11:37 AM
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?



    You will have to look into DBA_TABLES (LAST_ANALYZED column), i would
    want to point you to the exact location where you can read some info
    inside http://tahiti.oracle.com but somehow strangely the site is down.



    If the site comes up, make sure you check the performance tuning guide,
    a chapter on CBO, can't remember the exact name (might be just CBO).



    -Venu

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Savio Pinto (s)
    Sent: 02 March 2006 16:32
    To: paul.baumgartel_at_credit-suisse.com; oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    Thanks, but how can i check/verify that the statistics are

    stored for a particular table in the data dictionary ?

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Baumgartel, Paul
    Sent: Thursday, March 02, 2006 9:31 AM
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    If any table mentioned in the query has statistics

    stored in the data dictionary, Oracle will use the CBO.



    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 10:28 AM
    To: oracle-l_at_freelists.org
    Subject: CBO question ?

    Hi,

    The execution plan for a query in oracle 9i

    shows "Optimizer= CHOOSE", how can i force the DB engine to use the COST
    based optimization for a query ?

    Oracle version is 9.2.0.4.0

    Thanks
    Savio Pinto

    ========================================================================
    ======
    Please access the attached hyperlink for an important

    electronic communications disclaimer:



    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html




    PLEASE NOTE: THE ABOVE MESSAGE WAS RECEIVED FROM THE INTERNET.


    On entering the GSi, this email was scanned for viruses by the
    Government Secure Intranet (GSi) virus scanning service supplied
    exclusively by Cable & Wireless in partnership with MessageLabs.


    Please see
    http://www.gsi.gov.uk/main/notices/information/gsi-003-2002.pdf for
    further details.


    In case of problems, please call your organisational IT helpdesk

    Land Registry at Homebuyer Show, 17-19 March, ExCeL London



    If you have received this e-mail and it was not intended for you, please
    let us know, and then delete it. Please treat our information in
    confidence, as you would expect us to treat yours.The Land Registry
    checks all mail and attachments for known viruses, however, you are
    advised that you open any attachments at your own risk.



    The original of this email was scanned for viruses by the Government
    Secure Intranet (GSi) virus scanning service supplied exclusively by
    Cable & Wireless in partnership with MessageLabs.

    On leaving the GSi this email was certified virus-free

    --
    http://www.freelists.org/webpage/oracle-l
  • Igor Neyman at Mar 2, 2006 at 4:47 pm
    You are wrong.


    Igor

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Andem, Venu
    Sent: Thursday, March 02, 2006 11:41 AM
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    Paul,


    I think its the other way round, if one of the tables in the query does
    not contain statistics then Oracle uses RBO. pls correct me if i am
    wrong.


    Venu

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Baumgartel, Paul
    Sent: 02 March 2006 15:31
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    If any table mentioned in the query has statistics stored in the

    data dictionary, Oracle will use the CBO.


    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 10:28 AM
    To: oracle-l_at_freelists.org
    Subject: CBO question ?

    Hi,

    The execution plan for a query in oracle 9i shows

    "Optimizer= CHOOSE", how can i force the DB engine to use the COST based
    optimization for a query ?

    Oracle version is 9.2.0.4.0

    Thanks
    Savio Pinto

    ========================================================================
    ======
    Please access the attached hyperlink for an important electronic

    communications disclaimer:


    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html




    PLEASE NOTE: THE ABOVE MESSAGE WAS RECEIVED FROM THE INTERNET.


    On entering the GSi, this email was scanned for viruses by the
    Government Secure Intranet (GSi) virus scanning service supplied
    exclusively by Cable & Wireless in partnership with MessageLabs.


    Please see
    http://www.gsi.gov.uk/main/notices/information/gsi-003-2002.pdf for
    further details.


    In case of problems, please call your organisational IT helpdesk


    Land Registry at Homebuyer Show, 17-19 March, ExCeL London



    If you have received this e-mail and it was not intended for you, please
    let us know, and then delete it. Please treat our information in
    confidence, as you would expect us to treat yours.The Land Registry
    checks all mail and attachments for known viruses, however, you are
    advised that you open any attachments at your own risk.



    The original of this email was scanned for viruses by the Government
    Secure Intranet (GSi) virus scanning service supplied exclusively by
    Cable & Wireless in partnership with MessageLabs.

    On leaving the GSi this email was certified virus-free

    --
    http://www.freelists.org/webpage/oracle-l
  • Baumgartel, Paul at Mar 2, 2006 at 4:50 pm
    Venu,


    Consider yourself corrected;-). An earlier posting on this contained the relevant section of the Oracle documentation, which states clearly that CBO is used if there are statistics on any table in the query. Results will probably be poor unless there are statistics for all tables (and their indexes).


    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Andem, Venu
    Sent: Thursday, March 02, 2006 11:41 AM
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    Paul,


    I think its the other way round, if one of the tables in the query does not contain statistics then Oracle uses RBO. pls correct me if i am wrong.


    Venu

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Baumgartel, Paul
    Sent: 02 March 2006 15:31
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    If any table mentioned in the query has statistics stored in the data dictionary, Oracle will use the CBO.


    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 10:28 AM
    To: oracle-l_at_freelists.org
    Subject: CBO question ?

    Hi,

    The execution plan for a query in oracle 9i shows "Optimizer= CHOOSE", how can i force the DB engine to use the COST based optimization for a query ?

    Oracle version is 9.2.0.4.0

    Thanks
    Savio Pinto

    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

    PLEASE NOTE: THE ABOVE MESSAGE WAS RECEIVED FROM THE INTERNET.

    On entering the GSi, this email was scanned for viruses by the Government Secure Intranet (GSi) virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs.

    Please see http://www.gsi.gov.uk/main/notices/information/gsi-003-2002.pdf for further details.

    In case of problems, please call your organisational IT helpdesk

    Land Registry at Homebuyer Show, 17-19 March, ExCeL London



    If you have received this e-mail and it was not intended for you, please let us know, and then delete it. Please treat our information in confidence, as you would expect us to treat yours.The Land Registry checks all mail and attachments for known viruses, however, you are advised that you open any attachments at your own risk.



    The original of this email was scanned for viruses by the Government Secure Intranet (GSi) virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs.

    On leaving the GSi this email was certified virus-free

    Please access the attached hyperlink for an important electronic communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

    --
    http://www.freelists.org/webpage/oracle-l
  • VIVEK_SHARMA at Mar 2, 2006 at 5:57 pm
    ISSUE - Getting HIGH Latch Free Wait on the following Latches after moving from RBO to CBO. ( ALL Objects been analyzed at 100 %). CPU Usage on DB Server has gone up by about 30 %. NOTE - Application has also been migrated to a Higher release along with the CBO movement.

    Qs Any init.ora parameters to Tune ?

    Would increasing _shared_pool_reserved_min_alloc to 6140 from the Default of 4400 Help?

    Setting cursorsharing = FORCE/SIMILAR caused "%sys" component of CPU Usage to shoot to 99 % within minutes of Database startup. Seemed to be hitting some Bug in 9.2.0.5 (64 Bit) on Solaris 9. Has anybody experienced this & is it solved in a 9i Higher Patch ? Which Patch on 9i is advisable?

    Qs Will Tuning of individual SQLs reduce this wait ?

    Any Other Advice please?

    Top 5 Timed Events

    ~~~~~~~~~~~~~~~~~~ % Total
    Event Waits Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    latch free 17,386,965 457,377 63.67
    CPU time 121,612 16.93
    db file sequential read 142,824,481 72,252 10.06
    log file sync 982,290 28,909 4.02
    db file scattered read 8,532,496 14,464 2.01
    -------------------------------------------------------------

    Wait Events for DB: UBIFIN Instance: UBIFIN Snaps: 1515 -1517

    Latch Activity for DB:

    Pct Avg Wait Pct
    Get Get Slps Time NoWait NoWait
    Latch Requests Miss /Miss (s) Requests Miss
    ------------------------ -------------- ------ ------ ------ ------------ ------
    cache buffers chains 4,141,035,268 0.3 1.2 0 311,630,019 26.7
    cache buffers lru chain 3,785,689 6.1 0.2 2300 321,104,446 25.1
    library cache 86,823,459 1.3 0.5 31780 56,524 17.4

    Database Size 1 TB, Number of Concurrent Users = 5000,

    Application Hybrid Application - Banking Product, Oracle 9.2.0.5 (64-Bit), Solaris 9, Number of CPUs on DB Server = 48

    Thanks Folks

    Vivek

    P.S. include my yahoo id (in CC) while replying please if possible

    CAUTION - Disclaimer *****************
    This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system.
    ***INFOSYS******** End of Disclaimer ********INFOSYS***
  • Kirtikumar Deshpande at Mar 2, 2006 at 10:06 pm
    Hi Vivek,


    There are a number of hidden parameters changed in 9i that affected CBO (as compared to
    8i CBO). I suggest contacting Oracle Support before changing any hidden parameters. I
    think this is know problem. If possible, test your application with 9.2.0.6. That seems
    to a stable version of 9i, so far.

    Regards,

    Kirti

    VIVEK_SHARMA wrote:
    ISSUE - Getting HIGH Latch Free Wait on the following Latches after moving from RBO to
    CBO. ( ALL Objects been analyzed at 100 %). CPU Usage on DB Server has gone up by about
    30 %. NOTE - Application has also been migrated to a Higher release along with the CBO
    movement.

    Qs Any init.ora parameters to Tune ?

    Would increasing _shared_pool_reserved_min_alloc to 6140 from the Default of 4400 Help?

    Setting cursorsharing = FORCE/SIMILAR caused "%sys" component of CPU Usage to shoot to
    99 % within minutes of Database startup. Seemed to be hitting some Bug in 9.2.0.5 (64
    Bit) on Solaris 9. Has anybody experienced this & is it solved in a 9i Higher Patch ?
    Which Patch on 9i is advisable?

    Qs Will Tuning of individual SQLs reduce this wait ?

    Any Other Advice please?

    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~ % Total
    Event Waits Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    latch free 17,386,965 457,377 63.67
    CPU time 121,612 16.93
    db file sequential read 142,824,481 72,252 10.06
    log file sync 982,290 28,909 4.02
    db file scattered read 8,532,496 14,464 2.01
    -------------------------------------------------------------
    Wait Events for DB: UBIFIN Instance: UBIFIN Snaps: 1515 -1517

    Latch Activity for DB:
    Pct Avg Wait Pct
    Get Get Slps Time NoWait NoWait
    Latch Requests Miss /Miss (s) Requests Miss
    ------------------------ -------------- ------ ------ ------ ------------ ------
    cache buffers chains 4,141,035,268 0.3 1.2 0 311,630,019 26.7
    cache buffers lru chain 3,785,689 6.1 0.2 2300 321,104,446 25.1
    library cache 86,823,459 1.3 0.5 31780 56,524 17.4


    Database Size 1 TB, Number of Concurrent Users = 5000,

    Application Hybrid Application - Banking Product, Oracle 9.2.0.5 (64-Bit), Solaris 9,
    Number of CPUs on DB Server = 48

    Thanks Folks

    Vivek

    P.S. include my yahoo id (in CC) while replying please if possible


    **************** CAUTION - Disclaimer *****************
    This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the
    use of the addressee(s). If you are not the intended recipient, please notify the
    sender by e-mail and delete the original message. Further, you are not to copy,
    disclose, or distribute this e-mail or its contents to any other person and any such
    actions are unlawful. This e-mail may contain viruses. Infosys has taken every
    reasonable precaution to minimize this risk, but is not liable for any damage you may
    sustain as a result of any virus in this e-mail. You should carry out your own virus
    checks before opening the e-mail or attachment. Infosys reserves the right to monitor
    and review the content of all messages sent to or from this e-mail address. Messages
    sent to or from this e-mail address may be stored on the Infosys e-mail system.
    ***INFOSYS******** End of Disclaimer ********INFOSYS***
    --
    http://www.freelists.org/webpage/oracle-l

    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Shiva subramaniam at Mar 3, 2006 at 12:00 am
    I am wondering if you are using DISM (Dynamic Intimate shared memory). If
    you are using DISM, please check if there is any swapping.

    Regards

    Shiva
    On 3/3/06, VIVEK_SHARMA wrote:


    ISSUE - Getting HIGH Latch Free Wait on the following Latches after moving
    from RBO to CBO. ( ALL Objects been analyzed at 100 %). CPU Usage on DB
    Server has gone up by about 30 %. NOTE - Application has also been migrated
    to a Higher release along with the CBO movement.

    Qs Any init.ora parameters to Tune ?

    Would increasing _shared_pool_reserved_min_alloc to 6140 from the Default
    of 4400 Help?

    Setting cursorsharing = FORCE/SIMILAR caused "%sys" component of CPU Usage
    to shoot to 99 % within minutes of Database startup. Seemed to be hitting
    some Bug in 9.2.0.5 (64 Bit) on Solaris 9. Has anybody experienced this &
    is it solved in a 9i Higher Patch ? Which Patch on 9i is advisable?

    Qs Will Tuning of individual SQLs reduce this wait ?

    Any Other Advice please?

    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~ %
    Total
    Event Waits Time (s) Ela
    Time
    -------------------------------------------- ------------ -----------
    --------
    latch free 17,386,965 457,377
    63.67
    CPU time 121,612
    16.93
    db file sequential read 142,824,481 72,252
    10.06
    log file sync 982,290 28,909
    4.02
    db file scattered read 8,532,496 14,464
    2.01
    -------------------------------------------------------------
    Wait Events for DB: UBIFIN Instance: UBIFIN Snaps: 1515 -1517

    Latch Activity for DB:
    Pct Avg
    Wait Pct
    Get Get Slps Time NoWait
    NoWait
    Latch Requests Miss /Miss (s)
    Requests Miss
    ------------------------ -------------- ------ ------ ------ ------------
    ------
    cache buffers chains 4,141,035,268 0.3 1.2 0 311,630,019
    26.7
    cache buffers lru chain 3,785,689 6.1 0.2
    2300 321,104,446 25.1
    library cache 86,823,459 1.3 0.5 31780
    56,524 17.4


    Database Size 1 TB, Number of Concurrent Users = 5000,

    Application Hybrid Application - Banking Product, Oracle 9.2.0.5(64-Bit), Solaris 9, Number of CPUs on DB Server = 48

    Thanks Folks

    Vivek

    P.S. include my yahoo id (in CC) while replying please if possible


    **************** CAUTION - Disclaimer *****************
    This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
    solely for the use of the addressee(s). If you are not the intended
    recipient, please notify the sender by e-mail and delete the original
    message. Further, you are not to copy, disclose, or distribute this e-mail
    or its contents to any other person and any such actions are unlawful. This
    e-mail may contain viruses. Infosys has taken every reasonable precaution to
    minimize this risk, but is not liable for any damage you may sustain as a
    result of any virus in this e-mail. You should carry out your own virus
    checks before opening the e-mail or attachment. Infosys reserves the right
    to monitor and review the content of all messages sent to or from this
    e-mail address. Messages sent to or from this e-mail address may be stored
    on the Infosys e-mail system.
    ***INFOSYS******** End of Disclaimer ********INFOSYS***
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Mar 2, 2006 at 5:03 pm
    There can be other triggers too:
    CHOOSE will use CBO if *any* one of these is true:

    At least one table in the query has statistics
    A table has CBO-only characteristics:

    table is an IOT
    table is partitioned
    table has parallel degree greater than 1

    Also, there may be other triggering events that invoke CBO.....


    -Mark


    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    "Exception: Some dividends may be reported as qualified dividends but
    are not qualified dividends. These include:

    * Dividends you received on any share of stock that you held for less
    than 61 days during the 121-day period that began 60 days before the
    ex-dividend date. The ex-dividend date is the first date following the
    declaration of a dividend on which the purchaser of a stock is not
    entitled to receive the next dividend payment. When counting the number
    of days you held the stock, include the day you disposed of the stock
    but not the day you acquired it. See the examples below. Also, when
    counting the number of days you held the stock, you cannot count certain
    days during which your risk of loss was diminished. See Pub. 550 for
    more details."

    --IRS, Form 1040-A Instruction Booklet, Line 9b: Qualified Dividends

    ________________________________

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Igor Neyman
    Sent: Thursday, March 02, 2006 11:47 AM
    To: Venu.Andem_at_landregistry.gsi.gov.uk; oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    You are wrong.

    Igor

    ________________________________

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Andem, Venu
    Sent: Thursday, March 02, 2006 11:41 AM
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    Paul,

    I think its the other way round, if one of the tables in the query does
    not contain statistics then Oracle uses RBO. pls correct me if i am
    wrong.

    Venu

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Baumgartel, Paul
    Sent: 02 March 2006 15:31
    To: oracle-l_at_freelists.org
    Subject: RE: CBO question ?

    If any table mentioned in the query has statistics stored in the
    data dictionary, Oracle will use the CBO.

    Paul Baumgartel
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Savio Pinto (s)
    Sent: Thursday, March 02, 2006 10:28 AM
    To: oracle-l_at_freelists.org
    Subject: CBO question ?

    Hi,

    The execution plan for a query in oracle 9i shows
    "Optimizer= CHOOSE", how can i force the DB engine to use the COST based
    optimization for a query ?

    Oracle version is 9.2.0.4.0

    Thanks
    Savio Pinto

    ========================================================================
    ======
    Please access the attached hyperlink for an important electronic
    communications disclaimer:

    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

    ========================================================================
    ======

    PLEASE NOTE: THE ABOVE MESSAGE WAS RECEIVED FROM THE INTERNET.

    On entering the GSi, this email was scanned for viruses by the
    Government Secure Intranet (GSi) virus scanning service supplied
    exclusively by Cable & Wireless in partnership with MessageLabs.

    Please see
    http://www.gsi.gov.uk/main/notices/information/gsi-003-2002.pdf for
    further details.

    In case of problems, please call your organisational IT helpdesk

    Land Registry at Homebuyer Show, 17-19 March, ExCeL London

    If you have received this e-mail and it was not intended for you, please
    let us know, and then delete it. Please treat our information in
    confidence, as you would expect us to treat yours.The Land Registry
    checks all mail and attachments for known viruses, however, you are
    advised that you open any attachments at your own risk.

    The original of this email was scanned for viruses by the Government
    Secure Intranet (GSi) virus scanning service supplied exclusively by
    Cable & Wireless in partnership with MessageLabs.

    On leaving the GSi this email was certified virus-free

    --
    http://www.freelists.org/webpage/oracle-l
  • Ram K at Mar 2, 2006 at 8:46 pm
    If Oracle is going to use CBO for partitioned tables by default, how does it
    work in the case of absence of statistics?
    Does it collect statistics at runtime? Usually partitioned tables tend to
    be big. I am interested to know how it works.
    Thanks.

    Ram.
    On 3/2/06, Bobak, Mark wrote:

    There can be other triggers too:
    CHOOSE will use CBO if *any* one of these is true:
    - At least one table in the query has statistics
    - A table has CBO-only characteristics:
    - table is an IOT
    - table is partitioned
    - table has parallel degree greater than 1

    Also, there may be other triggering events that invoke CBO.....

    -Mark


    *--*
    *Mark J. Bobak*
    *Senior Oracle Architect*
    *ProQuest Information & Learning*

    "Exception: Some dividends may be reported as qualified dividends but are
    not qualified dividends. These include:

    � Dividends you received on any share of stock that you held for less than
    61 days during the 121-day period that began 60 days before the ex-dividend
    date. The ex-dividend date is the first date following the declaration of a
    dividend on which the purchaser of a stock is not entitled to receive the
    next dividend payment. When counting the number of days you held the stock,
    include the day you disposed of the stock but not the day you acquired it.
    See the examples below. Also, when counting the number of days you held the
    stock, you cannot count certain days during which your risk of loss was
    diminished. See Pub. 550 for more details."

    --IRS, Form 1040-A Instruction Booklet, Line 9b: Qualified Dividends


    ------------------------------
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Igor Neyman
    *Sent:* Thursday, March 02, 2006 11:47 AM
    *To:* Venu.Andem_at_landregistry.gsi.gov.uk; oracle-l_at_freelists.org
    *Subject:* RE: CBO question ?


    You are wrong.

    Igor

    ------------------------------
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Andem, Venu
    *Sent:* Thursday, March 02, 2006 11:41 AM
    *To:* oracle-l_at_freelists.org
    *Subject:* RE: CBO question ?

    Paul,

    I think its the other way round, if one of the tables in the query does
    not contain statistics then Oracle uses RBO. pls correct me if i am wrong.

    Venu

    -----Original Message-----
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org]*On Behalf Of *Baumgartel, Paul
    *Sent:* 02 March 2006 15:31
    *To:* oracle-l_at_freelists.org
    *Subject:* RE: CBO question ?

    If any table mentioned in the query has statistics stored in the data
    dictionary, Oracle will use the CBO.


    *Paul Baumgartel*
    paul.baumgartel_at_credit-suisse.com
    212.538.1143

    -----Original Message-----
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org]*On Behalf Of *Savio Pinto (s)
    *Sent:* Thursday, March 02, 2006 10:28 AM
    *To:* oracle-l_at_freelists.org
    *Subject:* CBO question ?

    Hi,

    The execution plan for a query in oracle 9i shows "Optimizer= CHOOSE", how
    can i force the DB engine to use the COST based optimization for a query ?

    Oracle version is 9.2.0.4.0

    Thanks
    Savio Pinto

    ==============================================================================
    Please access the attached hyperlink for an important electronic communications disclaimer:
    http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
    ==============================================================================


    PLEASE NOTE: THE ABOVE MESSAGE WAS RECEIVED FROM THE INTERNET.
    On entering the GSi, this email was scanned for viruses by the Government
    Secure Intranet (GSi) virus scanning service supplied exclusively by Cable &
    Wireless in partnership with MessageLabs.

    Please see http://www.gsi.gov.uk/main/notices/information/gsi-003-2002.pdffor further details.

    In case of problems, please call your organisational IT helpdesk

    *Land Registry at Homebuyer Show, 17-19 March, ExCeL London*



    If you have received this e-mail and it was not intended for you, please
    let us know, and then delete it. Please treat our information in confidence,
    as you would expect us to treat yours.The Land Registry checks all mail
    and attachments for known viruses, however, you are advised that you open
    any attachments at your own risk.



    The original of this email was scanned for viruses by the Government
    Secure Intranet (GSi) virus scanning service supplied exclusively by Cable &
    Wireless in partnership with MessageLabs.

    On leaving the GSi this email was certified virus-free
    --
    Thanks,
    Ram.

    --
    http://www.freelists.org/webpage/oracle-l
  • Michael McMullen at Mar 2, 2006 at 8:59 pm
    Just ran in to this today. For some reason my biggest partitioned table
    160Gb doesn't have table level statistics. I noticed the queries doing
    dynamic sampling.

    Mike
  • Ram K at Mar 2, 2006 at 11:45 pm
    Mike,

    What version of Oracle are you using? 10g?

    Thanks.
    On 3/2/06, Michael McMullen wrote:

    Just ran in to this today. For some reason my biggest partitioned table
    160Gb doesn't have table level statistics. I noticed the queries doing
    dynamic sampling.

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

    --
    Thanks,
    Ram.

    --
    http://www.freelists.org/webpage/oracle-l
  • Gints Plivna at Mar 2, 2006 at 10:03 pm
    Resending because of overquoting.

    Don't know if it is different for partitioned tables, but here are
    defaults (nothing is said whether it is or is not for partitioned
    tables and indexes, probably someone smarter will add some clever word
    here):
    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/stats.htm#25056

    But if you haven't analyzed tables you can use dynamic_sampling either
    as hint or initialization parameter optimizer_dynamic_sampling.
    Here are link for various dynamic_samling levels
    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#11792

    If I remeber correctly then for 9i optimizer_dynamic_sampling default
    value is 1, for 10g it is 2.

    Gints

    2006/3/2, Ram K :
    If Oracle is going to use CBO for partitioned tables by default, how does it
    work in the case of absence of statistics?
    Does it collect statistics at runtime? Usually partitioned tables tend to
    be big. I am interested to know how it works.
    Thanks.

    Ram.
    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Mar 3, 2006 at 10:27 am
    Funnily enough, the first (technical) slide in my
    "Migrating to CBO" presentation at Hotsos shows
    how you can increase the CPU and latch activity
    even if every single execution plan stays the same.

    You really need a snapshot from a comparable period
    running under RBO before drawing any conclusions.

    (a) It is possible that you have a few changes in execution
    path that result in much more logical I/O - hence much
    more CPU usage. In this case, you might have virtually
    no change in the amount of latch activity on the library
    cache - but have much more of a problem getting latches
    or coming back onto a CPU after sleeping for a latch
    because of CPU starvation.

    (b) On the other hand, you may simply be doing much more
    work whilst parsing, which would mean an increase in
    latch acquisition on the library cache, and latch competition.
    Would increasing _shared_pool_reserved_min_alloc to 6140 from the Default of
    4400 Help?
    Probably not. But since you have 5,000 users, can you
    check that your shared_pool_reserved is at least 5,000 * 25KB
    plus a few dozen MB. One possible pressure point on latching
    comes from Oracle attempting to allocate a memory chunk for
    the session parameters. If you have lots of sessions logging on
    and off all the time, you need to get these chunks into the
    reserved pool.

    Your comment about cursor_sharing = force/similar could
    be indicative of other latch activity going sky-high and causing
    extreme blocking. (I would have bet on that if you had only
    complained about 'similar', but 'force' makes me a little cautious -
    it is possible, of course, that you get the same symptom for two
    different reasons).
    Qs Will Tuning of individual SQLs reduce this wait ?
    Quite possibly - if your underlying problem is scenario
    (a) above. In 9.2.0.5 I would have a quick check for
    execution plans that were showing btree-bitmap conversions,
    and subquery-unnesting. These are operations that have
    caused people particular grief in the past on upgrades.
    Otherwise look for SQL that appears to be much more CPU
    intensive than it was before the upgrade. (Again, if you have
    the snapshots across a similar period - at level 5 for the "top
    sql" reports).

    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
    Subject: HIGH latch free Wait on moving from RBO to CBO?
    Date: Thu, 2 Mar 2006 23:27:22 +0530
    From: "VIVEK_SHARMA"


    ISSUE - Getting HIGH Latch Free Wait on the following Latches after moving
    from RBO to CBO. ( ALL Objects been analyzed at 100 %). CPU Usage on DB Server
    has gone up by about 30 %. NOTE - Application has also been migrated to a
    Higher release along with the CBO movement.

    Qs Any init.ora parameters to Tune ?

    Would increasing _shared_pool_reserved_min_alloc to 6140 from the Default of
    4400 Help?

    Setting cursorsharing = FORCE/SIMILAR caused "%sys" component of CPU Usage to
    shoot to 99 % within minutes of Database startup. Seemed to be hitting some
    Bug in 9.2.0.5 (64 Bit) on Solaris 9. Has anybody experienced this & is it
    solved in a 9i Higher Patch ? Which Patch on 9i is advisable?

    Qs Will Tuning of individual SQLs reduce this wait ?

    Any Other Advice please?

    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~ % Total
    Event Waits Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    latch free 17,386,965 457,377 63.67
    CPU time 121,612 16.93
    db file sequential read 142,824,481 72,252 10.06
    log file sync 982,290 28,909 4.02
    db file scattered read 8,532,496 14,464 2.01
    -------------------------------------------------------------
    Wait Events for DB: UBIFIN Instance: UBIFIN Snaps: 1515 -1517

    Latch Activity for DB:
    Pct Avg Wait
    Pct
    Get Get Slps Time NoWait
    NoWait
    Latch Requests Miss /Miss (s) Requests
    Miss
    ------------------------ -------------- ------ ------ ------ ------------ ------
    cache buffers chains 4,141,035,268 0.3 1.2 0 311,630,019
    26.7
    cache buffers lru chain 3,785,689 6.1 0.2 2300 321,104,446
    25.1
    library cache 86,823,459 1.3 0.5 31780 56,524
    17.4


    Database Size 1 TB, Number of Concurrent Users = 5000,
    --
    http://www.freelists.org/webpage/oracle-l
  • VIVEK_SHARMA at Mar 3, 2006 at 3:38 pm
    High "Latch free" WAIT Resolved by Tuning the BAD SQLs which were doing FULL Table Scans.


    CPU Usage on BOTH APP & DB Servers thus Fell down to 60 % from 100 %.


    Thanks Anand, Kirti, Jonathan for the quick & valuable feedback



    From: Anand Rao
    Sent: Fri 3/3/2006 10:35 AM
    To: VIVEK_SHARMA; vivek_sharma_1_at_yahoo.com
    Cc: oracle-l
    Subject: Re: HIGH latch free Wait on moving from RBO to CBO?

    Hi Vivek,

    Yes, as Kirti mentioned, there are too many variables to check.

    It would be wise to actually *TEST* your application in a phased manner rather than just arbitrarily changing CURSOR_SHARING or other parameters.

    if i am not mistaken, the last time i touched the your application on a 5 TB database, it suffered heavily with the same problem on 9.2.0.5 on Solaris 9 on a 48 CPU machine.

    you really need to change your SQL. That's the long term solution.

    from the small statspack extract, it looks like you have problems with library cache latch rather than CBC latch. but a more detailed information from Statspack, V$LATCH, V$LATCH_CHILDREN will be helpful to you.

    Metalink has excellent articles on Shared Pool tuning, assuming that you have isolated the problem to pure lib cache issues.

    for the short term, increase your session_cached_cursors to 500 or more and check the results. leave open_cursors at 200. Remember, your shared pool needs a proportional increase too. I guess you are already using 1GB or more. On top of that you have MTS :-)

    have you configured large_pool_size ?

    Have you tried dividing your shared pool into multiple sub pools?

    need to set _kghdsidx_count=3 or 5

    contact Oracle Support before doing this change.

    you have to dig deeper and find out what is causing this latch contention.

    Is it too many (hard) parses?
    are you sharing SQL? Version count, etc.
    Aging of objects?
    Heavy Sequence usage?
    Too many lookups to the Dictionary Cache

    are you using LMTs and ASSM?

    I guess you are already using Partitioning for both tables and indexes.

    many factors can contribute to the overall symptom. you have to test it in an organised fashion.

    see if session_cached_cursors relieves you a bit. use cursor_sharing=similar and see if it makes any difference.

    regards
    anand

    On 03/03/06, shiva subramaniam wrote:

    I am wondering if you are using DISM (Dynamic Intimate shared memory). If you are using DISM, please check if there is any swapping.




    Regards



    Shiva




    On 3/3/06, VIVEK_SHARMA wrote:

    ISSUE - Getting HIGH Latch Free Wait on the following Latches after moving from RBO to CBO. ( ALL Objects been analyzed at 100 %). CPU Usage on DB Server has gone up by about 30 %. NOTE - Application has also been migrated to a Higher release along with the CBO movement.


    Qs Any init.ora parameters to Tune ?


    Would increasing _shared_pool_reserved_min_alloc to 6140 from the Default of 4400 Help?


    Setting cursorsharing = FORCE/SIMILAR caused "%sys" component of CPU Usage to shoot to 99 % within minutes of Database startup. Seemed to be hitting some Bug in 9.2.0.5 (64 Bit) on Solaris 9. Has anybody experienced this & is it solved in a 9i Higher Patch ? Which Patch on 9i is advisable?


    Qs Will Tuning of individual SQLs reduce this wait ?


    Any Other Advice please?


    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~ % Total
    Event Waits Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    latch free 17,386,965 457,377 63.67
    CPU time 121,612 16.93
    db file sequential read 142,824,481 72,252 10.06
    log file sync 982,290 28,909 4.02
    db file scattered read 8,532,496 14,464 2.01
    -------------------------------------------------------------
    Wait Events for DB: UBIFIN Instance: UBIFIN Snaps: 1515 -1517

    Latch Activity for DB:
    Pct Avg Wait Pct
    Get Get Slps Time NoWait NoWait
    Latch Requests Miss /Miss (s) Requests Miss
    ------------------------ -------------- ------ ------ ------ ------------ ------
    cache buffers chains 4,141,035,268 0.3 1.2 0 311,630,019 26.7
    cache buffers lru chain 3,785,689 6.1 0.2 2300 321,104,446 25.1
    library cache 86,823,459 1.3 0.5 31780 56,524 17.4

    Database Size 1 TB, Number of Concurrent Users = 5000,

    Application Hybrid Application - Banking Product, Oracle 9.2.0.5 (64-Bit), Solaris 9, Number of CPUs on DB Server = 48

    Thanks Folks

    Vivek

    P.S. include my yahoo id (in CC) while replying please if possible

    **************** CAUTION - Disclaimer *****************
    This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system.
    ***INFOSYS******** End of Disclaimer ********INFOSYS***

Related Discussions

People

Translate

site design / logo © 2021 Grokbase