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

Ok, the noparallel hint helped, but the hidden parameters did not. That was very instructive for me.

Thanks and Regards

Am Di., 29. Jan. 2019 um 17:29 Uhr schrieb Andy Sayer <andysayer_at_gmail.com>:

> 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';
>
> This may help to explain why no_merge happens to work; and it's possibly
> only a matter of luck that 12.1.0.2 was okay for this person.
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: Jonathan Lewis
> Sent: 29 January 2019 14:50:39
> To: Andy Sayer
> Subject: Fw: a strange behavior in Oracle 12.2.0.1 by nested PLSQL cursor
>
>
> Andy,
>
> It looks like my email to Oracle-L is disappearing again.
> Here's one I sent a little while ago - which reproduces two earlier ones
> explaining the problems.
> Can you forward this please.
>
> Regards
> Jonathan Lewis
>
>
> ________________________________________
> From: Jonathan Lewis
> Sent: 29 January 2019 14:24
> To: ORACLE-L
> Subject: Re: a strange behavior in Oracle 12.2.0.1 by nested PLSQL cursor
>
> It looks like my previous 2 posts haven't arrived.
> In order:
>
> ------------------------------------------
> -- "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