Executing a query for the output without doing the SQLNet round trips
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 02 Feb 2009 23:19:46 +0800 (SGT)
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>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
