Opened 8 years ago

Closed 8 years ago

#27323 closed Cleanup/optimization (fixed)

Optimize Oracle introspection by using USER_SEQUENCES instead of USER_CATALOG

Reported by: Mariusz Felisiak Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: Oracle sequences
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Little optimization. Using USER_SEQUENCES instead of USER_CATALOG in Oracle query which verify that sequence exists or not is more optimal.

Change History (9)

comment:1 by Mariusz Felisiak, 8 years ago

Summary: Oracle backend cleanupUsing USER_SEQUENCES instead of USER_CATALOG

comment:2 by Mariusz Felisiak, 8 years ago

Has patch: set
Triage Stage: UnreviewedReady for checkin

comment:3 by Simon Charette, 8 years ago

Triage Stage: Ready for checkinAccepted

Please don't mark your own patches as RFC, see our ticket triaging doc's.

comment:4 by Simon Charette, 8 years ago

comment:5 by Tim Graham, 8 years ago

Could you explain why it's more optimal so we have that rationale recorded?

in reply to:  5 comment:6 by Mariusz Felisiak, 8 years ago

Replying to Tim Graham:

Could you explain why it's more optimal so we have that rationale recorded?

Query with USER_CATALOG has a cost equal 3 and it has 2 conditions in WHERE clause.
Query with USER_SEQUENCES has a cost equal 2 and it has 1 condition in WHERE clause.

USER_CATALOG is quite complicate, it depends on the _CURRENT_EDITION_OBJ and it contains all of the user objects from which we are choosing sequences:

CREATE OR REPLACE FORCE VIEW "SYS"."_CURRENT_EDITION_OBJ" ("OBJ#", "DATAOBJ#", "DEFINING_OWNER#", "NAME", "NAMESPACE", "SUBNAME", "TYPE#", "CTIME", "MTIME", "STIME", "STATUS", "REMOTEOWNER", "LINKNAME", "FLAGS", "OID$", "SPARE1", "SPARE2", "SPARE3", "SPARE4", "SPARE5", "SPARE6", "OWNER#", "DEFINING_EDITION") AS 
  select o."OBJ#",o."DATAOBJ#",o."OWNER#",o."NAME",o."NAMESPACE",o."SUBNAME",o."TYPE#",o."CTIME",o."MTIME",o."STIME",o."STATUS",o."REMOTEOWNER",o."LINKNAME",o."FLAGS",o."OID$",o."SPARE1",o."SPARE2",o."SPARE3",o."SPARE4",o."SPARE5",o."SPARE6",
       o.spare3,
       case when (o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87) or
                  bitand(u.spare1, 16) = 0) then
         null
       when (u.type# = 2) then
        (select eo.name from obj$ eo where eo.obj# = u.spare2)
       else
        'ORA$BASE'
       end
from obj$ o, user$ u
where o.owner# = u.user#
  and (   /* non-versionable object */
          (   o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87,88)
           or bitand(u.spare1, 16) = 0)
          /* versionable object visible in current edition */
       or (    o.type# in (4,5,7,8,9,10,11,12,13,14,22,87)
           and (   (u.type# <> 2 and
                    sys_context('userenv', 'current_edition_name') = 'ORA$BASE')
                or (u.type# = 2 and
                    u.spare2 = sys_context('userenv', 'current_edition_id'))
                or exists (select 1 from obj$ o2, user$ u2
                           where o2.type# = 88
                             and o2.dataobj# = o.obj#
                             and o2.owner# = u2.user#
                             and u2.type#  = 2
                             and u2.spare2 =
                                  sys_context('userenv', 'current_edition_id'))
               )
          )
      );

USER_SEQUENCES is much more readable and it contains only sequences:

CREATE OR REPLACE FORCE VIEW "SYS"."USER_SEQUENCES" ("SEQUENCE_NAME", "MIN_VALUE", "MAX_VALUE", "INCREMENT_BY", "CYCLE_FLAG", "ORDER_FLAG", "CACHE_SIZE", "LAST_NUMBER") AS 
  select o.name,
      s.minvalue, s.maxvalue, s.increment$,
      decode (s.cycle#, 0, 'N', 1, 'Y'),
      decode (s.order$, 0, 'N', 1, 'Y'),
      s.cache, s.highwater
from sys.seq$ s, sys.obj$ o
where o.owner# = userenv('SCHEMAID')
  and o.obj# = s.obj#;

To sum up, in the first approach we choose all objects (including sequences), and in the second step we limit results to sequences with the given name. In the second approach we choose sequences and next limit results to the given name.

comment:7 by Shai Berger, 8 years ago

Triage Stage: AcceptedReady for checkin

I verified that USER_SEQUENCES is available on Oracle 11, I don't really care if it is as optimized there.

I left one comment on the PR, otherwise I think we're good to go.

comment:8 by Tim Graham, 8 years ago

Summary: Using USER_SEQUENCES instead of USER_CATALOGOptimize Oracle introspection by using USER_SEQUENCES instead of USER_CATALOG

comment:9 by Tim Graham <timograham@…>, 8 years ago

Resolution: fixed
Status: assignedclosed

In 794f866c:

Fixed #27323 -- Optimized Oracle introspection by using USER_SEQUENCES instead of USER_CATALOG.

Note: See TracTickets for help on using tickets.
Back to Top