Hi Tom,

I am spotting a performance bottleneck when performing Full Table Scan on all the tables. Take a small table example,

SQL> SELECT COUNT(*) FROM schema.frozen_woappoint;
COUNT(*)
----------
1767373

SQL> select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='FROZEN_WOAPPOINT';
SEGMENT_NAME SEGMENT_TYPE MB
---------------------------------------- ------------------ ----------
FROZEN_WOAPPOINT TABLE 144

I found it super slow (3 minutes) when loading to SSIS. To rule out other factors like driver / network / buffer, I perform simply local machine SQL*Plus query:

SQL> set termout off
SQL> set time off
16:40:58 SQL>
16:41:03 SQL> @speeding.sql
16:43:29 SQL>

Where speeding.sql is a SELECT * STATMENT and this table has no index.

So it still takes more than 2 minutes. I know it's always subjective to say fast or slow. But the observation is that on the DB machine, no matter CPU / RAM / IO / Network are all very idle with no bottleneck found. Therefore I performed a 10046 trace to look at it.

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 117826 15.17 15.43 17379 134063 0 1767373
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 117828 15.17 15.44 17379 134063 0 1767373

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 998
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1767373 1767373 1767373 TABLE ACCESS FULL FROZEN_WOAPPOINT (cr=134063 pr=17379 pw=0 time=9836353 us cost=4787 size=123716110 card=1767373)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 117826 0.00 0.65
direct path read 2 0.00 0.00
SQL*Net message from client 117826 73.68 261.22
********************************************************************************

It's direct path read, and most of the time spent on "SQL*Net message from client". From google and your web site I'm told this is 1) Network issue 2) Application design let DB waiting for its own input. But my case is local machine using local login and my client is the 64-bit SQL*Plus of the same ORACLE_HOME.

I have tried further tests:
1) arraysize 3000: several seconds gain only
2) force parallel query 4: No gain in elapsed time
3) alter session set db_file_multiblock_read_count = 0; : No gain in elapsed time
4) Bypass buffer cache -> Actually it's already direct path read but I tried it anyway but no luck

SQL> desc axis.frozen_woappoint;
Name Null? Type
----------------------------------------- -------- ----------------------------
WAP_JOB_PRE NOT NULL VARCHAR2(6)
WAP_JOB_NO NOT NULL VARCHAR2(10)
WAP_EMPNO VARCHAR2(10)
WAP_APPOINT_DATE DATE
WAP_ARRIVAL_DATE DATE
WAP_APPOINT_SLOT VARCHAR2(2)
WAP_STATUS VARCHAR2(2)
WAP_LINE_NO NOT NULL NUMBER(12)
WAP_PRINT_IND VARCHAR2(1)
WAP_PRINT_DATE DATE
WAP_CLIENT_REF VARCHAR2(10)
WAP_APPOINT_STATUS VARCHAR2(2)
WAP_TRADE VARCHAR2(4)
WAP_WORK_SLOTS NUMBER(4)
WAP_REASON VARCHAR2(4)
WAP_SCHEDULER_APPOINT_ID VARCHAR2(10)
WAP_SCHEDULER_WORKFLOW VARCHAR2(4)
WAP_CHANGED_DATE DATE
WAP_REVIEW_REQ VARCHAR2(1)
WAP_REVIEWED_BY VARCHAR2(20)
WAP_REVIEWED_DATE DATE

SQL>
DB Version: 12.1.0.2.0 No any patch applied
SGA: 6GB
PGA: 4GB
Windows 2008 R2
Virtual Machine with Xeon 4 Core + 16GB RAM

Actually I have seen all the tables have the same slow response time in FTS. I thought it's an IO issue but IO is mostly idle during testing. We're building a template for a Data warehouse and the data copy speed to staging is critical for the whole stack. Thank you for any comment / input you may have.