Re: Executing a query for the output without doing the SQLNet round trips
- From: Syed Jaffar Hussain <sjaffarhussain@xxxxxxxxx>
- To: hkchital@xxxxxxxxxxxxxx
- Date: Tue, 3 Feb 2009 09:14:48 +0300
How about playing around with SQL *Net Packet size parameters (SDU & TDU)?
Regards,
Jaffar
On Mon, Feb 2, 2009 at 6:19 PM, Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>wrote:
>
> 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
>
>
>
--
Best Regards,
Syed Jaffar Hussain
Oracle Certified Master (10g)
http://www.oracle.com/technology/ocm/shussain.html
Oracle ACE
http://apex.oracle.com/pls/otn/f?p=19297:4:1579866181463918::NO:4:P4_ID:126
OCP 8i,9i & 10g DBA
RAC Certified Expert
Official Oracle RAC SIG Representative for Saudi Arabian region (
http://www.oracleracsig.org/)
I blog at
http://jaffardba.blogspot.com/
--------------------
"Winners don't do different things. They do things differently."
Other related posts:
