Danny,
Have you tried generating explain plans with and
without bind variables and comparing them? That
should help you identify if it's truly related to bind
variables. What are the wait events for the queries
that are running slower?
HTH,
- Anita
- Danny Hughes <dhughes_at_knobias.com> wrote:
> 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____--
> --____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_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).
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: abardeen1_at_yahoo.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 Sun Oct 21 2001 - 18:36:41 CDT
