Re: Query does not work in RAC
Date: Sun, 14 Apr 2013 19:55:25 -0600
Message-ID: <516B5E0D.5090008_at_gmail.com>
On 14/04/2013 6:36 PM, Eriovaldo Andrietta wrote:
> Hi,
> Thanks all for informations, and I would like to explain the scenary:
>
> The Oracle Instance is 11g. I donīt have details about the OS, It was
> running in a Customerīs machine.
11g is not a version. It is a family of versions. Could mean any of 2,000 patches have been applied (from 11.1.0.3 (beta), 11.1.0.6, 11.1.0.7 -> 11.2.0.1 ... 11.2.0.3)
> But .. I was working in a change and faced this issue. Imagine it, when we
> have a window of time to apply changes, and suddenly the sql stop... It is
> bad....
You still have not told us what ;'stop' means. Were there any specific
error messages?
> At the change moment, the unique purpose is solve the issue.
> I ordered the DBA to review parameters.
> Change the sql at this critical moment, is a little dangerous, because it
> can generate different result. The SQL was tested and approved.
Yes - changing any SQL can be dangerous. Then again, leaving it
unchanged potentially could be dangerous as well. Esxpecially since it
does not seem to work, unchanged.
> It is obviously that the sql must always run fast and generate the correct
> result in order to attend the business rules.
Generate the correct result *should* be the first priority. Fast ...
well, that is a nice thing.
> I can say that now I have more informations and experiences:
>
> - The USE_HASH hint uses temporary tablespace.
As do many many many other forms of SELECT and join. It's not whether
you use it, it's WHY are you overflowing memory to force it to be used.
> - The SQL has different behavior in different instances, it is obvious.
Absolutely - this is the basic premise of 4GL ... you tell it what you
want to accomplish, and you *let it determine, based on it's environment
information, HOW it should accomplish that.*
> - Each SQL must be analyzed and very well tested before Change
Same as any other program. No difference here.
> - Do not accept sql with poor construction,
See previous
> - Be sure that HINT used is actually necessary
HINTS are a LAST resort.
> - Be sure that statistics are actualized,
Yes. The accuracy of the decision process that creates the execution
plan is very dependent on this
> - Double check all important parameters and variables involved are Ok.
Again, no different from any other program
> - The team must be prepared to use all resources that Oracle offers to
> identify issues like that, as : DBMS_XPLAN and others.
And yet again, no different from any other program
> what more ... ?
Understand that Oracle's SQL engine does not behave the same as SQL
Server's SQL engine which does not behave the same as DB2's SQL engine
which does not behave the same as MySQL's SQL engine(s).
You must understand what the engine is doing, in single statement mode, in concurrent mode, and and concurrent-RAC mode, to be able to troubleshoot. And all of this is dependent on the exact (repeat - EXACT) version, including all patches, especially since many many patches are created specifically to impact and adjust THIS AREA of code.
As part of this, you really want to understand how PGA is used, and how
it might impact this kind of operation.
> In my case, the SQL should run only once, and I just changed the USE_HASH
> to USE_MERGE and solved my problem ...
Lucky. But do you know WHY? And whether this will impact anything in
the future? Or will this lead to another hit-and-miss myth?
> and changing successfully completed.
Congrats
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 15 2013 - 03:55:25 CEST