trca工具的安装及使用

跟踪分析器，也称为TRCANLZR或TRCA，是由Oracle软件技术支持中心提供的一种工具。 TRCA输入事件10046生成的一个或多个SQL跟踪，并以两种格式（html和文本）输出诊断报告。 这些报告通常用于诊断表现不佳的进程。TRCA从提供的trc中识别出消耗高的SQL，然后连接到数据库并收集其解释计划，基于成本的优化器CBO统计信息，元数据，配置参数以及影响被分析过程的性能的类似元素。为获得最佳输出结果，必须在生成跟踪的相同系统和数据库中安装和使用TRCA。 但这不是强制要求。了解在生产系统中安装TRCA并不总是可行的，TRCA能够使用不同的系统分析跟踪。

SQL> create tablespace trca datafile '/oradata/ORCL/datafile/trca.dbf' size 100M;Tablespace created.SQL> start tracreate.sqlSP2-0310: unable to open file "tracreate.sql"SQL> start tacreate.sql  adding: 171123062856_02_tacusr.log (deflated 73%)Uninstalling TRCA, please waitTADOBJ completed.SQL> SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.About to DROP users TRCANLZR and TRCADMIN. Press RETURN to continue.SQL> SQL> REM If DROP USER command fails then a session is currently connected.SQL> PROSQL> DROP USER &&tool_repository_schema. CASCADE;old   1: DROP USER &&tool_repository_schema. CASCADEnew   1: DROP USER TRCANLZR CASCADESQL> DROP USER &&tool_administer_schema. CASCADE;old   1: DROP USER &&tool_administer_schema. CASCADEnew   1: DROP USER TRCADMIN CASCADESQL> SQL> WHENEVER SQLERROR CONTINUE;SQL> SQL> DROP ROLE &&role_name.;old   1: DROP ROLE &&role_name.new   1: DROP ROLE TRCA_USER_ROLESQL> SQL> SET ECHO OFF;TADUSR completed.TADROP completed.  adding: 171123063335_01_tacreate.log (deflated 90%)Define the TRCANLZR user password (hidden and case sensitive).Specify TRCANLZR password: Re-enter password: User TRCANLZR createdUser TRCANLZR alteredUser TRCADMIN createdUser TRCADMIN alteredSet up TRCANLZR temporary and default tablespacesBelow are the list of online tablespaces in this database.Decide which tablespace you wish to create the TRCANLZR tablesand indexes.  This will also be the TRCANLZR user default tablespace.Specifying the SYSTEM tablespace will result in the installationFAILING, as using SYSTEM for tools data is not supported.Wait...TABLESPACE                     FREE_SPACE_MB------------------------------ -------------TRCA                                      99ABCTEST                                   99OGG                                       99Above is the list of online tablespaces in this database.Decide which tablespace you wish to create the TRCANLZR tablesand indexes.  This will also be the TRCANLZR user default tablespace.Specifying the SYSTEM tablespace will result in the installationFAILING, as using SYSTEM for tools data is not supported.Tablespace name is case sensitive.Default tablespace [UNKNOWN]: TRCAChoose the TRCANLZR user temporary tablespace.Specifying the SYSTEM tablespace will result in the installationFAILING, as using SYSTEM for the temporary tablespace is not recommended.Wait...TABLESPACE_NAME------------------------------TEMPTablespace name is case sensitive.Temporary tablespace [UNKNOWN]: TEMPThe main application user of TRCA is the schemaowner that generated the SQL Trace to be analyzed.For example, on an EBS application you wouldenter APPS.You will not be asked to enter its password.To add more TRCA users after this installationis completed simply grant them the TRCA_USER_ROLErole.Main application user of TRCA: TESTType of TRCA repositoryCreate TRCA repository as Temporary or Permanent objects?Enter T for Temporary or P for Permanent.T is recommended and default value.Type of TRCA repository [T]: TACUSR completed.No errors.  adding: 171123063337_02_tacusr.log (deflated 80%)TAUTLTEST completed.  adding: 171123063603_09_tautltest.log (deflated 60%)TACOBJ completed.  adding: 171123063603_03_tacobj.log (deflated 85%)SQL> PRO Dropping Libraries for TRCADropping Libraries for TRCASQL> SET TERM OFF;tool_repository_schema: "TRCANLZR"tool_administer_schema: "TRCADMIN"role_name: "TRCA_USER_ROLE"Creating ProceduresCreating Package Specs TRCA$GNo errors.Creating Package Specs TRCA$PNo errors.Creating Package Specs TRCA$TNo errors.Creating Package Specs TRCA$INo errors.Creating Package Specs TRCA$ENo errors.Creating Package Specs TRCA$RNo errors.Creating Package Specs TRCA$XNo errors.Creating ViewsCreating Package Body TRCA$GNo errors.Creating Package Body TRCA$PNo errors.Creating Package Body TRCA$TNo errors.Creating Package Body TRCA$INo errors.Creating Package Body TRCA$ENo errors.Creating Package Body TRCA$RNo errors.Creating Package Body TRCA$XNo errors.Creating Grants on LibrariesTool Version----------------11.4.5.8Install Date----------------20171123Directories--------------------------------------------------------------------------------------------------------------------------------TRCA$INPUT1(VALID)      /u01/app/oracle/diag/rdbms/orcl/orcl/traceTRCA$INPUT2(VALID)      /u01/app/oracle/diag/rdbms/orcl/orcl/traceTRCA$STAGE(VALID)       /u01/app/oracle/diag/rdbms/orcl/orcl/traceuser_dump_dest          /u01/app/oracle/diag/rdbms/orcl/orcl/tracebackground_dump_dest    /u01/app/oracle/diag/rdbms/orcl/orcl/traceLibraries--------------------------------------------------------------------------------------------------------------------------------VALID PACKAGE TRCA$I /* $Header: 224270.1 tacpkgi.pks 11.4.5.0 2012/11/21 carlos.sierra $ */VALID PACKAGE TRCA$E /* $Header: 224270.1 tacpkge.pks 11.4.5.0 2012/11/21 carlos.sierra $ */VALID PACKAGE TRCA$G /* $Header: 224270.1 tacpkgg.pks 11.4.5.0 2012/11/21 carlos.sierra $ */VALID PACKAGE TRCA$P /* $Header: 224270.1 tacpkgp.pks 11.4.5.0 2012/11/21 carlos.sierra $ */VALID PACKAGE TRCA$R /* $Header: 224270.1 tacpkgr.pks 11.4.5.0 2012/11/21 carlos.sierra $ */VALID PACKAGE TRCA$T /* $Header: 224270.1 tacpkgt.pks 11.4.5.0 2012/11/21 carlos.sierra $ */VALID PACKAGE TRCA$X /* $Header: 224270.1 tacpkgx.pks 11.4.5.0 2012/11/21 carlos.sierra $ */VALID PACKAGE BODY TRCA$I /* $Header: 224270.1 tacpkgi.pkb 11.4.5.1 2012/11/27 carlos.sierra $ */VALID PACKAGE BODY TRCA$E /* $Header: 224270.1 tacpkge.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */VALID PACKAGE BODY TRCA$G /* $Header: 224270.1 tacpkgg.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */VALID PACKAGE BODY TRCA$P /* $Header: 224270.1 tacpkgp.pkb 11.4.5.8 2013/05/10 carlos.sierra $ */VALID PACKAGE BODY TRCA$R /* $Header: 224270.1 tacpkgr.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */VALID PACKAGE BODY TRCA$T /* $Header: 224270.1 tacpkgt.pkb 11.4.5.7 2013/04/05 carlos.sierra $ */VALID PACKAGE BODY TRCA$X /* $Header: 224270.1 tacpkgx.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */TACPKG completed.  adding: 171123063609_04_tacpkg.log (deflated 80%)TAUTLTEST completed.  adding: 171123063619_09_tautltest.log (deflated 59%)TRCA users must be granted TRCA_USER_ROLE before using this tool.TACREATE completed. Installation completed successfully.SQL> quitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@11g install]$ cd ../run/[oracle@11g run]$ sqlplus test/oracle_4USQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 23 06:38:02 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> START trcanlzr.sql orcl_ora_1832_10046.trcPL/SQL procedure successfully completed.Parameter 1:Trace Filename or control_file.txt (required)Value passed to trcanlzr.sql:~~~~~~~~~~~~~~~~~~~~~~~~~~~~TRACE_FILENAME: orcl_ora_1832_10046.trcAnalyzing orcl_ora_1832_10046.trcDEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)****** NOTE:*** If you get one of these errors it means TRCANLZR is not installed:***   PLS-00201: identifier 'TRCADMIN.TRCA$P' must be declared***   ORA-00904: "TRCADMIN"."TRCA$G"."VALIDATE_USER": invalid identifier*** In such case look for errors in NN_*.log files created during install.***... please wait ...To monitor progress, login into another session and execute:SQL> SELECT * FROM TRCADMIN.trca$_log_v;... analyzing trace(s) ...Trace Analyzer completed.Review first trcanlzr_error.log file for possible fatal errors.Review next trca_e30147.log for parsing messages and totals.Copying now generated files into local directoryTKPROF: Release 11.2.0.4.0 - Development on Thu Nov 23 06:39:26 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.TKPROF: Release 11.2.0.4.0 - Development on Thu Nov 23 06:39:26 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  adding: trca_e30147.html (deflated 91%)  adding: trca_e30147.log (deflated 85%)  adding: trca_e30147_nosort.tkprof (deflated 85%)  adding: trca_e30147_sort.tkprof (deflated 85%)  adding: trca_e30147.txt (deflated 88%)  adding: trcanlzr_error.log (deflated 82%)test of trca_e30147.zip OKdeleting: trcanlzr_error.logArchive:  trca_e30147.zip  Length      Date    Time    Name---------  ---------- -----   ----   185024  11-23-2017 06:39   trca_e30147.html    19022  11-23-2017 06:39   trca_e30147.log    26831  11-23-2017 06:39   trca_e30147_nosort.tkprof    26863  11-23-2017 06:39   trca_e30147_sort.tkprof    98424  11-23-2017 06:39   trca_e30147.txt---------                     -------   356164                     5 filesFile trca_e30147.zip has been createdTRCANLZR completed.SQL>quitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@11g run]$ lstrca_e30147.zip  trcanlzr.sql  trcasplit.sql[oracle@11g run]$  

解压压缩文件，可以看到既有html文件，又有tkprof文件

[oracle@11g run]$ unzip trca_e30147.zip Archive:  trca_e30147.zip  inflating: trca_e30147.html          inflating: trca_e30147.log           inflating: trca_e30147_nosort.tkprof    inflating: trca_e30147_sort.tkprof    inflating: trca_e30147.txt