Discussions
Categories
- 385K All Categories
- 2.5K Data
- 580 Big Data Appliance
- 2K Data Science
- 452K Databases
- 222.3K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 32 Multilingual Engine
- 575 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 575 SQLcl
- 4K SQL Developer Data Modeler
- 187.7K SQL & PL/SQL
- 21.5K SQL Developer
- 10 Data Integration
- 10 GoldenGate
- 297.6K Development
- 3 Application Development
- 18 Developer Projects
- 140 Programming Languages
- 294.3K Development Tools
- 118 DevOps
- 3.1K QA/Testing
- 646.3K Java
- 30 Java Learning Subscription
- 37K Database Connectivity
- 185 Java Community Process
- 107 Java 25
- 22.1K Java APIs
- 138.3K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 21 Java Essentials
- 172 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 206 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 577 LiveLabs
- 41 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 191 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 240 Portuguese
How can this bad query be improved?
Answers
-
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> -
IDM99 wrote:Note the "magical" appearance of the predicate "delete_session_id is null".
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))
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 -
Note the "magical" appearance of the predicate "delete_session_id is null".there is the index for 2nd table porject:
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.
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? -
why the trace event not work? thanks.
-
jmft2012 wrote:This suggests that there are not RLS predicates on the tables.
why the trace event not work? thanks.
Have you checked for views and synonyms.
Regards
Jonathan Lewis -
jmft2012 wrote:That's not the answer to the question I asked, try:
there is the index for 2nd table porject:
NDX_PROJECT_DSI (DELETE_SESSION_ID , DELETE_DATE )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_positionThen post the results. -
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
-
Jonathan Lewis wrote:you were right there is the view TASK, the predicate "delete_session_id is null"jmft2012 wrote:This suggests that there are not RLS predicates on the tables.
why the trace event not work? thanks.
Have you checked for views and synonyms.
Regards
Jonathan Lewis
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 -
jmft2012 wrote:I should have given you BREAK command as well.
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
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 -
>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 LewisNDX_PROJECT_DSI DELETE_SESSION_ID DELETE_DATE QUEST_SX_IDX4BECE9AA31131B7 DELETE_SESSION_ID DELETE_DATEexample of dup indexes on project.
Edited by: jmft2012 on Mar 4, 2013 4:53 AM
This discussion has been closed.