We are troubleshooting several performance issues on the application, the specific test case below takes about 23 seconds in the 1st run. In reality it should only a second or more to run..
Identified one SQL that takes about 21.36 seconds from the overall elapsed 23 seconds. When the user ran the same test again on the application, it only took 1 second in the 2nd run. ThHe same sql that took 21.36 seconds in the first run took only 0.33 seconds in the second run.
I understand it is due to the buffer cache that the 2nd run was much faster but is this a normal behaviour or will tuning/analyzing the SQL improve the performance in the 1st run or when the SQL's are not in cache?
1st RUN:
SELECT DISTINCT table1.id, ...................
...,0
FROM
table1,table2,table3,table4,table5,table6
where
..............;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.17 0.14 0 12 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.87 21.22 6360 12712 0 158
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.04 21.36 6360 12724 0 158
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 151
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
db file sequential read 6360 0.05 20.60
SQL*Net message from client 2 0.41 0.47
SQL*Net more data to client 4 0.00 0.00
2nd RUN:
SELECT DISTINCT table1.id, ...................
...,0
FROM
table1,table2,table3,table4,table5,table6
where
..............;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.10 0.12 0 12 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.15 0.20 11 13125 0 158
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.26 0.33 11 13137 0 158
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 151
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
db file sequential read 11 0.00 0.02
SQL*Net message from client 2 0.38 0.42
SQL*Net more data to client 4 0.00 0.00
asynch descriptor resize 1 0.00 0.00
********************************************************************************