Elapsed time per execution is far away from actual elapsed time.
Hi i am using 10.2.0.1.0 version of oracle.
i am getting one of the sql at the top elapsed time section of my AWR report. Which shows as below.
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
50,196 2,448 1,889 26.57 2.43 d0fzs3vkxwfx9 JDBC Thin Client SELECT A.NOTIFYSCHEDULEPK, A....
When i am executing the same sql with hardcoded values for the bind variable its taking 00:00:00.73 , but in AWR its showing 26.57 Sec for one time execution. So i am suspecting below things.
1. May be its due to the fact that , its taking much time for one or more of the bind variable that i am not able to find out and test.
2. May be its due the bind peeking issue, some bind variable needs some different plan of execution, but due to the stored path of
execution in shared pool ,its taking same plan and ended up in more elapsed time and resource consumption.
So how could i resolve the issue?
my sql:
SELECT A.NOTIFYSCHEDULEPK,
A.SCHEDULEPK,
A.USERPK,
A.ARGUMENTS,
A.STARTDATETIME,
A.LASTTIMESENT,
A.NEXTVALID,
A.WORKTOBEDONE
FROM A, C, D
WHERE a.NOTIFICATIONPK = :1
AND a.ACTIVE = 1
AND A.USERPK = D.USERPK
AND D.COMPANYPK = C.COMPANYPK
AND C.COMPANYPK = :2
AND D.STATUS - BITAND (D.STATUS, 1) + 1 = D.STATUS;
Execution Plan
----------------------------------------------------------
Plan hash value: 980877400
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 77 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 77 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 27 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | IDX_c | 1 | 8 | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | d | 1 | 19 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | idx_a | 1 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | a | 1 | 50 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | IDX_a | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------