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" <NDATFM_at_labor.state.ny.us> 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-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).
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).
Received on Mon Sep 30 2002 - 12:18:28 CDT