Hi I am using Version 11.2.0.4.0 of oracle. We have a query which is running longer few times 2hrs+, when i verified the dba_hist_sql_stat, i see the DB time being ~5minutes but then was wondering why the query was running for 2hr+ from application. Then i reran the query manually from sql prompt, what i found from the real time monitor that there exist big differenec between "Elapsed time" and "Duration". So then i notice the existing run was with default arraysize of -15, so i again reran the query from another session with arraysize 5000, and i see the query complete in ~5minutes vs the one(with default arraysize 15) was completed in ~30minutes. I have published both Realtime monitor and the Autotrace statistics below. My query is something as below , it selecting 61 columns out of the stage table. And it doesnt have any predicate section as its just direct SELECT from table.
select c1, c2, c3.... c61
1)Should we look into the application/client(infromatica) fetch size here and fix that so that it will accept bigger arraysize/chunks from database?
2)As we are fetching lot of columns out of this query , so had a doubt that, is there any relation between fetching all these large number of columns(~61 columns) or their data types and the arraysize?
3)I am seeing difference in the two key "statistics" section in auto trace output, as "bytes received via SQL*Net from client" and "SQL*Net roundtrips to/from client"?
****************************************** plan with Arraysize 5000****************************************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Execution Started : 07/25/2017 13:05:01
First Refresh Time : 07/25/2017 13:05:01
Last Refresh Time : 07/25/2017 13:09:34
Duration : 273s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 682
Global Stats
============================================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
============================================================================================
| 176 | 35 | 72 | 68 | 682 | 143K | 24638 | 2GB | 4833 | 1GB |
============================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1769357577)
======================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
======================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 272 | +2 | 1 | 3M | | | | | | | 36.31 | Cpu (18) |
| | | | | | | | | | | | | | | | | SQL*Net more data to client (43) |
| 1 | SORT ORDER BY | | 3M | 202K | 272 | +2 | 1 | 3M | 22182 | 1GB | 4833 | 1GB | 1G | 1G | 55.95 | Cpu (31) |
| | | | | | | | | | | | | | | | | direct path read temp (51) |
| | | | | | | | | | | | | | | | | direct path write temp (12) |
| 2 | TABLE ACCESS FULL | TAB1 | 3M | 25723 | 47 | +1 | 1 | 3M | 2456 | 1GB | | | | | 7.74 | Cpu (4) |
| | | | | | | | | | | | | | | | | direct path read (9) |
======================================================================================================================================================================================================
3402540 rows selected.
Elapsed: 00:04:32.84
Execution Plan
----------------------------------------------------------
Plan hash value: 1769357577
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3402K| 902M| | 202K (2)| 00:11:52 |
| 1 | SORT ORDER BY | | 3402K| 902M| 1329M| 202K (2)| 00:11:52 |
| 2 | TABLE ACCESS FULL| TAB1 | 3402K| 902M| | 25723 (6)| 00:01:31 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1294 recursive calls
3 db block gets
143566 consistent gets
292727 physical reads
0 redo size
521987286 bytes sent via SQL*Net to client
5804 bytes received via SQL*Net from client
682 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
3402540 rows processed
****************************************** plan with Arraysize 15****************************************
3402540 rows selected.
Elapsed: 01:00:51.45
Execution Plan
----------------------------------------------------------
Plan hash value: 1769357577
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3402K| 902M| | 202K (2)| 00:11:52 |
| 1 | SORT ORDER BY | | 3402K| 902M| 1329M| 202K (2)| 00:11:52 |
| 2 | TABLE ACCESS FULL| TAB1 | 3402K| 902M| | 25723 (6)| 00:01:31 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1256 recursive calls
3 db block gets
143562 consistent gets
292727 physical reads
1832 redo size
534193576 bytes sent via SQL*Net to client
1588209 bytes received via SQL*Net from client
226837 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
3402540 rows processed
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Execution Started : 07/25/2017 12:27:08
First Refresh Time : 07/25/2017 12:27:08
Last Refresh Time : 07/25/2017 13:27:59
Duration : 3651s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 226837
Global Stats
============================================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
============================================================================================
| 135 | 38 | 91 | 4.99 | 227K | 143K | 24638 | 2GB | 5663 | 1GB |
============================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1769357577)
=================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
=================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 3650 | +2 | 1 | 3M | | | | | | | 15.08 | Cpu (19) |
| 1 | SORT ORDER BY | | 3M | 202K | 3652 | +0 | 1 | 3M | 22182 | 1GB | 5663 | 1GB | 1G | 1G | 76.98 | Cpu (24) |
| | | | | | | | | | | | | | | | | direct path read temp (55) |
| | | | | | | | | | | | | | | | | direct path write temp (18) |
| 2 | TABLE ACCESS FULL | TAB1 | 3M | 25723 | 44 | +1 | 1 | 3M | 2456 | 1GB | | | | | 7.94 | Cpu (1) |
| | | | | | | | | | | | | | | | | direct path read (9) |
=================================================================================================================================================================================================