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

Recently, when watching some Oracle sharing, I often mentioned exporting the dump of ASH to other people for analysis, but I didn't know what kind of operation it was, so I read all kinds of blog posts on the Internet. So do your own experiment and do an export and import.

Experimental environment: Oracle 11.2.0.4+rhel 7.2

Execute the following statement to dump the ASH information

Sys@ORA11G > alter system set events' immediate trace name ashdump level 10'

In the above statement, level means to dump the last n minutes of data in the ASH buffer. Here, take 10 minutes as an example.

Type the corresponding trace file using the following statement

Sys@ORA11G > SELECT value FROM v$diag_info WHERE name = 'Default Trace File';VALUE-/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_7556.trc

Let's take a brief look at the contents of the trace file:

From the two figures above, you can see that the trace file contains three types of information: the basic information of trace, the method of importing dump and the actual data of ash. In fact, as long as you follow the method given by the file in trace, you can easily import dump data into the database.

1. Create a temporary table

CREATE TABLE ashdump ASSELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum

Ashldr.ctl

3. Import the data from the trace file into the temporary table using the sqlldr tool

[oracle@rhel7 ash] $sqlldr zx/zx control=ashldr.ctl data=ora11g_ora_7556.trc errors=1000000SQL*Loader: Release 11.2.0.4.0-Production on Sat Sep 23 17:16:40 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Commit point reached-logical record count 19Commit point reached-logical record count 37Commit point reached-logical record count 55Commit point reached-logical record count 73Commit point reached-logical record count 91Commit point reached-logical record count 109Commit point reached-logical record count 127Commit point reached-logical record count 145Commit point reached-logical record count 181Commit point reached-logical record count 199Commit point reached-logical record count 217Commit point reached-logical record count 235Commit point reached-logical record count 253Commit point reached-logical record count 271Commit point reached-logical record count 289Commit point reached- Logical record count 307Commit point reached-logical record count 325Commit point reached-logical record count 343Commit point reached-logical record count 361Commit point reached-logical record count 379Commit point reached-logical record count 397Commit point reached-logical record count 415Commit point reached-logical record count 415Commit point reached-logical record count 433Commit point reached-logical record count 469Commit point reached-logical record count 487Commit point reached-logical record count 505Commit point reached-logical record count 523Commit point reached-logical record count 541Commit point reached-logical record count 559Commit point reached-logical record count 577Commit point reached-logical record count 595Commit point reached-logical Record count 613Commit point reached-logical record count 631Commit point reached-logical record count 649Commit point reached-logical record count 651zx@ORA11G > select count (*) from ashdump COUNT (*)-650

After the successful import of the data, we can now do further analysis according to the specific problems.

In fact, the steps of export and import are very simple. Oracle gives the steps directly. The difficulty is how to use the data for further analysis after getting the data, so as to find out the root cause of the problem.

Analyze the number of sessions per sampletime as follows

Reference: http://www.eygle.com/archives/2009/08/howto_dump_ashinfo.html

Https://antognini.ch/2017/07/offline-analysis-of-ash-data-with-ashdump/

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.