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<[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >"<DHUGHES cc: Sent by: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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>[EMAIL PROTECTED] <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: [EMAIL PROTECTED]>"<[EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).