AW: query taking a long time

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_at_gmail.com> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>, "list, oracle"
<oracle-l_at_freelists.org>
     

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_at_t-online.de 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






--
http://www.freelists.org/webpage/oracle-l





image/png attachment: MljcuqFf.png