Oracle wait event 3 construct a DirectPathwrite wait event and construct a LogFileSync wait event

Article 1 "oracle wait Event 1 generate dbfilescatteredread wait event by accessing table and index data respectively" leonarding. blog.51cto. com60455251105411 Article 2 oracle wait

Article 1 "oracle wait Event 1 generate dbfilescatteredread wait event by accessing table and index data respectively" http://leonarding.blog.51cto.com/6045525/1105411article 2 "oraclewait"

Article 1 oracle wait Event 1 generate db file scattered read wait events by accessing table and index data respectively
Article 2 oracle wait event 2 construct a DB File Sequential Read wait event and construct a Direct Path Read wait event
Article 3 oracle wait event 3 construct a Direct Path write wait event and construct a Log File Sync wait event

Dear All:
After five days of waiting for the oracle event series to complete, it is not easy for a to have a sleep night, how many endless guesses, here is the first write-off poem

2012 all-day thoughts

Outstanding oracle

Night light and hard work

Only for non-white teenagers

Reward the teacher for holding mm

5. Construct a Direct Path write wait event. Wait for the event to be displayed in the v $ session_wait and 10046 trace files and paste the entire demonstration process.

Direct path write wait event: When "write to disk" occurs, because write is also an intermediate state, as long as it is an intermediate state data, there is no need to share it with other users, therefore, this data will not be shared in SGA, and will be written directly from the PGA to the disk.

Scenario: append is used to insert data. when inserting data in this way, the data_buffer_cache of SGA will be skipped and the data file will be inserted directly, it will not scan the free space in the data file and insert it directly to the end of the file, so the efficiency is high.

When the data is sorted, if the PGA is full, the Hong Kong virtual host will be written to the temp tablespace of the disk. The U.S. server will not pass through the SGA because the sorted data is in the intermediate state, therefore, a "direct path write" Wait event is generated.

LEO1 @ LEO1> select table_name, index_name from user_indexes where table_name = 'leo1 '; Use the LEO1 table

TABLE_NAME INDEX_NAME

-------------------------------------------------------------------------

LEO1 IDX_LEO1

LEO1 @ LEO1> select count (*) from leo1; 71959 rows of records

COUNT (*)

------------------

71959

LEO1 @ LEO1> execute dbms_stats.gather_table_stats (ownname => 'leo1 ', tabname => 'leo1', cascade => TRUE );

PL/SQL procedure successfully completed. Statistical analysis

Shown in v $ session_wait View

LEO1 @ LEO1> set serveroutput on

LEO1 @ LEO1> create or replace procedure p4

As

Begin

For I in 1 .. 8

Loop

Insert/* + append */into leo1 select * from leo1;

Rollback;

End loop;

Dbms_output.put_line ('successfully ');

End;

/

2 3 4 5 6 7 8 9 10 11

Procedure created.
The storage process p4 is created for 8 cycles, and append hint is added for direct insertion. During this period, the "direct path write" Wait event will occur.

Append considerations

The Append method is called direct path loading. This method is used because oracle does not scan idle blocks in the freelist linked list and inserts data directly after the waterline, so the speed is faster.

(1) The append method does not record the redo. Once the data is not saved to the disk during the insertion process or the power is down, the inserted data cannot be recovered.

(2) because the table's free space is not used, this method is a waste of storage space.

(3) insert/* + append */into leo1 select * from leo1; a level 6 lock will be added to this table, this means that no insert update delete select or other operations are allowed in the session without commit/rollback. Otherwise, an error is reported.

ORA-12838: cannot read/modify a object after modifying it in parallel

Explanation: