Re: query taking a long time

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