Re: Bind Variables

Fri, 19 Oct 2001 15:33:32 -0700 



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.




[EMAIL PROTECTED] on 10/19/2001 03:07:56 PM

Please respond to [EMAIL PROTECTED]

To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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<[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 &lt; 5 seconds to load all =
of the sudden took &gt; 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
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: [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).






-- 
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).























					Reply via email to