Oracle 性能调优学习笔记(二)- ----Oracle SQL TRACE和TKPROF使用

Oracle SQL TRACE和TKPROF使用
启动sql trace 之后收集的信息包括
      1.解析、执行、返回数据的次数
      2.cpu和执行命令的时间
      3.物理读和逻辑读的次数
      4.系统处理的记录数
      5.库缓冲区错误

1.查看系统用户dump
show parameter dump;
background_dump_dest:存储alertlog和后台进程trace的.
user_dump_dest:存储用户的trace文件.
max_dump_file_size:跟踪文件的大小.

alter system set max_dump_file_size=500;
alter system set user_dump_dest=''; 

2.初始化SQL TRACE
  A.timed_statistics参数：(一般用户和dba)
      timed_statistics=true  允许sql trace 和其他的一些动态性能视图收集与时间有关的参数、
  系统级别:
      alter system set timed_statistics=true;
  会话级别:
     alter session set timed_statistics=true;
  查看dump的文件的大小
  B.sql_trace参数(dba权限可以修改)
     sql_trace=true表示启动跟踪,false表示禁用跟踪.
	   系统级别:
         alter system set sql_trace=true scope=spfile;
        会话级别:
         alter session set sql_trace=true;
		通过存储过程启动sql_trace
		execute rdbms_system.set_sql_trace_in_session(SID,SERIAL#,true);
		ps：9为SID，7为SERIAL# true为开启跟踪

		
 使用说明:
 1.用户dba用户登录
   sqlplus /nolog
   conn /as sysdba
 2.设置初始化跟踪参数
    alter session set timed_statistics=true;
	dba用户跟踪:
	alter session set sql_true=true;
	dba跟踪其他用户(利用sys跟踪tbs,sysdba登录):
	select sid,serial#,command,username 
	   from v$session
	     where username='TBS';
		 SID:121
		 SERIAL#:4786
	exec  dbms_system.set_sql_trace_in_session(121,4786,true);
	
	查看TBS用户的跟踪文件:
	用户跟踪文件的命名格式为:<oracle-SID>-ora-<PSID>.trc
	       备注:<oracle-SID>:oracle 的SID.
		        <PSID>:跟踪用户sid.
				
3.查找跟踪文件
     根据会话中用户的名称查找对应paddr,根据paddr查找对应的进程spid.
	select spid from v$process 
	     where addr = (
		       select paddr from v$session
					where username='TBS'
				);
    
   如果oracle-SID为ticket,spid=35572,那么对应的跟踪文件为: ticket-ora-35572.trc.
   然后使用TKPROF查看执行信息:
	  
	
TKPROF的使用
     tkprof 的目的是将sql trace 生成的跟踪文件转换成用户可以理解的格式.
	 tkprof可对.trc文件进行分析,并生成一个分析文件.并且在分析.trc文件,生
	 成分析文件时按各种选项进行排序,其中比较有用的一个排序选项是fchela，
	 即按照elapsed time fetching来对分析的结果排序（记住要设置初始化参数
	 time_statistics=true），生成的.prf文件将把最消耗时间的sql放在最前面
	 显示。另外一个有用的参数就是sys，这个参数设置为no可以阻止所有以sys
	 用户执行的sql被显示出来，这样可以减少分析出来的文件的复杂度，便于查看。


    1. 格式
      tkprof inputfile outputfile [optional | parameters ]
    参数和选项：
     explain=user/password 执行explain命令将结果放在SQL trace的输出文件中
     table=schema.table 指定tkprof处理sql trace文件时临时表的模式名和表名
     insert=scriptfile 创建一个文件名为scriptfile的文件，包含了tkprof存放的输出sql语句
     sys=[yes/no] 确定系统是否列出由sys用户产生或重调的sql语句
     print=number 将仅生成排序后的第一条sql语句的输出结果
     record=recordfile 这个选项创建一个名为recorderfile的文件，包含了所有重调用的sql语句
     sort=sort_option 按照指定的方法对sql trace的输出文件进行降序排序
             sort_option 选项
                prscnt  按解析次数排序
                prscpu  按解析所花cpu时间排序
                prsela  按解析所经历的时间排序
                prsdsk  按解析时物理的读操作的次数排序
                prsqry  按解析时以一致模式读取数据块的次数排序
                prscu   按解析时以当前读取数据块的次数进行排序
                execnt  按执行次数排序
                execpu  按执行时花的cpu时间排序
                exeela  按执行所经历的时间排序
                exedsk  按执行时物理读操作的次数排序
                exeqry  按执行时以一致模式读取数据块的次数排序
                execu   按执行时以当前模式读取数据块的次数排序
                exerow  按执行时处理的记录的次数进行排序
                exemis  按执行时库缓冲区的错误排序
                fchcnt  按返回数据的次数进行排序
                fchcpu  按返回数据cpu所花时间排序
                fchela  按返回数据所经历的时间排序
                fchdsk  按返回数据时的物理读操作的次数排序
                fchqry  按返回数据时一致模式读取数据块的次数排序
                fchcu   按返回数据时当前模式读取数据块的次数排序
                fchrow  按返回数据时处理的数据数量排序
通过TKPROF生成相关的文件:
tkprof ticket-ora-35572.trc ticket_tbs.prf aggregate=yes sys=no sort=fchela