Re: Query does not work in RAC
- From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
- To: Tim Gorman <tim@xxxxxxxxx>
- Date: Sun, 14 Apr 2013 21:36:08 -0300
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.
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....
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.
It is obviously that the sql must always run fast and generate the correct
result in order to attend the business rules.
I can say that now I have more informations and experiences:
- The USE_HASH hint uses temporary tablespace.
- The SQL has different behavior in different instances, it is obvious.
- Each SQL must be analyzed and very well tested before Change
- Do not accept sql with poor construction,
- Be sure that HINT used is actually necessary
- Be sure that statistics are actualized,
- Double check all important parameters and variables involved are Ok.
- The team must be prepared to use all resources that Oracle offers to
identify issues like that, as : DBMS_XPLAN and others.
what more ... ?
In my case, the SQL should run only once, and I just changed the USE_HASH
to USE_MERGE and solved my problem ...
and changing successfully completed.
Thanks
Eriovaldo
On Sun, Apr 14, 2013 at 4:03 PM, Tim Gorman <tim@xxxxxxxxx> wrote:
> It might be helpful to post a prettified form of the SQL. It generally
> isn't necessary to include too much of the SELECT clause, but the FROM,
> WHERE, GROUP BY, ORDER BY, etc clauses all pertain to the execution plan.
>
> If you know how to use the PL/SQL package DBMS_XPLAN, then it would also
> be an excellent idea to post the observed execution plans along with the
> prettified SQL text, rather than just trying to describe them to us.
>
> If you don't know how to use DBMS_XPLAN, then please google it or just
> look at posts like
> "http://www.oracle-base.com/articles/9i/dbms_xplan.php"; or
> "http://jonathanlewis.wordpress.com/category/oracle/dbms_xplan/";.
>
> As Hans and John have politely been asking, provide the information to
> help us help you.
>
>
> On 4/14/2013 12:39 PM, John Hurley wrote:
> > Ahh ... well perhaps you should just ask the DBA for a bigger TEMP space
> allocation than 50 gb?
> >
> > Seriously you need to understand the data that is involved in the query
> ... and the SQL ... it is pretty unusual to have something chew up that
> much temp space without missing join conditions.
> >
> > As far as "when to force a hint" in SQL ... well the current thinking is
> "hardly ever".
> >
> > Current thinking is that one ONLY forces a hint under very carefully
> considered circumstances.
> >
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
