My Oracle Support Banner

This Query is Consuming a lot of Time on the Database, What can be Done? (Doc ID 878282.1)

Last updated on AUGUST 29, 2019

Applies to:

Identity Manager - Version 9.1.0.2 to 9.1.0.2
Information in this document applies to any platform.
Information in this document applies to any platform.

Goal

It has been found that the following query is run often against the Oracle Identity Manager (OIM) Schema and takes a lot of database processing. Is there a way to avoid this and the performance hits this causes?

SELECT COUNT (o1) AS COUNT
  FROM (SELECT oti.sch_key AS o1
          FROM oim_owner.oti
         WHERE oti.pkg_type = :"SYS_B_0"
           AND (   oti.osi_assigned_to_usr_key = :1
                OR oti.osi_assigned_to_usr_key IN (
                      SELECT pxd_orig_usr_key proxieduser
                        FROM oim_owner.pxd
                       WHERE pxd_proxy_key = :2
                         AND pxd_start_date < TO_DATE (:"SYS_B_1", :"SYS_B_2")
                         AND (   pxd_end_date >
                                               TO_DATE (:"SYS_B_3",
                                                        :"SYS_B_4")
                              OR pxd_end_date IS NULL
                             ))
               )
        UNION
        SELECT oti.sch_key AS o1
          FROM oim_owner.oti
         WHERE oti.pkg_type = :"SYS_B_5"
           AND oti.osi_assigned_to_ugp_key IN (SELECT ugp_key
                                                 FROM oim_owner.usg
                                                WHERE usr_key = :3))

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.