Consistent gets are calculated based on session arraysize and Segment size. Watch what happens with different array size.
rajesh@ORA10GR2> create table t
2 nologging
3 as
4 select * from
5 all_objects;
Table created.
Elapsed: 00:00:04.90
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.62
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec show_space(USER,'T','TABLE');
l_total_blocks**************** 768
l_total_bytes***************** 6291456
l_unused_blocks*************** 60
l_unused_bytes**************** 491520
l_last_used_extent_file_id**** 7
l_last_used_extent_block_id*** 104713
l_last_used_block************* 68
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 0
l_fs2_bytes******************* 0
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 688
l_full_bytes****************** 5636096
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
rajesh@ORA10GR2>
The total size of the Table is 708 (768 - 60) Blocks.
rajesh@ORA10GR2> show arraysize;
arraysize 150
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2> select * from t;
50086 rows selected.
Elapsed: 00:00:00.23
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50086 | 4548K| 161 (3)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T | 50086 | 4548K| 161 (3)| 00:00:02 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1020 consistent gets
0 physical reads
0 redo size
2044854 bytes sent via SQL*Net to client
4059 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50086 rows processed
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
We set the arraysize to 150, so it is 150 row / fetch. So in total its 50068/150 = 333 fetches, and table segment size is 708 blocks so in total its 1041 which matches almost with consistent gets.
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> show arraysize;
arraysize 500
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from t;
50086 rows selected.
Elapsed: 00:00:00.23
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50086 | 4548K| 161 (3)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T | 50086 | 4548K| 161 (3)| 00:00:02 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
793 consistent gets
0 physical reads
0 redo size
2015263 bytes sent via SQL*Net to client
1496 bytes received via SQL*Net from client
102 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50086 rows processed
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select ( 50086/500) + 708 from dual;
(50086/500)+708
---------------
808.172
Elapsed: 00:00:00.03
rajesh@ORA10GR2>
As the arraysize increses consistent get's reduced. we get more row per fetch with increased array size.
rajesh@ORA10GR2> create index t_ind on t(owner,object_name,object_type);
Index created.
Elapsed: 00:00:00.82
rajesh@ORA10GR2> exec show_space(USER,'T_IND','INDEX');
l_total_blocks**************** 384
l_total_bytes***************** 3145728
l_unused_blocks*************** 20
l_unused_bytes**************** 163840
l_last_used_extent_file_id**** 7
l_last_used_extent_block_id*** 104969
l_last_used_block************* 108
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 1
l_fs2_bytes******************* 8192
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 349
l_full_bytes****************** 2859008
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
rajesh@ORA10GR2>
The total size of index T_IND is 364 (384 - 20) blocks.
rajesh@ORA10GR2> show arraysize ;
arraysize 500
rajesh@ORA10GR2>
rajesh@ORA10GR2> select owner,object_name,object_type
2 from t;
50086 rows selected.
Elapsed: 00:00:00.18
Execution Plan
----------------------------------------------------------
Plan hash value: 1176281950
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50086 | 1956K| 80 (3)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| T_IND | 50086 | 1956K| 80 (3)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
457 consistent gets
349 physical reads
0 redo size
1499995 bytes sent via SQL*Net to client
1496 bytes received via SQL*Net from client
102 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50086 rows processed
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select ( 50086/500) + 364 from dual;
(50086/500)+364
---------------
464.172
Elapsed: 00:00:00.03
rajesh@ORA10GR2>
Now, 464 matches with consistent gets again !
rajesh@ORA10GR2> show arraysize;
arraysize 2
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select owner,object_name,object_type
2 from t;
50086 rows selected.
Elapsed: 00:00:01.96
Execution Plan
----------------------------------------------------------
Plan hash value: 1176281950
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50086 | 1956K| 80 (3)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| T_IND | 50086 | 1956K| 80 (3)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25214 consistent gets
0 physical reads
0 redo size
4667629 bytes sent via SQL*Net to client
275858 bytes received via SQL*Net from client
25044 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50086 rows processed
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select ( 50086/2) + 364 from dual;
(50086/2)+364
-------------
25407
Elapsed: 00:00:00.03
rajesh@ORA10GR2>
Now again consistent gets matches for the arraysize 2.
rajesh@ORA10GR2> show arraysize;
arraysize 1
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select owner,object_name,object_type
2 from t;
50086 rows selected.
Elapsed: 00:00:01.95
Execution Plan
----------------------------------------------------------
Plan hash value: 1176281950
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50086 | 1956K| 80 (3)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| T_IND | 50086 | 1956K| 80 (3)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25214 consistent gets
0 physical reads
0 redo size
4667629 bytes sent via SQL*Net to client
275858 bytes received via SQL*Net from client
25044 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50086 rows processed
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select ( 50086/1) + 364 from dual;
(50086/1)+364
-------------
50450
Elapsed: 00:00:00.01
rajesh@ORA10GR2>
setting the arraysize=1 doesn't match with consistent gets, ofcourse if you set arraysize=1 internally it will be treated as arraysize=2
Now let's see what happens in case of multiple tables in a query.
rajesh@ORA10GR2> create table t1
2 as
3 select * from
4 all_objects;
Table created.
Elapsed: 00:00:00.90
rajesh@ORA10GR2>
rajesh@ORA10GR2> create table t2
2 as
3 select * from
4 user_objects;
Table created.
Elapsed: 00:00:00.07
rajesh@ORA10GR2>
rajesh@ORA10GR2> create table t3
2 as
3 select *
4 from all_objects
5 where object_type ='TABLE';
Table created.
Elapsed: 00:00:00.62
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.48
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T3');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec show_space(user,'T1','TABLE');
l_total_blocks**************** 768
l_total_bytes***************** 6291456
l_unused_blocks*************** 60
l_unused_bytes**************** 491520
l_last_used_extent_file_id**** 7
l_last_used_extent_block_id*** 105353
l_last_used_block************* 68
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 0
l_fs2_bytes******************* 0
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 688
l_full_bytes****************** 5636096
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> select 768 - 60 from dual;
768-60
----------
708
Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec show_space(user,'T2','TABLE');
l_total_blocks**************** 8
l_total_bytes***************** 65536
l_unused_blocks*************** 4
l_unused_bytes**************** 32768
l_last_used_extent_file_id**** 7
l_last_used_extent_block_id*** 104921
l_last_used_block************* 4
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 0
l_fs2_bytes******************* 0
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 1
l_full_bytes****************** 8192
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@ORA10GR2>
rajesh@ORA10GR2> select 8-4 from dual;
8-4
----------
4
Elapsed: 00:00:00.01
rajesh@ORA10GR2> exec show_space(user,'T3','TABLE');
l_total_blocks**************** 32
l_total_bytes***************** 262144
l_unused_blocks*************** 7
l_unused_bytes**************** 57344
l_last_used_extent_file_id**** 6
l_last_used_extent_block_id*** 2225
l_last_used_block************* 1
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 0
l_fs2_bytes******************* 0
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 21
l_full_bytes****************** 172032
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@ORA10GR2>
rajesh@ORA10GR2> select 32 - 7 from dual;
32-7
----------
25
Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> select t1.object_name,t2.object_type, t3.owner
2 from t1 ,t2, t3
3 where t1.object_name = t2.object_name
4 and t1.object_type = t2.object_type
5 and t3.object_name = t2.object_name
6 and t3.object_type = t2.object_type
7 /
11 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 98820498
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 1280 | 172 (3)| 00:00:03
|* 1 | HASH JOIN | | 16 | 1280 | 172 (3)| 00:00:03
|* 2 | HASH JOIN | | 16 | 736 | 11 (10)| 00:00:01
| 3 | TABLE ACCESS FULL| T2 | 16 | 240 | 3 (0)| 00:00:01
| 4 | TABLE ACCESS FULL| T3 | 1633 | 50623 | 7 (0)| 00:00:01
| 5 | TABLE ACCESS FULL | T1 | 50086 | 1663K| 160 (2)| 00:00:02
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME" AND
"T1"."OBJECT_TYPE"="T2"."OBJECT_TYPE")
2 - access("T3"."OBJECT_NAME"="T2"."OBJECT_NAME" AND
"T3"."OBJECT_TYPE"="T2"."OBJECT_TYPE")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
721 consistent gets
0 physical reads
0 redo size
706 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
rajesh@ORA10GR2>
rajesh@ORA10GR2> show arraysize;
arraysize 150
rajesh@ORA10GR2>
rajesh@ORA10GR2> select (11/150)+708+4+25 from dual;
(11/150)+708+4+25
-----------------
737.073333
Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2>
When pulling data from multiple tables, consistent gets are caculated based on arraysize and size of each segments involved in query execution.
JDBC Default's to arraysize of 10 even in 11GR2 server using ojdbc5.jar
create table t
nologging
as
select * from all_objects;
exec dbms_stats.gather_table_stats(user,'T');
import java.sql.*;
import oracle.jdbc.OracleDriver;
public class bind_test
{
public static void main(String args[]) throws Exception
{
Class.forName("oracle.jdbc.OracleDriver");
String url="jdbc:oracle:thin:@LDX0000040BN3Q1:1521:ora11gr2";
Connection conn=DriverManager.getConnection(url,"rajesh","oracle");
conn.setAutoCommit(false);
Statement stmt=conn.createStatement();
ResultSet rs1=stmt.executeQuery("alter session set timed_statistics=true");
ResultSet rs2=stmt.executeQuery("alter session set events '10046 trace name context forever, level 12'");
ResultSet rs=stmt.executeQuery("select * from T");
while (rs.next())
{
System.out.println(rs.getString(1));
}
stmt.close();
System.out.println("O.K");
}
}
And Tkprof show's me this
SQL ID: ahgbnyrbh7bp1
Plan Hash: 1601196873
select *
from
T
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 7154 0.42 0.34 0 8074 0 71537
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7156 0.42 0.34 0 8074 0 71537
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
71537 TABLE ACCESS FULL T (cr=8074 pr=0 pw=0 time=171609 us cost=287 size=6939089 card=71537)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 7155 0.00 0.01
SQL*Net message from client 7155 0.24 7.12
Disk file operations I/O 1 0.00 0.00
rajesh@ORA10GR2> create table t
2 nologging
3 as
4 select * from
5 all_objects;
Table created.
Elapsed: 00:00:04.90
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.62
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec show_space(USER,'T','TABLE');
l_total_blocks**************** 768
l_total_bytes***************** 6291456
l_unused_blocks*************** 60
l_unused_bytes**************** 491520
l_last_used_extent_file_id**** 7
l_last_used_extent_block_id*** 104713
l_last_used_block************* 68
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 0
l_fs2_bytes******************* 0
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 688
l_full_bytes****************** 5636096
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
rajesh@ORA10GR2>
The total size of the Table is 708 (768 - 60) Blocks.
rajesh@ORA10GR2> show arraysize;
arraysize 150
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2> select * from t;
50086 rows selected.
Elapsed: 00:00:00.23
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50086 | 4548K| 161 (3)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T | 50086 | 4548K| 161 (3)| 00:00:02 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1020 consistent gets
0 physical reads
0 redo size
2044854 bytes sent via SQL*Net to client
4059 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50086 rows processed
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
We set the arraysize to 150, so it is 150 row / fetch. So in total its 50068/150 = 333 fetches, and table segment size is 708 blocks so in total its 1041 which matches almost with consistent gets.
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> show arraysize;
arraysize 500
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from t;
50086 rows selected.
Elapsed: 00:00:00.23
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50086 | 4548K| 161 (3)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T | 50086 | 4548K| 161 (3)| 00:00:02 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
793 consistent gets
0 physical reads
0 redo size
2015263 bytes sent via SQL*Net to client
1496 bytes received via SQL*Net from client
102 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50086 rows processed
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select ( 50086/500) + 708 from dual;
(50086/500)+708
---------------
808.172
Elapsed: 00:00:00.03
rajesh@ORA10GR2>
As the arraysize increses consistent get's reduced. we get more row per fetch with increased array size.
rajesh@ORA10GR2> create index t_ind on t(owner,object_name,object_type);
Index created.
Elapsed: 00:00:00.82
rajesh@ORA10GR2> exec show_space(USER,'T_IND','INDEX');
l_total_blocks**************** 384
l_total_bytes***************** 3145728
l_unused_blocks*************** 20
l_unused_bytes**************** 163840
l_last_used_extent_file_id**** 7
l_last_used_extent_block_id*** 104969
l_last_used_block************* 108
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 1
l_fs2_bytes******************* 8192
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 349
l_full_bytes****************** 2859008
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
rajesh@ORA10GR2>
The total size of index T_IND is 364 (384 - 20) blocks.
rajesh@ORA10GR2> show arraysize ;
arraysize 500
rajesh@ORA10GR2>
rajesh@ORA10GR2> select owner,object_name,object_type
2 from t;
50086 rows selected.
Elapsed: 00:00:00.18
Execution Plan
----------------------------------------------------------
Plan hash value: 1176281950
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50086 | 1956K| 80 (3)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| T_IND | 50086 | 1956K| 80 (3)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
457 consistent gets
349 physical reads
0 redo size
1499995 bytes sent via SQL*Net to client
1496 bytes received via SQL*Net from client
102 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50086 rows processed
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select ( 50086/500) + 364 from dual;
(50086/500)+364
---------------
464.172
Elapsed: 00:00:00.03
rajesh@ORA10GR2>
Now, 464 matches with consistent gets again !
rajesh@ORA10GR2> show arraysize;
arraysize 2
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select owner,object_name,object_type
2 from t;
50086 rows selected.
Elapsed: 00:00:01.96
Execution Plan
----------------------------------------------------------
Plan hash value: 1176281950
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50086 | 1956K| 80 (3)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| T_IND | 50086 | 1956K| 80 (3)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25214 consistent gets
0 physical reads
0 redo size
4667629 bytes sent via SQL*Net to client
275858 bytes received via SQL*Net from client
25044 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50086 rows processed
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select ( 50086/2) + 364 from dual;
(50086/2)+364
-------------
25407
Elapsed: 00:00:00.03
rajesh@ORA10GR2>
Now again consistent gets matches for the arraysize 2.
rajesh@ORA10GR2> show arraysize;
arraysize 1
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select owner,object_name,object_type
2 from t;
50086 rows selected.
Elapsed: 00:00:01.95
Execution Plan
----------------------------------------------------------
Plan hash value: 1176281950
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50086 | 1956K| 80 (3)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| T_IND | 50086 | 1956K| 80 (3)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
25214 consistent gets
0 physical reads
0 redo size
4667629 bytes sent via SQL*Net to client
275858 bytes received via SQL*Net from client
25044 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50086 rows processed
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select ( 50086/1) + 364 from dual;
(50086/1)+364
-------------
50450
Elapsed: 00:00:00.01
rajesh@ORA10GR2>
setting the arraysize=1 doesn't match with consistent gets, ofcourse if you set arraysize=1 internally it will be treated as arraysize=2
Now let's see what happens in case of multiple tables in a query.
rajesh@ORA10GR2> create table t1
2 as
3 select * from
4 all_objects;
Table created.
Elapsed: 00:00:00.90
rajesh@ORA10GR2>
rajesh@ORA10GR2> create table t2
2 as
3 select * from
4 user_objects;
Table created.
Elapsed: 00:00:00.07
rajesh@ORA10GR2>
rajesh@ORA10GR2> create table t3
2 as
3 select *
4 from all_objects
5 where object_type ='TABLE';
Table created.
Elapsed: 00:00:00.62
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.48
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T3');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec show_space(user,'T1','TABLE');
l_total_blocks**************** 768
l_total_bytes***************** 6291456
l_unused_blocks*************** 60
l_unused_bytes**************** 491520
l_last_used_extent_file_id**** 7
l_last_used_extent_block_id*** 105353
l_last_used_block************* 68
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 0
l_fs2_bytes******************* 0
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 688
l_full_bytes****************** 5636096
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> select 768 - 60 from dual;
768-60
----------
708
Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec show_space(user,'T2','TABLE');
l_total_blocks**************** 8
l_total_bytes***************** 65536
l_unused_blocks*************** 4
l_unused_bytes**************** 32768
l_last_used_extent_file_id**** 7
l_last_used_extent_block_id*** 104921
l_last_used_block************* 4
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 0
l_fs2_bytes******************* 0
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 1
l_full_bytes****************** 8192
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@ORA10GR2>
rajesh@ORA10GR2> select 8-4 from dual;
8-4
----------
4
Elapsed: 00:00:00.01
rajesh@ORA10GR2> exec show_space(user,'T3','TABLE');
l_total_blocks**************** 32
l_total_bytes***************** 262144
l_unused_blocks*************** 7
l_unused_bytes**************** 57344
l_last_used_extent_file_id**** 6
l_last_used_extent_block_id*** 2225
l_last_used_block************* 1
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 0
l_fs2_bytes******************* 0
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 21
l_full_bytes****************** 172032
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
rajesh@ORA10GR2>
rajesh@ORA10GR2> select 32 - 7 from dual;
32-7
----------
25
Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> select t1.object_name,t2.object_type, t3.owner
2 from t1 ,t2, t3
3 where t1.object_name = t2.object_name
4 and t1.object_type = t2.object_type
5 and t3.object_name = t2.object_name
6 and t3.object_type = t2.object_type
7 /
11 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 98820498
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 1280 | 172 (3)| 00:00:03
|* 1 | HASH JOIN | | 16 | 1280 | 172 (3)| 00:00:03
|* 2 | HASH JOIN | | 16 | 736 | 11 (10)| 00:00:01
| 3 | TABLE ACCESS FULL| T2 | 16 | 240 | 3 (0)| 00:00:01
| 4 | TABLE ACCESS FULL| T3 | 1633 | 50623 | 7 (0)| 00:00:01
| 5 | TABLE ACCESS FULL | T1 | 50086 | 1663K| 160 (2)| 00:00:02
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME" AND
"T1"."OBJECT_TYPE"="T2"."OBJECT_TYPE")
2 - access("T3"."OBJECT_NAME"="T2"."OBJECT_NAME" AND
"T3"."OBJECT_TYPE"="T2"."OBJECT_TYPE")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
721 consistent gets
0 physical reads
0 redo size
706 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
rajesh@ORA10GR2>
rajesh@ORA10GR2> show arraysize;
arraysize 150
rajesh@ORA10GR2>
rajesh@ORA10GR2> select (11/150)+708+4+25 from dual;
(11/150)+708+4+25
-----------------
737.073333
Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2>
When pulling data from multiple tables, consistent gets are caculated based on arraysize and size of each segments involved in query execution.
JDBC Default's to arraysize of 10 even in 11GR2 server using ojdbc5.jar
create table t
nologging
as
select * from all_objects;
exec dbms_stats.gather_table_stats(user,'T');
import java.sql.*;
import oracle.jdbc.OracleDriver;
public class bind_test
{
public static void main(String args[]) throws Exception
{
Class.forName("oracle.jdbc.OracleDriver");
String url="jdbc:oracle:thin:@LDX0000040BN3Q1:1521:ora11gr2";
Connection conn=DriverManager.getConnection(url,"rajesh","oracle");
conn.setAutoCommit(false);
Statement stmt=conn.createStatement();
ResultSet rs1=stmt.executeQuery("alter session set timed_statistics=true");
ResultSet rs2=stmt.executeQuery("alter session set events '10046 trace name context forever, level 12'");
ResultSet rs=stmt.executeQuery("select * from T");
while (rs.next())
{
System.out.println(rs.getString(1));
}
stmt.close();
System.out.println("O.K");
}
}
And Tkprof show's me this
SQL ID: ahgbnyrbh7bp1
Plan Hash: 1601196873
select *
from
T
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 7154 0.42 0.34 0 8074 0 71537
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7156 0.42 0.34 0 8074 0 71537
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
71537 TABLE ACCESS FULL T (cr=8074 pr=0 pw=0 time=171609 us cost=287 size=6939089 card=71537)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 7155 0.00 0.01
SQL*Net message from client 7155 0.24 7.12
Disk file operations I/O 1 0.00 0.00
Hi Rajesh,
ReplyDeleteFantastic article and making calculations look so simple and easy to understand.. A long mystery resolved...
Rajesh..any idea on how PR is calculated.. currently I am noticing in SQL*Plus that PR is around equal to the Blocks in the table.. => 1 block is being fetched to Buffer per visit to Disk.. Is there any parameter setting in SQL*Plus that described this behavior.
PR - Physical Reads happen in two cases.
Delete1) Reading your data in from datafiles - Doing I/O to the datafiles to retrieve index and table data. These operations will be followed immediately by a logical I/O to the cache
2) Direct reads from TEMP - This is in response to sort area or hash area not being able to support the entire sort or hash in memory. Oracle is forced to swap data to Temp and read it back later. These physical reads bypass the buffer cache and will not incure a logical IO.