Download Oracle Trace File Analyzer
Jocelin Gil
If Oracle Trace File Analyzer is already installed, then reinstalling performs an upgrade to the existing location. If Oracle Trace File Analyzer is not already installed, then the recommended location is /opt/oracle.tfa.
The trace file collects SQL statements as they are submitted for execution to the Oracle RDBMS. The SQL Trace includes all related information such as various timings (execute, fetch), row counts, total run times and more. The trace file also includes the wait events, or the work that the SQL statement has asked Oracle to do. When there is a performance issue, the DBA often looks at tools like AWR (automated workload repository; Toad for Oracle DBA edition also has a nice interface to this data) and can see the wait events that are keeping the Oracle database busy. SQL Trace allows for the SQL statements that are contributing to the wait event time to be captured.
The trace is created in a subdirectory where the Oracle software is installed. Typically. a developer does not have access to this trace file or may not even know the name of the trace file. Not a problem! Toad for Oracle retrieves and displays the trace file contents after the SQL is done executing.
The trace facility can be turned on and off. There is syntax to do this from within a program, from a command prompt, or from within Toad for Oracle DBA Edition. Why would you want this information for SQL performance tuning? To find the slowest SQL in a particular application. Trace can be turned on for the user session, for another session, or for the entire database (maybe Oracle Support might want this information). For the application user, turning SQL Trace on for an existing application experiencing performance issues allows for the SQL, all of its statistics and the executed Explain Plan to be captured.
TKProf is the interpreter for this trace file. Toad for Oracle has an excellent interface to this command line tool called TKProf Wizard. This wizard can be started for existing trace files from the menu item Database à Diagnose à TKProf Wizard. In the illustration above, the sort options allow for the SQL within the trace file to be put into this order. For example, if you are looking for long executing SQL, you would sort by Elapsed Time Executing (highlighted above) and the trace file would put the SQL and its related data in this order.
The above trace file shows the SQL statement, a grid of various statistics, the ACTUAL explain plan this SQL statement executed with (remember, pushing the Explain Plan button produces an Explain Plan but not necessarily the Explain Plan the SQL executed with), and the work this SQL statement asked the Oracle database to do, in the form of wait events.
Toad for Oracle DBA Edition can run the SQL Trace and its associated TKProf interpreter with lots of options. I cover useful Oracle trace files in my SQL Performance Tuning course, onsite, remote, or video on demand.
Toad for Oracle has the ability to run a SQL Trace right from the Editor window. Again, this saves the hassle of using the command prompt with lengthy and error prone syntax for both starting/stopping the trace and for running the TKProf tool.
As shown above, this SQL Trace is for the single SQL statement in the Editor window. You have to go to the Trace tab and check both of these boxes before executing the SQL. Toad for Oracle will turn on SQL Trace, pull the raw trace file to your workstation, then run the TKProf interface.
There are times when it would be nice to capture a series of SQL from an application. This can be difficult to do from the command line but easy within Toad for Oracle. This technique is a good option for capturing SQL performance issues and for running and reviewing the SQL before putting code into production. If you can capture the SQL in a trace file, you can find the problem SQL and get useful statistics and valuable Explain Plans all at the same time.
The Session Browser can view lock contention, kill the session (with proper permissions of course) and turn on/off SQL Trace. This will capture all the SQL from this user session until the trace is turned off.
When your test is complete, simply click the red button and Toad for Oracle will ask you if you want to retrieve your SQL Trace file. Click Yes. You can see above that my Toad for Oracle is the DBA edition because the Trace File Analyzer starts and the SQL Trace file is loaded. In this example, I have a command line SQL*Plus session going. I turned on SQL Trace using the green button, I ran the ABC example SQL we have been using in these lessons, then I turned off the trace using the red button.
Using this interface, you can sort the SQL easily, you can see the wait events and view the SQL that is associated with those wait events. Notice at the bottom, you can see the path and trace file name that was created by your request.
3. Now do the same on node 2: ( dbhost-2)root@dbhost-2:/export/home/software/AHF# ./ahf_setupAHF Installer for Platform SunOS Architecture SPARCAHF Installation Log : /tmp/ahf_install_202000_19808_2020_08_21-17_53_53.logStarting Autonomous Health Framework (AHF) InstallationAHF Version: 20.2.0 Build Date: 202006260723AHF is already installed at /opt/oracle.ahfInstalled AHF Version: 19.3.1 Build Date: 201911181744Do you want to upgrade AHF [Y]N : yLogin using root is disabled in sshd config. Installing AHF only on Local NodeUpgrading /opt/oracle.ahfShutting down AHF ServicesUpgrade Log File /gridapp/app/oracle.ahf/data/dbhost-2/diag/ahf/ahf_upgrade_21082020_17_54_36.logOld/New Lucene Versions - 8.1.1/8.1.1Lucene upgrade not requiredWARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.Starting AHF ServicesWARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.Starting TFA..Waiting up to 100 seconds for TFA to be started... . . . .. . . . .. . . . .. . . . .Successfully started TFA Process... . . . .WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.TFA Started and listening for commandsWARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.AHF sucessfully upgraded to latest version.---------------------------------------------------------------. Host TFA Version TFA Build ID Upgrade Status +---------+-------------+----------------------+----------------+ dbhost-2 20.2.0.0.0 20200020200626072308 UPGRADED dbhost-1 20.2.0.0.0 20200020200626072308 UPGRADED '---------+-------------+----------------------+----------------'root@dbhost-2:/export/home/software# tfactl status.-----------------------------------------------------------------------------------------------. Host Status of TFA PID Port Version Build ID Inventory Status +---------+---------------+-------+------+------------+----------------------+------------------+ dbhost-1 RUNNING 29271 5000 20.2.0.0.0 20200020200626072308 COMPLETE dbhost-2 RUNNING 29938 5000 20.2.0.0.0 20200020200626072308 COMPLETE '---------+---------------+-------+------+------------+----------------------+------------------' AHF setup has been completed for both nodes.
By using the functions in rdsadmin_adrci_util, you can list and package problems and incidents, and also show trace files. All functions return a task ID. This ID forms part of the name of log file that contains the ADRCI output, as in dbtask-task_id.log. The log file resides in the BDUMP directory. You can download the log file by following the procedure described in Downloading a database log file.
You can use the Amazon RDS function rdsadmin.rdsadmin_adrci_util.show_adrci_tracefile to list trace files under the trace directory and all incident directories under the current ADR home. You can also show the contents of trace files and incident trace files.
DBSophic's Trace Analyzer helps database administrators quickly analyze trace events stored in multiple trace files and trace tables. Within a few minutes, the software enables the user to view all the trace events analyzed, de-parameterized, grouped and clearly arranged in a tree format and ordered by their resource consumption. The results display the total resource consumption and enable the user to drill down to specific parameter value sets.
"Trace Analyzer eliminates the need to read hundreds of thousands of trace file events, thus saving time and resources," Ami Levin, chief technology officer for DBSophic, tells 5 Minute Briefing. "It allows DBAs to quickly identify operations and processes that cause slow database performance and enables them to easily pinpoint the batches or statements that are consuming the most resources. Furthermore, it enables easy drill down to specific executions with parameter value sets to quickly identify execution anomalies. Lastly, it enables DBAS to determine immediately which batches are overloading their servers, and provides an overall yet detailed view providing IT managers with the opportunity to see the whole picture."
df19127ead