Re: a strange behavior in Oracle 12.2.0.1 by nested PLSQL cursor

Jonathan Lewis is having trouble sending things to the list again, he’s asked me to forward this on:

Andy,

Somethin you might pass on.

I've just done a quick search on MoS slow queries against all_constraints, and found
Query to Dictionary ALL_CONSTRAINTS Slow after Upgrade to 12.1.0.2 (Doc ID 2266016.1)

Workaround is to set one of three hidden parameters:

ALTER SESSION SET "_optimizer_push_pred_cost_based" = FALSE;
ALTER SESSION SET "_optimizer_squ_bottomup" = FALSE;
ALTER SESSION SET "_optimizer_cost_based_transformation" = 'OFF';

• "deep dive"

  ---

Okay,
I ran your test and got no output on 12.2.01 Traced it (10046 level 4)
Found that the query against all_constraints went parallel and returned no data, so added a /*+ noparallel */ hint to the query, then (belts and braces) "alter session disable parallel query;" Ran the test again - got the expected result. It looks like the parallel execution loses the value of sys_context. I would check whether 12.1.0.2 and 11.2.0.4 ran the query parallel or whether they ran it serially, and if they're running t serially check what happens if you force it parallel.

• After the no_merge comment:

  ---

That (ed. the no_merge) may be luck rather than anything else. I just tried the same thing and still saw the problem (and parallel execution). In your case it's possible that the presence of the no_merge hint resulted in Oracle materializing the subquery and maybe that made it run serially - i.e. it was about object_statistics rather than functionality.

P.S. Looking at the execution plans, my 12.2 (corrected from 12.1) is translating all_constraints to a query involving int$int$DBA_CONSTRAINTS, while the 12.1 query is a "more traditional" massive join of lots of tables - so the base problem seems to start with the appearance of a CDB-mechanism of all_constraints. (I'm running 12.2. from a PDB, while the 12.1 is non-PDB database)

On Tue, 29 Jan 2019 at 16:19, Ahmed <gherrami_at_gmail.com> wrote:

> Hi Andy,
>
> I tested two variants of the block. Once with ref cursor and bind variable
> and once without explicit cursor. So dynamic code using bind variable and
> insert bulk collect.
> The result is always the same (works everywhere but not on my 12.2).
> With the no_merge hint works everywhere.
>
> The no_merge workaround is sure not a reasonable solution. I have
> completely rewritten the block. I'm not a fan of such nested loops anyway.
>
>
> See the two PL / SQL block bellow.
>
> Thanks and Regards
>
> --using ref cursor
> DECLARE
>   -- table that has Foreignkey pointing on it
>   v_tbl_name VARCHAR2(30) := 'PARENT';
>   v_tbl_child VARCHAR2(30);
>   v_cons_name VARCHAR2(30);
> CURSOR get_massdata_tableinfo
>     IS
>       SELECT v_tbl_name table_name FROM dual
>
>     ;
>   get_fks_as_refcursr sys_refcursor;
>   v_stmt VARCHAR2(4000) := 'WITH
>       user_constr AS
>       (
>         SELECT *
>          FROM all_constraints
>          WHERE owner = sys_context(''USERENV'', ''CURRENT_SCHEMA'')
>       )
>       SELECT r.constraint_name,
>              r.table_name
>        FROM user_constr r,
>             user_constr t
>        WHERE t.table_name = :par_target_table
>          AND t.constraint_type = ''P''
>          AND t.constraint_name = r.r_constraint_name';
>
> BEGIN
>   FOR crec IN get_massdata_tableinfo
>     LOOP
>       --
>       dbms_output.put_line('Table Name ' || crec.table_name);
>       -- disable FK´s pointing to table
>       OPEN get_fks_as_refcursr FOR v_stmt USING crec.table_name ;
>
>       LOOP
>         FETCH get_fks_as_refcursr INTO v_cons_name,v_tbl_child;
>         EXIT WHEN get_fks_as_refcursr%NOTFOUND;
>         dbms_output.put_line('ALTER TABLE ' || v_tbl_child||
>              ' DISABLE CONSTRAINT ' || v_cons_name);
>
>       END LOOP;
>       CLOSE get_fks_as_refcursr ;
>     END LOOP;
> END;
>
> --using dynamic sql + bind variable
> DECLARE
>   -- table that has Foreignkey pointing on it
>   v_tbl_name VARCHAR2(30) := 'PARENT';
>   v_tbl_child sys.odcivarchar2list := sys.odcivarchar2list();
>   v_cons_name sys.odcivarchar2list := sys.odcivarchar2list();
>
> CURSOR get_massdata_tableinfo
>     IS
>       SELECT v_tbl_name table_name FROM dual
>
>     ;
>   v_stmt VARCHAR2(4000) := 'WITH
>       user_constr AS
>       (
>         SELECT *
>          FROM all_constraints
>          WHERE owner = sys_context(''USERENV'', ''CURRENT_SCHEMA'')
>       )
>       SELECT r.constraint_name,
>              r.table_name
>        FROM user_constr r,
>             user_constr t
>        WHERE t.table_name = :par_target_table
>          AND t.constraint_type = ''P''
>          AND t.constraint_name = r.r_constraint_name';
>
> BEGIN
>   FOR crec IN get_massdata_tableinfo
>     LOOP
>       --
>       dbms_output.put_line('Table Name ' || crec.table_name);
>       -- disable FK´s pointing to table
>       EXECUTE IMMEDIATE v_stmt BULK COLLECT INTO v_cons_name,v_tbl_child
> USING crec.table_name;
>       FOR i IN 1..v_cons_name.count LOOP
>         dbms_output.put_line('ALTER TABLE ' || v_tbl_child(i)||
>              ' DISABLE CONSTRAINT ' || v_cons_name(i));
>
>       END LOOP;
>     END LOOP;
> END;
>
> Am Di., 29. Jan. 2019 um 15:12 Uhr schrieb Ahmed <gherrami_at_gmail.com>:
>
>> It's already a cursor. As you can also see in the example.
>> We believe that this is an Oracle bug. Also in 12.1 we had to deal with
>> similar things.
>>
>> Regards
>> Ahmed
>>
>> Am Di., 29. Jan. 2019 um 15:00 Uhr schrieb Andy Sayer <
>> andysayer_at_gmail.com>:
>>
>>> It works for me in the pl/sql on 12.2.0.1 with JAN2019 patch set.
>>>
>>> Your notes on the no_merge helping suggest that this is a bug caused by
>>> some rewrite.
>>>
>>> Difference in behaviour between pl/sql and sql might be due to the
>>> implicit bind variable being used in the pl/sql, try using binds in your
>>> sql to see if the problem occurs.
>>>
>>> Hope that helps,
>>> Andy
>>>
>>>
>>> On Tue, 29 Jan 2019 at 13:29, Ahmed <gherrami_at_gmail.com> wrote:
>>>
>>>> I first built the workaround with no_merge, so I can do at least a
>>>> regression test.
>>>> The regression took about an hour. (should take about 25 minutes .In
>>>> regression, we have only a few data. But at least no crashes or any
>>>> Ora-00600 errors.).
>>>> Looks like the upgrading will make us headache.
>>>>
>>>> If the regression is over, I'll start a performance test  see which SQL
>>>> become slower.
>>>>
>>>> Your suggestion sounds interesting concerning the performance.
>>>>
>>>> Thanks
>>>>
>>>> Am Di., 29. Jan. 2019 um 14:04 Uhr schrieb kathy duret <
>>>> katpopins21_at_yahoo.com>:
>>>>
>>>>> Didn't read the thread and someone may have suggested this already but
>>>>> we have turned off the adaptive tuning feature in 12.1 .
>>>>>
>>>>> You might try turning it off in a lower environment and see how things
>>>>> perform afterwards.
>>>>>
>>>>> I think Oracle is "retooling" this feature in the future.  We are
>>>>> skipping 12.2 and going to 12.3 due to some bugs.
>>>>>
>>>>> Good Luck
>>>>>
>>>>>
>>>>> Kathy Duret
>>>>>
>>>>>
>>>>>
>>>>> On Tuesday, January 29, 2019, 4:29:04 AM CST, Ahmed <
>>>>> gherrami_at_gmail.com> wrote:
>>>>>
>>>>>
>>>>> A colleague came up with the idea to use a no_merge hint. Then
>>>>> everything works again. Very strange.
>>>>>
>>>>>     CURSOR get_fks(par_target_table user_tables.table_name%TYPE)
>>>>>     IS
>>>>>       WITH
>>>>>       user_constr AS
>>>>>       (
>>>>>         SELECT /*+ no_merge */  *
>>>>>          FROM all_constraints
>>>>>          WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
>>>>>       )
>>>>>       SELECT r.constraint_name,
>>>>>              r.table_name
>>>>>        FROM user_constr r,
>>>>>             user_constr t
>>>>>        WHERE t.table_name = par_target_table
>>>>>          AND t.constraint_type = 'P'
>>>>>          AND t.constraint_name = r.r_constraint_name
>>>>>     ;
>>>>>
>>>>> Regards
>>>>> Ahmed Fikri
>>>>>
>>>>> Am Di., 29. Jan. 2019 um 10:33 Uhr schrieb Ahmed <gherrami_at_gmail.com>:
>>>>>
>>>>> Yes the schema running the code is the same as the schema owning the
>>>>> tables
>>>>>
>>>>> That's what I did:
>>>>>
>>>>> CREATE USER tester IDENTIFIED BY tester QUOTA 10M ON USERS;
>>>>> GRANT CREATE SESSION TO tester;
>>>>> GRANT CREATE TABLE TO tester;
>>>>> -- after loggon as tester
>>>>> CREATE TABLE PARENT(ID NUMBER PRIMARY KEY);
>>>>> CREATE TABLE PARENT(ID NUMBER PRIMARY KEY);
>>>>> CREATE TABLE CHILD(ID NUMBER, parent_id NUMBER ,CONSTRAINT FOREIGN KEY
>>>>> REFERENCES PARENT(ID));
>>>>>
>>>>> DECLARE
>>>>>   -- table that has Foreignkey pointing on it
>>>>>   v_tbl_name VARCHAR2(30) := 'PARENT';
>>>>> CURSOR get_massdata_tableinfo
>>>>>     IS
>>>>>       SELECT v_tbl_name table_name FROM dual
>>>>>
>>>>>     ;
>>>>>     CURSOR get_fks(par_target_table user_tables.table_name%TYPE)
>>>>>     IS
>>>>>       WITH
>>>>>       user_constr AS
>>>>>       (
>>>>>         SELECT *
>>>>>          FROM all_constraints
>>>>>          WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
>>>>>       )
>>>>>       SELECT r.constraint_name,
>>>>>              r.table_name
>>>>>        FROM user_constr r,
>>>>>             user_constr t
>>>>>        WHERE t.table_name = par_target_table
>>>>>          AND t.constraint_type = 'P'
>>>>>          AND t.constraint_name = r.r_constraint_name
>>>>>     ;
>>>>> BEGIN
>>>>>   FOR crec IN get_massdata_tableinfo
>>>>>     LOOP
>>>>>       --
>>>>>       dbms_output.put_line('Table Name ' || crec.table_name);
>>>>>       -- disable FK´s pointing to table
>>>>>       FOR rec IN get_fks(crec.table_name) --no rows in 12.2.0.1 (but
>>>>> it works in 11.2.0.4 and 12.1.0.2)
>>>>>       LOOP
>>>>>         dbms_output.put_line('ALTER TABLE ' || rec.table_name ||
>>>>>              ' DISABLE CONSTRAINT ' || rec.constraint_name);
>>>>>
>>>>>       END LOOP;
>>>>>     END LOOP;
>>>>>
>>>>> END;
>>>>>
>>>>>  I get AS Output:
>>>>> Table Name PARENT
>>>>>
>>>>> But on 11.2.0.4 or 12.1.0.2-- the same steps I get as Output:
>>>>> Table Name PARENT
>>>>> ALTER TABLE CHILD DISABLE CONSTRAINT SYS_C00444321
>>>>>
>>>>> This confuses me
>>>>>
>>>>> The context of these error is an attempt to upgrade our software from
>>>>> 11.2.0.4 to 12.2.0.1.
>>>>> Everything works fine on 11.2.0.4. In the example block above I try to
>>>>> summarize only the error here in this list.
>>>>>
>>>>> we have patched the following patch.
>>>>>
>>>>> ARU platform id: 226
>>>>>
>>>>> ARU platform description:: Linux x86-64
>>>>>
>>>>>
>>>>>
>>>>> Installed Top-level Products (1):
>>>>>
>>>>>
>>>>>
>>>>> Oracle Database 12c
>>>>> 12.2.0.1.0
>>>>>
>>>>> There are 1 products installed in this Oracle Home.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Interim patches (1) :
>>>>>
>>>>>
>>>>>
>>>>> Patch  28662603     : applied on Sat Nov 24 14:07:07 CET 2018
>>>>>
>>>>> Unique Patch ID:  22485591
>>>>>
>>>>> Patch description:  "Database Oct 2018 Release Update :
>>>>> 12.2.0.1.181016 (28662603)"
>>>>>    Created on 5 Oct 2018, 03:26:32 hrs PST8PDT
>>>>>
>>>>> Regards
>>>>> Ahmed Fikri
>>>>>
>>>>> Am Di., 29. Jan. 2019 um 10:05 Uhr schrieb Jonathan Lewis <
>>>>> jonathan_at_jlcomp.demon.co.uk>:
>>>>>
>>>>>
>>>>> It works for me on an unpatched 12.2.0.1
>>>>>
>>>>> In my case I log in, create a parent and child table, then run your
>>>>> code.  Is the schema running your code the same as the schema owning the
>>>>> tables ?
>>>>> Could there be a difference in privileges rather than a bug.
>>>>>
>>>>> Regards
>>>>> Jonathan Lewis
>>>>>
>>>>>
>>>>>
>>>>> ________________________________________
>>>>> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>
>>>>> on behalf of Ahmed <gherrami_at_gmail.com>
>>>>> Sent: 28 January 2019 14:57
>>>>> To: ORACLE-L
>>>>> Subject: a strange behavior in Oracle 12.2.0.1 by nested PLSQL cursor
>>>>>
>>>>> Hi,
>>>>>
>>>>> The following block should provide the commands to disable the foreign
>>>>> key pointing on a table. It works wonderfully in 11.2.0.4 and 12.1.0.2 but
>>>>> not on 12.2.0.1. Does not trigger an error but also no results. You can
>>>>> test it, just replace 'MYTABLE' with a table that has foreign key pointing
>>>>> on it.
>>>>>
>>>>> DECLARE
>>>>>   -- table that has Foreignkey pointing on it
>>>>>   v_tbl_name VARCHAR2(30) := 'MYTABLE';
>>>>> CURSOR get_massdata_tableinfo
>>>>>     IS
>>>>>       SELECT v_tbl_name table_name FROM dual
>>>>>
>>>>>     ;
>>>>>     CURSOR get_fks(par_target_table user_tables.table_name%TYPE)
>>>>>     IS
>>>>>       WITH
>>>>>       user_constr AS
>>>>>       (
>>>>>         SELECT *
>>>>>          FROM all_constraints
>>>>>          WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
>>>>>       )
>>>>>       SELECT r.constraint_name,
>>>>>              r.table_name
>>>>>        FROM user_constr r,
>>>>>             user_constr t
>>>>>        WHERE t.table_name = par_target_table
>>>>>          AND t.constraint_type = 'P'
>>>>>          AND t.constraint_name = r.r_constraint_name
>>>>>     ;
>>>>> BEGIN
>>>>>   FOR crec IN get_massdata_tableinfo
>>>>>     LOOP
>>>>>       --
>>>>>       dbms_output.put_line('Table Name ' || crec.table_name);
>>>>>       -- disable FK´s pointing to table
>>>>>       FOR rec IN get_fks(crec.table_name) --no rows in 12.2.0.1 (but
>>>>> it works in 11.2.0.4 and 12.1.0.2)
>>>>>       LOOP
>>>>>         dbms_output.put_line('ALTER TABLE ' || rec.table_name ||
>>>>>              ' DISABLE CONSTRAINT ' || rec.constraint_name);
>>>>>
>>>>>       END LOOP;
>>>>>     END LOOP;
>>>>>
>>>>> END;
>>>>>
>>>>> If I call the SQL directly, then I get records:
>>>>>
>>>>> WITH
>>>>>       user_constr AS
>>>>>       (
>>>>>         SELECT *
>>>>>          FROM all_constraints
>>>>>          WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA')
>>>>>       )
>>>>>       SELECT r.constraint_name,
>>>>>              r.table_name
>>>>>        FROM user_constr r,
>>>>>             user_constr t
>>>>>        WHERE t.table_name = 'MY_TABLE'
>>>>>          AND t.constraint_type = 'P'
>>>>>          AND t.constraint_name = r.r_constraint_name
>>>>>
>>>>> Is this a bug or have I just missed something?
>>>>>
>>>>> Regards
>>>>> Ahmed Fikri
>>>>>
>>>>>
>>>>> --
>>>>> http://www.freelists.org/webpage/oracle-l
>>>>>
>>>>>
>>>>>

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