Shulou(Shulou.com)05/31 Report--

This article shares with you the content of an example analysis of Oracle logical reading. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Physical read (physical read)

When a data block is read for the first time, it is cached in buffer cache, and the second time it is read and modified, it is buffer cache in memory. Here is an example:

1.1 first read:

C: "Documents and Settings" Paul Yi > sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0-Production on Thu Feb 28 09:32:04 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0-Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0-Production

SQL > set autotrace traceonly

SQL > select * from test

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)

1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

Statistics

175 recursive calls

0 db block gets

24 consistent gets

9 physical reads-9 physical readings

0 redo size

373 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

1 rows processed

1.2 second read

SQL > select * from test

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)

1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

Statistics

0 recursive calls

0 db block gets

7 consistent gets

0 physical reads-there is no physical read, read directly from buffer cache

0 redo size

373 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

1.3 data blocks are re-read into buffer cache, which occurs in the

If there is new data that needs to be read into Buffer Cache and Buffer Cache does not have enough free space, Oracle replaces the LRU data in the LRU linked list according to the LRU algorithm. When this data is accessed again, it needs to be re-read from disk.

SQL > alter session set events' immediate trace name flush_cache';-- clear the data buffer

Session altered.

SQL > select * from test

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)

1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

Statistics

0 recursive calls

0 db block gets

7 consistent gets

6 physical reads-physical reading has happened again

0 redo size

373 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

two。 Logical read (buffer read)

Logical reading refers to reading blocks from (or view from) Buffer Cache. According to the mode of accessing data blocks, it can be divided into instant read (Current Read) and consistent read (Consistent Read). Note: logical IO only reads logically, not writes logically.

Instant reading

Instant read reads the current latest data of the data block. There is only one current block in the Buffer Cache at any time. Instant reading usually occurs when data is modified or deleted. At this point, the process puts a row-level lock on the data and identifies the data as "dirty" data.

SQL > select * from test for update

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)

1 0 FOR UPDATE

2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

Statistics

0 recursive calls

1 db block gets

14 consistent gets

0 physical reads

252 redo size

386 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL >

Consistent reading

Oracle is a multi-user system. When a session starts to read data before it finishes reading, other sessions may modify the data it is about to read. If the session reads the modified data, it will cause data inconsistency. Consistency reading is to ensure the consistency of data. On all blocks in Buffer Cache, there is the SCN when the block was last modified. If a transaction needs to modify the data in the block, it first saves a copy of the pre-modified data and the block of SCN in the rollback segment, and then updates the data of the block in Buffer Cache and its SCN, and identifies it as "dirty" data. When other processes read the block, they first compare the SCN on the block with their own SCN. If the SCN on the data block is less than or equal to the SCN of the process itself, the data on the data block is read directly; if the SCN on the data block is greater than the SCN of the process itself, the modified block read data is found in the rollback segment. In general, normal queries are consistent reads.

Here is an example to help you understand the consistency reading:

In session 1:

SQL > select * from test

ID

-

one thousand

SQL > update test set id=2000

1 row updated.

In session 2:

SQL > set autotrace on

SQL > select * from test

ID

-

one thousand

Execution Plan

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=8)

1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=4 Bytes=8)

Statistics

0 recursive calls

0 db block gets

9 consistent gets when there is nothing to do update, 7 consistent gets means that there are 2 more consistent gets, which are to be obtained from the rollback segment.

0 physical reads

52 redo size

373 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL >

Logical read is a very important metric in Oracle performance tuning. It is not only easy to collect, but also can tell us a lot about the workload of the database engine. Logical reads are the number of blocks read from the cache when the SQL statement is executed.

Logical reading has four benefits in Oracle tuning:

The main results are as follows: (1) logical reading is restricted by the operation of CPU ability, so it well reflects the use of CPU.

(2) logical reads may lead to physical reads, so by reducing the number of logical reads, the number of Icano operations is likely to be reduced.

(3) logical reading is subject to serial operation. Since the optimization of multi-user load is often considered, minimizing logical reading will help to avoid scalability.

(4) the number of logical reads can be obtained at the SQL statement and execution plan level through SQL trace files and dynamic performance views.

The following is a detailed description of the relevant knowledge of logical reading, as a summary of their own learning.

As we all know, data block is the most basic read and write unit of oracle, but the data that users need is not the whole block, but the rows or columns in the block. When the user issues the SQL statement, the parsing and execution of the statement is completed, and the data fetching phase begins. At this stage, the server process will first read the data block from the data file into buffer cache. This process is called physical read. Physical reading, every time a block is read, it is a physical reading. When a block is sent to buffer cache, it cannot be passed to the user immediately, because what the user needs is not the whole block, but the rows in the block. The process of reading rows from the block of buffer cache is logical reading. To complete a logical read, the server process first looks for the buffer cache chain where the block resides in the hash table. Once found, you need to add a cache buffer chains latch to the chain. after the latch is successful, look for the specified block in the chain and add a pin lock on the block. And release the cache buffer chains latch. Then you can access the rows in the block. Instead of fetching all the qualified lines in the block at once, the server process fetches a certain number of rows at a time according to your crawl command. After these lines are fetched, they will be passed to the client user via PGA. Once the row is removed from the buffer cache, the session releases the PIN added to the block. Even if this logical reading is over. If you want to grab the remaining rows in the block, the server process will apply for the cache bufffer latch again. Add PIN to the block again. This is another logical reading. That is, every time the server process applies for a cache buffer latch, it is a logical read. The number of rows read per logical read can be set in the grab command.

Logical reads are closely related to Cache buffer chains latches, and it was mentioned in an article in TOM that every time a process applies for a Cache buffer chains latch, it is a logical read. However, logical reads are not the same as Cache buffer chains latches, and each logical read requires at least two Cache buffer chains latches in 9i. Logical reading refers to the process of locating blocks in the Hash table.

Here are my tests:

Step 1: create a test table:

Create table jj_one (id number (5), name char (40))

Step 2: insert 100 rows

Begin

For i in 1..100 loop

Insert into jj_one values (iMagnesia aaaaa')

End loop

End

/

Or: insert into jj_one select rownum,'aaa' from dba_objects where rownum select bk,max (id), min (id) from (select dbms_rowid.rowid_block_number (rowid) bk,id from jj_one) group by bk

BK MAX (ID) MIN (ID)

42594 81 1

42595 100 82

As you can see, the table occupies two blocks, with rows from 1 to 81 for ID in block 42594 and rows from 82 to 42595 for ID.

Step 4: the device batch read parameter is 15

Sid=10 pid=11 > set arraysize 15

Because the default value in 9i or 10g is 15, this step can be omitted if this setting has not been changed.

Step 5: view line 1:

Sid=11 pid=12 > set autot trace stat

Sid=11 pid=12 > select * from jj_one where id select * from jj_one where id select * from jj_one where id alter session set events' 10046 trace name context forever, level 14'

The session has changed.

Declare

Type mid is table of jj_one.id%type

Mid1 mid

Cursor c is select id from jj_one where id > = 1 and id alter session set events' 10046 trace name context off'

The session has changed.

Format the trace results with Tkprof:

E:/oracle/admin/mytwo/udump > tkprof mytwo_ora_756.trc m3.txt

View the M3.txt file:

Call count cpu elapsed disk query current rows

--

Parse 1 0.00 0.00 00 00

Execute 1 0.00 0.00 00 00

Fetch 1 0.00 0.00 0 3 0 15

--

Total 3 0.00 0.00 0 3 0 15

There are only three logical readings. Of these three logical reads, one is for the block in which the line is located, and the other two are for the head of the paragraph.

The experiment is over

As can be seen from the above experiments, in batch reading, the setting of batch size can affect the number of logical reads. The larger the batch size, the less logical reads will be if you read the same number of rows. And the fewer times the server interacts with the client, the less data can be transmitted over the network. Let's take a look at the test:

Batch size is 1:

Sid=11 pid=12 > set arraysize 1

Sid=11 pid=12 > select * from jj_one

Selected 100 rows.

Statistical information

54 consistent gets

7206 bytes sent via SQL*Net to client

911 bytes received via SQL*Net from client

51 SQL*Net roundtrips to/from client

100 rows processed

Batch size is 100:

Sid=11 pid=12 > set arraysize 100

Sid=11 pid=12 > select * from jj_one

Selected 100 rows.

Statistical information

6 consistent gets

1277 bytes sent via SQL*Net to client

372 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

100 rows processed

The difference is very obvious. The value of bytes sent via SQL*Net to client is about six times different.

But this does not mean that the higher the batch size, the faster the speed, otherwise, Oracle directly set it to a maximum value, not on the line, why let us adjust it ourselves!

After the row is read from the Buffer cache, it is cached in the PGA (specifically in the run-time zone of the cursor) and then passed to the client. If the batch size is too large, the memory footprint of the PGA and client will also increase. Moreover, if a large amount of data is transmitted intermittently on the network, it will also have a certain impact on the network. Let's take a look at the effect of batch size on PGA:

Perform the following procedure in session 11:

Declare

Type mid is table of t1.id%type

Mid1 mid

Cursor c is select id from t1

Begin

Open c

Loop

Fetch c bulk collect into mid1 limit 5000

Exit when c%notfound

End loop

Dbms_output.put_line (c%rowcount)

Close c

End

/

Observe the memory footprint of session 11 in another session:

Sid=10 pid=11 > @ pga-there are instructions at the bottom of this script

Enter a value for user: 11

Original value 7: and b.sid= & user

New value 7: and b.sid= 11

PGA Used PGA Alloc PGA Max

561508 779492 779492

Then change the batch size of the procedure in session 11 to 1:fetch c bulk collect into mid1 limit 5000; try again

Observe the PGA usage of session 11 in another session:

Sid=10 pid=11 > @ pga

Enter a value for user: 11

Original value 7: and b.sid= & user

New value 7: and b.sid= 11

PGA Used PGA Alloc PGA Max

184388 250668 250668

The memory footprint of a batch size of 5000 is about three times that of a batch size of 1. In addition, the test table must be larger, my test table is 1000000 rows, otherwise it is not easy to see the results. In 10G, basically the same results can be obtained.

The PGA.SQL script is as follows:

-- pga_by_process.sql:

SELECT

A.pga_used_mem "PGA Used"

A.pga_alloc_mem "PGA Alloc"

A.pga_max_mem "PGA Max"

FROM v$process a minute vanguard session b

Where a.addr = b.paddr

And b.sid= & user

Thank you for reading! This is the end of this article on "sample Analysis of Oracle Logic Reading". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.