CHAO_PING:
After another session update, the buffer gets of the SELECT statement will become better. This is understandable. Because Oracle needs to get the previous image of the Block he needs to get from other blocks.
However, this parameter here seems to be a bit south of the understanding: SQL> Select * from T where object_id = 100; Object_id Owner ------------------------- --------------- 100 CC Execution Plan ------------------------------- --------------------------- 0 SELECT Statement Optimizer = first_ROWS (COST = 2 card = 14 BYtes = 420) 1 0 Table Access (by INDEX ROWID) of 'T' (COST = 2 Card = 14 Ytes = 420) 2 1 Index (Range Scan) of 'IDX_T' (COST = 1 Rd = 14) statistics ------- -------------------------------------------------- - 0 recursive calls 0 db block gets 4 consistent gets 3 physical reads 0 redo size 424 bytes sent via SQL * Net to client 426 bytes received via SQL * Net from client 2 SQL * Net roundtrips to / from client 1 sorts (memory) 0 Sorts (Disk) 1 Rows Processed Expeates additional session, all UPDATE this table. Since here, SELECT: SQL> / Object_id Owner ------ ---- ------------------------------ 100 cc Execution Plan ------------ ---------------------------------------------- 0 SELECT Statement Optimizer = First_ROWS (COST = 2 card = 14 bytes = 420) 1 0 Table Access (by index rowid) of 't' (COST = 2 card = 14 bytes = 420) 2 1 index (Range scan) of 'idx_t' (Non -Unique) (COST = 1 Card =
14) STATISTICS ----------------------------------------------- ---------- 0 Recursive Calls 0 DB Block Gets 557 Consistent Gets 0 Physical Reads 52 Redo Size 424 BYTES SENT VIA SQL * NET To Client 426 Bytes Received Via SQL * Net from Client 2 SQL * Net RoundTrips To / from client 0 sorts (memory) 0 sorts (disk) 1 Rows Processed SQL> / Object_id Owner ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ 100 CC Execution Plan ---------------------------------- ------------------------ 0 SELECT Statement Optimizer = first_ROWS (COST = 2 card = 14 BYtes = 420) 1 0 Table Access (by index rowid) Of 't' (COST = 2 Card = 14 BYtes = 420) 2 1 Index (Range Scan) of 'IDX_T' (Non-Unique) (COST = 1 Card = 14) statistics ---------- ------------------------------------------------ 0 Recursive Calls 0 DB Block G ets 4 consistent gets 0 physical reads 0 redo size 424 bytes sent via SQL * Net to client 426 bytes received via SQL * Net from client 2 SQL * Net roundtrips to / from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / Object_id Owner ---------- ------------------------------ 100 cc Execution Plan - -------------------------------------------------- ------- 0 Select Statement Optimizer = first_ROWS (COST = 2 Card = 14 BYtes = 420) 1 0 Table Access (By Index RowID) of 'T' (COST = 2 Card =
14 BYtes = 420) 2 1 Index (Range Scan) of 'IDX_T' (Non-Unique) (COST = 1 Card = 14) statistics -------------------- -------------------------------------- 0 Recursive Calls 0 DB Block Gets 4 Construct Gets 0 Physical reads 0 redo size 424 bytes sent via SQL * Net to client 426 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 T; count (*) ---------- 6996 Execution Plan ----------------------------- ----------------------------- 0 Select Statement Optimizer = first_ROWS (COST = 2 Card = 1) 1 0 Sort (aggregate) 2 1 Table Access (Full) of 'T' (COST = 2 Card = 1307) Statistics ------------------------------- --------------------------- 0 Recursive Calls 12 DB Block Get 20 C onsistent gets 0 physical reads 0 redo size 368 bytes sent via SQL * Net to client 426 bytes received via SQL * Net from client 2 SQL * Net roundtrips to / from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
Here if Update is just rarely recorded, then the number of consistent gets on this side will be much smaller. This can be understood. Update Out of the table, why use this record, buffer gets will add so much? Standard SESSION UPDATE After: sql> / count (*) ---------- 6996 Execution Plan ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------ 0 Select Statement Optimizer = first_ROWS (COST = 2 Card = 1) 1 0 Sort (aggregate) 2 1 Table Access (Full) of 'T' (COST = 2 Card = 1307) Statistics ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------- 0 Recursive Calls 12 DB Block Gets 7033 Consistent Gets 0 Physical Reads 832 Redo Size 368 BYTES SENT VIA SQL * Net to Client 426 BYTES RECEIVED VIA SQL * NETROM Cliant 2 SQL * Net Roundtrips To / from Client 0 Sorts (Memory) 0 Sorts (Disk) 1 Rows Processed That session commit: SQL> / count (*) ---- ------ 6996 Execution Plan ----------------------------------------- ----------------- 0 SELECT Statement Optimizer = first_ROWS (COST = 2 Card = 1) 1 0 Sort (aggregate) 2 1 Table Access (Full) of 'T' (COST = 2 Card = 1307) statistics ----------- ---------------------------------------------- 0 Recursive Calls via SQL 12 db block gets 21 consistent gets 0 physical reads 60 redo size 368 bytes sent * Net to client 426 bytes received via SQL * Net from client 2 SQL * Net roundtrips to / from client 0 sorts (memory) 0 sorts (disk) 1 Rows Processed However, if you update other records (except Object_ID = 100), the location LOCK is located.
Quote:
SQL> Update T Set Owner = 'c' WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) <> 17470; 6380 Rows Updated. Execution Plan ----------------------- ----------------------------------- 0 Update Statement Optimizer = first_ROWS (COST = 2 card = 4 Bytes = 9 6) 1 0 Update of 'T' 2 1 Table Access (Full) of 'T' (COST = 2 Card = 4 Bytes = 96) Statistics ----------------- ----------------------------------------- 0 Recursive Calls 6530 DB Block Gets 19 Consistent gets 0 physical reads 1561860 redo size 622 bytes sent via SQL * Net to client 577 bytes received via SQL * Net from client 3 SQL * Net roundtrips to / from client 1 sorts (memory) 0 sorts (disk) 6380 rows processed
This SELECT cost is still constant SQL> Select * from T where object_id = 100; Object_id Owner ----------------------------------------------------------------------------------------------------------------------------------------- ---------- 100 ZC Execution Plan ------------------------------------ ---------------------- 0 Select Statement Optimizer = first_ROWS (COST = 1 Card = 1 Bytes = 3 0) 1 0 Table Access (by index rowid) OF 'T' (COST = 1 Card = 1 Bytes = 30) 2 1 Index (Range Scan) of 'Idx_t' (COST = 1 Card = 1) statistics ----------- ---------------------------------------------- 0 Recursive Calls via SQL 0 db block gets 4 consistent gets 0 physical reads 0 redo size 436 bytes sent * Net to client 504 bytes received via SQL * Net from client 2 SQL * Net roundtrips to / from client 0 sorts (memory) 0 sorts (disk) 1 Rows Processed If only Update changes a record of the Block Block, then buffer gets is added to 2: quote:
SQL> Update T set Owner = 'c' Where dbms_rowid.rowid_block_number (rowid) = 17470 and rownum = 1; 1 row updated. Execution Plan -------------------------------------------------------------------------------------------------------------- -------------------------------------- 0 Update Statement Optimizer = first_ROWS (COST = 2 Card = 1 bytes = 2 4) 1 0 Update of 'T' 2 1 count (stopkey) 3 2 Table access (full) of 't' (COST = 2 card = 1 Bytes = 24) statistics -------- -------------------------------------------------- 37 recursive calls 2 db block gets 8 consistent gets 0 physical reads 384 redo size 623 bytes sent via SQL * Net to client 588 bytes received via SQL * Net from 3 SQL * Net roundtrips client to / from client 1 sorts (memory) 0 sorts (Disk) 1 ROWS Processed
SQL> / Object_id Owner ---------- ------------------------------ 100 ZC Execution Plan - -------------------------------------------------- ------- 0 Select Statement Optimizer = first_ROWS (COST = 1 Card = 1 Bytes = 3 0) 1 0 Table Access (By Index RowID) of 'T' (COST = 1 Card = 1 BYtes = 30) 2 1 Index (Range Scan) of 'IDX_T' (Non-Unique) (COST = 1 Card = 1) statistics -------------------------- -------------------------------- 0 Recursive Calls 0 DB Block Gets 6 Consistent Gets 0 Physical Reads 52 Redo Size 436 BYtes SNT VIA SQL * NET To Client 504 Bytes Received Via SQL * Net from Client 2 SQL * Net Roundtrips To / from Clom Clom Clism 0 Sorts (Memory) 0 Sorts (Disk) 1 Rows Processed
If Update has all the records of this record, the consisteng gets will be the same result in the local record of Update: SQL> Update T set Owner = 'c' WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER (RowID) = 17470; 616 Rows Updated . Execution plan ----------------------------------------------- ----------- 0 Update Statement Optimizer = first_ROWS (COST = 2 Card = 1 Bytes = 2 4) 1 0 Update of 'T' 2 1 Table Access (Full) Of 'T' (COST = 2 card = 1 Bytes = 24) statistics ----------------------------------------- ----------------- 0 Recursive Calls 630 DB Block Gets 19 Consistent Gets 0 Physical Reads 150724 Redo Size 624 BYTES SENT VIA SQL * NET To Client 576 BYtes Received Via SQL * Net from Client 3 SQL * Net RoundTrips To / from Client 1 Sorts (Memory) 0 Sorts (Disk) 616 Rows Processed So SELECT Cost: SQL> / Object_id Owner --------------- ----------------------- 100 ZC EXECUTION Plan --------- ------------------------------------------------- 0 Select Statement Optimizer = first_ROWS (COST = 1 Card = 1 Bytes = 3 0) 1 0 Table Access (By Index RowID) of 'T' (COST = 1 Card = 1 Bytes = 30) 2 1 Index (Range Scan) of ' IDX_T '(Non-Unique) (COST = 1 Card =
1) Statistics ----------------------------------------------- ----------- 0 Recursive Calls 0 DB Block Gets 622 Consistent Gets 0 Physical Reads 52 Redo Size 436 BYTES SENT VIA SQL * NET to Client 504 Bytes Received Via SQL * Net from Clom Client 2 SQL * Net RoundTrips To / from client 0 Sorts (Memory) 0 Sorts (Disk) 1 Rows ProcessedBiti_Rainy Reply: Buffer Gets? Decided on the CR Block generated when generating consistent gets, select count (*) from x $ bh where state = 3; here The number of changes represents the generation of Cr Block Reconstruction. For Oracle, if you find a record, discovery has been changed, then copy the current block to a new place, retrieve the BEFORE Image from the rollback segment and rollback BLOCK, such block's State is 3, for each record in Block, it will be refactored once (discovery to be modified, copy to new Block (CR), then row ---> itl -> Transaction Table - -> undo record ---> ROLLBACK New BLCOK (CR)), so it is related to the number of records in one block. But here should pay attention, although many Crs have been refactored, in my previous test, I found a Block Cr Block in Select in Select, no more than 3 blocks. That is to say, the same DBA Cache Buffer Chain under the CR type under the Cache Buffer CHAIN Blocks do not exceed 3, I estimate that it is to save memory and reduce a policy of searching for time waiting for the latch (cache buffer chain). Because the Cr Block is now available once (actually this is the control of an Oracle hidden parameter)
Test is as follows
SQL> CREATE TABLE TEST (A Number); Table Created. SQL> INSERT INTO TEST SELECT ROWNUM from T where rownum <1001; 1000 rows created. Sql> commit; commit completion. Sql> select count (*) from T; Count *) ---------- 154986 SQL> Update test set a = 0 where a = 1; 1 row updated. SQL> Update test set a = 0 WHERE A <100; 99 rows updated. Sql> through Open Different SQLPlus Repeated Execution The following statement observes the change of the following X $ BH query table SQL> Select * from test where a <101; 100 rows selected. Execution Plan -------------------------------------------------------------------------------------------- -------------------------------------------- SELECT Statement Optimizer = choose 1 0 TABLE Access (Full) of 'Test' Statistics -------------------------------------- -------------------- 0 Recursive Calls 4 DB Block Gets 110 Consistent Gets 0 Physical Reads 52 Redo Size 2475 Bytes Sent Via Sql * Net To Client 1091 BYtes Received Via SQL * Net from Client 8 SQL * NET ROUNDTRIPS TO / FROM CLIE NT 0 Sorts (Memory) 0 Sorts (Disk) 100 Rows Processed SQL> SQL> SELECT TS #, File #, DBABLK, STATE, OBJ from x $ bh where state = 3 Order BY 1, 2, 3, 4;
TS # file # dbablk state obj --------------------------- ------- 0 1 133 3 4294967294 0 1 925 3 6 0 1 930 3 6 0 1 11579 3 10 0 1 11579 3 10 0 1 27273 3 34 0 1 33837 3 6 0 1 34264 3 8 0 1 34264 3 8 0 1 34264 3 8 0 1 34264 3 8 TS # file # dbablk State Obj ---------------------------- ------------------ 0 1 34268 3 8 0 1 34268 3 8 0 1 34268 3 8 0 1 34268 3 8 0 1 34768 3 2 0 1 34768 3 2 0 1 34788 3 18 2 3 3 24825 2 3 3 3 3 24825 2 3 3 3 3 24825 2 3 3 3 24825 TS # file # dbablk state obj --- ------------------------------------------ 2 3 3 3 24825 2 3 643 3 27155 2 3 643 3 27155
