| 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
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____-- --____HALPYZPZYFFYCGAKLKOY____-- --____CIVUFTDKZVQDOGJWYCGU____
Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" CONTENT-DESCRIPTION: S/MIME Cryptographic Signature
MIIKkwYJKoZIhvcNAQcCoIIKhDCCCoACAQExCzAJBgUrDgMCGgUAMAsGCSqGSIb3DQEHAaCCCJ4w
ggI8MIIBpaADAgECAgMD6X8wDQYJKoZIhvcNAQEEBQAwgZIxCzAJBgNVBAYTAlpBMRUwEwYDVQQI
EwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEPMA0GA1UEChMGVGhhd3RlMR0wGwYD
VQQLExRDZXJ0aWZpY2F0ZSBTZXJ2aWNlczEoMCYGA1UEAxMfUGVyc29uYWwgRnJlZW1haWwgUlNB
IDIwMDAuOC4zMDAeFw0wMTAxMDUxNTQ3MzdaFw0wMjAxMDUxNTQ3MzdaMEUxHzAdBgNVBAMTFlRo
YXd0ZSBGcmVlbWFpbCBNZW1iZXIxIjAgBgkqhkiG9w0BCQEWE2RodWdoZXNAa25vYmlhcy5jb20w
XDANBgkqhkiG9w0BAQEFAANLADBIAkEAn8T4J83YTOXIk3iPl8Hpg58yaB/YQk0hWUHIDe26ZCwJ
P7MnmKJkyiTRP3Vl7WJdt3SOBuLYsQa4bewJrG5SHQIDAQABozAwLjAeBgNVHREEFzAVgRNkaHVn
aGVzQGtub2JpYXMuY29tMAwGA1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEEBQADgYEAloEUXE3MsxSe
oNIMwOdM7l+j6zat3yCav2T1s06uBaus9hRgruTGI0+HzkPdjj0zJ4Q2krHO1QzKnRz24vExupK+
2GR13ldRWLfoDphFJyvBXuI0x2AhPpC+KY04jBojgtKmwX//BoKquodT89CzcDrZaU4FRU5OG7cM
Hc4q/c8wggMpMIICkqADAgECAgEMMA0GCSqGSIb3DQEBBAUAMIHRMQswCQYDVQQGEwJaQTEVMBMG
A1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBD
b25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYD
VQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFs
LWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDAwODMwMDAwMDAwWhcNMDIwODI5MjM1OTU5WjCBkjEL
MAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMQ8w
DQYDVQQKEwZUaGF3dGUxHTAbBgNVBAsTFENlcnRpZmljYXRlIFNlcnZpY2VzMSgwJgYDVQQDEx9Q
ZXJzb25hbCBGcmVlbWFpbCBSU0EgMjAwMC44LjMwMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKB
gQDeMzKmY8cJJUU+0m54J2eBxdqIGYKXDuNEKYpjNSptcDz63K737nRvMLwzkH/5NHGgo22Y8cNP
omXbDfpL8dbdYaX5hc1VmjUanZJ1qCeu2HL5ugL217CR3hzpq+AYA6h8Q0JQUYeDPPA5tJtUihOH
/7ObnUlmAC0JieyUa+mhaQIDAQABo04wTDApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0
ZUxhYmVsMS0yOTcwEgYDVR0TAQH/BAgwBgEB/wIBADALBgNVHQ8EBAMCAQYwDQYJKoZIhvcNAQEE
BQADgYEAcxtvJmWL/xU0S1liiu1EvknH6A27j7kNaiYqYoQfuIdjdBxtt88aU5FL4c3mONntUPQ6
bDSSrOaSnG7BIwHCCafvS65y3QZn9VBvLli4tgvBUFe17BzX7xe21Yibt6KIGu05Wzl9NPy2lhgl
TWr0ncXDkS+plrgFPFL83eliA0gwggMtMIIClqADAgECAgEAMA0GCSqGSIb3DQEBBAUAMIHRMQsw
CQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRvd24xGjAY
BgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZpY2F0aW9uIFNlcnZpY2Vz
IERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG
9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNOTYwMTAxMDAwMDAwWhcNMjAx
MjMxMjM1OTU5WjCB0TELMAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UE
BxMJQ2FwZSBUb3duMRowGAYDVQQKExFUaGF3dGUgQ29uc3VsdGluZzEoMCYGA1UECxMfQ2VydGlm
aWNhdGlvbiBTZXJ2aWNlcyBEaXZpc2lvbjEkMCIGA1UEAxMbVGhhd3RlIFBlcnNvbmFsIEZyZWVt
YWlsIENBMSswKQYJKoZIhvcNAQkBFhxwZXJzb25hbC1mcmVlbWFpbEB0aGF3dGUuY29tMIGfMA0G CSqGSIb3DQEBAQUAA4GNADCBiQKBgQDUadfUsJRkW3HpR9gMUbbqcpGwhF59LQ2PexLfhSV1KHQ6
QixjJ5+Ve0vvfhmHHYbqo925zpZkGsIUbkSsfOaP6E0PcR9AOKYAo4d49vmUhl6t6sBeduvZFKNd
bnp8DKVLVX8GGSl/npom1Wq7OCQIapjHsdqjmJH9edvlWsQcuQIDAQABoxMwETAPBgNVHRMBAf8E
BTADAQH/MA0GCSqGSIb3DQEBBAUAA4GBAMfskn5O+PWWpWdiKqTwTRFg0G+NYFhhrCa7UjVcCM8w
+6hKloofYkIjjBcP9LpknBesRynfnZhe0mxgcVyirNx54+duAEcftQ0o6AKd5Jr9E/Sm2Xyx+Nxf
IyYJkYBz0BQb3kOpgyXy5pwvFcr+pquKB3WLDN1RhGvk+NHOd6KBMYIBvTCCAbkCAQEwgZowgZIx
CzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEP
MA0GA1UEChMGVGhhd3RlMR0wGwYDVQQLExRDZXJ0aWZpY2F0ZSBTZXJ2aWNlczEoMCYGA1UEAxMf
UGVyc29uYWwgRnJlZW1haWwgUlNBIDIwMDAuOC4zMAIDA+l/MAkGBSsOAwIaBQCggbowGAYJKoZI
hvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDExMDE5MTIyNTM3WjAjBgkqhkiG
9w0BCQQxFgQU3vwcV0TcDDLoMi5+wTvkDNbUdxkwWwYJKoZIhvcNAQkPMU4wTDANBggqhkiG9w0D
AgIBKDAOBggqhkiG9w0DAgICAIAwCgYIKoZIhvcNAwQwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcw
BwYFKw4DAhowDQYJKoZIhvcNAQEBBQAEQDuCiN4lfx9MA2lqIEIVUEt1K7xwOEJ/34UTLtCMkhsN
Gg+WQMXB//YNdd9OY/Q1asVeSwn2p1ufm63qedRj3YQ=
--____CIVUFTDKZVQDOGJWYCGU____--
-- 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).Received on Fri Oct 19 2001 - 12:53:33 CDT
 |
 |