RE: Does the case of an Oracle query statement affect query perfo

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 30 Sep 2002 08:34:41 -0800
Message-ID: <F001.004DC88C.20020930083441@fatcity.com>

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-Hoooo!

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" <Rajendra.Jamadagni_at_espn.com> 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: wisernet100_at_yahoo.com

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: 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: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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: 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 Mon Sep 30 2002 - 11:34:41 CDT