Forum Stats

  • 3,852,382 Users
  • 2,264,100 Discussions
  • 7,905,056 Comments

Discussions

How can this bad query be improved?

24

Answers

  • jmft2012
    jmft2012 Member Posts: 371 Blue Ribbon
    here you go .. thanks
    18:29:44 SQL> select clndr_id , count(*) from task where (clndr_id = 597) group by clndr_id 
    	union select clndr_id , count(*) from project where (clndr_id = 597) group by clndr_id 
    18:29:46   2  /
    
      CLNDR_ID   COUNT(*)
    ---------- ----------
           597     136390
           597    5589739
    
    18:41:44 SQL> 
    18:41:44 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));    
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    SQL_ID  bv3d7gnr100vm, child number 0
    -------------------------------------
    select clndr_id , count(*) from task where (clndr_id = :"SYS_B_0")
    group by clndr_id union select clndr_id , count(*) from project where
    (clndr_id = :"SYS_B_1") group by clndr_id
    
    Plan hash value: 3115928431
    
    --------------------------------------------------------------------------------------------------------
    
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    | Id  | Operation                 | Name                 | E-Rows |  OMem |  1Mem | Used-Mem | Used-Tmp|
    
    --------------------------------------------------------------------------------------------------------
    
    |   0 | SELECT STATEMENT          |                      |        |       |       |          |         |
    
    |   1 |  SORT UNIQUE              |                      |      2 |  2048 |  2048 | 2048  (0)|         |
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
    |   2 |   UNION-ALL               |                      |        |       |       |          |         |
    
    |   3 |    SORT GROUP BY NOSORT   |                      |      1 |       |       |          |         |
    
    |*  4 |     VIEW                  | index$_join$_003     |   5553K|       |       |          |         |
    
    |*  5 |      HASH JOIN            |                      |        |   207M|    11M|  176M (1)|     181K|
    
    |*  6 |       INDEX RANGE SCAN    | NDX_TASK_CALENDAR    |   5553K|       |       |          |         |
    
    |*  7 |       INDEX FAST FULL SCAN| NDX_TASK_PROJ_RSRC   |   5553K|       |       |          |         |
    
    |   8 |    SORT GROUP BY NOSORT   |                      |      1 |       |                  |         |
    
    |*  9 |     INDEX FAST FULL SCAN  | NDX_PROJECT_CALENDAR |    136K|       |       |          |         |
    
    --------------------------------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter("CLNDR_ID"=:SYS_B_0)
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
       5 - access(ROWID=ROWID)
       6 - access("CLNDR_ID"=:SYS_B_0)
       7 - filter("DELETE_SESSION_ID" IS NULL)
       9 - filter(("CLNDR_ID"=:SYS_B_1 AND "DELETE_SESSION_ID" IS NULL))
    
    Note
    -----
       - Warning: basic plan statistics not available. These are only collected when:
    
           * hint 'gather_plan_statistics' is used for the statement or
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
           * parameter 'statistics_level' is set to 'ALL', at session or system level
    
    
    
    38 rows selected.
    
    18:41:45 SQL> 
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,056 Blue Diamond
    IDM99 wrote:
    18:29:44 SQL> select clndr_id , count(*) from task where (clndr_id = 597) group by clndr_id 
    	union select clndr_id , count(*) from project where (clndr_id = 597) group by clndr_id 
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    5 - access(ROWID=ROWID)
    6 - access("CLNDR_ID"=:SYS_B_0)
    7 - filter("DELETE_SESSION_ID" IS NULL)
    9 - filter(("CLNDR_ID"=:SYS_B_1 AND "DELETE_SESSION_ID" IS NULL))
    Note the "magical" appearance of the predicate "delete_session_id is null".

    Either your tables are really view of the form "select * from delete_session_id is null", or there's a security predicate on the two tables which introduces that predicate (you could use event 10730 to dump any security predicates to the session's trace file).

    The index on the second table presumably includes this column, the index on the tasks table doesn't - so the optmizer has chosen an index join over a tablescan. You need to consider adding the column to the index.

    Regards
    Jonathan Lewis
  • jmft2012
    jmft2012 Member Posts: 371 Blue Ribbon
    Note the "magical" appearance of the predicate "delete_session_id is null".

    Either your tables are really view of the form "select * from delete_session_id is null", or there's a security predicate on the two tables which introduces that predicate (you could use event 10730 to dump any security predicates to the session's trace file).

    The index on the second table presumably includes this column, the index on the tasks table doesn't - so the optmizer has chosen an index join over a tablescan. You need to consider adding the column to the index.
    there is the index for 2nd table porject:
    NDX_PROJECT_DSI (DELETE_SESSION_ID , DELETE_DATE )

    I did trace event 10730:

    ALTER SESSION SET EVENTS '10730 trace name context forever, level 12';
    then the query:
    select clndr_id , count(*) from task where (clndr_id = 597) group by clndr_id union select clndr_id , count(*) from project where (clndr_id = 597) group by clndr_id
    /

    But I do not see the trace file created in the dum loc.
    What I have might missed?
  • jmft2012
    jmft2012 Member Posts: 371 Blue Ribbon
    why the trace event not work? thanks.
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,056 Blue Diamond
    jmft2012 wrote:
    why the trace event not work? thanks.
    This suggests that there are not RLS predicates on the tables.
    Have you checked for views and synonyms.

    Regards
    Jonathan Lewis
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,056 Blue Diamond
    jmft2012 wrote:

    there is the index for 2nd table porject:
    NDX_PROJECT_DSI (DELETE_SESSION_ID , DELETE_DATE )
    That's not the answer to the question I asked, try:
    set pagesize 60
    set linesize 132
    set trimspool on
    
    column column_name format a32
    
    select 
    	table_owner, table_name, index_name, column_name 
    from 
    	dba_ind_columns 
    where 
    	table_name = '{your table name}'
    order by
    	table_owner, table_name, index_name, column_position
    Then post the results.
  • jmft2012
    jmft2012 Member Posts: 371 Blue Ribbon
    the two tables' indexes.
    I commented out the table_owner (same owner for both tables) and truncated those Quest indexing full names for the confidentiality of the work site.
    noticed that there are numbers of duplicated indexes on the tables, how Oracle let this happened?
    Table TASK:
    
    TABLE_OWNE TABLE_NAME      INDEX_NAME                       COLUMN_NAME
    ---------- --------------- -------------------------------- -----------------------
    ****    TASK            NDX_FN_TASK_CODE                 SYS_NC00104$
    ****    TASK            NDX_TASK_CALENDAR                CLNDR_ID
    ****    TASK            NDX_TASK_CODE                    PROJ_ID
    ****    TASK            NDX_TASK_CODE                    TASK_CODE
    ****    TASK            NDX_TASK_DSI                     DELETE_SESSION_ID
    ****    TASK            NDX_TASK_DSI                     DELETE_DATE
    ****    TASK            NDX_TASK_LOCATION                LOCATION_ID
    ****    TASK            NDX_TASK_PROJWBS                 WBS_ID
    ****    TASK            NDX_TASK_PROJ_RSRC               PROJ_ID
    ****    TASK            NDX_TASK_PROJ_RSRC               RSRC_ID
    ****    TASK            NDX_TASK_PROJ_RSRC               DELETE_SESSION_ID
    ****    TASK            NDX_TASK_RSRC                    RSRC_ID
    ****    TASK            NDX_TASK_TASK_CODE_PROJ_ID       TASK_CODE
    ****    TASK            NDX_TASK_TASK_CODE_PROJ_ID       PROJ_ID
    ****    TASK            NDX_TASK_UPDATE_DATE             PROJ_ID
    ****    TASK            NDX_TASK_UPDATE_DATE             UPDATE_DATE
    ****    TASK            NDX_TASK_UPDATE_DATE             TASK_ID
    ****    TASK            NDX_TASK_UPDATE_DATE             DELETE_SESSION_ID
    ****    TASK            PK_TASK                          TASK_ID
    ****    TASK            QUEST_SX_C57   			 WBS_ID
    ****    TASK            QUEST_SX_C57   			 PROJ_ID
    
    Table PROJECT
    
    
    TABLE_OWNE TABLE_NAME      INDEX_NAME                       COLUMN_NAME
    ---------- --------------- -------------------------------- ----------------------
    ****    PROJECT         NDX_PROJECT_ACCOUNT              ACCT_ID
    ****    PROJECT         NDX_PROJECT_BASETYPE             BASE_TYPE_ID
    ****    PROJECT         NDX_PROJECT_CALENDAR             CLNDR_ID
    ****    PROJECT         NDX_PROJECT_CALENDAR             DELETE_SESSION_ID
    ****    PROJECT         NDX_PROJECT_CHECKOUT_USER_ID     CHECKOUT_USER_ID
    ****    PROJECT         NDX_PROJECT_DSI                  DELETE_SESSION_ID
    ****    PROJECT         NDX_PROJECT_DSI                  DELETE_DATE
    ****    PROJECT         NDX_PROJECT_LAST_FIN_DATES       LAST_FIN_DATES_ID
    ****    PROJECT         NDX_PROJECT_LOCATION             LOCATION_ID
    ****    PROJECT         NDX_PROJECT_MATRIX               MATRIX_ID
    ****    PROJECT         NDX_PROJECT_ORIG_PROJ_ID         ORIG_PROJ_ID
    ****    PROJECT         NDX_PROJECT_SOURCE_PROJ_ID       SOURCE_PROJ_ID
    ****    PROJECT         NDX_PROJECT_UPD                  UPDATE_DATE
    ****    PROJECT         NDX_PROJECT_UPD                  DELETE_SESSION_ID
    ****    PROJECT         PK_PROJECT                       PROJ_ID
    ****    PROJECT         QUEST_SX_IDX91   		 ORIG_PROJ_ID
    ****    PROJECT         QUEST_SX_IDX91   		 DELETE_DATE
    ****    PROJECT         QUEST_SX_ID55		         ADD_BY_NAME
    ****    PROJECT         QUEST_SX_IDX7 		         DELETE_SESSION_ID
    ****    PROJECT         QUEST_SX_IDX7		         DELETE_DATE
    ****    PROJECT         QUEST_SX_IDX51		         ADD_BY_NAME
    ****    PROJECT         QUEST_SX_IDX51		         PROJECT_FLAG
    ****    PROJECT         QUEST_SX_IDX54		         ORIG_PROJ_ID
    ****    PROJECT         QUEST_SX_IDX54   		 ADD_BY_NAME
    ****    PROJECT         QUEST_SX_IDX57   		 ORIG_PROJ_ID
    ****    PROJECT         QUEST_SX_IDX57   		 PROJECT_FLAG
    ****    PROJECT         QUEST_SX_IDX3B  		 PROJECT_FLAG
    ****    PROJECT         QUEST_SX_IDX20   		 PROJECT_FLAG
    ****    PROJECT         QUEST_SX_IDX20   		 DELETE_SESSION_ID
    ****    PROJECT         QUEST_SX_IDX9B                   ORIG_PROJ_ID
    ****    PROJECT         QUEST_SX_IDX9B                   DELETE_SESSION_ID
    ****    PROJECT         SK_PROJECT                       PROJ_SHORT_NAME
  • jmft2012
    jmft2012 Member Posts: 371 Blue Ribbon
    Jonathan Lewis wrote:
    jmft2012 wrote:
    why the trace event not work? thanks.
    This suggests that there are not RLS predicates on the tables.
    Have you checked for views and synonyms.

    Regards
    Jonathan Lewis
    you were right there is the view TASK, the predicate "delete_session_id is null"


    select "TASK_ID","PROJ_ID","WBS_ID","CLNDR_ID","EST_WT","PHYS_COMPLET_FDBK_FLAG",
    "LOCK_PLAN_FLAG","AUTO_COMPUTE_ACT_FLAG","COMPLETE_PCT_TYPE","TASK_TYPE","DURATION_TYPE",
    "REVIEW_TYPE","STATUS_CODE","TASK_CODE","TASK_NAME","RSRC_ID","TOTAL_FLOAT_HR_CNT",
    "FREE_FLOAT_HR_CNT","REMAIN_DRTN_HR_CNT","ACT_WORK_QTY","REMAIN_WORK_QTY","TARGET_WORK_QTY",
    "TARGET_DRTN_HR_CNT","TARGET_EQUIP_QTY","ACT_EQUIP_QTY","REMAIN_EQUIP_QTY","CSTR_DATE",
    "ACT_START_DATE","ACT_END_DATE","LATE_START_DATE","LATE_END_DATE","EXPECT_END_DATE",
    "EARLY_START_DATE","EARLY_END_DATE","RESTART_DATE","REEND_DATE","TARGET_START_DATE",
    "TARGET_END_DATE","REVIEW_END_DATE","REM_LATE_START_DATE","REM_LATE_END_DATE","CSTR_TYPE",
    "PRIORITY_TYPE","GUID","TMPL_GUID","CSTR_DATE2","CSTR_TYPE2","ACT_THIS_PER_WORK_QTY",
    "ACT_THIS_PER_EQUIP_QTY","DRIVING_PATH_FLAG","FLOAT_PATH","FLOAT_PATH_ORDER","SUSPEND_DATE","R
    ESUME_DATE","EXTERNAL_EARLY_START_DATE","EXTERNAL_LATE_END_DATE","UPDATE_DATE",
    "UPDATE_USER","CREATE_DATE","CREATE_USER","DELETE_SESSION_ID","DELETE_DATE","LOCATION_ID",
    "CONTROL_UPDATES_FLAG" from *****.TASK where delete_session_id is null
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,056 Blue Diamond
    Answer ✓
    jmft2012 wrote:
    the two tables' indexes.
    I commented out the table_owner (same owner for both tables) and truncated those Quest indexing full names for the confidentiality of the work site.
    noticed that there are numbers of duplicated indexes on the tables, how Oracle let this happened?
    ****    TASK            NDX_TASK_CALENDAR                CLNDR_ID
    
    ****    TASK            NDX_TASK_CODE                    PROJ_ID
    ****    TASK            NDX_TASK_CODE                    TASK_CODE
    I should have given you BREAK command as well.
    The indexes aren't duplicated, but there are multi-column indexes.

    Notice how several indexes on the TASK table end with DELETE_SESSION_ID - you need to add that column to the NDX_TASK_CALENDAR index as well if you want to avoid the massive hash join - otherwise the only alternatives have to access the table to satisfy the query.

    Your backout plan for implementing in production would be to add the longer index as an invisible one to check for side effects before switching it to visible and switching the short index to invisible for a few days before dropping it.

    Regards
    Jonathan Lewis
  • jmft2012
    jmft2012 Member Posts: 371 Blue Ribbon
    edited Mar 4, 2013 4:53AM
    >
    I should have given you BREAK command as well.
    The indexes aren't duplicated, but there are multi-column indexes.

    Notice how several indexes on the TASK table end with DELETE_SESSION_ID - you need to add that column to the NDX_TASK_CALENDAR index as well if you want to avoid the massive hash join - otherwise the only alternatives have to access the table to satisfy the query.

    Your backout plan for implementing in production would be to add the longer index as an invisible one to check for side effects before switching it to visible and switching the short index to invisible for a few days before dropping it.

    Regards
    Jonathan Lewis
    NDX_PROJECT_DSI                  DELETE_SESSION_ID
                                     DELETE_DATE
    
    
    QUEST_SX_IDX4BECE9AA31131B7      DELETE_SESSION_ID
                                     DELETE_DATE
    example of dup indexes on project.

    Edited by: jmft2012 on Mar 4, 2013 4:53 AM
This discussion has been closed.