Performance affected by number of columns in select list
We have noticed in 11.2.0.1 on AIX6.1 that the disk read throughput of SELECT statements is drammatically affected by the number of columns in the select list. We are running full table scans. If we select all columns of say, a 180 column table with simple, non-chained column types (NUMBERs, small VARCHAR2s) we are getting a paltry 14MB/s net disk read performance. As we reduce the column list, the performance increases until selecting a single column approaches the potential of the underlying disk sub-system - we see 180MB/s.
We are doing the select from a PL/SQL block which returns 100 row fetches. We do nothing (for the purpose of the test) with the rows so all is happening within the server process - no data transfer across the network.
Tracing shows no significant waiting on I/O or latches.
If we run create table as select * from table, the performance is also high.
As far as I am aware, Oracle reads complete blocks from disk into the buffer cache and then there will be some processing. I cannot understand why the disk reads are so affected by the column list - it's as though Oracle is having to wait until it can re-read.
We have cio on jfs2 with asynchronous I/O in effect - we see this in our truss analysis of the process. It's disk based I/O. Raw datafile doesn't alter anything and again, small column lists are performing well.
Anybody know why this should occur?