Re: query taking a long time
Date: Thu, 05 Nov 2020 14:39:21 -0500
Message-ID: <c651d363c8c7492b96df67e7e7eec2d806180913.camel_at_gmail.com>
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-1217Received on Thu Nov 05 2020 - 20:39:21 CET
-- http://www.freelists.org/webpage/oracle-l
- image/png attachment: MljcuqFf.png