Using Oracle Trace Analyzer (trcanlzr.sql)
Using Oracle Trace Analyzer (trcanlzr.sql)
Dave Moore: Author of Oracle Utilities
Oracle has provided another utility initially designed for performance tuning Oracle Applications. Trace Analyzer is provided in the form of a PL/SQL package (TRCA$ ). The Trace Analyzer utility is available via download on the Oracle Metalink web site.
How Oracle trace analyzer Works
Trace Analyzer requires that a one-time configuration be performed. During this configuration, many objects are installed in the database to serve as a tracing repository. Once downloaded from Metalink and installed, a SQL script can be executed passing in the name of the trace file.
Executing Oracle Trace Analyzer
First, tracing needs enabled at the appropriate level. For example, to provide maximum trace data, a Level 12 trace can be started for the current session:
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
After the session executes for enough time to gain needed data, the trcanlzr SQL script can be executed. It requires the name of the directory object. This object points to the physical operating system directory for the user_dump_dest. The installation of the utility will automatically create the directory object required (named UDUMP).
SQL>@d:\trcanlzr.sql UDUMP asg920xr_ora_13033.trc
Once executed, the output will be displayed on the screen and a spool file is created.
Is Oracle trace Analyzer better than tkprof?
Traditionally, tkprof has been the best tracing diagnostics tool available. That is, until the introduction of Trace Analyzer which is everything tkprof is and more. However, as of version 10.2, the Trace Analyzer utility is still not shipped with the Oracle DBMS like tkprof.
Given access to both utilities, Trace Analyzer has the following advantages:
1. Trace Analyzer provides the actual values of the bind variables in SQL. N
相关文档：
oracle的同义词总结：
从字面上理解就是别名的意思，和试图的功能类似。就是一种映射关系。
1.创建同义词语句：
create public synonym table_name for user.table_name;
其中第一个user_table和第二个user_table可以不一样。
此外如果要创建一个远程的数据库上的某张表 ......
1个实例
create table tjob2(tt date);
创建一个存储过程
create or replace procedure t26 is
begin
insert into tjob2 values(sysdate);
commit;
end t26;
创建job，每分钟执行一次
SQL> declare
2 tjob number;
3 begin
4 sys.dbms_jo ......
Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件，imp命令可以把dmp文件从本地导入到远处的数据库服务器中。 利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用……
Oracle数据导入导出imp/exp就相当� ......
分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
SQL> select t.empno,t.ename,sum(t.sal)
2 from emp t;
select t.empno,t.ename,sum(t.sal)
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> selec ......
第一种用法：
SELECT (CASE type WHEN 'u' THEN '用户表' WHEN 's' THEN '系统表' ELSE '其他' END) AS TABLEKIND,* from SYSOBJECTS
第二中用法：
SELECT (CASE WHEN crdate<'2006-01-01' THEN 'early' WHEN crdate<'2009-01-01' THEN 'nearly' ELSE 'now' END) AS PERIOD,* from SYSOBJECTS ......
