Oracle 11G direct path read is very beautiful and hurtful, oracle11g

Oracle 11G direct path read is very beautiful and hurtful, oracle11g
Direct path read

In 11g, full table scan may use direct path read to bypass the buffer cache. Such full table scan is physical read.

In 10 Gbit/s, it is read through gc buffer, so there is no direct path read problem.

High direct path read may be caused:

1. A large number of disk sorting operations, such as order by, group by, union, distinct, and rollup, cannot be sorted in PGA. The temp tablespace must be used for sorting. When reading the sorting result from the temporary tablespace, direct path read is generated.

2. A large number of Hash Join operations use temp tablespace to save the hash partition.

3. Parallel Processing of SQL statements

4. full table scan for large tables: in the middle, the full table scan algorithm has new changes. Based on the table size, cache size, and other information, determine whether to bypass the SGA and directly read data from the disk Oracle11g. 10 Gb reads all data through the cache, which is called table scan (large ). 11g considers that direct path reading is used for a large table, which may be faster than hash reading (db file scattered reads) for data files in 10g, and less latch is used.

A large amount of direct path read wait time is most likely an application issue. The direct path read event is driven by SQL statements that execute direct read operations from temporary or conventional tablespaces. When the input content is greater than the work area in PGA, the SQL statement with the function to be sorted writes the sorting result to the temporary tablespace, And the sorting sequence strings in the temporary tablespace are then merged, used to provide the final result. When reading the sorting result, the Oracle session waits on the direct path read wait event. DB_FILE_DIRECT_IO_COUNT initialization parameters may affect the performance of direct path read.

An implicit parameter:

_ Serial_direct_read = false disable direct path read

_ Serial_direct_read = true enable direct path read

Alter sytem set "_ serial_direct_read" = never scope = both sid = '*'; can significantly reduce direct path read

It looks pretty when it brings another wait event, that is, to refresh the dirty data in the cache back to the data file. The check point event is triggered, and tasks written by DBWR are frequent.

A large number of read IO leads to slow IO, slow IO slows down DBWR writing, and the check point event will block DML. OLTP is a serious accident.

Because the application has a large number of full table query statements.

What is serial number read/write?

Direct path read/write

Generally, when Oracle directly reads data to the PGA process, this read does not need to go through SGA. The three parameters of the direct path read wait event are file number (absolute file number), first dba, and block cnt. In Oracle 10g/11g, this wait event is classified as User I/O.
Db file sequential read, db file scattered read, and direct path read are common methods of centralized data reading. The three methods are briefly described.

This type of reading is usually used in the following scenarios:
· Disk sorting I/O operations;
· Query subordinate processes in parallel;
· Pre-read operation.
The most common case is the first case. In the DSS System, it is normal to have a large number of direct path reads, but in the OLTP system, a significant direct path read usually means that the system application has problems, this results in a large number of disk sorting and reading operations.
Direct paht write usually occurs in Oracle Directly Writing data from PGA to data files or temporary files. This write operation can bypass SGA. The three parameters of the direct path write wait event are: file number (absolute file number), first dba, and block cnt. in Oracle 10g/11g, this wait event is classified as User I/O in the same way as direct path read.
Such write operations are generally used in the following scenarios:
· Direct path Loading;
· Parallel DML operations;
· Disk sorting;
· Write the Uncached "LOB" segment, and then record it as direct path write (lob) Wait.
Most common direct path writes are caused by disk sorting. For this write wait, we should find the data files with the most frequent I/O operations (if there are too many sort operations, it is likely to be temporary files), scattered load, accelerate write operations.
1. Disk sorting diagnosis:
If the system has too many disk sorts, temporary tablespace operations will occur frequently. In this case, you can consider allocating different temporary tablespace for different users and using multiple temporary files, write Data to different disks or bare devices to reduce competition and improve performance. For Oracle 8i databases, you should consider using temporary Local tablespace instead of dictionary management.
In this case, the sort_area_size can be appropriately increased before Oracle 9i; from Oracle 9i, pga_aggregate_target can be appropriately increased to reduce disk write for disk sorting, this improves system and application response. However, the application should be checked in time to check whether excessive sorting is caused by application problems, so as to fundamentally solve the problem.
2. Performance problems caused by parallel queries:
Sometimes, incorrect use of parallel queries in the application system may cause application problems. Statspack's Top 5 time event output shows that the direct path read consumes a high wait time, while the memory sorting rate is very high or even 100% (In-memory Sort %: 100.00) obviously, the Direct Path Read here is not caused by sorting. Note that another Wait event (KJC: Wait for msg sends to complete) is related to parallelism, therefore, it is preliminarily determined that the direct path read here may be related to parallelism.
Note: In the Statspack report, there is a performance indicator called In-memory Sort Ratio, which is used to measure the sorting operation of the system, this indicator is derived from two statistical information: sorts (disk) and sorts (memory:
In-memory Sort Ratio = sorts (... the remaining full text>

Oracle exists execution sequence

First,
The two query statements have different query results.
The first statement:
Select count (1) from dual where exists (SELECT 1 from xxx where yy is not null); the result can only be 1 or 0.
The second statement:
Select count (1) from xxx where yy is not null; the result IS the number of yy fields NOT empty in xxx table.

Second,
If you only judge whether the yy field in the xxx table has a non-null record, the efficiency of the first statement should be higher.
In my understanding, the block (if no index is available) of a table must be included in the buffer cache (not the direct path read), but some operations will be reduced when judgment is made, when the first yy is not null is determined, the following judgment operations will not continue.
Even so, I think the efficiency should not be much improved, and at most it will save the cpu usage.
If you want to know how the two statements are executed, you can start session 10046 event tracking to compare the similarities and differences between the two SQL statements.