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

Today, I will talk to you about how to analyze the slow writing problem of DBA_HIST_EVENT_HISTOGRAM positioning GPFS. Many people may not understand it very well. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1 problem

After receiving the monitoring alarm on September 1, the batch generation of files in August was slow and did not be completed in the window.

2 Analysis

The logic for generating batch files is simple, looping over a query statement and writing files using utl_file.put_line in turn (files are on the cluster file system GPFS).

Querying SQL execution plan, no exception was found.

Query gv$active_session_history and find that the session wait event is concentrated on "utl_file Iripple O":

Sql_id

Wait_class

Event

Count

5nddq6b1a4bbu

User I/O

Utl_file I/O

22708

5nddq6b1a4bbu

three hundred and ninety one

75m4xybvbvj7y

Concurrency

Os thread startup

three

75m4xybvbvj7y

seven hundred and thirty five

Other

Enq: PS-contention

four

The waiting time distribution of the corresponding utl_file Ithumb O wait event in the query dba_hist_event_histogram is as follows:

SNAP_ID

INSTANCE_NUMBER

EVENT_NAME

WAIT_TIME_MILLI

WAIT_COUNT

80837

one

Utl_file I/O

one

608614205

80837

one

Utl_file I/O

two

123584

80837

one

Utl_file I/O

four

970730

80837

one

Utl_file I/O

eight

25320

80837

one

Utl_file I/O

sixteen

three hundred and sixty three

80837

one

Utl_file I/O

thirty-two

ninety

80837

one

Utl_file I/O

sixty-four

sixteen

80837

one

Utl_file I/O

one hundred and twenty eight

fifty-six

80837

one

Utl_file I/O

two hundred and fifty six

one

80837

one

Utl_file I/O

five hundred and twelve

one

80837

two

Utl_file I/O

one

3069290

80837

two

Utl_file I/O

two

one

80837

two

Utl_file I/O

four

two

80837

two

Utl_file I/O

eight

one

80837

two

Utl_file I/O

thirty-two

five

80837

two

Utl_file I/O

sixty-four

8624

80837

two

Utl_file I/O

one hundred and twenty eight

17714

80837

two

Utl_file I/O

two hundred and fifty six

4315

80837

two

Utl_file I/O

five hundred and twelve

one hundred and eighteen

80837

two

Utl_file I/O

1024

six

It can be found from the above table that the waiting times wait_count of instance 1 decreases rapidly and steadily with the increase of waiting time wait_time_milli, while the waiting times of instance 2 wait_count does not decrease with the increase of waiting time wait_time_milli. There is an obvious peak of 17714 during wait_time_milli=128ms, and it is suspected that the write GPFS is slow.

3 Test verification

Through testing, compare the performance difference between writing local file system and writing GPFS file.

-- write local file system

Declare

G_file utl_file.file_type

Begin

Dbms_output.enable (null)

G_file: = UTL_FILE.fopen ('LOCAL_DIR','test20170805.txt','W')

For x in 1..1000000 loop

Utl_file.put_line (g_file, x | | rpad ('Xuejingjingjie 1000))

End loop

Utl_file.fclose (g_file)

End

/

-- write GPFS file system

Declare

G_file utl_file.file_type

Begin

Dbms_output.enable (null)

G_file: = UTL_FILE.fopen ('GPFS_DIR','test20170805.txt','W')

For x in 1..1000000 loop

Utl_file.put_line (g_file, x | | rpad ('Xuejingjingjie 1000))

End loop

Utl_file.fclose (g_file)

End

/

The test results are as follows:

Order

File size

Local file (sec)

GPFS file (sec)

Remarks

one

100MB

7.4

7.5

Open a new file, write

two

100MB

8.2

seventy-two

Reopen the undeleted original file, write to

three

1GB

seventy-four

seventy-five

Open a new file, write

four

1GB

seventy-five

seven hundred and fifty six

Reopen the undeleted original file, write to

five

1GB

seventy-four

six hundred and seventy six

Reopen the undeleted original file, write to

From the table above, you can see:

Rule 1: when writing the same file repeatedly, writing the GPFS file system is an order of magnitude slower than writing the local file.

Rule 2: if you write a new file, the write speed is about the same as the local file system

At this point, it is determined that the root cause of the problem is that the slow writing of GPFS caused the batch files not to be completed in the window.

After reading the above, do you have any further understanding of how to analyze the slow writing problem of DBA_HIST_EVENT_HISTOGRAM positioning GPFS? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.