How can this bad query be improved?

jmft2012

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

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

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

why the trace event not work? thanks.

Jonathan Lewis

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

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

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

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

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

>

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