Shulou(Shulou.com)06/01 Report--

Editor's Note: congratulations to Mr. Luo Haixiong on joining the Oracle ACE community. He is an expert in database SQL development and performance optimization, and he is also a senior moderator of ITPUB Forum. We have sorted out a document of AWR naked data analysis written by Mr. Luo for your reference (the same PPT and related source code downloads, please follow the official account reply: RollingPig

@? / rdbms/admin/awrrpt.sql-Standard report, overall performance report for a specific period of time

@? / rdbms/admin/awrddrpt.sql-comparison report, performance comparison over two time periods

@? / rdbms/admin/ashrpt.sql-- ASH report, historical session performance report for a specific period of time

@? / rdbms/admin/awrsqrpt.sql-- SQL report, SQL performance report for a specific time period

The AWR/ASH report is good, but it also has some flaws.

First of all, AWR reflects peer-to-peer data. For example, if I generate an AWR report from 9:00 to 12:00 today, what I see is the change between 12:00 and 9:00. However, we cannot see what 9:00-10:00 and 10:00-1100 respectively look like.

Another problem is that AWR lists all the data, but lacks links between the data.

AWR mixes a lot of useless data, so it takes 30 seconds to a few minutes to generate AWR reports, so if we have naked data, we can actually mine the performance information of Oracle database more efficiently and deeply.

In the naked data, there are four main types of indicators recorded.

The most common one is "cumulative value".

For example, logical reads of the database are recorded in dba_hist_sysstat. What is recorded is not the logical read generated in this hour, but the total logical read from the time the database was started to the time the snapshot was taken. This is called cumulative value, and most indicators are cumulative values.

There are also some data that record the "current value"

For example, the current PGA usage of the database, the number of sessions in the database, and, more specifically, the change between two snapshots. We can think of this as a pre-calculation, and the two most common types of data that record changes are SQL-related statistics and segment-related statistics. Of course, SQL/Segment records the changes as well as the cumulative values.

There is another category, which records "statistical values."

That is, the data over a period of time is saved after statistics, which are mainly METRIC data. For example, CPU per second, maximum waiting time per second, etc.

For DBA, the most important thing is to change the value.

The amount of change between two snapshots. This is a simple SQL to get the redo size information in the historical performance information of the database

Select SNAP_ID,STAT_NAME,VALUE from DBA_HIST_SYSSTAT

Where STAT_NAME='redo size' order by snap_id

What we are seeing now is the cumulative value. So, how to get the change value easily?

1. To get the change value, you need to take out the later record and subtract the previous record.

If it is only two time points, the easiest way is to access the table twice and then subtract it.

Select a.value-b.value

From DBA_HIST_SYSSTAT A,DBA_HIST_SYSSTAT B

Where A.STAT_NAME='redo size' and

A.STAT_NAME = B.STAT_NAME and a.snap_id = 123 and b.snap_id = 122

This is the difference between the two points, but it is not enough for us to play.

Sometimes, we want to get the change value between every two consecutive snapshots over a period of time. For example, from 9:00 to 21:00, we would like to get 9:00-10:00. 20:00-21:00, the change value for each time period.

Here we talk about the analysis function of Oracle.

Oracle's analysis function provides the ability to access data across rows within a result set. The LEAD/LAG in the parsing function is a powerful tool for getting data across rows.

LAG: data in the same group that precedes the current row

LEAD: data in the same group after the current row

As shown in the figure, you can see that what we want is to take the current value minus lag value.

Select snap_id,stat_name

Value-lag (value) over

(partition by stat_name order by snap_id)

From dba_hist_sysstat

Where stat_name = 'redo size'

Order by snap_id

This is the complete syntax of the parsing function LAG.

3. We are generally not satisfied with getting the change value of an index. The following table is what we want to get.

Another way to write advanced SQL is introduced here: column-to-column transformation.

You can understand how to use sum (case when.. Then.. End) or max (case when.. Then.. End), but using Case when to write column-column conversion is easy to make SQL lengthy and error-prone.

In Oracle 11g, it provides a more convenient way to convert rows and columns.

As you can see, PIVOT with yellow capitalization is a sharp tool for column-to-column conversion introduced in Oracle 11g. With PIVOT, the increase or decrease of indicators is extremely simple:

It is easy to add two indicators, if you think the list is not good-looking, you can also specify your own.

In fact, we can easily take out the "Load Profile" part of the AWR report through column conversion, and it is a number of continuously changing values.

Copy the results of the run to Excel, and it's easy to get a beautiful trend chart.

However, there is a problem with this chart: the REDO Size in the chart is in units of byte, and the value is too large, so that all other indicators are almost equal to zero, and multiple indicators have to go to the same chart, and they can also see their respective trends, which is very useful for the analysis of multi-index correlation.

At this time, another analysis function came out. Yes, because we are analyzing the performance data of Oracle, we need to use "analysis function" a lot.

Analysis function: Ratio_To_Report calculates the proportion of current row data in all data in the same group. For example, there are three rows in my result set, which are 1, 1, 3, and 6. So the row corresponding to 1 accounts for 10% of the total data (1 / 3 / 6), and the result is 0.1 (10%).

Select * from (

Select snaptime,RATIO_TO_REPORT (value) over (partition by stat_name) value,stat_name,snap_id

From (… ) PIVOT (sum (value) for stat_name in (

()) Order by snap_id

In this picture, everyone is equal, and it is more convenient to see whether there is a correlation between the various indicators.

Let me show you another SQL, or ratio_to_report. This time, the result we got is actually another very important data in the AWR report: Top Timed Events.

I put together the CPU time and non-idle events for each time period, and then calculate the percentage of each event (including CPU) in each time period, resulting in Top Timed Events, which is consecutive multiple time data.

When watching AWR, there are several areas that must be seen.

The first one is LOAD PROFILE.

Refer to the previous section used to demonstrate the lag () function:

Select * from (select snap_id,STAT_NAME

Value-lag (value) over (partition by STAT_NAME

Order by snap_id) value

From dba_hist_sysstat where stat_name in (

'redo size','execute count','DB time','physical reads'

) PIVOT (sum (value) for stat_name in (

'redo size','execute count','DB time','physical reads'

)) order by snap_id

Add the part of stat_name, plus other indicators of LOAD PROFILE, and you will have a complete load profile.

Through load profile, you can have an accurate understanding of the overall load of the system.

The second part, Top timed events, is the part of the most time-consuming waiting event (including CPU).

Through this section, you can understand the performance bottlenecks of the entire system:

Select snap_id,event,pct | |'% 'PCT,time from (

Select snap_id,event,round (time) time

Round (RATIO_TO_REPORT (TIME) over (partition by snap_id) * 100Pol 1) pct

From (select 'CPU Time' EVENT,snap_id,value/100-LAG (value) over (partition by stat_name order by snap_id) / 100 TIME

From DBA_HIST_SYSSTAT where stat_name = 'CPU used by this session'

Union all select event_name,snap_id, time_waited_micro/1e6-

LAG (time_waited_micro) over (partition by event_name order by snap_id) / 1e6

From DBA_HIST_SYSTEM_EVENT where waitresses classically qualified

) where time > 0) where pct > 1 order by snap_id,time desc

Generally speaking, knowing the system load and system bottleneck, we also need to understand the third part: Top SQL

Through Top SQL, we can see which major statements the system has run.

However, the Top SQL in the traditional AWR report is flawed. The main problem is that its information is scattered.

When judging SQL, I will combine multiple indicators. Execution time (elapsed Time), CPU time (CPU Time), logical read (Buffer gets), physical read (disk reads), number of execution (executions), number of rows returned (rows_processed), however, traditional awr reports, these metrics are distributed in different locations. It looks inconvenient. For example, there are execution time, number of execution times, CPU time. But there is a lack of logical reading, physical reading, the number of rows returned, and sometimes, you have to look for it.

So, I often access naked data and use SQL to extract Top SQL containing complete information directly from the database.

In addition, depending on the situation, we may be concerned about different points. For example, if the system consumes a lot of CPU, we are more concerned about SQL order by CPU, and when the SQL order by CPU is serious, we are concerned about physical reading. So the SQL I use can support fetching Top N SQL sorted by different indicators at the same time.

For example, Top 10 by elapsed time, Top 10 by CPU, Top 10 by disk reads.

As we all know, the traditional order by + rownum < N method only supports ranking one of the indicators, which is obviously not enough. And the analysis function once again played a role.

Select sql.*, (select SQL_TEXT from dba_hist_sqltext t)

Where t.sql_id = sql.sql_id and rownum=1) SQLTEXT

From (select a. *

RANK () over (order by els desc) as r_els

RANK () over (order by phy desc) as r_phy

RANK () over (order by get desc) as r_get

RANK () over (order by exe desc) as r_exe

RANK () over (order by CPU desc) as r_cpu

From (

Select sql_id,sum (executions_delta) exe,round (sum (elapsed_time_delta) / 1e6, 2) els

, round (sum (cpu_time_delta) / 1e6, 2) cpu

Round (sum (iowait_delta) / 1e6, 2) iow,sum (buffer_gets_delta) get

Sum (disk_reads_delta) phy,sum (rows_processed_delta) RWO

Round (sum (elapsed_time_delta) / greatest (sum (executions_delta), 1) / 1e6heli4) elsp

Round (sum (cpu_time_delta) / greatest (sum (executions_delta), 1) / 1e6, 4) cpup

Round (sum (iowait_delta) / greatest (sum (executions_delta), 1) / 1e6, 4) iowp

Round (sum (buffer_gets_delta) / greatest (sum (executions_delta), 1), 2) getp

Round (sum (disk_reads_delta) / greatest (sum (executions_delta), 1), 2) phyp

Round (sum (rows_processed_delta) / greatest (sum (executions_delta), 1), 2) ROWP

From dba_hist_sqlstat s

-where snap_id between... And...

Group by sql_id) a

) SQL where r_els

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.