Forum Stats

  • 3,770,902 Users
  • 2,253,180 Discussions
  • 7,875,652 Comments

Discussions

Slow performance querying from all_arguments

Alexander Pavlik
Alexander Pavlik Member Posts: 42 Blue Ribbon

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

Best Answer

Answers