From Victoria_Financial.VICTORIA_FINANCIAL@lnn.com Fri, 19 Oct 2001 15:33:32 -0700 From: Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL@lnn.com Date: Fri, 19 Oct 2001 15:33:32 -0700 Subject: Re: Bind Variables Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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@gelco.com on 10/19/2001 03:07:56 PM Please respond to ORACLE-L@fatcity.com To: Multiple recipients of list ORACLE-L 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 >" 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@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.

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@knobias.com
www.knobias.com
--____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 MIIKkwYJKoZIhvcNAQcCoIIKhDCCCoACAQExCzAJBgUrDgMCGgUAMAsGCSqGSIb3DQEHAaCCCJ4 w ggI8MIIBpaADAgECAgMD6X8wDQYJKoZIhvcNAQEEBQAwgZIxCzAJBgNVBAYTAlpBMRUwEwYDVQQ I EwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEPMA0GA1UEChMGVGhhd3RlMR0wGwY D VQQLExRDZXJ0aWZpY2F0ZSBTZXJ2aWNlczEoMCYGA1UEAxMfUGVyc29uYWwgRnJlZW1haWwgUlN B IDIwMDAuOC4zMDAeFw0wMTAxMDUxNTQ3MzdaFw0wMjAxMDUxNTQ3MzdaMEUxHzAdBgNVBAMTFlR o YXd0ZSBGcmVlbWFpbCBNZW1iZXIxIjAgBgkqhkiG9w0BCQEWE2RodWdoZXNAa25vYmlhcy5jb20 w XDANBgkqhkiG9w0BAQEFAANLADBIAkEAn8T4J83YTOXIk3iPl8Hpg58yaB/YQk0hWUHIDe26ZCw J P7MnmKJkyiTRP3Vl7WJdt3SOBuLYsQa4bewJrG5SHQIDAQABozAwLjAeBgNVHREEFzAVgRNkaHV n aGVzQGtub2JpYXMuY29tMAwGA1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEEBQADgYEAloEUXE3MsxS e oNIMwOdM7l+j6zat3yCav2T1s06uBaus9hRgruTGI0+HzkPdjj0zJ4Q2krHO1QzKnRz24vExupK + 2GR13ldRWLfoDphFJyvBXuI0x2AhPpC+KY04jBojgtKmwX//BoKquodT89CzcDrZaU4FRU5OG7c M Hc4q/c8wggMpMIICkqADAgECAgEMMA0GCSqGSIb3DQEBBAUAMIHRMQswCQYDVQQGEwJaQTEVMBM G A1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSB D b25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgY D VQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmF s LWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDAwODMwMDAwMDAwWhcNMDIwODI5MjM1OTU5WjCBkjE L MAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMQ8 w DQYDVQQKEwZUaGF3dGUxHTAbBgNVBAsTFENlcnRpZmljYXRlIFNlcnZpY2VzMSgwJgYDVQQDEx9 Q ZXJzb25hbCBGcmVlbWFpbCBSU0EgMjAwMC44LjMwMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQK B gQDeMzKmY8cJJUU+0m54J2eBxdqIGYKXDuNEKYpjNSptcDz63K737nRvMLwzkH/5NHGgo22Y8cN P omXbDfpL8dbdYaX5hc1VmjUanZJ1qCeu2HL5ugL217CR3hzpq+AYA6h8Q0JQUYeDPPA5tJtUihO H /7ObnUlmAC0JieyUa+mhaQIDAQABo04wTDApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF 0 ZUxhYmVsMS0yOTcwEgYDVR0TAQH/BAgwBgEB/wIBADALBgNVHQ8EBAMCAQYwDQYJKoZIhvcNAQE E BQADgYEAcxtvJmWL/xU0S1liiu1EvknH6A27j7kNaiYqYoQfuIdjdBxtt88aU5FL4c3mONntUPQ 6 bDSSrOaSnG7BIwHCCafvS65y3QZn9VBvLli4tgvBUFe17BzX7xe21Yibt6KIGu05Wzl9NPy2lhg l TWr0ncXDkS+plrgFPFL83eliA0gwggMtMIIClqADAgECAgEAMA0GCSqGSIb3DQEBBAUAMIHRMQs w CQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRvd24xGjA Y BgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZpY2F0aW9uIFNlcnZpY2V z IERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgQ0ExKzApBgkqhki G 9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNOTYwMTAxMDAwMDAwWhcNMjA x MjMxMjM1OTU5WjCB0TELMAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1U E BxMJQ2FwZSBUb3duMRowGAYDVQQKExFUaGF3dGUgQ29uc3VsdGluZzEoMCYGA1UECxMfQ2VydGl m aWNhdGlvbiBTZXJ2aWNlcyBEaXZpc2lvbjEkMCIGA1UEAxMbVGhhd3RlIFBlcnNvbmFsIEZyZWV t YWlsIENBMSswKQYJKoZIhvcNAQkBFhxwZXJzb25hbC1mcmVlbWFpbEB0aGF3dGUuY29tMIGfMA0 G CSqGSIb3DQEBAQUAA4GNADCBiQKBgQDUadfUsJRkW3HpR9gMUbbqcpGwhF59LQ2PexLfhSV1KHQ 6 QixjJ5+Ve0vvfhmHHYbqo925zpZkGsIUbkSsfOaP6E0PcR9AOKYAo4d49vmUhl6t6sBeduvZFKN d bnp8DKVLVX8GGSl/npom1Wq7OCQIapjHsdqjmJH9edvlWsQcuQIDAQABoxMwETAPBgNVHRMBAf8 E BTADAQH/MA0GCSqGSIb3DQEBBAUAA4GBAMfskn5O+PWWpWdiKqTwTRFg0G+NYFhhrCa7UjVcCM8 w +6hKloofYkIjjBcP9LpknBesRynfnZhe0mxgcVyirNx54+duAEcftQ0o6AKd5Jr9E/Sm2Xyx+Nx f IyYJkYBz0BQb3kOpgyXy5pwvFcr+pquKB3WLDN1RhGvk+NHOd6KBMYIBvTCCAbkCAQEwgZowgZI x CzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjE P MA0GA1UEChMGVGhhd3RlMR0wGwYDVQQLExRDZXJ0aWZpY2F0ZSBTZXJ2aWNlczEoMCYGA1UEAxM f UGVyc29uYWwgRnJlZW1haWwgUlNBIDIwMDAuOC4zMAIDA+l/MAkGBSsOAwIaBQCggbowGAYJKoZ I hvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDExMDE5MTIyNTM3WjAjBgkqhki G 9w0BCQQxFgQU3vwcV0TcDDLoMi5+wTvkDNbUdxkwWwYJKoZIhvcNAQkPMU4wTDANBggqhkiG9w0 D AgIBKDAOBggqhkiG9w0DAgICAIAwCgYIKoZIhvcNAwQwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgc w BwYFKw4DAhowDQYJKoZIhvcNAQEBBQAEQDuCiN4lfx9MA2lqIEIVUEt1K7xwOEJ/34UTLtCMkhs N Gg+WQMXB//YNdd9OY/Q1asVeSwn2p1ufm63qedRj3YQ= --____CIVUFTDKZVQDOGJWYCGU____-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danny Hugh INET: dhughes@knobias.com>"