Re: Very slow Oracle dictionary query after upgrade to 19.19 from 12.1.0.2
From: Jonathan Lewis <"Jonathan>
Date: Fri, 22 Nov 2024 18:14:28 +0000
Message-ID: <CAGtsp8nKtoP7fAKZP36KJDqpkKAM=CC00PEHSpAL_nui==6h3Q_at_mail.gmail.com>
7 - access("O"."OBJ#"="A"."OBJ#")
9 - filter("O"."TYPE#"=13 AND "O"."SUBNAME" IS NULL OR "O"."TYPE#"=7 OR
"O"."TYPE#"=8 OR
"O"."TYPE#"=9 OR "O"."TYPE#"=11 OR "O"."TYPE#"=14) 10 - filter(((-"KZSPRPRV")=(-144) OR (-"KZSPRPRV")=(-141)) AND ("CON_ID"=0 OR "CON_ID"=3) AND
20 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
"O2"."OWNER#"="U2"."USER#")
Note
Date: Fri, 22 Nov 2024 18:14:28 +0000
Message-ID: <CAGtsp8nKtoP7fAKZP36KJDqpkKAM=CC00PEHSpAL_nui==6h3Q_at_mail.gmail.com>
Mladen,
Thanks for posting; that's interesting.
I got the same plan as you for 'ALL', but this is the plan I got for
"current_directory"
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
123 | 365 (1)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 |
123 | | |
|* 2 | FILTER | | |
| | |
|* 3 | HASH JOIN | | 370 |
45510 | 365 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | USER$ | 134 |
2412 | 4 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 370 |
38850 | 361 (1)| 00:00:01 |
| 6 | INDEX FULL SCAN | I_USER2 | 134 |
3350 | 1 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 370 |
29600 | 360 (1)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN | I_ARGUMENT1 | 4660 |
100K| 12 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | OBJ$ | 5851 |
331K| 348 (1)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KZSPR | 2 |
18 | 0 (0)| 00:00:01 |
| 11 | NESTED LOOPS SEMI | | 1 |
18 | 2 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KZSRO | 2 |
12 | 0 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 |
12 | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$ | 1 |
6 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 |
| 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$ | 1 |
6 | 2 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 |
| 1 (0)| 00:00:01 |
| 18 | NESTED LOOPS SEMI | | 1 |
29 | 2 (0)| 00:00:01 |
|* 19 | INDEX SKIP SCAN | I_USER2 | 1 |
20 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | I_OBJ4 | 1 |
9 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter(("U"."NAME"=SYS_CONTEXT('USERENV','CURRENT_USER') OR EXISTS
(SELECT 0 FROM
"SYS"."X$KZSPR" "X$KZSPR" WHERE ((-"KZSPRPRV")=(-144) OR
(-"KZSPRPRV")=(-141)) AND ("CON_ID"=0 OR
"CON_ID"=3) AND "INST_ID"=USERENV('INSTANCE')) OR EXISTS
(SELECT 0 FROM "SYS"."OBJAUTH$"
"OBJAUTH$","SYS"."X$KZSRO" "X$KZSRO" WHERE ("CON_ID"=0 OR
"CON_ID"=3) AND "OBJ#"=:B1 AND
"GRANTEE#"="KZSROROL" AND "PRIVILEGE#"=12)) AND
(BITAND("U"."SPARE1",16)=0 OR
BITAND("O"."FLAGS",1048576)=1048576 OR "O"."TYPE#"<>88 AND
NOT EXISTS (SELECT 0 FROM
"SYS"."USER_EDITIONING$" "UE" WHERE "UE"."USER#"=:B2 AND
"TYPE#"=:B3) OR EXISTS (SELECT 0 FROM
"SYS"."USER_EDITIONING$" "UE" WHERE "UE"."USER#"=:B4 AND
"UE"."TYPE#"=:B5) AND
(SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND
"U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
EXISTS (SELECT 0 FROM
"SYS"."USER$" "U2","SYS"."OBJ$" "O2" WHERE
"O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND
"O2"."DATAOBJ#"=:B6 AND "U2"."TYPE#"=2 AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_e
dition_id'))))))
3 - access("O"."SPARE3"="U"."USER#")
5 - access("O"."OWNER#"="U"."USER#")
7 - access("O"."OBJ#"="A"."OBJ#")
9 - filter("O"."TYPE#"=13 AND "O"."SUBNAME" IS NULL OR "O"."TYPE#"=7 OR
"O"."TYPE#"=8 OR
"O"."TYPE#"=9 OR "O"."TYPE#"=11 OR "O"."TYPE#"=14) 10 - filter(((-"KZSPRPRV")=(-144) OR (-"KZSPRPRV")=(-141)) AND ("CON_ID"=0 OR "CON_ID"=3) AND
"INST_ID"=USERENV('INSTANCE'))
12 - filter("CON_ID"=0 OR "CON_ID"=3)
13 - access("OBJ#"=:B1 AND "GRANTEE#"="KZSROROL" AND "PRIVILEGE#"=12)
filter("GRANTEE#"="KZSROROL" AND "PRIVILEGE#"=12)
14 - filter("TYPE#"=:B1)
15 - access("UE"."USER#"=:B1)
16 - filter("UE"."TYPE#"=:B1)
17 - access("UE"."USER#"=:B1)
19 - access("U2"."TYPE#"=2 AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'
)))
filter("U2"."TYPE#"=2 AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'
)))
20 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
"O2"."OWNER#"="U2"."USER#")
Note
- this is an adaptive plan
67 rows selected.
Three differences, though: first I was running 19.11, second I was running as SYS (which was lazy and I shouldn't have), finally I added different comments to the two "explain plan for ..." just in case Oracle had learned to do anything unusual about explain plan for a previously explain query.
Regards
Jonathan Lewis
On Fri, 22 Nov 2024 at 17:54, Mladen Gogala <dmarc-noreply_at_freelists.org> wrote:
> 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 - 19:14:28 CET