This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Feb 15, 2013 10:44 AM by user522961 RSS

Rebuild Index

user522961 Newbie
Currently Being Moderated
Hi,
the following query takes more than 3 hours.

the wait event is on an index : PSCRECV_HDR. Should we rebuild that index ? Or problème is from something else ?
How can I verify the health of index ?
show parameter optimizer
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.3
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

Explain plan for
SELECT A.BUSINESS_UNIT, D.DESCR FROM PS_SP_RECV1_NONVW A, PS_SP_RCV1_NONVWLN D WHERE D.BUSINESS_UNIT LIKE 'I%' AND D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD='FRA' UNION SELECT  BUSINESS_UNIT, DESCR FROM PS_SP_RECV1_NONVW A WHERE BUSINESS_UNIT LIKE 'IF%' AND NOT EXISTS (SELECT 'X' FROM PS_SP_RCV1_NONVWLN D WHERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND 

explain plan succeeded.
159 ms 
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
                                                                                                                                                                                                                                                                                                             
-----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
| Id  | Operation                         | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)|                                                                                                                                                                                                        
-----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
|   0 | SELECT STATEMENT                  |                    |  9960M|   538G|       |   313M  (1)|                                                                                                                                                                                                        
|   1 |  SORT ORDER BY                    |                    |  9960M|   538G|   410G|   313M  (1)|                                                                                                                                                                                                        
|   2 |   SORT UNIQUE                     |                    |  9960M|   538G|   633G|   188M  (1)|                                                                                                                                                                                                        
|   3 |    UNION-ALL                      |                    |       |       |       |            |                                                                                                                                                                                                        
|*  4 |     HASH JOIN                     |                    |  9960M|   538G|  3944K| 31301  (98)|                                                                                                                                                                                                        
|*  5 |      INDEX FAST FULL SCAN         | PSCRECV_HDR        |   201K|  1577K|       |   187   (2)|                                                                                                                                                                                                        
|*  6 |      HASH JOIN                    |                    | 98659 |  4817K|       |   191   (2)|                                                                                                                                                                                                        
|   7 |       NESTED LOOPS                |                    |       |       |       |            |                                                                                                                                                                                                        
|   8 |        NESTED LOOPS               |                    |     1 |    42 |       |     3   (0)|                                                                                                                                                                                                        
|   9 |         MERGE JOIN CARTESIAN      |                    |     1 |    12 |       |     2   (0)|                                                                                                                                                                                                        
|* 10 |          INDEX RANGE SCAN         | PS_BUS_UNIT_TBL_FS |     1 |     6 |       |     1   (0)|                                                                                                                                                                                                        
|  11 |          BUFFER SORT              |                    |     1 |     6 |       |     1   (0)|                                                                                                                                                                                                        
|* 12 |           INDEX RANGE SCAN        | PS_BUS_UNIT_TBL_FS |     1 |     6 |       |     1   (0)|                                                                                                                                                                                                        
|* 13 |         INDEX UNIQUE SCAN         | PS_BUS_UNIT_LANG   |     1 |       |       |     0   (0)|                                                                                                                                                                                                        
|  14 |        TABLE ACCESS BY INDEX ROWID| PS_BUS_UNIT_LANG   |     1 |    30 |       |     1   (0)|                                                                                                                                                                                                        
|* 15 |       INDEX FAST FULL SCAN        | PSCRECV_HDR        |   201K|  1577K|       |   187   (2)|                                                                                                                                                                                                        
|* 16 |     FILTER                        |                    |       |       |       |            |                                                                                                                                                                                                        
|* 17 |      HASH JOIN                    |                    |   100K|  3450K|       |   190   (2)|                                                                                                                                                                                                        
|  18 |       TABLE ACCESS BY INDEX ROWID | PS_BUS_UNIT_TBL_FS |     1 |    27 |       |     2   (0)|                                                                                                                                                                                                        
|* 19 |        INDEX RANGE SCAN           | PS_BUS_UNIT_TBL_FS |     1 |       |       |     1   (0)|                                                                                                                                                                                                        
|* 20 |       INDEX FAST FULL SCAN        | PSCRECV_HDR        |   201K|  1577K|       |   187   (2)|                                                                                                                                                                                                        
|  21 |      NESTED LOOPS                 |                    |  9178 |   215K|       |   681   (1)|                                                                                                                                                                                                        
|  22 |       NESTED LOOPS                |                    |     1 |    16 |       |     0   (0)|                                                                                                                                                                                                        
|* 23 |        INDEX UNIQUE SCAN          | PS_BUS_UNIT_LANG   |     1 |    10 |       |     0   (0)|                                                                                                                                                                                                        
|* 24 |        INDEX UNIQUE SCAN          | PS_BUS_UNIT_TBL_FS |     1 |     6 |       |     0   (0)|                                                                                                                                                                                                        
|* 25 |       INDEX RANGE SCAN            | PSCRECV_HDR        |  9178 | 73424 |       |   681   (1)|                                                                                                                                                                                                        
-----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
                                                                                                                                                                                                                                                                                                             
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                             
   4 - access("A"."BUSINESS_UNIT"="A"."BUSINESS_UNIT" AND                                                                                                                                                                                                                                                    
              "A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
   5 - filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                      
   6 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
  10 - access("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
       filter("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
  12 - access("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
       filter("B"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
  13 - access("B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT" AND "C"."LANGUAGE_CD"='FRA')                                                                                                                                                                                                                           
       filter("C"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                                                 
  15 - filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT" LIKE 'I%')                                                                                                                                                                                                                                      
  16 - filter( NOT EXISTS (SELECT 0 FROM SYSADM."PS_BUS_UNIT_LANG"                                                                                                                                                                                                                                           
              "C",SYSADM."PS_BUS_UNIT_TBL_FS" "B",SYSADM."PS_RECV_HDR" "A" WHERE                                                                                                                                                                                                                             
              "A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT" AND "A"."RECV_STATUS"<>'C' AND                                                                                                                                                                                                                         
              "A"."BUSINESS_UNIT"=:B1 AND "B"."BUSINESS_UNIT"=:B2 AND                                                                                                                                                                                                                                        
              "B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT" AND "C"."LANGUAGE_CD"='FRA' AND                                                                                                                                                                                                                        
              "C"."BUSINESS_UNIT"=:B3))                                                                                                                                                                                                                                                                      
  17 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
  19 - access("B"."BUSINESS_UNIT" LIKE 'IF%')                                                                                                                                                                                                                                                                
       filter("B"."BUSINESS_UNIT" LIKE 'IF%')                                                                                                                                                                                                                                                                
  20 - filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT" LIKE 'IF%')                                                                                                                                                                                                                                     
  23 - access("C"."BUSINESS_UNIT"=:B1 AND "C"."LANGUAGE_CD"='FRA')                                                                                                                                                                                                                                           
  24 - access("B"."BUSINESS_UNIT"=:B1)                                                                                                                                                                                                                                                                       
       filter("B"."BUSINESS_UNIT"="C"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
  25 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")                                                                                                                                                                                                                                                       
       filter("A"."RECV_STATUS"<>'C' AND "A"."BUSINESS_UNIT"=:B1)                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                             
Note                                                                                                                                                                                                                                                                                                         
-----                                                                                                                                                                                                                                                                                                        
   - 'PLAN_TABLE' is old version                                                                                                                                                                                                                                                                             

65 rows selected

1 273 ms écoulées

==============================================================================================
SQL> select sid, osuser,row_wait_obj#,program,event from v$session where program like '%Dev%' and osuser='myuser';

       SID OSUSER          ROW_WAIT_OBJ# PROGRAM         EVENT
---------- --------------- ------------- --------------- ---------------
        29 myuser                51715 SQL Developer   SQL*Net message
                                                          from client

SQL> select  object_type, object_name, data_object_id
  2  from dba_objects
  3  where object_id = 51715;

OBJECT_TYPE         OBJECT_NAME     DATA_OBJECT_ID
------------------- --------------- --------------
INDEX               PSCRECV_HDR              51715
Thanks.
  • 1. Re: Rebuild Index
    6363 Guru
    Currently Being Moderated
    Are statistics accurate, does the query return approximately ten billion rows, and if so how long would you expect that to take?
    Can you post a SQL trace file with actual execution details?
  • 2. Re: Rebuild Index
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user522961 wrote:
    SQL> select sid, osuser,row_wait_obj#,program,event from v$session where program like '%Dev%' and osuser='myuser';
    
    SID OSUSER          ROW_WAIT_OBJ# PROGRAM         EVENT
    ---------- --------------- ------------- --------------- ---------------
    29 myuser                51715 SQL Developer   SQL*Net message
    from client
    This is simply telling you that the last object you waited on was 51715, it doesn't tell you that the object was where you spent your time.

    Looking at the execution plan, the fast full scans of the index were probably only a few seconds (of db time) - even if Oracle's estimated cost for the scans was badly out, it's unlikely to be estimate a cost equivalent to a couple of seconds and give an actual time of 3 hours.

    Since you're on 11.2.0.3, and if you're licensed for the diagnostic and performance packs, you could use the real-time SQL Monitoring feature to find out where the time really went. (dbms_sqltune.report_sql_monitor() is the relevant pipelined function; or you could query v$sql_monitor and v$sql_plan_monitor directly).

    If the plan is not still in memory, you could go back to ASH (or the history version) and sample the plan from there - again if you've paid for the performance pack license.

    Regards
    Jonathan Lewis
  • 3. Re: Rebuild Index
    mtefft Journeyer
    Currently Being Moderated
    I see your plan has a MERGE JOIN CARTESIAN, which is frequently (but not always) a sign that the query is missing one or more join predicates. The extremely large estimated row count also points in that direction.

    Have you verified that the query is actually correct? How many rows actually resulted - is it reasonable?

    Can you post the full SQL (the original post did not get it all) and the DDL (including indexes and foreign-key constraints) for the tables involved in the query?
  • 4. Re: Rebuild Index
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    user522961 wrote:
    Hi,
    the following query takes more than 3 hours.

    the wait event is on an index : PSCRECV_HDR. Should we rebuild that index ? Or problème is from something else ?
    How can I verify the health of index ?
    It looks like you've already asked for advice for the same query here and got some helpful feedback: query  with union does not finish

    If this is the case, why do you open another thread for the same query/question?

    Since you seem to be able to reproduce the problem, if are not allowed to use Real-Time SQL Monitoring / Active Session History as outlined by Jonathan, you could use extended Rowsource Statistics (ALTER SESSION SET STATISTICS_LEVEL = ALL) and run DBMS_XPLAN.DISPLAY_CURSOR with the "ALLSTATS LAST" formatting option to get an idea where the time actually is spent.

    Your EXPLAIN PLAN output shows the same anomaly as in the other thread: The HASH JOIN at operation ID 4 shows these join predicates:

    (*"A"."BUSINESS_UNIT"="A"."BUSINESS_UNIT"* AND "A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")

    Note the spurious "A"."BUSINESS_UNIT"="A"."BUSINESS_UNIT" predicate, this might indicate that the optimizer went somewhere wrong while applying query transformations, so this might be a bug - as Jonathan already suspected in the other thread due to the strange cardinality estimates (millions instead of only a couple of rows).

    You haven't followed up some of the advices Jonathan had given there, like testing with the NO_MERGE hints.

    If you can't / don't want to change the query you'll have to go for Stored Outlines / SQL Plan Baselines to get the desired plan as long as you don't know what triggers the bad plan.

    Randolf
  • 5. Re: Rebuild Index
    jgarry Guru
    Currently Being Moderated
    >
    Your EXPLAIN PLAN output shows the same anomaly as in the other thread: The HASH JOIN at operation ID 4 shows these join predicates:

    (*"A"."BUSINESS_UNIT"="A"."BUSINESS_UNIT"* AND "A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT")

    Note the spurious "A"."BUSINESS_UNIT"="A"."BUSINESS_UNIT" predicate, this might indicate that the optimizer went somewhere wrong while applying query transformations, so this might be a bug - as Jonathan already suspected in the other thread due to the strange cardinality estimates (millions instead of only a couple of rows).
    The OP aliases PS_SP_RECV1_NONVW A twice, once on either side of the union. This late on Friday, I can't decide if that should be wrong.
  • 6. Re: Rebuild Index
    user522961 Newbie
    Currently Being Moderated
    Thanks to all.

    Then for the moment it is not the problem of index.

    And :
    -yes I had another thread and thank to Jonathan I had a solution. But since the query is run from an application PeopleSoft web page then it changes for the variable (%I% or %FR% ...) then it would be parsed again and sql_baseplan can not be applied to it.
    -the query returns just one row (and it should , it is ok).
    -I will try to use DBMS_SQLTUNE.REPORT_SQL_MONITOR() as suggested to see where the time was spending.

    Thanks again and see you again.
  • 7. Re: Rebuild Index
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    user522961 wrote:
    -yes I had another thread and thank to Jonathan I had a solution. But since the query is run from an application PeopleSoft web page then it changes for the variable (%I% or %FR% ...) then it would be parsed again and sql_baseplan can not be applied to it.
    Since you seem to have the required licenses (Diagnostic + Tuning Pack) you could try to use a SQL Profile instead of a SQL Plan Baseline. Although SQL Profiles are not intended to be used in that way, there is a script officially supported by Oracle that allows using SQL Profiles for the same purpose as SQL Plan Baselines:

    http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/

    The key detail is that SQL Profiles allow a "force match" option which isn't available with SQL Plan Baselines, so SQLs that only differ in literals won't prevent the SQL Profile from being applied to the statement. So you might want to give this workaround a try if necessary.

    Randolf
  • 8. Re: Rebuild Index
    user522961 Newbie
    Currently Being Moderated
    Thanks again.
    I reran the query and got some information. Do they give you any clout ?
    SQL> select sql_id,sql_exec_id,sql_exec_start from v$sql_monitor where sql_id='9dbt4f5wbhnm0';
    
    SQL_ID        SQL_EXEC_ID SQL_EXEC
    ------------- ----------- --------
    9dbt4f5wbhnm0    16777216 02/02/13
    
    SQL> col wait_clas format a20
    SQL> col event format a40
    SQL> SELECT NVL(wait_class,'CPU') AS wait_class, NVL(event,'CPU') AS event, COUNT(*)
      2       FROM v$active_session_history a
      3       WHERE sql_id = '9dbt4f5wbhnm0'
      4       AND sql_exec_id = 16777216
      5            GROUP BY wait_class, event;
    
    WAIT_CLASS                                                       EVENT                                   COUNT(*)
    ---------------------------------------------------------------- ---------------------------------------- ----------
    CPU                                                              CPU                                    9439
    User I/O                                                         db file scattered read                    1
    User I/O                                                         Disk file operations I/O                  1
    Scheduler                                                        resmgr:cpu quantum                        1
    
    
    ==================================elapsed time=======================================
    SELECT ROUND(elapsed_time    /1000000)     AS "Elapsed (s)",
         ROUND(cpu_time             /1000000,3)   AS "CPU (s)",
         ROUND(queuing_time         /1000000,3)   AS "Queuing (s)",
         ROUND(application_wait_time/1000000,3)   AS "Appli wait (s)",
         ROUND(concurrency_wait_time/1000000,3)   AS "Concurrency wait (s)",
         ROUND(cluster_wait_time    /1000000,3)   AS "Cluster wait (s)",
         ROUND(user_io_wait_time    /1000000,3)   AS "User io wait (s)"
              FROM v$sql_monitor
         WHERE sql_id = '9dbt4f5wbhnm0'
         AND sql_exec_id = 16777216
    
    Elapsed (s)    CPU (s) Queuing (s) Appli wait (s) Concurrency wait (s) Cluster wait (s) User io wait (s)
    ----------- ---------- ----------- -------------- -------------------- ---------------- ----------------
           9479   9417,688           0              0                    0                0              ,66
    
    SELECT 
         ROUND(physical_read_bytes  /(1024*1024)) AS "Phys reads (MB)",
         ROUND(physical_write_bytes /(1024*1024)) AS "Phys writes (MB)",
         buffer_gets                              AS "Buffer gets",
         ROUND(plsql_exec_time/1000000,3)         AS "Plsql exec (s)",
         ROUND(java_exec_time /1000000,3)         AS "Java exec (s)"
         FROM v$sql_monitor
         WHERE sql_id = '9dbt4f5wbhnm0'
         AND sql_exec_id = 16777216
    
    Phys reads (MB) Phys writes (MB) Buffer gets Plsql exec (s) Java exec (s)
    --------------- ---------------- ----------- -------------- -------------
                  7                0        2121              0             0
    Regards.
  • 9. Re: Rebuild Index
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    user522961 wrote:
    I reran the query and got some information. Do they give you any clout ?
    It would probably be more helpful if you really used the Real-Time SQL Monitoring report rather than querying directly from the underlying views. Also V$ACTIVE_SESSION_HISTORY from 11g on has a SQL_PLAN_LINE_ID / SQL_PLAN_OPERATION / SQL_PLAN_OPTIONS column so that you can tell which operation of the execution plan was active when the sample took place.

    According to the information you've provided you've spent almost 100% of your time on CPU - but it would good to look at the actual report to see the actual cardinalities and operations that burned all the CPU.

    Note: In 11g only the combination of SQL_ID / SQL_EXEC_START and SQL_EXEC_ID together uniquely identifies an instance of a SQL execution - so you have to careful if you query corresponding views directly, your queries miss the SQL_EXEC_START information (Date + Time portion)

    Randolf
  • 10. Re: Rebuild Index
    user522961 Newbie
    Currently Being Moderated
    Thanks again.
    Here is the sql_monitor report :
    SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(
      2    sql_id       => '753fxx2yz0905',
      3    type         => 'TEXT',
      4    report_level => 'ALL') AS report
      5  FROM dual;
    SQL Monitoring Report
    
    SQL Text
    ------------------------------
    SELECT /*+ MONITOR */A.BUSINESS_UNIT, D.DESCR FROM PS_SP_RECV1_NONVW A, PS_SP_RCV1_NONVWLN D WHERE D.BUSINESS_UNIT LIKE 'I%' AND D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD='FRA' UNION SELECT BUSINESS_UNIT, DESCR FROM PS_SP_RECV1_NONVW A WHERE BUSINESS_UNIT LIKE 'IF%' AND NOT EXISTS (SELECT 'X' FROM PS_SP_RCV1_NONVWLN D WHERE D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD='FRA') ORDER BY 1
    
    Global Information
    ------------------------------
     Status              :  DONE (ALL ROWS)
     Instance ID         :  1
     Session             :  SYSADM (35:33149)
     SQL ID              :  753fxx2yz0905
     SQL Execution ID    :  16777216
     Execution Started   :  02/03/2013 07:43:24
     First Refresh Time  :  02/03/2013 07:43:24
     Last Refresh Time   :  02/03/2013 10:34:13
     Duration            :  10249s
     Module/Action       :  SQL Developer/-
     Service             :  SYS$USERS
     Program             :  SQL Developer
     Fetch Calls         :  1
    
    Global Stats
    ===========================================================================
    | Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  |
    | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
    ===========================================================================
    |   10249 |    9883 |     0.80 |      365 |     1 |   2178 |  158 |   7MB |
    ===========================================================================
    
    SQL Plan Monitoring Details (Plan Hash Value=1460278311)
    ==========================================================================================================================================================================================
    | Id |              Operation              |        Name        |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity |      Activity Detail       |
    |    |                                     |                    | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |        (# samples)         |
    ==========================================================================================================================================================================================
    |  0 | SELECT STATEMENT                    |                    |         |       |         1 | +10249 |     1 |        1 |      |       |       |          |                            |
    |  1 |   SORT ORDER BY                     |                    |     10G |  314M |         1 | +10249 |     1 |        1 |      |       |  2048 |          |                            |
    |  2 |    SORT UNIQUE                      |                    |     10G |  189M |     10249 |     +1 |     1 |        1 |      |       |  2048 |    62.06 | Cpu (3996)                 |
    |    |                                     |                    |         |       |           |        |       |          |      |       |       |          | resmgr:cpu quantum (10)    |
    |  3 |     UNION-ALL                       |                    |         |       |     10237 |     +1 |     1 |       4G |      |       |       |     5.81 | Cpu (375)                  |
    |  4 |      HASH JOIN                      |                    |     10G | 31301 |     10249 |     +1 |     1 |       4G |      |       |   18M |    32.08 | Cpu (2071)                 |
    |  5 |       INDEX FAST FULL SCAN          | PSCRECV_HDR        |    202K |   187 |         1 |     +1 |     1 |     202K |   89 |   5MB |       |          |                            |
    |  6 |       HASH JOIN                     |                    |   98659 |   191 |     10249 |     +1 |     1 |     202K |      |       |  414K |     0.02 | Cpu (1)                    |
    |  7 |        NESTED LOOPS                 |                    |         |       |         1 |     +1 |     1 |        1 |      |       |       |          |                            |
    |  8 |         NESTED LOOPS                |                    |       1 |     3 |         1 |     +1 |     1 |        1 |      |       |       |          |                            |
    |  9 |          MERGE JOIN CARTESIAN       |                    |       1 |     2 |         1 |     +1 |     1 |        1 |      |       |       |          |                            |
    | 10 |           INDEX RANGE SCAN          | PS_BUS_UNIT_TBL_FS |       1 |     1 |         1 |     +1 |     1 |        1 |    1 |  8192 |       |          |                            |
    | 11 |           BUFFER SORT               |                    |       1 |     1 |         1 |     +1 |     1 |        1 |      |       |  2048 |          |                            |
    | 12 |            INDEX RANGE SCAN         | PS_BUS_UNIT_TBL_FS |       1 |     1 |         1 |     +1 |     1 |        1 |      |       |       |          |                            |
    | 13 |          INDEX UNIQUE SCAN          | PS_BUS_UNIT_LANG   |       1 |       |         1 |     +1 |     1 |        1 |    1 |  8192 |       |          |                            |
    | 14 |         TABLE ACCESS BY INDEX ROWID | PS_BUS_UNIT_LANG   |       1 |     1 |         1 |     +1 |     1 |        1 |    1 |  8192 |       |          |                            |
    | 15 |        INDEX FAST FULL SCAN         | PSCRECV_HDR        |    202K |   187 |     10249 |     +1 |     1 |     202K |   13 | 776KB |       |     0.03 | Cpu (1)                    |
    |    |                                     |                    |         |       |           |        |       |          |      |       |       |          | db file scattered read (1) |
    | 16 |      FILTER                         |                    |         |       |         1 | +10249 |     1 |        0 |      |       |       |          |                            |
    | 17 |       HASH JOIN                     |                    |    101K |   190 |         1 | +10249 |     1 |     202K |      |       |  388K |          |                            |
    | 18 |        TABLE ACCESS BY INDEX ROWID  | PS_BUS_UNIT_TBL_FS |       1 |     2 |         1 | +10249 |     1 |        1 |    1 |  8192 |       |          |                            |
    | 19 |         INDEX RANGE SCAN            | PS_BUS_UNIT_TBL_FS |       1 |     1 |         1 | +10249 |     1 |        1 |    1 |  8192 |       |          |                            |
    | 20 |        INDEX FAST FULL SCAN         | PSCRECV_HDR        |    202K |   187 |         1 | +10249 |     1 |     202K |   50 | 880KB |       |          |                            |
    | 21 |       NESTED LOOPS                  |                    |    9178 |   681 |         1 | +10249 |     1 |        1 |      |       |       |          |                            |
    | 22 |        NESTED LOOPS                 |                    |       1 |       |         1 | +10249 |     1 |        1 |      |       |       |          |                            |
    | 23 |         INDEX UNIQUE SCAN           | PS_BUS_UNIT_LANG   |       1 |       |         1 | +10249 |     1 |        1 |    1 |  8192 |       |          |                            |
    | 24 |         INDEX UNIQUE SCAN           | PS_BUS_UNIT_TBL_FS |       1 |       |         1 | +10249 |     1 |        1 |      |       |       |          |                            |
    | 25 |        INDEX RANGE SCAN             | PSCRECV_HDR        |    9178 |   681 |         1 | +10249 |     1 |        1 |      |       |       |          |                            |
    ==========================================================================================================================================================================================
    And SQL_MONITIR_LIST and AWR (just for 3 hours while query was running :
    SQL> SELECT DBMS_SQLTUNE.report_sql_monitor_list(
      2    sql_id       => '753fxx2yz0905',
      3    type         => 'TEXT',
      4    report_level => 'ALL') AS report
      5  FROM dual;
    
    
                                                                                    SQL Monitoring List
                                                                                   =====================
    
    
    ===================================================================================================================================================================================
    |     Status      | Duration |    SQL Id     | Exec Id  |        Start        |  User  |  Module/Action  | Dop | DB Time | IOs |                     SQL Text                     |
    ===================================================================================================================================================================================
    | DONE (ALL ROWS) |   10249s | 753fxx2yz0905 | 16777216 | 02/03/2013 07:43:24 | SYSADM | SQL Developer/- |     |  10249s | 158 | SELECT /*+ MONITOR */A.BUSINESS_UNIT, D.DESCR    |
    |                 |          |               |          |                     |        |                 |     |         |     | FROM PS_SP_RECV1_NONVW A, PS_SP_RCV1_NONVWLN D   |
    |                 |          |               |          |                     |        |                 |     |         |     | WHERE D.BUSINESS_UNIT LIKE 'I%' AND              |
    |                 |          |               |          |                     |        |                 |     |         |     | D.BUSINESS_UNIT=A.BUSINESS_UNIT AND              |
    |                 |          |               |          |                     |        |                 |     |         |     | D.LANGUAGE_CD='FRA' UNION SELECT B...            |
    ===================================================================================================================================================================================
    
    SQL> SPOOL OFF
    Top 5 Timed Foreground Events
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                               Avg
                                                              wait   % DB
    Event                                 Waits     Time(s)   (ms)   time Wait Class
    ------------------------------ ------------ ----------- ------ ------ ----------
    DB CPU                                            9,542          93.3
    db file scattered read               58,076         124      2    1.2 User I/O
    db file sequential read              58,723         109      2    1.1 User I/O
    resmgr:cpu quantum                   11,730          31      3     .3 Scheduler
    log file sync                         3,219          19      6     .2 Commit
    
    Statistic Name                                       Time (s) % of DB Time
    ------------------------------------------ ------------------ ------------
    sql execute elapsed time                              9,905.8         96.9
    DB CPU                                                9,542.4         93.3
    PL/SQL execution elapsed time                            21.4           .2
    parse time elapsed                                       10.5           .1
    hard parse elapsed time                                   8.7           .1
    hard parse (sharing criteria) elapsed time                6.3           .1
    hard parse (bind mismatch) elapsed time                   5.8           .1
    repeated bind elapsed time                                1.3           .0
    PL/SQL compilation elapsed time                           0.3           .0
    connection management call elapsed time                   0.0           .0
    failed parse elapsed time                                 0.0           .0
    DB time                                              10,225.7
    background elapsed time                                 890.4
    background cpu time                                      16.8
    Thanks a lot for help.
  • 11. Re: Rebuild Index
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    user522961 wrote:
    SQL Plan Monitoring Details (Plan Hash Value=1460278311)
    ==========================================================================================================================================================================================
    | Id |              Operation              |        Name        |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity |      Activity Detail       |
    |    |                                     |                    | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |        (# samples)         |
    ==========================================================================================================================================================================================
    |  0 | SELECT STATEMENT                    |                    |         |       |         1 | +10249 |     1 |        1 |      |       |       |          |                            |
    |  1 |   SORT ORDER BY                     |                    |     10G |  314M |         1 | +10249 |     1 |        1 |      |       |  2048 |          |                            |
    |  2 |    SORT UNIQUE                      |                    |     10G |  189M |     10249 |     +1 |     1 |        1 |      |       |  2048 |    62.06 | Cpu (3996)                 |
    |    |                                     |                    |         |       |           |        |       |          |      |       |       |          | resmgr:cpu quantum (10)    |
    |  3 |     UNION-ALL                       |                    |         |       |     10237 |     +1 |     1 |       4G |      |       |       |     5.81 | Cpu (375)                  |
    |  4 |      HASH JOIN                      |                    |     10G | 31301 |     10249 |     +1 |     1 |       4G |      |       |   18M |    32.08 | Cpu (2071)                 |
    |  5 |       INDEX FAST FULL SCAN          | PSCRECV_HDR        |    202K |   187 |         1 |     +1 |     1 |     202K |   89 |   5MB |       |          |                            |
    |  6 |       HASH JOIN                     |                    |   98659 |   191 |     10249 |     +1 |     1 |     202K |      |       |  414K |     0.02 | Cpu (1)                    |
    ==========================================================================================================================================================================================
    >
    So interestingly the optimizer's estimates were quite in the right ballpark for that bad plan - the question is then of course why it favored this plan, which looks like a bug.

    You can see that HASH JOIN between the row source from operation ID 5 (The Index Fast Full Scan) and the result of the other joins (operation ID 6 and children) produced 4G rows, which is then collapsed into a single row by the SORT UNIQUE operation ID = 2. Both operations require a massive amount of CPU time, and don't need any logical I/O (which I forgot to mention in my previous reply that your execution only generated very few physical and logical I/Os and therefore I expected something like this, CPU consumed by HASH JOINs or SORTs for example).

    The crucial point seems to be that in contrast to the UNION ALL plan the SORT UNIQUE operation on the Index Fast Full Scan row source (operation ID 5) is considered redundant by the optimizer apparently due to the UNION operator which requires another SORT UNIQUE on top. So semantically this assumption is correct, and the plan saves SORT UNIQUE operations, but at the cost of a massive intermediate join result - which is actually reflected in the cardinallity and cost estimates.

    So the optimizer ought to have considered the variant with the additional SORT UNIQUEs prior to the join which would keep the intermediate join result small, at the cost of additional SORT UNIQUE operations, which is clearly the better alternative in this case.

    We haven't seen the view definitions of the involved views PS_SP_RECV1_NONVW and PS_SP_RCV1_NONVWLN which would be interesting to see where these SORT UNIQUEs come from in first place.

    It is clearly a case that you should raise with Oracle support to have them look at it why the optimizer doesn't come up with a better plan - it might be related to some parameter settings that PeopleSoft dictates, but may be it is a general issue.

    If you happen to have a class of queries that is affected by this problem, and depending on how these views are used elsewhere, you might be able to put some hints into these views that allow working around the issue.

    Apart from that only explicit hinting in the query like those already mentioned by Jonathan in the other thread or using the SQL Plan Baseline / SQL Profile approach are things that come to my mind at present, but may be Jonathan has some more ideas.

    Randolf
  • 12. Re: Rebuild Index
    user522961 Newbie
    Currently Being Moderated
    Thanks.
  • 13. Re: Rebuild Index
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    user522961 wrote:
    Thanks.
    You're welcome. I've asked for the view definitions, by the way, so if you could post them here it would be helpful to understand where those SORT UNIQUE operations came from in first place.

    Randolf
  • 14. Re: Rebuild Index
    user522961 Newbie
    Currently Being Moderated
    Thank you Randolf.
    Sorry for delay. I was busy for some other deployment.
    Here are the view definitions
    I used different_from' instead of symbol. The symbol does not appear.
    SQL> select view_name, text from dba_views where view_name like '%PS_SP_RCV1_NON%';
    
     
    
    VIEW_NAME
    
    ------------------------------
    
    TEXT
    
    --------------------------------------------------------------------------------
    
    PS_SP_RCV1_NONVWLN
    
    SELECT DISTINCT A.BUSINESS_UNIT , C.LANGUAGE_CD , C.DESCR FROM PS_RECV_HDR A , P
    
    S_BUS_UNIT_TBL_FS B , PS_BUS_UNIT_LANG C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
    
     AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.RECV_STATUS different_from 'C'
    
     
    
    PS_SP_RCV1_NONVW_I
    
    SELECT DISTINCT A.BUSINESS_UNIT , B.DESCR FROM PS_RECV_HDR A , PS_BUS_UNIT_TBL_F
    
    S B , PS_BUS_UNIT_LANG C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.RECV_STAT
    
    US different_from 'C' AND A.BUSINESS_UNIT = C.BUSINESS_UNIT (+) AND C.LANGUAGE_CD ='FRA'
    Best regards.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points