Skip to Main Content
  • Questions

  • dictionary views performances all_* faster than user_*

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Giorgio.

Asked: April 01, 2016 - 3:17 pm UTC

Last updated: April 05, 2016 - 2:03 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi guys

I'm investigating some performance issues with one of our programs. Basically we're looping through FKs to dynamically update a value to another in the parent table and all children.

While doing profiling, I discovered that the single slowest point of execution was a single query:

      select uc.table_name,
             ucc.column_name
      from user_constraints uc
      inner join user_cons_columns ucc
      on ucc.constraint_name = uc.constraint_name
      and ucc.table_name = uc.table_name
      where uc.constraint_type = 'R'
      and uc.r_constraint_name in ('PERSON_PK', 'PERSON_UN')


I tried changing the query using all_* views instead of user_

      select uc.table_name,
             ucc.column_name
      from all_constraints uc
      inner join all_cons_columns ucc
      on ucc.constraint_name = uc.constraint_name
      and ucc.table_name = uc.table_name
      where uc.constraint_type = 'R'
      and uc.r_constraint_name in ('PERSON_PK', 'PERSON_UN')
      and uc.owner = 'SOLAR';


and realized that the latter was much faster.
However, since we would rather not hard-code the owner in a query, I'd like to now if this may be a configuration issue of our database or if there's a valid reason for oracle to be faster using all_* views.

Let me give you some more data to work with.
This is our db version:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

An example:

select count(1)
from all_constraints 
where owner = 'SYS';


returns 4298 rows in 0.222 seconds.

select count(1)
from user_constraints;


returns 4298 rows in 1.212 seconds.

Here are the explain plans.
For all_constraints:

 Plan Hash Value  : 544941751 

------------------------------------------------------------------------------------
| Id   | Operation                   | Name       | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT            |            |    1 |   184 |    1 | 00:00:01 |
|    1 |   SORT AGGREGATE            |            |    1 |   184 |      |          |
|  * 2 |    FILTER                   |            |      |       |      |          |
|    3 |     PX COORDINATOR          |            |      |       |      |          |
|    4 |      PX SEND QC (RANDOM)    | :TQ10000   |    2 |   368 |    1 | 00:00:01 |
|    5 |       PX PARTITION LIST ALL |            |    2 |   368 |    1 | 00:00:01 |
|  * 6 |        FIXED TABLE FULL     | X$COMVW$   |    2 |   368 |    1 | 00:00:01 |
|    7 |     NESTED LOOPS SEMI       |            |    1 |    15 |    2 | 00:00:01 |
|  * 8 |      FIXED TABLE FULL       | X$KZSRO    |    1 |     6 |    0 | 00:00:01 |
|  * 9 |      INDEX RANGE SCAN       | I_OBJAUTH2 |   10 |    90 |    2 | 00:00:01 |
| * 10 |     FIXED TABLE FULL        | X$KZSPR    |    2 |    18 |    0 | 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("OWNER"=SYS_CONTEXT('USERENV','CURRENT_USER') OR EXISTS (SELECT 0 FROM "SYS"."OBJAUTH$" "OBJAUTH$",SYS."X$KZSRO" "X$KZSRO" WHERE ("CON_ID"=0 OR "CON_ID"=8) AND
  "OBJ#"=OBJ_ID(:B1,:B2,:B3,:B4) AND "GRANTEE#"="KZSROROL") OR EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-397) OR
  (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND ("CON_ID"=0 OR "CON_ID"=8) AND "INST_ID"=USERENV('INSTANCE')))
* 6 - filter("OWNER"='SYS' AND ("INT$INT$DBA_CONSTRAINTS"."OBJECT_TYPE#"=4 OR "INT$INT$DBA_CONSTRAINTS"."OBJECT_TYPE#"=2 AND (("INT$INT$DBA_CONSTRAINTS"."ORIGIN_CON_ID"=0 OR
  "INT$INT$DBA_CONSTRAINTS"."ORIGIN_CON_ID"=1) OR "INT$INT$DBA_CONSTRAINTS"."SHARING"=0)))
* 8 - filter("CON_ID"=0 OR "CON_ID"=8)
* 9 - access("GRANTEE#"="KZSROROL" AND "OBJ#"=OBJ_ID(:B1,:B2,:B3,:B4))
* 10 - filter(((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-397) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND ("CON_ID"=0 OR "CON_ID"=8) AND
  "INST_ID"=USERENV('INSTANCE'))


for user_constraints:
 Plan Hash Value  : 3473734361 

--------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                     | Name                 | Rows   | Bytes   | Cost | Time     |
--------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                              |                      |      1 |         | 1111 | 00:00:01 |
|    1 |   SORT AGGREGATE                              |                      |      1 |         |      |          |
|    2 |    PX COORDINATOR                             |                      |        |         |      |          |
|    3 |     PX SEND QC (RANDOM)                       | :TQ10000             |      1 |         |      |          |
|    4 |      SORT AGGREGATE                           |                      |      1 |         |      |          |
|    5 |       VIEW                                    | USER_CONSTRAINTS     |    191 |         | 1111 | 00:00:01 |
|    6 |        UNION-ALL                              |                      |        |         |      |          |
|    7 |         PX PARTITION LIST ALL                 |                      |      2 |     158 |    1 | 00:00:01 |
|  * 8 |          FIXED TABLE FULL                     | X$COMVW$             |      2 |     158 |    1 | 00:00:01 |
|    9 |         PX SELECTOR                           |                      |        |         |      |          |
| * 10 |          FILTER                               |                      |        |         |      |          |
| * 11 |           HASH JOIN RIGHT OUTER               |                      |    189 |   49707 | 1111 | 00:00:01 |
|   12 |            INDEX FULL SCAN                    | I_USER2              |    168 |     672 |    1 | 00:00:01 |
| * 13 |            HASH JOIN OUTER                    |                      |    189 |   48951 | 1110 | 00:00:01 |
| * 14 |             HASH JOIN OUTER                   |                      |    189 |   47439 | 1005 | 00:00:01 |
|   15 |              JOIN FILTER CREATE               | :BF0000              |    189 |   44982 |  555 | 00:00:01 |
| * 16 |               HASH JOIN RIGHT OUTER           |                      |    189 |   44982 |  555 | 00:00:01 |
|   17 |                INDEX FULL SCAN                | I_USER2              |    168 |     672 |    1 | 00:00:01 |
| * 18 |                HASH JOIN OUTER                |                      |    189 |   44226 |  554 | 00:00:01 |
| * 19 |                 HASH JOIN                     |                      |    189 |   42525 |  527 | 00:00:01 |
|   20 |                  INDEX FULL SCAN              | I_USER2              |    168 |    4032 |    1 | 00:00:01 |
|   21 |                  NESTED LOOPS                 |                      |    189 |   37989 |  526 | 00:00:01 |
|   22 |                   NESTED LOOPS                |                      |    205 |   37989 |  526 | 00:00:01 |
| * 23 |                    HASH JOIN                  |                      |    205 |   36900 |  116 | 00:00:01 |
| * 24 |                     HASH JOIN                 |                      |    207 |   32913 |   32 | 00:00:01 |
| * 25 |                      TABLE ACCESS FULL        | USER$                |      2 |     300 |    5 | 00:00:01 |
|   26 |                      TABLE ACCESS FULL        | CON$                 |  20679 |  186111 |   27 | 00:00:01 |
| * 27 |                     TABLE ACCESS FULL         | CDEF$                |  20530 |  431130 |   83 | 00:00:01 |
| * 28 |                    INDEX RANGE SCAN           | I_OBJ1               |      1 |         |    1 | 00:00:01 |
|   29 |                   TABLE ACCESS BY INDEX ROWID | OBJ$                 |      1 |      21 |    2 | 00:00:01 |
|   30 |                 TABLE ACCESS FULL             | CON$                 |  20679 |  186111 |   27 | 00:00:01 |
|   31 |              VIEW                             | _CURRENT_EDITION_OBJ | 106466 | 1384058 |  450 | 00:00:01 |
| * 32 |               FILTER                          |                      |        |         |      |          |
|   33 |                JOIN FILTER USE                | :BF0000              | 106761 | 4804245 |  450 | 00:00:01 |
| * 34 |                 HASH JOIN                     |                      | 106761 | 4804245 |  450 | 00:00:01 |
|   35 |                  INDEX FULL SCAN              | I_USER2              |    168 |    4032 |    1 | 00:00:01 |
|   36 |                  TABLE ACCESS FULL            | OBJ$                 | 106761 | 2241981 |  449 | 00:00:01 |
| * 37 |                TABLE ACCESS FULL              | USER_EDITIONING$     |      1 |       6 |    2 | 00:00:01 |
|   38 |                NESTED LOOPS                   |                      |      1 |      29 |    3 | 00:00:01 |
| * 39 |                 INDEX SKIP SCAN               | I_USER2              |      1 |      20 |    1 | 00:00:01 |
| * 40 |                 INDEX RANGE SCAN              | I_OBJ4               |      1 |       9 |    2 | 00:00:01 |
| * 41 |                TABLE ACCESS FULL              | USER_EDITIONING$     |      1 |       6 |    2 | 00:00:01 |
|   42 |             INDEX FAST FULL SCAN              | I_OBJ1               | 106761 |  854088 |  104 | 00:00:01 |
| * 43 |           TABLE ACCESS FULL                   | USER_EDITIONING$     |      1 |       6 |    2 | 00:00:01 |
|   44 |           NESTED LOOPS                        |                      |      1 |      29 |    3 | 00:00:01 |
| * 45 |            INDEX SKIP SCAN                    | I_USER2              |      1 |      20 |    1 | 00:00:01 |
| * 46 |            INDEX RANGE SCAN                   | I_OBJ4               |      1 |       9 |    2 | 00:00:01 |
| * 47 |           TABLE ACCESS FULL                   | USER_EDITIONING$     |      1 |       6 |    2 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 8 - filter("INT$INT$DBA_CONSTRAINTS"."OBJECT_TYPE#"=4 AND "INT$INT$DBA_CONSTRAINTS"."OWNER"=SYS_CONTEXT('USERENV','CURRENT_USER'))
* 10 - filter( NOT EXISTS (SELECT 0 FROM SYS."USER_EDITIONING$" "UE" WHERE "TYPE#"=:B1 AND "UE"."USER#"=:B2) AND "O"."TYPE#"<>88 OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR
  (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#"=:B3 AND "U2"."TYPE#"=2 AND
  "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))) AND EXISTS (SELECT 0 FROM SYS."USER_EDITIONING$" "UE" WHERE "UE"."TYPE#"=:B4 AND "UE"."USER#"=:B5))
* 11 - access("OI"."OWNER#"="UI"."USER#"(+))
* 13 - access("C"."ENABLED"="OI"."OBJ#"(+))
* 14 - access("C"."ROBJ#"="RO"."OBJ#"(+))
* 16 - access("RC"."OWNER#"="USER#"(+))
* 18 - access("C"."RCON#"="RC"."CON#"(+))
* 19 - access("O"."OWNER#"="U"."USER#")
* 23 - access("OC"."CON#"="C"."CON#")
* 24 - access("OC"."OWNER#"="USER#")
* 25 - filter(DECODE("U"."TYPE#",2,SUBSTR("U"."EXT_USERNAME",0,30),"U"."NAME")=SYS_CONTEXT('USERENV','CURRENT_USER'))
* 27 - filter("C"."TYPE#"<>12 AND "C"."TYPE#"<>8 AND ("C"."TYPE#"<14 OR "C"."TYPE#">17))
* 28 - access("C"."OBJ#"="O"."OBJ#" AND "O"."TYPE#"=2)
* 28 - filter("O"."TYPE#"=2)
* 32 - filter( NOT EXISTS (SELECT 0 FROM SYS."USER_EDITIONING$" "UE" WHERE "TYPE#"=:B1 AND "UE"."USER#"=:B2) AND "O"."TYPE#"<>88 OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR
  (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#"=:B3 AND "U2"."TYPE#"=2 AND
  "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))) AND EXISTS (SELECT 0 FROM SYS."USER_EDITIONING$" "UE" WHERE "UE"."TYPE#"=:B4 AND "UE"."USER#"=:B5))
* 34 - access("O"."OWNER#"="U"."USER#")
* 37 - filter("TYPE#"=:B1 AND "UE"."USER#"=:B2)
* 39 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
* 39 - filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
* 40 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
* 41 - filter("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)
* 43 - filter("TYPE#"=:B1 AND "UE"."USER#"=:B2)
* 45 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
* 45 - filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
* 46 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
* 47 - filter("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)


Note
-----
- dynamic sampling used for this statement



Can anyone please explain if all_* views are really meant to be faster than user_* views and why?

thank you very much.

and Connor said...

First thing I would look at is - run

DBMS_STATS.GATHER_DICTIONARY_STATS
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

and re-benchmark.

If the 'all' views are still quicker, perhaps look at using the "user" psuedocolumn, ie, "where owner = user" or similar context variable to avoid having to hard code.

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thank you

Giorgio Bortoli, April 04, 2016 - 2:39 pm UTC

Thanks Connor, I hadn't thought about the "user" pseudocolumn. I'm still waiting for the statistics to finish (it's been several hours already) to confirm if that was causing the performance issue or not
Chris Saxon
April 05, 2016 - 2:03 am UTC

Let us know how you go.

Oracle 12c R1 Bug

Dale, November 14, 2017 - 10:41 pm UTC

On the other hand, Gather System or Fix Object stats might work due to Oracle 12.1 bug related to table SYS.X$KZSRO.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_STATS

More on PL/SQL routine DBMS_STATS here