How can this bad query be improved?

Jonathan Lewis · Mar 4, 2013 6:00AM

jmft2012 wrote:

NDX_PROJECT_DSI                  DELETE_SESSION_ID
DELETE_DATE


QUEST_SX_IDX4BECE9AA31131B7      DELETE_SESSION_ID
DELETE_DATE

example of dup indexes on project.

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.

Regards
Jonathan Lewis

Cherif bh · Mar 4, 2013 6:27AM

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,

jmft2012 · Mar 4, 2013 6:49AM

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.

Cherif bh · Mar 4, 2013 8:09AM

jmft2012 · Mar 4, 2013 9:12AM

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.

 
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

Jonathan Lewis · Mar 4, 2013 12:33PM

jmft2012 wrote:

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.

Be careful - you're probably not running the same query.

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 · Mar 4, 2013 3:52PM

Jonathan Lewis wrote:

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.

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.

See: http://jonathanlewis.wordpress.com/2013/03/04/duplicate-indexes/

Regards
Jonathan Lewis

Hoek · Mar 4, 2013 4:49PM

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

jmft2012 · Mar 4, 2013 8:51PM

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?

Mohamed Houri · Mar 5, 2013 3:35AM

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 this

Elapsed 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

Forum Stats

Quick Links

Categories

How can this bad query be improved?

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

How can this bad query be improved?

Answers