Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 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
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How can this bad query be improved?
Answers
-
jmft2012 wrote:Can't happen - you should get Oracle error - ORA-01408: such column list already indexed.
NDX_PROJECT_DSI DELETE_SESSION_ID DELETE_DATE QUEST_SX_IDX4BECE9AA31131B7 DELETE_SESSION_ID DELETE_DATE
example of dup indexes on project.
That's partly why I included the table_owner in my query - to avoid the possibility that there were two tables with the same name in different schemas.
You said you'd commented out the table_owner from the query - can you check with the table_owner back in place.
Regards
Jonathan Lewis -
Hi,
Your query is below:
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
I can see that you need to count all rows for clndr_id=value_1 from table task and the same for project table clndr_id=value_2.
So there is no need for group by section :
Could you please replace it by :
select VALUE_1 , count(*) from task where (clndr_id = VALUE_1)
union
select VALUE_2 , count(*) from project where (clndr_id = VALUE_2)
And check the result.
Thanks, -
TABLE_OWNE TABLE_NAME INDEX_NAME COLUMN_NAME ---------- --------------- -------------------------------- ------------------------- XXXUSER PROJECT NDX_PROJECT_DSI DELETE_SESSION_ID XXXUSER PROJECT NDX_PROJECT_DSI DELETE_DATE XXXUSER PROJECT QUEST_SX_IDXXXXXXXXA31160631B7 DELETE_SESSION_ID XXXUSER PROJECT QUEST_SX_IDXXXXXXXXA31160631B7 DELETE_DATE
from your query.
two identical indexs on the same table. I did "X" for the confidentiality. -
Hi,
I think the issue is related to index join
|* 4 | VIEW | index$_join$_003 | 5553K| | | | |
Could you please try the query I have sent to you and send us the explain plan ?
Thanks -
BIJGA wrote:Great BUGA! i did sqlplus' autotrace. Notice that the execution plan of orig was not the same as from the sqlplus' autotrace's.
Hi,
I think the issue is related to index join
|* 4 | VIEW | index$_join$_003 | 5553K| | | | |
Could you please try the query I have sent to you and send us the explain plan ?
Thanks
Your recommendation worked great. We need to notify the 3rd party on this and see if they can make the change in their code.
thanks a lot.select clndr_id , count(*) from task where (clndr_id = 597) group by clndr_id 2 union select clndr_id , count(*) from project where (clndr_id = 597) group by clndr_id ; Execution Plan ---------------------------------------------------------- -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 9 | 6947 | | 1 | SORT UNIQUE | | 2 | 9 | 6947 | | 2 | UNION-ALL | | | | | | 3 | SORT GROUP BY NOSORT | | 1 | 5 | 6798 | | 4 | INDEX FAST FULL SCAN| NDX_TASK_CALENDAR | 5554K| 26M| 5911 | | 5 | SORT GROUP BY NOSORT | | 1 | 4 | 149 | | 6 | INDEX FAST FULL SCAN| NDX_PROJECT_CALENDAR | 136K| 533K| 131 | -------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 142474 consistent gets 26040 physical reads 0 redo size 262 bytes sent via SQL*Net to client 239 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed select 597 , count(*) from task where (clndr_id = 597) 2 union select 597 , count(*) from project where (clndr_id = 597); Execution Plan ---------------------------------------------------------- -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 9 | 24157 | | 1 | SORT UNIQUE | | 2 | 9 | 24157 | | 2 | UNION-ALL | | | | | | 3 | SORT AGGREGATE | | 1 | 5 | 23618 | | 4 | INDEX FAST FULL SCAN| NDX_TASK_CALENDAR | 5554K| 26M| 5911 | | 5 | SORT AGGREGATE | | 1 | 4 | 539 | | 6 | INDEX FAST FULL SCAN| NDX_PROJECT_CALENDAR | 136K| 533K| 131 | -------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 26094 consistent gets 5057 physical reads 0 redo size 272 bytes sent via SQL*Net to client 239 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed
-
jmft2012 wrote:Be careful - you're probably not running the same query.
Great BUGA! i did sqlplus' autotrace. Notice that the execution plan of orig was not the same as from the sqlplus' autotrace's.
Your recommendation worked great. We need to notify the 3rd party on this and see if they can make the change in their code.
thanks a lot.
Unless you've changed the index to include the delete_session_id your latest test is querying the TABLE, while the production query is querying the VIEW which includes the predicate on delete_session_id.
Check that you've got the same predicates - which means you can't just use autotrace.
Regards
Jonathan Lewis -
Jonathan Lewis wrote:After writing that I came up with a few ideas of how you could (apparently) get the same columns indexed in the same order in the same schema - and threw it open as a little puzzle on my blog: one of the answers that came up is possibly the answer in your case. Perhaps one of the indexes is a "nosegment" index, created to test the suitability of an index before creating it - with the accepted index being created with a different name from the original test.
Can't happen - you should get Oracle error - ORA-01408: such column list already indexed.
That's partly why I included the table_owner in my query - to avoid the possibility that there were two tables with the same name in different schemas.
You said you'd commented out the table_owner from the query - can you check with the table_owner back in place.
See: http://jonathanlewis.wordpress.com/2013/03/04/duplicate-indexes/
Regards
Jonathan Lewis -
Interesting thread.
Another thing caught my attention besides the contributions already made: the :SYS_B_0 (and so on) bind variable names indicate that the value for parameter CURSOR_SHARING is no longer EXACT (which is the default).
Can you check the value for CURSOR_SHARING in V$PARAMETER?
Perhaps the queries are constructed through dynamic SQL (and sessions dynamically altered) through 3rd party?
Can you tell us a bit more about the application?
A few links expanding more about it:
https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:599762300346943686#4086773600346337418
http://jonathanlewis.wordpress.com/?s=cursor_sharing -
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 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 31.49 142.06 23573 58920 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 31.50 142.07 23573 58920 0 2 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 34 (PX) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 2 2 2 SORT UNIQUE (cr=58923 pr=23573 pw=22868 time=142069540 us cost=58277 size=24 card=2) 2 2 2 UNION-ALL (cr=58923 pr=23573 pw=22868 time=142069256 us) 1 1 1 SORT GROUP BY NOSORT (cr=58330 pr=23573 pw=22868 time=141991737 us cost=58128 size=7card=1) 5589739 5589739 5589739 VIEW index$_join$_003 (cr=58330 pr=23573 pw=22868 time=237535434 us cost=57240 size=38875249 card=5553607) 5589739 5589739 5589739 HASH JOIN (cr=58330 pr=23573 pw=22868 time=235732289 us) 5590158 5590158 5590158 INDEX RANGE SCAN NDX_TASK_CALENDAR (cr=21676 pr=644 pw=0 time=2665546 us cost=11057 size=38875249 card=5553607)(object id 24749) 6673774 6673774 6673774 INDEX FAST FULL SCAN NDX_TASK_PROJ_RSRC (cr=36651 pr=61 pw=0 time=2951971 us cost=21921 size=38875249 card=5553607)(object id 217274) 1 1 1 SORT GROUP BY NOSORT (cr=593 pr=0 pw=0 time=77486 us cost=149 size=17 card=1) 136390 136390 136390 INDEX FAST FULL SCAN NDX_PROJECT_CALENDAR (cr=593 pr=0 pw=0 time=55291 us cost=132 size=2320313 card=136489)(object id 154409) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 2 SORT (UNIQUE) 2 UNION-ALL 1 SORT (GROUP BY NOSORT) 5589739 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'TASK' (TABLE) 5589739 INDEX MODE: ANALYZED (RANGE SCAN) OF 'NDX_TASK_CALENDAR' (INDEX) 5590158 SORT (GROUP BY NOSORT) 6673774 INDEX MODE: ANALYZED (RANGE SCAN) OF 'NDX_PROJECT_CALENDAR' (INDEX)
BIJGA's suggestion:select :"SYS_B_0" , count(*) from task where (clndr_id = :"SYS_B_1") union select :"SYS_B_2" , count(*) from project where (clndr_id = :"SYS_B_3") call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 29.53 84.92 22870 58920 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 29.54 84.93 22870 58920 0 2 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 34 (PX) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 2 2 2 SORT UNIQUE (cr=58923 pr=22870 pw=22868 time=84929755 us cost=77945 size=24 card=2) 2 2 2 UNION-ALL (cr=58923 pr=22870 pw=22868 time=84929632 us) 1 1 1 SORT AGGREGATE (cr=58330 pr=22870 pw=22868 time=84883511 us cost=77045 size=7 card=1) 5589739 5589739 5589739 VIEW index$_join$_003 (cr=58330 pr=22870 pw=22868 time=196934870 us cost=57240 size=38875249 card=5553607) 5589739 5589739 5589739 HASH JOIN (cr=58330 pr=22870 pw=22868 time=195136077 us) 5590158 5590158 5590158 INDEX RANGE SCAN NDX_TASK_CALENDAR (cr=21676 pr=2 pw=0 time=2353598 us cost=11057 size=38875249 card=5553607)(object id 24749) 6673774 6673774 6673774 INDEX FAST FULL SCAN NDX_TASK_PROJ_RSRC (cr=36651 pr=0 pw=0 time=3206373 us cost=21921 size=38875249 card=5553607)(object id 217274) 1 1 1 SORT AGGREGATE (cr=593 pr=0 pw=0 time=46082 us cost=900 size=17 card=1) 136390 136390 136390 INDEX FAST FULL SCAN NDX_PROJECT_CALENDAR (cr=593 pr=0 pw=0 time=56350 us cost=132 size=2320313 card=136489)(object id 154409)
we can see though the both having the similar logic/physical reads. the recommended ran faster, Why? -
we can see though the both having the similar logic/physical reads. the recommended ran faster, Why?
You should have introduced the list of waiting events that accompagnies the row source operations. Something like thisElapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ control file sequential read 4 0.00 0.00 db file sequential read 302812 0.62 1913.89 latch: cache buffers chains 3 0.04 0.04 direct path write temp 501 0.01 0.30 SQL*Net message to client 17272 0.00 0.04 db file scattered read 120 0.02 0.63 direct path read temp 608 0.14 1.71 SQL*Net message from client 17272 44.81 31865.74 SQL*Net more data to client 15 0.00 0.00 latch: object queue header operation 1 0.00 0.00 latch: library cache 3 0.03 0.04 latch: library cache pin 1 0.00 0.00 latch: cache buffer handles 1 0.00 0.00
It might be that in the first case you've spent an enormous time waiting for the client(SQL*Net message from client) to ask the data set generated by your logical and physical I/O.
By the way there is a difference in the physical read between the two cases (23573-22870) = 703 extra physical reads.
Best regards
Mohamed Houri
www.hourim.wordpress.com
This discussion has been closed.