RE: Does the case of an Oracle query statement affect query perfo
I never said both wouldn't be listed separately... in fact I said they WOULD. I said cursor_sharing would NOT change case, but would only affect the statement if you used a literal in it. --- "Mercadante, Thomas F" <[EMAIL PROTECTED]> wrote: > Rachel, > > This is what I thought, but list members say differently. > > I just tried a simple test: > > Ran the following two queries: > > select count(*) from tomsqltest; > SELECT COUNT(*) FROM TOMSQLTEST; > > and then: > > select hash_value,executions,sql_text from v$sql > where upper(sql_text) like '%TOMSQLTEST%' > / > HASH_VALUE EXECUTIONS SQL_TEXT > -- -- > -- > 2930079574 3 select hash_value,executions,sql_text from > v$sql w > here upper(sql_text) like '%TOMSQLTEST%' > > 542760132 1 SELECT COUNT(*) FROM TOMSQLTEST > 1802081865 1 select count(*) from tomsqltest > > Looks like Raj is correct. Both statements are listed as separate > and > different entries in the v$sql area. > > Learned something new today! I can go home and have a beer! > Wooo-H! > > Tom Mercadante > Oracle Certified Professional > > > -Original Message- > Sent: Monday, September 30, 2002 11:53 AM > To: Multiple recipients of list ORACLE-L > perfo > > > > I don't have papers to substantiate this, but in our 9012 database > > before we > > started using cursor_sharing we used to run out of our 600M SGA, > but > > since > > we started using CS, it went down. > > > That should have nothing to do with the case of a statement and > everything to do with using literals. AFAIK, cursor_sharing does not > change the case of a statement > > Saying that the case used to type in the statement causes a > performance > hit is not true. The performance hit comes from not standardizing the > SQL statement, so that Oracle has to reparse it because although it's > identical, the case is different so the statement is seen as > different. > You can use all uppercase, all lowercase, any combination of the two > you want, as long as you are consistent. > > > --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > > Tom, > > > > Well it simply comes to when Oracle will parse the query and try to > > find a > > "matching" sql to hash to in SGA, if it finds one, it will hash to > > the same > > one, else it will have to create a new hash entry. > > > > In pre-8i (before the cursor_sharing days) it would treat uppercase > > and > > lowercase queries are different. > > > > I don't have papers to substantiate this, but in our 9012 database > > before we > > started using cursor_sharing we used to run out of our 600M SGA, > but > > since > > we started using CS, it went down. > > > > Raj > > __ > > Rajendra Jamadagni MIS, ESPN Inc. > > Rajendra dot Jamadagni at ESPN dot com > > Any opinion expressed here is personal and doesn't reflect that of > > ESPN Inc. > > > > QOTD: Any clod can have facts, but having an opinion is an art! > > -Original Message- > > Sent: Monday, September 30, 2002 9:48 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Raj, > > > > Do you have any test cases or white papers to support your > statement? > > Especially the part about > > > > "if you mix-n-match that will make Oracle do more work." > > > > never heard of this before and I am interested if it is true. > > Tom Mercadante > > Oracle Certified Professional > > > > This > > e-mail message is confidential, intended only for the named > > recipient(s) above and may contain information that is privileged, > > attorney work product or exempt from disclosure under applicable > law. > > If you have received this message in error, or are not the named > > recipient(s), please immediately notify corporate MIS at (860) > > 766-2000 and delete this e-mail message from your computer, Thank > > > you.*2 > > > > > __ > Do you Yahoo!? > New DSL Internet Access from SBC & Yahoo! > http://sbc.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > 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.or
RE: Does the case of an Oracle query statement affect query perfo
Rachel, This is what I thought, but list members say differently. I just tried a simple test: Ran the following two queries: select count(*) from tomsqltest; SELECT COUNT(*) FROM TOMSQLTEST; and then: select hash_value,executions,sql_text from v$sql where upper(sql_text) like '%TOMSQLTEST%' / HASH_VALUE EXECUTIONS SQL_TEXT -- -- -- 2930079574 3 select hash_value,executions,sql_text from v$sql w here upper(sql_text) like '%TOMSQLTEST%' 542760132 1 SELECT COUNT(*) FROM TOMSQLTEST 1802081865 1 select count(*) from tomsqltest Looks like Raj is correct. Both statements are listed as separate and different entries in the v$sql area. Learned something new today! I can go home and have a beer! Wooo-H! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, September 30, 2002 11:53 AM To: Multiple recipients of list ORACLE-L perfo > I don't have papers to substantiate this, but in our 9012 database > before we > started using cursor_sharing we used to run out of our 600M SGA, but > since > we started using CS, it went down. That should have nothing to do with the case of a statement and everything to do with using literals. AFAIK, cursor_sharing does not change the case of a statement Saying that the case used to type in the statement causes a performance hit is not true. The performance hit comes from not standardizing the SQL statement, so that Oracle has to reparse it because although it's identical, the case is different so the statement is seen as different. You can use all uppercase, all lowercase, any combination of the two you want, as long as you are consistent. --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > Tom, > > Well it simply comes to when Oracle will parse the query and try to > find a > "matching" sql to hash to in SGA, if it finds one, it will hash to > the same > one, else it will have to create a new hash entry. > > In pre-8i (before the cursor_sharing days) it would treat uppercase > and > lowercase queries are different. > > I don't have papers to substantiate this, but in our 9012 database > before we > started using cursor_sharing we used to run out of our 600M SGA, but > since > we started using CS, it went down. > > Raj > __ > Rajendra Jamadagni MIS, ESPN Inc. > Rajendra dot Jamadagni at ESPN dot com > Any opinion expressed here is personal and doesn't reflect that of > ESPN Inc. > > QOTD: Any clod can have facts, but having an opinion is an art! > -Original Message- > Sent: Monday, September 30, 2002 9:48 AM > To: Multiple recipients of list ORACLE-L > > > Raj, > > Do you have any test cases or white papers to support your statement? > Especially the part about > > "if you mix-n-match that will make Oracle do more work." > > never heard of this before and I am interested if it is true. > Tom Mercadante > Oracle Certified Professional > > This > e-mail message is confidential, intended only for the named > recipient(s) above and may contain information that is privileged, > attorney work product or exempt from disclosure under applicable law. > If you have received this message in error, or are not the named > recipient(s), please immediately notify corporate MIS at (860) > 766-2000 and delete this e-mail message from your computer, Thank > you.*2 > __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Yo
RE: Does the case of an Oracle query statement affect query perfo
Title: RE: Does the case of an Oracle query statement affect query perform Thanks Tim, I didn't knew these differences between PROC/PLSQL and other modules ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Tim Gorman [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 11:04 AM To: Multiple recipients of list ORACLE-L Subject: Re: Does the case of an Oracle query statement affect query perfo All that he is referring to is the possibility that "mixing-n-matching" will cause the same SQL statement to be hashed differently, thus stored individually in the Shared SQL Area cache, thus more "hard parses" unnecessarily. More "hard-parses" is indeed "more work"... Though technically correct, there are many steps between someone coding a SQL statement and this end-result of additional hard-parses... If a developer or end-user is working via a precompiler/interpreter such as PRO*C, SQLJ, or PL/SQL or many other reporting tools, then the upper- and lower-case issues will be largely made irrelevant as the precompiler/interpreter tends to set all SQL command-text some similar convention before passing to the RDBMS (i.e. all upper-case and remove all unnecessary white-space, etc)... If it is not SQL developers writing this SQL into program-modules but instead end-users working interactively, then you have to ask yourself how many times they can type in and execute SQL in order for the increased number of "hard-parses" to matter. Assume 200 ad-hoc interactive end-user sessions, each typing in and executing slightly different SQL 20 times per day. That's 4000 more "hard-parses" -- no big deal... There are likely more circumstances to consider... However, if the people doing this coding are developers working in a low-level API such as OCI (i.e. C or C++), DBI::DBD (i.e. Perl), or JDBC (i.e. Java), then this SQL text will be sent straight to the RDBMS parser where it will indeed cause additional hard-parses. Since this code might be embedded inside a high-concurrency application, this problem could grow quite serious, especially if the developers follow-up this particular "bad habit" with other bad habits such as embedded literal data values, etc... As always, the severity of the problem is dependent on specific circumstances. It could be no problem at all, it could be the harbinger for serious problems... *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Does the case of an Oracle query statement affect query perfo
> I don't have papers to substantiate this, but in our 9012 database > before we > started using cursor_sharing we used to run out of our 600M SGA, but > since > we started using CS, it went down. That should have nothing to do with the case of a statement and everything to do with using literals. AFAIK, cursor_sharing does not change the case of a statement Saying that the case used to type in the statement causes a performance hit is not true. The performance hit comes from not standardizing the SQL statement, so that Oracle has to reparse it because although it's identical, the case is different so the statement is seen as different. You can use all uppercase, all lowercase, any combination of the two you want, as long as you are consistent. --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > Tom, > > Well it simply comes to when Oracle will parse the query and try to > find a > "matching" sql to hash to in SGA, if it finds one, it will hash to > the same > one, else it will have to create a new hash entry. > > In pre-8i (before the cursor_sharing days) it would treat uppercase > and > lowercase queries are different. > > I don't have papers to substantiate this, but in our 9012 database > before we > started using cursor_sharing we used to run out of our 600M SGA, but > since > we started using CS, it went down. > > Raj > __ > Rajendra Jamadagni MIS, ESPN Inc. > Rajendra dot Jamadagni at ESPN dot com > Any opinion expressed here is personal and doesn't reflect that of > ESPN Inc. > > QOTD: Any clod can have facts, but having an opinion is an art! > -Original Message- > Sent: Monday, September 30, 2002 9:48 AM > To: Multiple recipients of list ORACLE-L > > > Raj, > > Do you have any test cases or white papers to support your statement? > Especially the part about > > "if you mix-n-match that will make Oracle do more work." > > never heard of this before and I am interested if it is true. > Tom Mercadante > Oracle Certified Professional > > This > e-mail message is confidential, intended only for the named > recipient(s) above and may contain information that is privileged, > attorney work product or exempt from disclosure under applicable law. > If you have received this message in error, or are not the named > recipient(s), please immediately notify corporate MIS at (860) > 766-2000 and delete this e-mail message from your computer, Thank > you.*2 > __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Does the case of an Oracle query statement affect query perfo
All that he is referring to is the possibility that
"mixing-n-matching" will cause the same SQL statement to be hashed differently,
thus stored individually in the Shared SQL Area cache, thus more "hard parses"
unnecessarily. More "hard-parses" is indeed "more work"...
Though technically correct, there are many steps
between someone coding a SQL statement and this end-result of additional
hard-parses...
If a developer or end-user is working via a
precompiler/interpreter such as PRO*C, SQLJ, or PL/SQL or many other
reporting tools, then the upper- and lower-case issues will be largely made
irrelevant as the precompiler/interpreter tends to set all SQL command-text
some similar convention before passing to the RDBMS (i.e. all upper-case
and remove all unnecessary white-space, etc)...
If it is not SQL developers writing this SQL into
program-modules but instead end-users working interactively, then you have to
ask yourself how many times they can type in and execute SQL in order for the
increased number of "hard-parses" to matter. Assume 200 ad-hoc
interactive end-user sessions, each typing in and executing slightly different
SQL 20 times per day. That's 4000 more "hard-parses" -- no big
deal...
There are likely more circumstances to
consider...
However, if the people doing this coding are
developers working in a low-level API such as OCI (i.e. C or C++), DBI::DBD
(i.e. Perl), or JDBC (i.e. Java), then this SQL text will be sent straight to
the RDBMS parser where it will indeed cause additional hard-parses. Since
this code might be embedded inside a high-concurrency application, this problem
could grow quite serious, especially if the developers follow-up this particular
"bad habit" with other bad habits such as embedded literal data values,
etc...
As always, the severity of the problem is dependent
on specific circumstances. It could be no problem at all, it could be the
harbinger for serious problems...
- Original Message -
From:
Mercadante, Thomas F
To: Multiple recipients of list ORACLE-L
Sent: Monday, September 30, 2002 7:48
AM
Subject: RE: Does the case of an Oracle
query statement affect query perfo
Raj,
Do
you have any test cases or white papers to support your statement?
Especially the part about
"if you mix-n-match that will make Oracle do
more work."
never heard of this before and I am interested if it is
true.
Tom Mercadante Oracle Certified Professional
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Monday, September 30,
2002 8:33 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: Does the case of an Oracle query statement
affect query perfo
As long as you stick to either (a) or (b) you will be okay ... if you
mix-n-match that will make Oracle do more work.
Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN
dot com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts,
but having an opinion is an
art!
-Original Message-From: Shantanu Datta
[mailto:[EMAIL PROTECTED]]Sent: Monday, September 30,
2002 3:58 AMTo: Multiple recipients of list
ORACLE-LSubject: Does the case of an Oracle query statement
affect query performance?
Hi,
Pardon me for such a naive
question, coz I am a novice when it comes to Oracle. This is basically got
to do with how Oracle parses a query.
Consider the following
queries:
a) SELECT column1, column2 FROM
table WHERE column0 = 5;
b) SELECT COLUMN1, COLUMN2 FROM
TABLE WHERE COLUMN0 =5;
Scenario 1: I use the naming
convention a) for ALL my queries
Scenario 2: I use the naming
convention b) for ALL my queries
Will there be any difference
in the execution time of the same queries in Scenario 1 vs
2?
Thanx in
advance,
Shantanu.
--
RE: Does the case of an Oracle query statement affect query perfo
I believe Raj is referring to the fact that Oracle will reuse SQL from the SQL Cache if the statement has been parsed already, but they must match verbatim. for example: a)SELECT column1, column2 FROM table WHERE column0 = 5; b)SELECT COLUMN1, COLUMN2 FROM TABLE WHERE COLUMN0 =5; will be stored in the sql cache with 2 different hash id's, so each will be stored separately in the cache. I have always found it recommended that a certain upper/lower case naming convention be followed to avoid this situation. -Joe --- "Mercadante, Thomas F" <[EMAIL PROTECTED]> wrote: > Raj, > > Do you have any test cases or white papers to support your > statement? > Especially the part about > > "if you mix-n-match that will make Oracle do more work." > > never heard of this before and I am interested if it is true. > > Tom Mercadante > Oracle Certified Professional __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
RE: Does the case of an Oracle query statement affect query perfo
Title: RE: Does the case of an Oracle query statement affect query perfo Tom, Well it simply comes to when Oracle will parse the query and try to find a "matching" sql to hash to in SGA, if it finds one, it will hash to the same one, else it will have to create a new hash entry. In pre-8i (before the cursor_sharing days) it would treat uppercase and lowercase queries are different. I don't have papers to substantiate this, but in our 9012 database before we started using cursor_sharing we used to run out of our 600M SGA, but since we started using CS, it went down. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 9:48 AM To: Multiple recipients of list ORACLE-L Subject: RE: Does the case of an Oracle query statement affect query perfo Raj, Do you have any test cases or white papers to support your statement? Especially the part about "if you mix-n-match that will make Oracle do more work." never heard of this before and I am interested if it is true. Tom Mercadante Oracle Certified Professional This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Does the case of an Oracle query statement affect query perfo
Raj, Do you have any test cases or white papers to support your statement? Especially the part about "if you mix-n-match that will make Oracle do more work." never heard of this before and I am interested if it is true. Tom Mercadante Oracle Certified Professional -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 8:33 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Does the case of an Oracle query statement affect query perfo As long as you stick to either (a) or (b) you will be okay ... if you mix-n-match that will make Oracle do more work. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Shantanu Datta [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 3:58 AMTo: Multiple recipients of list ORACLE-LSubject: Does the case of an Oracle query statement affect query performance? Hi, Pardon me for such a naive question, coz I am a novice when it comes to Oracle. This is basically got to do with how Oracle parses a query. Consider the following queries: a) SELECT column1, column2 FROM table WHERE column0 = 5; b) SELECT COLUMN1, COLUMN2 FROM TABLE WHERE COLUMN0 =5; Scenario 1: I use the naming convention a) for ALL my queries Scenario 2: I use the naming convention b) for ALL my queries Will there be any difference in the execution time of the same queries in Scenario 1 vs 2? Thanx in advance, Shantanu. --
RE: Does the case of an Oracle query statement affect query perfo
Oracle sees (a) and (b) as two different queries and parses them both. For them to be identical the text must match, including the white spaces. I suggest a small test under *identical conditions* to see if execution time varies ;) - Kirti -Original Message-From: Shantanu Datta [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 2:58 AMTo: Multiple recipients of list ORACLE-LSubject: Does the case of an Oracle query statement affect query performance? Hi, Pardon me for such a naive question, coz I am a novice when it comes to Oracle. This is basically got to do with how Oracle parses a query. Consider the following queries: a) SELECT column1, column2 FROM table WHERE column0 = 5; b) SELECT COLUMN1, COLUMN2 FROM TABLE WHERE COLUMN0 =5; Scenario 1: I use the naming convention a) for ALL my queries Scenario 2: I use the naming convention b) for ALL my queries Will there be any difference in the execution time of the same queries in Scenario 1 vs 2? Thanx in advance, Shantanu. --
RE: Does the case of an Oracle query statement affect query perfo
As long as you stick to either (a) or (b) you will be okay ... if you mix-n-match that will make Oracle do more work. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Shantanu Datta [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 3:58 AMTo: Multiple recipients of list ORACLE-LSubject: Does the case of an Oracle query statement affect query performance? Hi, Pardon me for such a naive question, coz I am a novice when it comes to Oracle. This is basically got to do with how Oracle parses a query. Consider the following queries: a) SELECT column1, column2 FROM table WHERE column0 = 5; b) SELECT COLUMN1, COLUMN2 FROM TABLE WHERE COLUMN0 =5; Scenario 1: I use the naming convention a) for ALL my queries Scenario 2: I use the naming convention b) for ALL my queries Will there be any difference in the execution time of the same queries in Scenario 1 vs 2? Thanx in advance, Shantanu. -- This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
