How can this bad query be improved?

Cherif bh

Hi,
Could you please test this and send explain plan:
select /*+ NO_INDEX( t NDX_TASK_PROJ_RSRC) */ :"SYS_B_0" , count(*) from task t where (clndr_id = :"SYS_B_1")
union select :"SYS_B_2" , count(*) from project where (clndr_id = :"SYS_B_3")

and send us the result of the below query :

select
a.table_name,
a.index_name,
a.uniqueness,
a.distinct_keys,
a.num_rows,
a.join_index,
a.last_analyzed
from all_indexes a
where a.index_name in ( 'NDX_TASK_PROJ_RSRC','NDX_TASK_CALENDAR')

Thanks,

Jonathan Lewis

jmft2012 wrote:

we can see though the both having the similar logic/physical reads. the recommended ran faster, Why?

If you check further up the thread I think you'll find that the first of the two queries you ran in this test only took 70.39 seconds a few days ago. If that's the case then the difference could be due to other activity going on on the machine, or changes in (O/S or disc) caching effects.

You might expect a small change in CPU because you're doing a "sort aggregate" (which means Oracle can keep a running count) instead of a "sort group by" (which requires an internal B-tree of distinct values to be maintained); but since there is only ever one value in the B-tree the difference should be RELATIVELY small.

You might like to pull the two plans from memory with the 'PROJECTION' formatting option (third parameter) to see how much difference there is in the data being passed up from one rowsource to the next. I think this will give you the explanation you need. Take a look at the HASH JOIN line - something has clearly gone wrong with the timing (perhaps because of oddities with the direct path write instrumentation) but they do show a different of about 40 seconds as the hash join dumps to disc.

When you have the N1 in the select list, it has to be included in the build table (first index) on the hash join, so the data set is larger - so the volume of data written to disc is larger when the hash join spills to disc - this would affect both the build and probe row sources.

If you're nervous about fixing this problem my modifying the index, you might consider testing the query with the code changed to access the base "TASK" table, rather than the view that is introducing the extra predicate. If nothing else it will give you an idea of how much benefit you could get from adding the column to the index.

Regards
Jonathan Lewis

Jonathan Lewis

BIJGA wrote:

Could you please test this and send explain plan:
select /*+ NO_INDEX( t NDX_TASK_PROJ_RSRC) */ :"SYS_B_0" , count(*) from task t where (clndr_id = :"SYS_B_1")
union select :"SYS_B_2" , count(*) from project where (clndr_id = :"SYS_B_3")

Looking at the index column lists previously supplied, your hint would give the query three options for the first part of the UNION:

****    TASK            NDX_TASK_DSI                     DELETE_SESSION_ID
****    TASK            NDX_TASK_DSI                     DELETE_DATE

****    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

Tablescan (as previously suggested by Hemant - and we're still waiting for the results), or an index hash join using one of the other two indexes that hold the delete_session_id: and we established a few days ago that the hash join is the expensive part of the problem, not the basic data access.

Regards
Jonathan Lewis

jmft2012

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

you were right the recommended had less on "direct path write "

jmft2012

If you're nervous about fixing this problem my modifying the index, you might consider testing the query with the code changed to access the base "TASK" table, rather than the view that is introducing the extra predicate. If nothing else it will give you an idea of how much benefit you could get from adding the column to the index.

Regards
Jonathan Lewis

Thanks A Lot Jonathan. on the dev instance I added the delete_session_id to the 'NDX_TASK_CALENDAR' and the new execution plan looked a lot better.
But we still need to address the disk I/O '15516".

{code}
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 cln
dr_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.13 87.31 15516 15554 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.13 87.32 15516 15554 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=15554 pr=15516 pw=0 time=87318668 us co
st=4511 size=24 card=2)
2 2 2 UNION-ALL (cr=15554 pr=15516 pw=0 time=87318549 us)
1 1 1 SORT GROUP BY NOSORT (cr=14961 pr=14936 pw=0 time=74344773 us cost=4361 size=7 card=1)
5589739 5589739 5589739 INDEX FAST FULL SCAN NDX_TASK_CALENDAR (cr=14961 pr=14936 pw=0 time=11271518 us cost=3475 size=38823918 card=5546274)(object id 333911)
1 1 1 SORT GROUP BY NOSORT (cr=593 pr=580 pw=0 time=12973738 us cost=149 size=17 card=1)
136390 136390 136390 INDEX FAST FULL SCAN NDX_PROJECT_CALENDAR (cr=593 pr=580 pw=0 time=724410 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 INDEX MODE: ANALYZED (RANGE SCAN) OF 'NDX_TASK_CALENDAR'
(INDEX)
1 SORT (GROUP BY NOSORT)
136390 INDEX MODE: ANALYZED (RANGE SCAN) OF
'NDX_PROJECT_CALENDAR' (INDEX)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 3 0.13 0.13
db file sequential read 2 0.50 0.76
db file scattered read 1092 1.40 82.29
SQL*Net message from client 2 0.07 0.11
{code}

Jonathan Lewis

jmft2012 wrote:

Thanks A Lot Jonathan. on the dev instance I added the delete_session_id to the 'NDX_TASK_CALENDAR' and the new execution plan looked a lot better.
But we still need to address the disk I/O '15516".

{code}
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 cln
dr_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.13 87.31 15516 15554 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.13 87.32 15516 15554 0 2

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2 2 2 SORT UNIQUE (cr=15554 pr=15516 pw=0 time=87318668 us co
st=4511 size=24 card=2)
2 2 2 UNION-ALL (cr=15554 pr=15516 pw=0 time=87318549 us)
1 1 1 SORT GROUP BY NOSORT (cr=14961 pr=14936 pw=0 time=74344773 us cost=4361 size=7 card=1)
5589739 5589739 5589739 INDEX FAST FULL SCAN NDX_TASK_CALENDAR (cr=14961 pr=14936 pw=0 time=11271518 us cost=3475 size=38823918 card=5546274)(object id 333911)
1 1 1 SORT GROUP BY NOSORT (cr=593 pr=580 pw=0 time=12973738 us cost=149 size=17 card=1)
136390 136390 136390 INDEX FAST FULL SCAN NDX_PROJECT_CALENDAR (cr=593 pr=580 pw=0 time=724410 us cost=132 size=2320313 card=136489)(object id 154409)



Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 3 0.13 0.13
db file sequential read 2 0.50 0.76
db file scattered read 1092 1.40 82.29
SQL*Net message from client 2 0.07 0.11
{code}

There are some contradictions in the timings that Oracle is giving you - the "sort group by nosort" is taking 74 seconds while the fast full scan of ndx_task_calendar is taking about 11 seconds. Yet the wait stats say the index fast full scans are taking 82 seconds. Moreover, the 63 "extra" seconds for the sort group by are NOT CPU seconds because we've only used 5 seconds CPU in total, and they're not read and write time for dumping to disc (a) because we shouldn't need to and (b) because pw = 0 in that line. So where's the delay coming from. (I think I pointed our earlier on that some other test seemed to suggest that something nasty is happening elsewhere on the machine - perhaps your Oracle environment is being choked by something).

One thing, though - now that we have eliminated the hash join we can put in the change suggested by baiju and change the sort group by lines to sort aggregate. There's no great reason why this should change the time dramatically, but it would be interesting to see what happens. (And I would repeat both versions of the query several times to see if their response time is highly variable anyway.)

Regards
Jonathan Lewis

jmft2012

Hemant K Chitale wrote:
If we assume that the blocks for these two tables aren't normally present during the peak period, you could "pre-load" the cache by running the query just before the peak period.

the table TASK size may be a problem to stay in cached. it is a 2.7 GB table.

Do you recommend we run the very same query before peak period?
it still may be pushed out SGA quickly.

what about to create a job running the query every 30 mins?

Hemant K Chitale

pre-loading the cache by running the query doesn't mean that the whole table is cached. Only the blocks that are scanned to satisfy the query are cached.
Part of the cache may be the filesystem cache if you are using one -- this can be larger than the database buffer cache on some systems.


Hemant K Chitale

Jonathan Lewis

jmft2012 wrote:

the table TASK size may be a problem to stay in cached. it is a 2.7 GB table.

How big is the new index - the PR on the fast full scan suggests it's about 120MB, is this correct ?

I've just emulated the first part of your query on that assumption (at 10% higher volume) and it completes on my machine in about 2.2 seconds. Even allowing for the fact that my CPU may be a lot faster than yours (you're not using one of those solaris boxes with lots of "cool threads per core" are you ?) and I happened to have a load O/S cache that "few seconds" is the ballpark you should be in.

Regards
Jonathan Lewis

jmft2012

Hemant K Chitale wrote:
pre-loading the cache by running the query doesn't mean that the whole table is cached. Only the blocks that are scanned to satisfy the query are cached.
Part of the cache may be the filesystem cache if you are using one -- this can be larger than the database buffer cache on some systems.


Hemant K Chitale

Thanks Hemant.
I will test with job of running the query every 1/2 hr.
As far as the file system cache we have Saloris 10, how can I find?