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

This article will explain in detail how to use Oracle TRCA to analyze 10046 Trace RAW files, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Oracle 10046 is a commonly used performance diagnostic tool. It can record the details such as the content, time and object of each step during the execution of SQL in the Trace file.

10046 directly generated Trace files we call RAW format files, RAW format records all the steps of the operation details. Reading the RAW file directly can help us understand the Oracle behavior in more detail, but the RAW format is generally larger and less readable.

Tkprof is a gadget introduced with the 10046 Trace tool for working with files in RAW format. Tkprof can extract the tracked file steps into itemized statistics of each SQL statement, and sort them according to time and CPU. However, Tkprof based on the command line also has some shortcomings, such as the analysis of Recursive Call between SQL statements is not very strong, some details are missing, and so on.

Therefore, Oracle Support provides another way to analyze files in RAW format, which is the TRCA (Trace Analyzer) toolkit.

1. Install and uninstall

TRCA is a free tool provided by Oracle Support, and we can download the installation package and documentation from MOS 224270.1.

Unlike Tkprof, TRCA is a tool that requires additional installation in the database server. The running process requires additional tablespaces to correspond to accounts.

We first upload its zip package to the server directory and extract it to the directory.

[oracle@SimpleLinux upload] $ls-l | grep trca

Drwxr-xr-x 7 root root 4096 Oct 25 09:36 trca

Create a TRCA tablespace, keeping the tablespace size around 200M-500M as far as possible. If the storage is too small, it is easy to report an error when running the processing.

SQL > create tablespace trcatbl datafile size 100m autoextend on

2 extent management local uniform. Size 1m

3 segment space management auto

Tablespace created

[root@SimpleLinux upload] # chown-R oracle:oinstall trca

[root@SimpleLinux upload] # ls-l

Total 716

-rwxr-xr-x 1 oracle oinstall 3037 Oct 22 09:42 hugepages_settings.sh

Drwxr-xr-x 3 root root 4096 Oct 15 11:28 iftop-0.17

-rw-r--r-- 1 root root 716800 Oct 15 11:27 iftop-0.17.tar.gz

Drwxr-xr-x 7 oracle oinstall 4096 Oct 25 09:36 trca

Go to the trca directory, where you can run the installation script.

[oracle@SimpleLinux upload] $cd trca/

[oracle@SimpleLinux trca] $ls-l

Total 36

Drwxr-xr-x 2 root root 4096 Oct 25 09:36 dict

Drwxr-xr-x 2 root root 4096 Oct 25 09:36 doc

Drwxr-xr-x 2 root root 4096 Oct 25 09:36 install

Drwxr-xr-x 2 root root 4096 Oct 25 09:36 run

-rw-r--r-- 1 root root 16227 Oct 25 09:36 trca_instructions.html

Drwxr-xr-x 2 root root 4096 Oct 25 09:36 utl

[oracle@SimpleLinux trca] $cd install/

[oracle@SimpleLinux install] $sqlplus / nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 25 09:40:59 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL > conn / as sysdba

Connected.

SQL >

Execute the installation script tacreate.sql and answer the script questions according to the prompts and information.

SQL > @ tacreate.sql

Zip warning: name not matched: * _ ta*.log

Zip error: Nothing to do! (TRCA_installation_logs_archive.zip)

Below are the list of online tablespaces in this database.

Decide which tablespace you wish to create the TRCANLZR tables

And indexes. This will also be the TRCANLZR user default tablespace.

(for reasons of space, there are omissions. )

TRCA users must be granted TRCA_USER_ROLE before using this tool.

TACREATE completed. Installation completed successfully.

Note that there are more interceptions in the above code snippets. There are two issues to pay attention to during installation: the first is to enter the name of the TRCA tool tablespace and temporary tablespace, which will be listed by the installation script, and we should pay attention to the case when we type it. The other is that during installation, you need to enter an Oracle user name that can be used with the TRCA tool, and you can enter one here. But don't worry, you can give others permission by assigning the role TRCA_USER_ROLE to other users later.

SQL > grant TRCA_USER_ROLE to public

2

Grant succeeded.

At this point, the TRCA installation is complete.

Uninstalling TRCA is easy, as long as you execute the tadrop.sql script in the install directory separately.

[oracle@SimpleLinux install] $ls-l | grep tadrop

-rw-r--r-- 1 root root 1308 Oct 25 09:36 tadrop.sql

[oracle@SimpleLinux install] $sqlplus / nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 25 09:47:48 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL > conn / as sysdba

Connected.

SQL > @ tadrop.sql

Uninstalling TRCA, please wait

(for reasons of space, there are omissions. )

SQL >

SQL > SET ECHO OFF

TADUSR completed.

TADROP completed.

2. Preparation of Trace RAW experimental files.

In order to carry out the experiment, we first prepare a 10046 Trace file.

SQL > select value from v$diag_info where name='Default Trace File'

VALUE

/ u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_3121.trc

SQL > alter session set events' 10046 trace name context forever, level 12'

Session altered.

SQL > select count (*) from dba_objects

COUNT (*)

-

75296

SQL > alter session set events' 10046 trace name context off'

Session altered.

[oracle@SimpleLinux trace] $ls-l | grep 3121

-rw-r- 1 oracle oinstall 21352 Oct 25 09:06 ora11g_ora_3121.trc

-rw-r- 1 oracle oinstall 244 Oct 25 09:06 ora11g_ora_3121.trm

Additional note: 11g, after the Trace file is generated, there will be a corresponding trm file. According to MOS, the trm file is used in some of Oracle's official performance diagnostic tools, including some Metadata information. So, don't delete it easily.

3. Run TRCA analysis

Once you have the trace file directory, you can analyze it. TRCA is a performance analysis tool, but its own CPU and memory consumption is small. In principle, we should choose to use the Trace source database for analysis.

To run TRCA, you need to run the script for the run directory in sqlplus.

[oracle@SimpleLinux trca] $cd run/

[oracle@SimpleLinux run] $ls-l

Total 20

-rw-r--r-- 1 root root 8587 Oct 25 09:36 trcanlzr.sql

-rw-r--r-- 1 root root 7784 Oct 25 09:36 trcasplit.sql

Use a user with TRCA_USER_ROLE privileges to execute the program.

[oracle@SimpleLinux run] $sqlplus / nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 25 09:54:32 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL > conn / as sysdba

Connected.

SQL > @ trcanlzr.sql / u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_3121.trc

PL/SQL procedure successfully completed.

Parameter 1:

Trace Filename or control_file.txt (required)

TKPROF: Release 11.2.0.3.0-Development on Fri Oct 25 10:07:21 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

(for reasons of space, there are omissions. )

Adding: trca_e79866.html (deflated 90%)

Adding: trca_e79866.log (deflated 83%)

Adding: trca_e79866_nosort.tkprof (deflated 86%)

Adding: trca_e79866_sort.tkprof (deflated 86%)

Adding: trca_e79866.txt (deflated 87%)

Adding: trcanlzr_error.log (deflated 83%)

Test of trca_e79866.zip OK

Deleting: trcanlzr_error.log

Archive: trca_e79866.zip

Length Date Time Name

-

172492 10-25-13 10:07 trca_e79866.html

16093 10-25-13 10:07 trca_e79866.log

22867 10-25-13 10:07 trca_e79866_nosort.tkprof

22899 10-25-13 10:07 trca_e79866_sort.tkprof

88541 10-25-13 10:07 trca_e79866.txt

--

322892 5 files

File trca_e79866.zip has been created

TRCANLZR completed.

SQL >

Finally, on the processing directory, a new zip file appears, including the results of the RAW file processing.

[oracle@SimpleLinux run] $ls-l

Total 60

-rw-r--r-- 1 oracle oinstall 39283 Oct 25 10:07 trca_e79866.zip

-rw-r--r-- 1 oracle oinstall 8587 Oct 25 09:36 trcanlzr.sql

-rw-r--r-- 1 oracle oinstall 7784 Oct 25 09:36 trcasplit.sql

Note that during the process, there is a section that describes the result file of the process, including the processing log, the html and txt files for the result report. In addition, TRCA also integrates the functions of tkprof, including sorted and unsorted tkprof result files in the result file.

4. Analysis of TRCA results.

Compared with the processing results of tkprof, the content of TRCA is much richer, and the processing results are much more readable. In view of the common recursive call relationship in SQL, the output in TRCA html format has a good processing demonstration.

The first one among them is the statistics of response time consumed by SQL.

Also for the Recursive Call relationship, see which link consumes more during the execution of a SQL statement.

We can also view the execution plan and corresponding statistics for each SQL statement.

Note that TRCA has a processing threshold. Not every statement can appear in the report, only if the consumption reaches a certain level (10%).

With the help of many tools provided by Oracle, we can easily understand the problems encountered, analyze and deal with them. As a supplement to tkprof, TRCA has greatly improved the readability of the report and the degree of detailed information.

On how to use Oracle TRCA to analyze 10046 Trace RAW files to share 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.