Re: Very slow Oracle dictionary query after upgrade to 19.19 from 12.1.0.2
Date: Fri, 22 Nov 2024 12:53:33 -0500
Message-ID: <7bb5e0b95f6ab91f39ab904bb464858b83fb7110.camel_at_gmail.com>
On Fri, 2024-11-22 at 17:14 +0000, Jonathan Lewis wrote:
>
> If you try a simple query such as 'select count(distinct package_name)
> from all_arguments' with container_data set to 'ALL' you'll find the plan
> is very different from the plan you get for container_data =
> 'CURRENT_DICTIONARY' - this difference between the 12c and the 19c tests
> would be sufficient to explain why the timing is so different (and, of
> course, why so many hints that reference specific query block names are
> unresolved or unused - all the extra objects mean that you don't
> necessarily get the query blocks referenced in the hint, and changes in
> joins means some of the hints can't apply any more).
>
> It is possible that the code changes to the views (especially if they
> bring in new tables or subqueries) are very likely to have the same
> effect.
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Fri, 22 Nov 2024 at 11:25, Satalabaha Oracle
> <satalabaha.oracle_at_gmail.com> wrote:
> >
> > Hi Jonathan,
> >
> > Thanks for the details.
> >
> >
> > I reviewed the hint report below [1], but I was unable to determine why
> > certain hints were not utilized.
> >
> >
> > [1]:
> >
> > https://gist.github.com/Satalabaha/4ae20275a218aaebc29df50484cf43f6
> >
> > Concerning your second point, v$sql_optimizer_env revealed an issue
> > [2]. Although we have applied patch 31142749 to our binaries and set
> > container_data to CURRENT_DICTIONARY before restarting the instance,
> > v$sql_optimizer_env still selects 'ALL' for container_data. Not sure if
> > this is playing some role here.
> >
> >
> > SQL> sho parameter container_data;
> >
> > NAME TYPE VALUE
> > ------------------------------------ --------------------------- ------
> > ------------------------
> > container_data string
> > CURRENT_DICTIONARY
> >
> > [2]:
> >
> > https://gist.github.com/Satalabaha/649e9447d12440c2dcc6df22ea6d34cd
> >
> >
> > Regarding #3,all the 3 views all_types. all_arguements and
> > all_procedures have undergone code changes.
> >
> > Regarding #4, optimizer_secure_view_merging is set to true for both 12c
> > and 19c.
> >
> > On Thu, Nov 21, 2024 at 9:20 PM Jonathan Lewis
> > <dmarc-noreply_at_freelists.org> wrote:
> > >
> > > One more thought. Do you have the same setting for
> > > optimizer_secure_view_merging in both systems. The view
> > > _current_edition_obj is access through a nested loop and "view pushed
> > > predicate" in 12c, but you have a hash join without predicate pushing
> > > in 19c. A change in that predicate might explain it; but possibly
> > > there's some other reason why predicate pushing hasn't taken place.
> > >
> > >
> > > Regards
> > > Jonathan Lewis
> > >
> > >
> > >
> > > On Wed, 20 Nov 2024 at 12:50, Satalabaha Oracle
> > > <dmarc-noreply_at_freelists.org> wrote:
> > > > Hello Experts,
> > > >
> > > > I need your advice on troubleshooting a very slow Oracle dictionary
> > > > query after upgrading from 12.1.0.2 to 19.19. The query execution
> > > > plan and SQL monitoring details for both versions are provided in
> > > > the links below. It seems the query is being called by
> > > > `OracleDatabaseMetaData.java` in the application. This query uses a
> > > > manual SQL profile and runs in a few milliseconds in 12.1, but
> > > > takes over 2 minutes in 19c. I'm unsure why the 12.1 SQL profile
> > > > isn't being utilized in 19c. In the gists below, I've executed the
> > > > SQL using the outline for the 12c query. Any guidance on this issue
> > > > would be greatly appreciated. Thanks!
> > > >
> > > >
> > > > 19c:
> > > >
> > > > https://gist.github.com/Satalabaha/eae70f3753dd1bf0401d2d49e3910170
> > > >
> > > > 12c:
> > > >
> > > > https://gist.github.com/Satalabaha/7a9ecabe423603844e249d1cdf6f2ff7
> > > >
> > > > --
> > > > Thanks,
> > > > Satalabaha
> >
> >
> > --
> > Thanks,
> > Satalabaha
Hi Jonathan,
I did the test you advised:
SQL> select count(distinct package_name) from all_arguments;
COUNT(DISTINCTPACKAGE_NAME)
1404
PLAN_TABLE_OUTPUT
___________________________________________________________________________________________________
SQL_ID auraqp5m3s8rr, child number 0
-------------------------------------
select count(distinct package_name) from all_arguments
Plan hash value: 4238146178
------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | VM_NWVW_1 | 2131 | | | |
| 3 | HASH GROUP BY | | 2131 | 1161K| 1161K| 1484K (0)|
|* 4 | FILTER | | | | | |
| 5 | PX COORDINATOR | | | 73728 | 73728 | |
| 6 | PX SEND QC (RANDOM) | :TQ10000 | 20000 | | | |
| 7 | PX PARTITION LIST ALL | | 20000 | | | |
| 8 | EXTENDED DATA LINK FULL| INT$DBA_ARGUMENTS | 20000 | | | |
|* 9 | FIXED TABLE FULL | X$KZSPR | 1 | | | |
|* 10 | HASH JOIN SEMI | | 1 | 1538K| 1538K| |
|* 11 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | | | |
|* 12 | FIXED TABLE FULL | X$KZSRO | 5 | | | |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("OWNER"=SYS_CONTEXT('USERENV','CURRENT_USER') OR IS NOT NULL OR IS NOT
NULL))
9 - filter((((-"KZSPRPRV")=(-144) OR (-"KZSPRPRV")=(-141)) AND
INTERNAL_FUNCTION("CON_ID") AND "INST_ID"=USERENV('INSTANCE')))
10 - access("GRANTEE#"="KZSROROL")
11 - access("OBJ#"=OBJ_ID(:B1,NVL(:B2,:B3),:B4,:B5) AND "PRIVILEGE#"=12)
filter("PRIVILEGE#"=12)
12 - filter(("CON_ID"=0 OR "CON_ID"=3))
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Elapsed: 00:00:00.569
SQL> show parameter container_data
NAME TYPE VALUE
-------------- ------ -----
container_data string ALL
Now CURRENT_DICTIONARY:
SQL> alter session set container_data=current_dictionary;
Session altered.
Elapsed: 00:00:00.001
SQL> select count(distinct package_name) from all_arguments;
COUNT(DISTINCTPACKAGE_NAME)
14
PLAN_TABLE_OUTPUT ___________________________________________________________________________ ________________________
SQL_ID auraqp5m3s8rr, child number 0
select count(distinct package_name) from all_arguments
Plan hash value: 4238146178
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | | | | | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | VIEW | VM_NWVW_1 | 2131 | | | | | 3 | HASH GROUP BY | | 2131 | 1161K| 1161K| 1484K (0)| |* 4 | FILTER | | | | | | | 5 | PX COORDINATOR | | | 73728 | 73728 | | | 6 | PX SEND QC (RANDOM) | :TQ10000 | 20000 | | | | | 7 | PX PARTITION LIST ALL | | 20000 | | | | | 8 | EXTENDED DATA LINK FULL| INT$DBA_ARGUMENTS | 20000 | | | | |* 9 | FIXED TABLE FULL | X$KZSPR | 1 | | | | |* 10 | HASH JOIN SEMI | | 1 | 1538K| 1538K| | |* 11 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | | | | |* 12 | FIXED TABLE FULL | X$KZSRO | 5 | | | | --------------------------------------------------------------------------- ---------------------
Predicate Information (identified by operation id):
4 - filter(("OWNER"=SYS_CONTEXT('USERENV','CURRENT_USER') OR IS NOT NULL OR
IS NOT
NULL))
9 - filter((((-"KZSPRPRV")=(-144) OR (-"KZSPRPRV")=(-141)) AND
INTERNAL_FUNCTION("CON_ID") AND "INST_ID"=USERENV('INSTANCE')))
10 - access("GRANTEE#"="KZSROROL")
11 - access("OBJ#"=OBJ_ID(:B1,NVL(:B2,:B3),:B4,:B5) AND "PRIVILEGE#"=12)
filter("PRIVILEGE#"=12)
12 - filter(("CON_ID"=0 OR "CON_ID"=3))
Note
- dynamic statistics used: dynamic sampling (level=AUTO)
- Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level
Elapsed: 00:00:00.203
I must say that I expected the following filter to change:
12 - filter(("CON_ID"=0 OR "CON_ID"=3)).
The plans are extremely similar. I was testing it on an Oracle 21.16 for Linux x86_64, running within a Docker container.
-- Mladen Gogala Database SME https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 22 2024 - 18:53:33 CET