| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Bind Variables
I have always used the CBO and statistics are generated fairly regularly. I use the analyze table estimate statistics which should not generate histogram information. I have even used the INDEX, FIRST_ROWS, and ALL_ROWS hints. None made a difference. I regenerated the statistics on all tables of a particular slow query with bind variables and it didn't make a difference either. Our platform is red hat linux 6.2 and oracle 8.1.7.
>>> kimberly.smith_at_gmd.fujitsu.com 10/19/01 18:58 PM >>> I have heard of it. Its a concern. However, with hints you can solve some of your problems. And then you could use Stored Outlines and get it stable. And then life will be groovy.
-----Original Message-----
[mailto:Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com]
Sent: Friday, October 19, 2001 3:45 PM
To: Multiple recipients of list ORACLE-L
Danny,
It sounds like when you "...moved to bind variables..." you did two things:
1. Switched to using CBO
2. Changed your application to take advantage of the bind variables.
I have never heard of switching to bind variables making things slower (and can not imagine when it would make it so -- the CBO vs. RBO is irrelevant in this case).
Thus the question: did you collect the statistics on all your application tables prior to switching to CBO?
If you did not have any statistics collected or had some tables that did not have statistics collected then Oracle may had been choosing wrong execution path.
Thanks,
Val Gamerman.
Cherie_Machler_at_gelco.com on 10/19/2001 03:07:56 PM
Please respond to ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Val Gamerman/Victoria Financial)
Danny,
Were you using histograms? Histograms do not work on bind variables,
they only work on literals.
I'm sure others will have more to say on the topic but that is one tidbit
that
I do have experience with on bind variables.
Cherie Machler
Oracle DBA
Gelco Information Network
"Danny
Hughes<dhughes_at_knobias.com To: Multiple
recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>"<DHUGHES cc:
Sent by: root_at_fatcity.com Subject: Bind
Variables
10/19/01 01:30 PM
Please respond to ORACLE-L
--____CIVUFTDKZVQDOGJWYCGU____
Content-Type: multipart/alternative; boundary="
____HALPYZPZYFFYCGAKLKOY____"
--____HALPYZPZYFFYCGAKLKOY____
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
I have a question about using bind variables. It is my understanding that
=
bind variables are to be used to keep the same SQL from being reparsed and
=
keeping multiple copies in the SGA, thus *speeding* things up. I also =
understand that by using bind variables that the CBO will not be able to =
optimally use the statistics to determine the most effective path to =
retrieve data. Some documentation states to use hints when you are using =
bind variables to tell the CBO which path to take. Other documentation =
states that bind variables don't like the CBO and to use the RBO.
After we looked at the library cache and saw thousands of copies of the =
same sql, and hundreds of different sql, we decided to look into converting
=
certain web pages to bind variables. After we converted to using bind =
variables, the pages that once took < 5 seconds to load all of the sudden =
took > 2 minutes to load. The db server was pegged at 100% from 8-5 every
=
day while this application was being used. We have converted back to =
literals and the pages are loading quickly again. While on bind variables,
=
we used every possible hint except RULE and had no luck. We didn't want =
to resort to using the RULE hint because oracle claims not to use the RBO =
(because of no further enhancements and possibly phasing it out), and to =
start using the CBO for everything.
There are so many confusing documents regarding the use of bind variables.
=
I wanted to see what the oracle community is doing in regards to using =
bind variables and what their experiences are.
TIA
Danny Hughes
DBA
Knobias.com
dhughes_at_knobias.com
www.knobias.com
--____HALPYZPZYFFYCGAKLKOY____
Content-Type: multipart/related; boundary="____KMQGUMGTJRAMPYVFBAEF____"
--____KMQGUMGTJRAMPYVFBAEF____
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
I have a question about using bind variables. It is my understanding that
=
bind variables are to be used to keep the same SQL from being reparsed and
=
keeping multiple copies in the SGA, thus *speeding* things up. I also =
understand that by using bind variables that the CBO will not be able to =
optimally use the statistics to determine the most effective path to =
retrieve data. Some documentation states to use hints when you are using =
bind variables to tell the CBO which path to take. Other documentation =
states that bind variables don't like the CBO and to use the RBO.
<br>
<br>After we looked at the library cache and saw thousands of copies of =
the same sql, and hundreds of different sql, we decided to look into =
converting certain web pages to bind variables. After we converted to =
using bind variables, the pages that once took < 5 seconds to load all =
of the sudden took > 2 minutes to load. The db server was pegged at =
100% from 8-5 every day while this application was being used. We have =
converted back to literals and the pages are loading quickly again. While
=
on bind variables, we used every possible hint except RULE and had no =
luck. We didn't want to resort to using the RULE hint because oracle =
claims not to use the RBO (because of no further enhancements and possibly
=
phasing it out), and to start using the CBO for everything.
<br>
<br>There are so many confusing documents regarding the use of bind =
variables. I wanted to see what the oracle community is doing in regards =
to using bind variables and what their experiences are.
<br> <br>TIA <br> <br>Danny Hughes <br>DBA <br>Knobias.com <br>dhughes_at_knobias.com <br>www.knobias.com <br> --____KMQGUMGTJRAMPYVFBAEF____--
Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" CONTENT-DESCRIPTION: S/MIME Cryptographic SignatureMIIKkwYJKoZIhvcNAQcCoIIKhDCCCoACAQExCzAJBgUrDgMCGgUAMAsGCSqGSIb3DQEHAaCCCJ4 w
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danny Hugh INET: dhughes_at_knobias.com>"<DHUGHES_at_knobias.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Cherie_Machler_at_gelco.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: kimberly.smith_at_gmd.fujitsu.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danny Hughes INET: dhughes_at_knobias.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Sat Oct 20 2001 - 11:58:09 CDT
 |
 |