4 Replies Latest reply: Oct 26, 2012 6:48 AM by Suddhasatwa_Bhaumik RSS

    SECURITY query running slow with Prompts!!

    Suddhasatwa_Bhaumik
      Hello All,

      Version: PeopleSoft HRMS 9 with PeopleTools 8.49.09
      DB Version: 10.2.0.3 (Oracle)

      My client is running a security query, given below, with and without prompts. Without prompts it is completing in 35 seconds but with prompts (even if the values in the prompts are blank!), the query is taking more than 4-5 hours but not completing!!
      SELECT /*+ OPT_PARAM('_optimizer_mjc_enabled', 'false') 
      opt_param('_optimizer_cartesian_enabled', 'false') opt_param('optimizer_index_caching', 0) opt_param('optimizer_index_cost_adj', 0)*/ B.OPRID, A.EMPLID, A.PWCUK_LEGACY_ID, A.NAME, A.EMPL_STATUS, A.EMPL_CLASS, 
      to_char(to_date( TO_CHAR(A.HIRE_DT, 'YYYY-MM-DD'), 'yyyy-mm-dd'), 'dd/mm/yyyy'), 
      to_char(to_date(decode( TO_CHAR(A.REHIRE_DT, 'YYYY-MM-DD'), '', 
      TO_CHAR(A.HIRE_DT, 'YYYY-MM-DD'), TO_CHAR(A.REHIRE_DT, 'YYYY-MM-DD')), 'yyyy-mm-dd'), 'dd/mm/yyyy'), 
      to_char(to_date( TO_CHAR(A.TERMINATION_DT, 'YYYY-MM-DD'), 'yyyy-mm-dd'), 'dd/mm/yyyy'), A.DEPTID, A.DEPT_DESCR, A.PWCUK_BUSINESSUNIT, A.PWCUK_BU_DESCR, A.PWCUK_SUBREGION, A.PWCUK_SR_DESCR, 
      A.PWCUK_REGION, A.PWCUK_R_DESCR, B.ROWSECCLASS, E.CLASSDEFNDESC, C.ROLENAME, D.DESCR, 
      Case C.ROLENAME When 'UK_OTG_Query_Access' then 'Y' When 'UK_Self_Service_Query_Access' then 'Y' When 'UK_Prtner_Affairs_Query_Acces' then 'Y' When 'UK_SelfServ_Sens_Basic_Query' then 'Y' When 'UK_ESC_Extra_Query_Access' then 'Y' When 'UK_BCI_Query_Access' then 'Y' When 'UK_Self_Service_Non_Sens_Q Acc' then 'Y' Else 'N' END, TO_CHAR(A.EFFDT, 'YYYY-MM-DD'), 
      TO_CHAR(A.EFFDT, 'YYYY-MM-DD'), D.ROLENAME FROM PS_PWCUK_EMP_C_VW A, PS_PERS_SRCH_QRY A1, PSOPRDEFN B, PS_ROLEU SER_VW C, PSROLEDEFN D, PSCLASSDEFN E WHERE A.EMPLID = A1.EMPLID 
      AND A1.OPRID = 'kcooper001a' AND ( B.OPRID = A.PWCE_GUID AND B.OPRID = C.OPRID AND C.ROLENAME NOT IN ('Orbit User', 'PWCUK_LOS_ADMIN_PLANNER', 'Query Designer', 'Query User', 'PWCE_EMEA_AUDIT_RLE_NO_BSE_TBL', 'PWCE_REPORT_DIST', 'EOPP_USER', 'PAPP_USER', 'PWCUK_XMLP_REPORT_DEVELOPER', 'GBR_PEOPLE_MANAGER_CONFIG_UPD', 'PWCUK_EX_EMPLOYEE', 'ReportSuperUser', 'PWCE JOBCODE LOAD UTILITY', 
      'PWCE EMPLOYEE RVW LOAD ACCESS', 'PwCE Bonus Upload Access', 'GBR_EP_SYSADMIN') AND ( C.ROLENAME NOT LIKE 'PWCUK_EP%' OR C.ROLENAME = 'PWCUK_EP_ADMIN') AND C.ROLENAME NOT LIKE 'PWCUK_SP%' AND C.ROLENAME NOT LIKE 'GBR_PMGR%' AND 0 < INSTR(:1, decode(trim(:2), null, ' ', B.OPRID)) AND 0 < INSTR(:3, decode(trim(:4), null, ' ', B.EMPLID)) AND 0 < INSTR(:5, decode(trim(:6), null, ' ', A.PWCUK_LEGACY_ID)) A
      ND 0 < INSTR(:7, decode(trim(:8), null, ' ', C.ROLENAME)) AND 0 < INSTR(:9, decode(trim(:10), null, ' ', E.CLASSID)) AND C.ROLENAME = D.ROLENAME AND E.CLASSID = B.ROWSECCLASS ) ORDER BY 4, 20
      Below are some more useful information I have gathered from DB level for this query:
      +--------------------------------------------------------------------------------------------------+
      |Plan HV     Min Snap  Max Snap  Execs       LIO            PIO            CPU         Elapsed     |
      +--------------------------------------------------------------------------------------------------+
      |770792495   39602     39747     5           1,181,648,326  6,823          7,433.93    7,481.60    |
      +--------------------------------------------------------------------------------------------------+
      .
      ========== PHV = 770792495==========
      First seen from "10/19/12 10:00:44" (snap #39602)
      Last seen from  "10/25/12 11:00:28" (snap #39747)
      .
      Execs          LIO            PIO            CPU            Elapsed
      =====          ===            ===            ===            =======
      5              1,181,648,326  6,823          7,433.93       7,481.60
      .
      Plan hash value: 770792495
      
      ----------------------------------------------------------------------------------------------------------
      | Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                    |                    |       |       |    35 (100)|          |
      |   1 |  SORT ORDER BY                      |                    |     1 |   645 |    35   (6)| 00:00:01 |
      |   2 |   NESTED LOOPS                      |                    |     1 |   645 |    34   (3)| 00:00:01 |
      |   3 |    NESTED LOOPS                     |                    |     6 |  1122 |    10  (10)| 00:00:01 |
      |   4 |     NESTED LOOPS                    |                    |     1 |   165 |     5   (0)| 00:00:01 |
      |   5 |      NESTED LOOPS                   |                    |     1 |   122 |     4   (0)| 00:00:01 |
      |   6 |       NESTED LOOPS                  |                    |     1 |    81 |     3   (0)| 00:00:01 |
      |   7 |        NESTED LOOPS                 |                    |   552 | 29256 |     2   (0)| 00:00:01 |
      |   8 |         INDEX FULL SCAN             | PSAPSROLEUSER      |   550 | 15950 |     1   (0)| 00:00:01 |
      |   9 |         TABLE ACCESS BY INDEX ROWID | PS_ROLEXLATOPR     |     1 |    24 |     1   (0)| 00:00:01 |
      |  10 |          INDEX UNIQUE SCAN          | PS_ROLEXLATOPR     |     1 |       |     1   (0)| 00:00:01 |
      |  11 |        TABLE ACCESS BY INDEX ROWID  | PSOPRDEFN          |     1 |    28 |     1   (0)| 00:00:01 |
      |  12 |         INDEX UNIQUE SCAN           | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
      |  13 |       TABLE ACCESS BY INDEX ROWID   | PSCLASSDEFN        |     1 |    41 |     1   (0)| 00:00:01 |
      |  14 |        INDEX UNIQUE SCAN            | PS_PSCLASSDEFN     |     1 |       |     1   (0)| 00:00:01 |
      |  15 |      TABLE ACCESS BY INDEX ROWID    | PSROLEDEFN         |     1 |    43 |     1   (0)| 00:00:01 |
      |  16 |       INDEX UNIQUE SCAN             | PS_PSROLEDEFN      |     1 |       |     1   (0)| 00:00:01 |
      |  17 |     VIEW                            | PS_PERS_SRCH_QRY   |   483 | 10626 |     5  (20)| 00:00:01 |
      |  18 |      SORT UNIQUE                    |                    |   483 | 62790 |     5  (20)| 00:00:01 |
      |  19 |       NESTED LOOPS                  |                    |   483 | 62790 |     4   (0)| 00:00:01 |
      |  20 |        NESTED LOOPS                 |                    |   483 | 49749 |     3   (0)| 00:00:01 |
      |  21 |         NESTED LOOPS                |                    |     1 |    67 |     2   (0)| 00:00:01 |
      |  22 |          TABLE ACCESS BY INDEX ROWID| PSOPRDEFN          |     1 |    40 |     1   (0)| 00:00:01 |
      |  23 |           INDEX UNIQUE SCAN         | PS_PSOPRDEFN       |     1 |       |     1   (0)| 00:00:01 |
      |  24 |          TABLE ACCESS BY INDEX ROWID| PS_SJT_OPR_CLS     |     1 |    27 |     1   (0)| 00:00:01 |
      |  25 |           INDEX RANGE SCAN          | PS_SJT_OPR_CLS     |     1 |       |     1   (0)| 00:00:01 |
      |  26 |         TABLE ACCESS BY INDEX ROWID | PS_SJT_CLASS_ALL   |   482 | 17352 |     1   (0)| 00:00:01 |
      |  27 |          INDEX RANGE SCAN           | PS_SJT_CLASS_ALL   |  1158 |       |     1   (0)| 00:00:01 |
      |  28 |        INDEX RANGE SCAN             | PS_SJT_PERSON      |     1 |    27 |     1   (0)| 00:00:01 |
      |  29 |    VIEW                             | PS_PWCUK_EMP_C_VW  |     1 |   458 |     4   (0)| 00:00:01 |
      |  30 |     UNION ALL PUSHED PREDICATE      |                    |       |       |            |          |
      |  31 |      TABLE ACCESS BY INDEX ROWID    | PS_PWCUK_EMPLOYEES |     1 |   169 |     1   (0)| 00:00:01 |
      |  32 |       INDEX RANGE SCAN              | PS_PWCUK_EMPLOYEES |     1 |       |     1   (0)| 00:00:01 |
      |  33 |      FILTER                         |                    |       |       |            |          |
      |  34 |       NESTED LOOPS OUTER            |                    |     1 |   220 |     3   (0)| 00:00:01 |
      |  35 |        NESTED LOOPS OUTER           |                    |     1 |   208 |     2   (0)| 00:00:01 |
      |  36 |         TABLE ACCESS BY INDEX ROWID | PS_PWCUK_EX_EMPLS  |     1 |   161 |     1   (0)| 00:00:01 |
      |  37 |          INDEX RANGE SCAN           | PS_PWCUK_EX_EMPLS  |     1 |       |     1   (0)| 00:00:01 |
      |  38 |         TABLE ACCESS BY INDEX ROWID | PS_PWCE_EP_ROLES   |     1 |    47 |     1   (0)| 00:00:01 |
      |  39 |          INDEX RANGE SCAN           | PS_PWCE_EP_ROLES   |     1 |       |     1   (0)| 00:00:01 |
      |  40 |        INDEX RANGE SCAN             | PS_PWCUK_EMPLOYEES |     1 |    12 |     1   (0)| 00:00:01 |
      |  41 |       SORT AGGREGATE                |                    |     1 |    23 |            |          |
      |  42 |        INDEX RANGE SCAN             | PS_PWCE_EP_ROLES   |     1 |    23 |     1   (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------------------------
      
      
                                                    Summary Execution Statistics Over Time
                                                                                    Avg                 Avg
      Snapshot                          Avg LIO             Avg PIO          CPU (secs)      Elapsed (secs)
      Time            Execs            Per Exec            Per Exec            Per Exec            Per Exec
      ------------ -------- ------------------- ------------------- ------------------- -------------------
      19-OCT 10:00        1      374,309,812.00            1,469.00            2,286.32            2,291.32
      25-OCT 10:00        3       86,033,085.00            1,567.67              543.68              546.11
      25-OCT 11:00        1      549,239,259.00              651.00            3,516.56            3,551.96
                   -------- ------------------- ------------------- ------------------- -------------------
      avg                        336,527,385.33            1,229.22            2,115.52            2,129.80
      sum                 5
      
                                                    Per-Plan Execution Statistics Over Time
                                                                                               Avg                 Avg
            Plan Snapshot                          Avg LIO             Avg PIO          CPU (secs)      Elapsed (secs)
      Hash Value Time            Execs            Per Exec            Per Exec            Per Exec            Per Exec
      ---------- ------------ -------- ------------------- ------------------- ------------------- -------------------
       770792495 19-OCT 10:00        1      374,309,812.00            1,469.00            2,286.32            2,291.32
                 25-OCT 10:00        3       86,033,085.00            1,567.67              543.68              546.11
                 25-OCT 11:00        1      549,239,259.00              651.00            3,516.56            3,551.96
      **********              -------- ------------------- ------------------- ------------------- -------------------
      avg                                   336,527,385.33            1,229.22            2,115.52            2,129.80
      sum                            5
      I'm not at all proficient in PeopleSoft.

      Please advice how we can get faster runs for this query.

      Note: We have already checked all other possibilities, like network, application, web services, etc, and they look normal.

      Thanks,
      Suddhasatwa
        • 1. Re: SECURITY query running slow with Prompts!!
          Nicolas.Gasparotto
          First of all, what does "prompt" mean ?
          Secondly, where this query come from ?
          Thirdly, why these hints in used ? Any tries without those ? Seriously, you should avoid such hint, especially against hidden parameters.
          Then, is this query ever work before ? Have you gather stats ? How ?

          You showed two snapshots, but they are 6 days from each other, any chance to get closer ones ? It is rather unsable and unhelpful to have such a gap in between.

          Nicolas.
          • 2. Re: SECURITY query running slow with Prompts!!
            Suddhasatwa_Bhaumik
            Hello nicolas

            Prompt means (for example) a text field for a user written query wherein you can enter input variables.

            This query is written/devised by my client himself for his end users.

            These hints are used to improve the cost of the query as well as the execution as per suggestions from Oracle support.

            Without these hints the query still takes the same time, but the cost is much higher.

            This query has been working until 24th.

            We gather statistics daily for all stale objects in the database.

            The gap in execution is based on requirement. When my client requires to run this from fron end , it is ran.


            Thanks,
            Suddhasatwa
            • 3. Re: SECURITY query running slow with Prompts!!
              Nicolas.Gasparotto
              If the hints are there only for the "cost", then I'm sorry to say, but they are useless. Did you say that was not efficient to the Oracle Support ?

              I asked earlier if that query already ran in a reasonnable time, is it the case, or always took that time ? Is it a change of behaviour after a db upgrade ?

              Have you tried to work with AWR snapshots with a short gap in between ? I mean between the AWR snapshots (every 15 minutes or so), not between the runs of the query.

              If there's no values for the bind variables, I assume this is what you mean when you said "no prompts", then Oracle can go much faster because of the few (or no?) data repartition to retrieve.
              However, when given values to some (all?) of the bind variables, then all the problem will be on the data repartition. That's why I was asking how you gathered statistics on the involved objects, in other words, the histograms may be wrong somehow.
              There's a lot of litterature on this, have a look to the Jonathan Lewis blog for more information.

              Anyway, I think there's not enough information here and does not look easy to work on it in that state from the other side of the network.

              Nicolas.
              • 4. Re: SECURITY query running slow with Prompts!!
                Suddhasatwa_Bhaumik
                Hello Nicolas

                Thanks for above note.

                Hints were advised by Oracle support which has decreased the cost and run time of this query when they are ran from database using SQL*Plus. When the same is ran via PeopleSoft without any values in the prompts, then the processing time goes up to 5 hours with no results.

                I have taken ample AWR reports for it, and have also used the SQLT tool (Provided by Oracle support) to generate SQL Tuning options for this query with no luck!

                Thanks for the info on re-partition. However it would be interesting for all reading this thread that no opther queries involving these objects (which are used in this query) is getting slower. All other aspects of the application are also running fine.

                Please advise if you/anyone else has any other places I can look into to tune this query.

                Note: In the meanwhile I would also sit with the PeopleSoft admins to check what these prompts are doing and how the query is getting executed on the database.

                Thanks.
                Suddhasatwa