Executing a query for the output without doing the SQLNet round trips
Date: Mon, 02 Feb 2009 23:19:46 +0800 (SGT)
Message-ID: <1233587986.49870f127b916_at_arrowana.singnet.com.sg>
I need a way to have Oracle execute a query but not send the output to my SQLPlus client -- ie I want to avoid the SQL*Net round trips. The larger SQL*Net round trips causes higher consistent gets.
In earlier versions, we could "wrap" a SELECT COUNT(*) around the query but the 10g optimizer is smart enough to realise that it doesn't need to send the whole output if we ask for a count(*).
I can get around the number of SQL*Net round trips by settin g a very high ARRAYSIZE but am looking for a way to do it in the query. Using a WITH and a MATERIALIZE hint also works but that makes the Explain Plan look ugly and I would have more explaining to do. Another option would be for the query to INSERT into a dummy table but my 'consistent gets'
The example below shows what I mean.
SQL> create table my_test_table as select * from dba_objects where object_id is not null;
Table created.
SQL> alter table my_test_table modify (owner not null, object_name not null);
Table altered.
SQL> create index my_test_table_ndx on my_test_table (owner, object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'MY_TEST_TABLE',estimate_percent=>100,method_opt=>'FOR COLUMNS OWNER, OBJECT_NAME size 250',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> select owner, object_type from my_test_table where owner = 'SYS';
22998 rows selected.
Execution Plan
Plan hash value: 965484217
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 22998 | 381K| 134 (3)| 00:00:02 | |* 1 | TABLE ACCESS FULL| MY_TEST_TABLE | 22998 | 381K| 134 (3)| 00:00:02 |
Predicate Information (identified by operation id):
1 - filter("OWNER"='SYS')
Statistics
1 recursive calls
0 db block gets
2210 consistent gets
0 physical reads
0 redo size
416617 bytes sent via SQL*Net to client
17355 bytes received via SQL*Net from client
1535 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22998 rows processed
SQL> select count(*) from ( select owner, object_type from my_test_table where owner = 'SYS' );
Execution Plan
Plan hash value: 2471429207
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 6 | 56 (4)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 6 | | ||* 2 | INDEX FAST FULL SCAN| MY_TEST_TABLE_NDX | 22998 | 134K| 56 (4)| 00:00:01 |
Predicate Information (identified by operation id):
2 - filter("OWNER"='SYS')
Statistics
1 recursive calls
0 db block gets
296 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set arraysize 5000
SQL> select owner, object_type from my_test_table where owner = 'SYS';
22998 rows selected.
Execution Plan
Plan hash value: 965484217
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 22998 | 381K| 134 (3)| 00:00:02 | |* 1 | TABLE ACCESS FULL| MY_TEST_TABLE | 22998 | 381K| 134 (3)| 00:00:02 |
Predicate Information (identified by operation id):
1 - filter("OWNER"='SYS')
Statistics
0 recursive calls
0 db block gets
707 consistent gets
0 physical reads
0 redo size
136810 bytes sent via SQL*Net to client
536 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22998 rows processed
SQL>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 02 2009 - 09:19:46 CST