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

This article introduces how to achieve the implementation of SQL statements through the SQL Id recorded in the AWR report, the content is very detailed, interested friends can refer to, hope to be helpful to you.

There are many ways to obtain sql's execution plan, such as interpretation and SQL tracking.

Here is another way to introduce you. This method can be easily combined with awr reports. For the resource-consuming sql statements found in the generated awr report, we can use the awrsqrpt.sql script provided by awr to obtain the sql statement execution plan.

1. The following is an example of how to get an execution plan for a sql statement with a sql ID of "8p23kcbgfqnk 4".

SQL > @? / rDBMS/admin/awrsqrpt.sql

Current instance

~

Database ID DB name inst num instance

--

1922648773 HSW 1 HSW

Specify the report type

~ ~ ~

Do you want a HTML report or a plain text report?

Enter 'html', as plain text' text' for the HTML report

Default is "html"

Enter the value of Report_type: Text

Specify type: text

Instances in this WorkloadRepository schema

~

The database ID is in the num DB name instance host

--

* 1922648773 1 HSW HSW HOUSW-LT

Database ID uses 1922648773

Use 1 as the instance number

Specifies the number of snapshot days to select from.

~ ~ ~

Entering the number of days (N) will result in the nearest

(n) list the number of days for the snapshot. Whether to press

Specifies a numeric list of all completed snapshots.

Enter the value of the num_d number: 3

List the snapshots completed in the last 3 days

Clasp

Instance DB name SnapID snapshot startup level

--

HSW 1 14 12:00 on September 1, 2010

September 2, 2010 13:00 1

September 3, 2010 14:00 1

September 4, 2010 15:00 1

16:04 on September 5, 2010

17:00 on September 6, 2010

7 September 2010 22:50 1

Specify start and end snapshot ID

~ ~

Enter a value for BEGIN_Snap: 6

Specified start snapshot ID:6

Enter the value of End_Snap: 7

Specified end Snapshot ID:7

Specify SQL ID

~

Enter the value of SQL_id: 8p23kcbgfqnk4

Specified SQL ID:8p23kcbgfqnk 4

Specify the name of the report

~ ~ ~

The default report file name is awrsqlrpt_1_6_7.txt. To use that name.

Press continue, otherwise enter an alternative.

Enter the value of Report_name: 8p23kcbgfqnk4

Use the report name 8p23kcbgfqnk 4

Workload Library SQL report

Summary during snapshot

DB name DBID instance Instnum publish RAC host

-

HSW 1922648773 HSW 1 10.2.0.3.0 No HOUSW-LT

Fast ID capture time session CURS/Sess

--

Start Snapshot: 6 14-10 September 17:00:12 15 2.3

End time: 7 14-10 September 22:50:29 17 2.3

Running time: 350.29 minutes (minutes)

DB time: 0.02 (min)

SQL Summary DB/Inst:HSW/HSW Snapshot: 6-7

Pass through

SQL ID time (MS)

--

8p23kcbgfqnk 4 19672

Select file #, block # and ts#, from seg$, where the type is # = 3

SQL ID:8p23kcbgfqnk4db/inst:HSW/HSW Snapshot: 6-7

First and last snapshot capture ID

Reference snapshot ID for use within snapshot scope

> Select file #, block # and ts#, from seg$, where the type is # = 3

The first capture of the total planned hash and the last capture

# value time (Ms) execute snapshot ID

1 1605285479 19672 1 7 7

Program 1 (PHV:1605285479)

Schedule Statistics DB/INST:HSW/HSW Snapshot: 6-7

> TotalDB time is the time that the SQL statement divides

The total time to the database multiplied by 100

Execute the STATNAME statement of% Snap each time

Time spent (milliseconds) 19672 19672.2 1351.7

CPU time (Ms) 32 32.3 79.6

Execute 1 N/A/N/A

Buffer acquisition 141 141.0 0.6

Disk read 0 0.0 0.0

Parse call 11.00.1

Line 0 0.0 NCMA

User I _ pico wait time (MS) 0 N _ A _ A _ A

Cluster wait time (MS) 0 N _ A _ A _ A

Application waiting time (Ms) 0 N / A / A / A

Concurrency wait time (MS) 0 N _ A _ A

Invalid 0 N _ A _ A _ A

Version count 1 NAccord A NAccord A

Shareable Mem (KB) 12 N Unip A N Unip A

Carry out the plan

\ x {e76f}\ x {e76f}

\ x {e76f}\ x {e76f

*

Full SQL text

SQL ID SQL text

--

8p23kcbgfqnk selects file #, block #, ts# from seg$where type # = 3

Report in writing to 8p23kcbgfqnk 4

two。 Summary

Knowing the SQL ID, we can use the awrsqrpt.sql script that comes with Oracle to quickly get the execution plan information of the SQL statement (the information shown in the above report is not limited to this).

On how to achieve the implementation of the SQL Id recorded in the AWR report to get the execution of SQL statements is shared here, I hope the above content can be of some help to 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.