AW: query taking a long time

From: "ahmed.fikri@xxxxxxxxxxx" <ahmed.fikri@xxxxxxxxxxx>
To: "gogala.mladen@xxxxxxxxx" <gogala.mladen@xxxxxxxxx>, "list, oracle" <oracle-l@xxxxxxxxxxxxx>, "mwf@xxxxxxxx" <mwf@xxxxxxxx>
Date: Fri, 6 Nov 2020 11:55:36 +0100 (CET)

Hi,

It was a production problem all batches got in stuck on the same query. No
chance to change anything in the code. Business pushing ....

Gathering dict and fixed objs stats (12.1.0.2) did not help. The query was
the whole time on the CPU running and running - no other wait
event!....(looks like infinite loop).

The query worked fine for last 2 months... after analyzing the execute plan
I found E-rows were much lower than the A-rows --> first Idea gathering
dict and fixed objs stats (Oracle 12.1.0.2, NOCONTAINER!) But it did not
help.

This time I trusted the AWR, I ran the SQLTune, then I created a sql
profile (according to sqltune's recommendation and I checked the EP again.
The EP looked different but I couldn't judge if it was better. I see only
that the new profile is used). I ran one batch, it worked, we started then
all remaining batches, (I could sleep that night :-), next day I see that
all batches was successfully complete)

Now we have time to see what happened and why. Thanks Mark and Mladen for
your suggestions.

Best regards
Ahmed

-----Original-Nachricht-----
Betreff: Re: query taking a long time
Datum: 2020-11-05T20:40:19+0100
Von: "Mladen Gogala" <gogala.mladen@xxxxxxxxx>
An: "ahmed.fikri@xxxxxxxxxxx" <ahmed.fikri@xxxxxxxxxxx>, "list, oracle"
<oracle-l@xxxxxxxxxxxxx>

Hi Ahmed!
First, try collecting dictionary stats. Second, try refactoring the SQL
into something like this:

with ALLC2 as ( SELECT /*+ materialize  */ CONSTRAINT NAME

FROM ALL_CONSTRAINTS WHERE TABLE NANE = 762 AND OWNER = :61 AND

CONSTRAINT TYPE IN ("P","U") )

SELECT "ALTER TABLE * || TABLE_NAME || ' * || :B3 || * CONSTRAINT *

|| ALLC1.CONSTRAINT_NAME AS ALTER_SQL, ALLC1.TABLE_NAME, ALLC1.STATUS

FROM ALL CONSTRAINTS ALLC1,  ALLC2 WHERE OWNER - :B1 AND

CONSTRAINT_TYPE-"R* AND’ ALLC1.R_CONSTRAINT_NANE =

ALLC2.CONSTRAINT_NAME

You are not querying your application tables, you are querying  dictionary
tables. That means that you cannot modify or index the underlying tables.
Also, it would help to know the database version, do you have dictionary
stats and system stats. Execution plan would also be helpful as well as the
SQL_TRACE output analyzed by tkprof or orasrp. Last observation is that 762
is a strange name for a table.  I'd try with 747 or 737-MAX.
Regards

On Wed, 2020-11-04 at 18:50 +0100, ahmed.fikri@xxxxxxxxxxx wrote:
  Hi all,

  following sql takes a long time to be executed. I have seen that the
  query was the whole time on CPU.
  I checked one execute plan and I found a lot of NLs and the E-Rows is
  much less than the A-Rows.
  I gathered the data dictionary and fixed objects statistics but it
  doesn't help. We are using 12.1.0.2

  any Idea?

  Best regards
  Ahmed



--

Mladen Gogala
Database Consultant
Tel: (347) 321-1217

References:
- query taking a long time
  - From: ahmed.fikri@xxxxxxxxxxx
- Re: query taking a long time
  - From: Mladen Gogala

AW: query taking a long time

Other related posts: