Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Slow performance querying from all_arguments

Aliaksandr PaulikOct 22 2020

Hello
I've noticed slow performance querying from all_arguments in a 19c PDB. Here's the example of the problematic query which takes more than 20 seconds:

Select 
object_name, position, decode(data_type, 'REF CURSOR', 'SYS_REFCURSOR', data_type) data_type, overload, argument_name,
data_level, data_length, data_precision, data_scale, default_value,
in_out, object_id, sequence
from  all_arguments
where object_id = (select object_id
from sys.user_objects 
where object_name =:PKG
and object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION'))
order by Object_Name, Overload, Sequence

The action plan shows steps 2-6 taking most of the time.

SQL_ID  0yyuf49fbnkta, child number 0
-------------------------------------
Select /*+ GATHER_PLAN_STATISTICS */ /*Alex test*/        object_name, 
position, decode(data_type, 'REF CURSOR', 'SYS_REFCURSOR', data_type) 
data_type, overload, argument_name,        data_level, data_length, 
data_precision, data_scale, default_value,        in_out, object_id, 
sequence from   all_arguments where  object_id = (select object_id      
    from sys.user_objects           where object_name =:PKG          
and object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION')) order by 
Object_Name, Overload, Sequence
 
Plan hash value: 2412696886
 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                   |      1 |        |       |    20 (100)|     49 |00:00:20.60 |     272K|    223K|       |       |          |
|   1 |  SORT ORDER BY                                 |                   |      1 |     21 |  8736 |    20  (25)|     49 |00:00:20.60 |     272K|    223K|  6144 |  6144 | 6144  (0)|
|*  2 |   FILTER                                       |                   |      1 |        |       |            |     49 |00:00:06.06 |     272K|    223K|       |       |          |
|   3 |    PX COORDINATOR                              |                   |      1 |        |       |            |     49 |00:00:06.06 |     272K|    223K| 73728 | 73728 |          |
|   4 |     PX SEND QC (RANDOM)                        | :TQ10000          |      1 |    200 | 83200 |     4 (100)|     49 |00:00:05.97 |     272K|    223K|       |       |          |
|   5 |      PX PARTITION LIST ALL                     |                   |      1 |    200 | 83200 |     4 (100)|     49 |00:00:05.97 |     272K|    223K|       |       |          |
|*  6 |       EXTENDED DATA LINK FULL                  | INT$DBA_ARGUMENTS |      2 |    200 | 83200 |     4 (100)|     49 |00:00:05.97 |     272K|    223K|       |       |          |
|*  7 |        VIEW                                    | USER_OBJECTS      |      1 |      2 |   184 |    15   (0)|      1 |00:00:00.01 |      20 |      0 |       |       |          |
|   8 |         UNION-ALL                              |                   |      1 |        |       |            |      2 |00:00:00.01 |      20 |      0 |       |       |          |
|   9 |          TABLE ACCESS BY INDEX ROWID           | SUM$              |      0 |      1 |    10 |     2   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 10 |           INDEX UNIQUE SCAN                    | I_SUM$_1          |      0 |      1 |       |     1   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 11 |          FILTER                                |                   |      1 |        |       |            |      2 |00:00:00.01 |      20 |      0 |       |       |          |
|* 12 |           FILTER                               |                   |      1 |        |       |            |      2 |00:00:00.01 |       8 |      0 |       |       |          |
|  13 |            NESTED LOOPS                        |                   |      1 |      1 |    61 |     5   (0)|      2 |00:00:00.01 |       8 |      0 |       |       |          |
|* 14 |             TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$              |      1 |      1 |    46 |     4   (0)|      2 |00:00:00.01 |       4 |      0 |       |       |          |
|* 15 |              INDEX RANGE SCAN                  | I_OBJ5            |      1 |      1 |       |     3   (0)|      2 |00:00:00.01 |       3 |      0 |       |       |          |
|* 16 |             INDEX RANGE SCAN                   | I_USER2           |      2 |      1 |    15 |     1   (0)|      2 |00:00:00.01 |       4 |      0 |       |       |          |
|* 17 |           TABLE ACCESS BY INDEX ROWID BATCHED  | USER_EDITIONING$  |      2 |      1 |     7 |     2   (0)|      2 |00:00:00.01 |       6 |      0 |       |       |          |
|* 18 |            INDEX RANGE SCAN                    | I_USER_EDITIONING |      2 |     12 |       |     1   (0)|     12 |00:00:00.01 |       4 |      0 |       |       |          |
|* 19 |           TABLE ACCESS BY INDEX ROWID BATCHED  | USER_EDITIONING$  |      2 |      1 |     7 |     2   (0)|      2 |00:00:00.01 |       6 |      0 |       |       |          |
|* 20 |            INDEX RANGE SCAN                    | I_USER_EDITIONING |      2 |     12 |       |     1   (0)|     12 |00:00:00.01 |       4 |      0 |       |       |          |
|  21 |           NESTED LOOPS SEMI                    |                   |      0 |      1 |    21 |     4   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 22 |            INDEX RANGE SCAN                    | I_OBJ4            |      0 |      1 |    10 |     3   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 23 |            INDEX RANGE SCAN                    | I_USER2           |      0 |    171 |  1881 |     1   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  24 |           NESTED LOOPS                         |                   |      0 |      1 |    36 |     6   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  25 |            NESTED LOOPS                        |                   |      0 |      1 |    26 |     4   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  26 |             TABLE ACCESS BY INDEX ROWID        | IND$              |      0 |      1 |    12 |     2   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 27 |              INDEX UNIQUE SCAN                 | I_IND1            |      0 |      1 |       |     1   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 28 |             TABLE ACCESS CLUSTER               | TAB$              |      0 |      1 |    14 |     2   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 29 |            INDEX RANGE SCAN                    | I_OBJ1            |      0 |      1 |    10 |     2   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 30 |           TABLE ACCESS CLUSTER                 | TAB$              |      0 |      1 |    14 |     3   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 31 |            INDEX UNIQUE SCAN                   | I_OBJ#            |      0 |      1 |       |     1   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 32 |           TABLE ACCESS BY INDEX ROWID          | SEQ$              |      0 |      1 |     9 |     2   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 33 |            INDEX UNIQUE SCAN                   | I_SEQ1            |      0 |      1 |       |     1   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 34 |           TABLE ACCESS BY INDEX ROWID          | IND$              |      0 |      1 |     9 |     2   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 35 |            INDEX UNIQUE SCAN                   | I_IND1            |      0 |      1 |       |     1   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 36 |          FILTER                                |                   |      1 |        |       |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 37 |           INDEX RANGE SCAN                     | I_LINK1           |      0 |      1 |    19 |     1   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 38 |    FIXED TABLE FULL                            | X$KZSPR           |      0 |      2 |    18 |     0   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 39 |    HASH JOIN SEMI                              |                   |      0 |      1 |    18 |     3   (0)|      0 |00:00:00.01 |       0 |      0 |  1355K|  1355K|          |
|* 40 |     INDEX RANGE SCAN                           | I_OBJAUTH1        |      0 |      1 |    13 |     3   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 41 |     FIXED TABLE FULL                           | X$KZSRO           |      0 |      2 |    10 |     0   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('_fast_full_scan_enabled' 'false')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$12")
      OUTLINE_LEAF(@"SEL$13")
      OUTLINE_LEAF(@"SEL$20")
      OUTLINE_LEAF(@"SEL$21")
      OUTLINE_LEAF(@"SEL$22")
      OUTLINE_LEAF(@"SEL$23")
      OUTLINE_LEAF(@"SEL$15")
      OUTLINE_LEAF(@"SEL$16")
      OUTLINE_LEAF(@"SEL$17")
      OUTLINE_LEAF(@"SEL$18")
      OUTLINE_LEAF(@"SEL$19")
      OUTLINE_LEAF(@"SEL$B740551B")
      MERGE(@"SEL$14" >"SEL$11")
      OUTLINE_LEAF(@"SEL$24")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$10")
      OUTLINE_LEAF(@"SEL$0098CF9D")
      MERGE(@"SEL$F5B21678" >"SEL$3")
      OUTLINE_LEAF(@"SEL$232D1B18")
      UNNEST(@"SEL$CF5359D5")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2" >"SEL$1")
      OUTLINE(@"SEL$11")
      OUTLINE(@"SEL$14")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$F5B21678")
      MERGE(@"SEL$ABDE6DFF" >"SEL$4")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$CF5359D5")
      MERGE(@"SEL$9" >"SEL$8")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$ABDE6DFF")
      MERGE(@"SEL$6" >"SEL$5")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$9")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$6")
      FULL(@"SEL$F5BB74E1" "INT$DBA_ARGUMENTS"@"SEL$2")
      PUSH_SUBQ(@"SEL$10")
      ORDER_SUBQ(@"SEL$F5BB74E1" "SEL$0098CF9D" "SEL$232D1B18")
      PQ_FILTER(@"SEL$F5BB74E1" SERIAL)
      INDEX(@"SEL$232D1B18" "OBJAUTH$"@"SEL$7" ("OBJAUTH$"."OBJ#" "OBJAUTH$"."GRANTOR#" "OBJAUTH$"."GRANTEE#" "OBJAUTH$"."PRIVILEGE#" "OBJAUTH$"."COL#"))
      FULL(@"SEL$232D1B18" "X$KZSRO"@"SEL$9")
      LEADING(@"SEL$232D1B18" "OBJAUTH$"@"SEL$7" "X$KZSRO"@"SEL$9")
      USE_HASH(@"SEL$232D1B18" "X$KZSRO"@"SEL$9")
      PARTIAL_JOIN(@"SEL$232D1B18" "X$KZSRO"@"SEL$9")
      FULL(@"SEL$0098CF9D" "X$KZSPR"@"SEL$6")
      NO_ACCESS(@"SEL$10" "USER_OBJECTS"@"SEL$10")
      INDEX(@"SEL$24" "L"@"SEL$24" ("LINK$"."OWNER#" "LINK$"."NAME"))
      INDEX_RS_ASC(@"SEL$B740551B" "O"@"SEL$14" ("OBJ$"."SPARE3" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."TYPE#" "OBJ$"."OWNER#" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" 
              "OBJ$"."SUBNAME" "OBJ$"."OBJ#"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$B740551B" "O"@"SEL$14")
      INDEX(@"SEL$B740551B" "U"@"SEL$14" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
      LEADING(@"SEL$B740551B" "O"@"SEL$14" "U"@"SEL$14")
      USE_NL(@"SEL$B740551B" "U"@"SEL$14")
      ORDER_SUBQ(@"SEL$B740551B" "SEL$17" "SEL$18" "SEL$19" "SEL$21" "SEL$22" "SEL$23" "SEL$20")
      PQ_FILTER(@"SEL$B740551B" SERIAL)
      INDEX(@"SEL$19" "O2"@"SEL$19" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))
      INDEX(@"SEL$19" "U2"@"SEL$19" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
      LEADING(@"SEL$19" "O2"@"SEL$19" "U2"@"SEL$19")
      USE_NL(@"SEL$19" "U2"@"SEL$19")
      PARTIAL_JOIN(@"SEL$19" "U2"@"SEL$19")
      INDEX_RS_ASC(@"SEL$18" "UE"@"SEL$18" ("USER_EDITIONING$"."USER#"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$18" "UE"@"SEL$18")
      INDEX_RS_ASC(@"SEL$17" "UE"@"SEL$17" ("USER_EDITIONING$"."USER#"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$17" "UE"@"SEL$17")
      INDEX_RS_ASC(@"SEL$16" "EO"@"SEL$16" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$16" "EO"@"SEL$16")
      INDEX_RS_ASC(@"SEL$15" "UE"@"SEL$15" ("USER_EDITIONING$"."USER#"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$15" "UE"@"SEL$15")
      INDEX_RS_ASC(@"SEL$23" "S"@"SEL$23" ("SEQ$"."OBJ#"))
      INDEX(@"SEL$22" "T"@"SEL$22" "I_OBJ#")
      INDEX_RS_ASC(@"SEL$21" "I"@"SEL$21" ("IND$"."OBJ#"))
      CLUSTER(@"SEL$21" "T"@"SEL$21")
      INDEX(@"SEL$21" "IO"@"SEL$21" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
      LEADING(@"SEL$21" "I"@"SEL$21" "T"@"SEL$21" "IO"@"SEL$21")
      USE_NL(@"SEL$21" "T"@"SEL$21")
      USE_NL(@"SEL$21" "IO"@"SEL$21")
      INDEX_RS_ASC(@"SEL$20" "I"@"SEL$20" ("IND$"."OBJ#"))
      INDEX_RS_ASC(@"SEL$13" "S"@"SEL$13" ("SUM$"."OBJ#"))
      INDEX_RS_ASC(@"SEL$12" "S"@"SEL$12" ("SUM$"."OBJ#"))
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("OWNER"=SYS_CONTEXT('USERENV','CURRENT_USER') OR  IS NOT NULL OR  IS NOT NULL))
   6 - filter("OBJECT_ID"=)
   7 - filter(("OBJECT_TYPE"='FUNCTION' OR "OBJECT_TYPE"='PACKAGE' OR "OBJECT_TYPE"='PROCEDURE'))
  10 - access("S"."OBJ#"=:B1)
  11 - filter(((BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"<>88 AND  IS NULL) OR ( IS NOT NULL AND (("U"."TYPE#"=2 AND 
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR ("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR  IS NOT NULL))) AND 
              (("O"."TYPE#"<>1 AND "O"."TYPE#"<>2 AND "O"."TYPE#"<>6) OR ("O"."TYPE#"=1 AND  IS NULL) OR ("O"."TYPE#"=2 AND =1) OR ("O"."TYPE#"=6 AND =1)) AND (("O"."TYPE#"<>1 AND 
              "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1))))
  12 - filter(('_default_auditing_options_'<>:PKG AND '_NEXT_OBJECT'<>:PKG))
  14 - filter(BITAND("O"."FLAGS",128)=0)
  15 - access("O"."SPARE3"=USERENV('SCHEMAID') AND "O"."NAME"=:PKG AND "O"."LINKNAME" IS NULL)
       filter("O"."LINKNAME" IS NULL)
  16 - access("O"."OWNER#"="U"."USER#")
  17 - filter("TYPE#"=:B1)
  18 - access("UE"."USER#"=:B1)
  19 - filter("UE"."TYPE#"=:B1)
  20 - access("UE"."USER#"=:B1)
  22 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
  23 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  27 - access("I"."OBJ#"=:B1)
  28 - filter(("I"."BO#"="T"."OBJ#" AND BITAND("T"."PROPERTY",36893488147419103232)=36893488147419103232))
  29 - access("IO"."OBJ#"="I"."BO#" AND "IO"."TYPE#"=2)
       filter("IO"."TYPE#"=2)
  30 - filter(BITAND("T"."PROPERTY",36893488147419103232)=0)
  31 - access("T"."OBJ#"=:B1)
  32 - filter((BITAND("S"."FLAGS",1024)=0 OR "S"."FLAGS" IS NULL))
  33 - access("S"."OBJ#"=:B1)
  34 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=8 OR "I"."TYPE#"=9))
  35 - access("I"."OBJ#"=:B1)
  36 - filter(NULL IS NOT NULL)
  37 - access("L"."OWNER#"=USERENV('SCHEMAID') AND "L"."NAME"=:PKG)
  38 - filter((((-"KZSPRPRV")=(-144) OR (-"KZSPRPRV")=(-141)) AND INTERNAL_FUNCTION("CON_ID") AND "INST_ID"=USERENV('INSTANCE')))
  39 - access("GRANTEE#"="KZSROROL")
  40 - access("OBJ#"=OBJ_ID(:B1,NVL(:B2,:B3),:B4,:B5) AND "PRIVILEGE#"=12)
       filter("PRIVILEGE#"=12)
  41 - filter(("CON_ID"=0 OR "CON_ID"=3))
 
Note
-----
   - this is an adaptive plan

In a non-pdb 11g database the same query takes less than 1 second.
Has anyone experienced the same issue?
Could you please help me investigating and resolving that issue.
Thank you,
Alex

This post has been answered by Aliaksandr Paulik on Oct 8 2021

Jump to Answer

Brett Calhoun

Alex,
I was able to run the same SQL on an almost empty 19c PDB in less than 1 sec. How many records do you have in SYS.USER_OBJECTS and ALL_ARGUMENTS? Do you have up-to-date fixed object and dictionary statistics (https://docs.oracle.com/en/database/oracle/oracle-database/18/spucs/gathering-dictionary-statistics-after-upgrading.html#GUID-8AF2DB10-9746-4938-B69F-D1F3890BEAF5)?
Thanks,
Brett

1 person found this helpful

Aliaksandr Paulik

Hi Brett
Thank you for response
My database is used in an E-Business Suite installation so contains a plenty of objects. Here's the number of records in each table queried from a PDB:

select count(*) from SYS.USER_OBJECTS;

199369

select count(*) from ALL_ARGUMENTS;

7505540

Gathering dictionary statistics across all the databases didn't help unfortunately
Regards,
Alex

Brett Calhoun

Alex,
Yeah, you have a lot more objects and arguments than in my test case! Unfortunately, I do not have any other ideas. Have you tried running the SQLT utility? It is a comprehensive diagnostic tool and will provide recommendations for problematic SQL. See Doc ID 215187.1.
Thanks,
Brett

1 person found this helpful

Aliaksandr Paulik

Brett
Anyway, thank you for an attempt!
I will try running SQLT, though have little hope as even the following simple query takes at least 10 seconds to complete:

select count(*) from all_arguments;

I don't see many possibilities for tuning here. SQL Tuning advisor for instance didn't give any recommendations.
Regards,
Alex

Aliaksandr Paulik

Answer

In my case the following helped as per
Slow Dictionary Query on ALL_ARGUMENTS (Doc ID 2707499.1)
Apply Patch 31142749
Execute in pdb that I use: alter system set CONTAINER_DATA=CURRENT scope=spfile;
Restart the database

Marked as Answer by Aliaksandr Paulik · Oct 8 2021

1 - 5

Added on Oct 22 2020

#database, #database-key-features, #multitenant, #sql

5 comments

1,596 views

Database Software

Slow performance querying from all_arguments

Comments

Post Details