Db:11.2.0.3
We have a 3rd party app and the web app runs very slow. We want to make the 3rd party to fix the issue. for the
app login process, I did an AWR , found the problem query it runs 10 mins. Then I did the sqltrace
here is it:
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.01 0.00 0 0 0 0
Fetch 2 53.32 612.03 81650 58920 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 53.34 612.04 81650 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=81650 pw=22868 time=113329109 us cost=58277 size=24 card=2)
2 2 2 UNION-ALL (cr=58923 pr=81650 pw=22868 time=113329001 us)
1 1 1 SORT GROUP BY NOSORT (cr=58330 pr=81070 pw=22868 time=104312437 us cost=58128 size=7 card=1)
5589739 5589739 5589739 VIEW index$_join$_003 (cr=58330 pr=81070 pw=22868 time=619784236 us cost=57240 size=38875249 card=5553607)
5589739 5589739 5589739 HASH JOIN (cr=58330 pr=81070 pw=22868 time=617373467 us)
5590158 5590158 5590158 INDEX RANGE SCAN NDX_TASK_CALENDAR (cr=21676 pr=21676 pw=0 time=113637058 us cost=11057 size=38875249 card=5553607)(object id 24749)
6673774 6673774 6673774 INDEX FAST FULL SCAN NDX_TASK_PROJ_RSRC (cr=36651 pr=36526 pw=0 time=213370625 us cost=21921 size=38875249 card=5553607)(object id 217274)
1 1 1 SORT GROUP BY NOSORT (cr=593 pr=580 pw=0 time=9016527 us cost=149 size=17 card=1)
136390 136390 136390 INDEX FAST FULL SCAN NDX_PROJECT_CALENDAR (cr=593 pr=580 pw=0 time=165434 us cost=132 size=2315876 card=136228)(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)
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 2 0.00 0.00
db file sequential read 22235 1.61 138.66
direct path write 1620 3.25 177.42
db file scattered read 2313 1.89 238.98
direct path read 385 1.72 19.52
SQL*Net message from client 2 0.11 0.21
Please make your comments.
Thanks in Advance.