<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

-----Mensaje original-----De: root@fatcity.com
En nombre de Tim GormanEnviado el:
lunes, 30 de septiembre de 2002 17:04Para: Multiple recipients of
list ORACLE-LAsunto: 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

Though technically correct, there are many steps
between someone coding a SQL statement and this end-result of additional

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

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 -----
Mercadante, Thomas F
To: Multiple recipients of list ORACLE-L

Sent: Monday, September 30, 2002 7:48
Subject: RE: Does the case of an Oracle
query statement affect query perfo


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
Tom Mercadante Oracle Certified Professional

-----Original Message-----From: Jamadagni, Rajendra
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.

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
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?
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

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

in advance,

Please see the official ORACLE-L FAQ: http://www.orafaq.com
Author: Pablo Campos Durante
INET: pdurante_at_agora-europe.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).

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 1 | next ›
Discussion Overview
grouporacle-l @
postedSep 30, '02 at 4:13p
activeSep 30, '02 at 4:13p

1 user in discussion

Pablo Campos Durante: 1 post



site design / logo © 2022 Grokbase