| what's the relationship between the number of blocks and consistent gets ? [message #558185] |
Wed, 20 June 2012 01:48  |
 |
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
|
Member |
|
|
SQL> CREATE TABLE TEST(ID INT ,NAME VARCHAR2(10));
SQL> CREATE INDEX IND_IDN ON TEST(ID);
SQL> BEGIN
2 FOR I IN 1 .. 1000
3 LOOP
4 EXECUTE IMMEDIATE 'INSERT INTO TEST VALUES('||I||',''LONION'')';
5 END LOOP;
6 COMMIT;
7 END;
8 /
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST',CASCADE=>TRUE);
SQL> SELECT DISTINCT DBMS_ROWID.rowid_block_number(ROWID) BLOCKS FROM TEST;
BLOCKS
-----------
61762
61764
61763
>>above , there have 3 blocks in table TEST .</div></div>
SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT * FROM TEST;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 10000 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1000 | 10000 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics information
----------------------------------------------------------
0 recursive calls
0 db block gets
72 consistent gets >> there have 72 consistent gets
0 physical reads
0 redo size
24957 bytes sent via SQL*Net to client
1111 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> SELECT /*+ INDEX_FFS(TEST IND_IDN)*/ * FROM TEST WHERE ID IS NOT NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 10000 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1000 | 10000 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID" IS NOT NULL)
Statistics information
----------------------------------------------------------
1 recursive calls
0 db block gets
72 consistent gets >> there have 72 consistent gets
0 physical reads
0 redo size
17759 bytes sent via SQL*Net to client
1111 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> SELECT COUNT(*) FROM TEST;
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 1000 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------
Statistics information
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets >> there have 5 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT COUNT(*) FROM TEST WHERE ID IS NOT NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 735384656
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 4 | |
|
|* 2 | INDEX FAST FULL SCAN| IND_IDN | 1000 | 4000 | 2 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID" IS NOT NULL)
Statistics information
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets >> there have 5 consistent gets
0 physical reads
0 redo size
408 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT COUNT(ID) FROM TEST WHERE ID IS NOT NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 735384656
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 4 | |
|
|* 2 | INDEX FAST FULL SCAN| IND_IDN | 1000 | 4000 | 2 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID" IS NOT NULL)
Statistics information
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets >> there have 5 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
QUESTION :
what's the relationship between the number of blocks and consistent gets ? how to calculate the consistent gets ?
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
