ORACLE 10 JDBC SQL跟踪示例

ORACLE 10 JDBC SQL跟踪示例
1、查询v$parameter视图，查看以下参数的值：
timed_statistics(动态参数，控制计时是否可用，设为TRUE,计时信息会添加到跟踪文件中)
如果为false进行设置
alter session set timed_statistics = TRUE;

max_dump_file_size(跟踪文件大小，一般不用调整)
alter session set max_dump_file_size = 45K;
alter session set max_dump_file_size = 45M;
alter session set max_dump_file_size = unlimited;(不做限制)

确定跟踪文件的位置：
user_dump_dest(专有服务器进程创建的跟踪文件所在目录)
background_dump_dest(后台进程创建的跟踪文件所在的目录)
select name,value from v$parameter where name like '%dump\_dest' escape '\';

background_dump_dest /home/oracle/oracle/product/10.2.0/db_2/admin/test/bdump

user_dump_dest /home/oracle/oracle/product/10.2.0/db_2/admin/test/udump


11g使用diagnostic_dest初始化参数

开启跟踪事件（10064）
alter session set events '10064 trace name context forever, level 12';（开启）
alter session set events '10064 trace name context off';(关闭)

在ORACLE 10G可以使用如下方法:
dbms_monitor.session_trace_enable;没有指定参数，使用默认参数，表示对当前调用的会话开启
waits:默认为TRUE,binds 默认为FALSE。
dbms_monitor.session_trace_enable(session_id =>127,
serial_num =>29,
waits =>TRUE,
binds =>FALSE)

关闭
dbms_monitor.session_trace_disable(session_id =>127,
serial_num =>29)

dbms_monitor默认只能被sys用户执行
grant execute on dbms_monitor to user;


public Object doInJdbc(Connection conn) throws Exception{
String[] strArr = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
//设置客户端、模块、操作信息，可以对具体的模块或是操作启用SQL跟踪
//dbms_monitor.client_id_trace_enable(client_id=>'dj.client',waits=>TRUE,binds=>FALSE);
//dbms_monitor.client_id_trace_disable(client_id=>'dj.client');
//dbms_monitor.serv_mod_act_trace_enabl(service_name=>'test',没有默认值
// module_name=>'test.module',默认值为any_module
// action_name=>'test.action',默认值为any_action
// waits=>TRUE,
// binds=>FALSE,
// instance_name=>NULL)RAC环境下需要指定具体的实例
strArr[OracleConnection.END_TO_END_CLIENTID_INDEX] = "dj.client";
strArr[OracleConnection.END_TO_END_ACTION_INDEX] = "test.action";//操作
strArr[OracleConnection.END_TO_END_MODULE_INDEX] ="test.module";//模块

OracleConnection oc = (OracleConnection)conn;
oc.setEndToEndMetrics(strArr, (short)0);

CallableStatement callStmt = conn.prepareCall("{call dbms_monitor.session_trace_enable}");
callStmt.execute();

boolean flag = stmt.execute("alter session set tracefile_identifier = 'arer'");//设置跟踪文件方便查找
System.out.print("set trace_file_identifier success:"+flag);//flag为false，原因待查，但是能设置上。

String sql = "select c_id,c_name,d_booking from t_eg_book where c_name like '%孔%'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);

rs.close();
stmt.close();
callStmt.close();



return null;
}

根据生成的跟踪文件，提取自己需要的信息:
trcsess [output=<output file name>] [session=<session ID>][clientid=<clientid>][action=<>][module=<>]

比如从test_ora_12489_arer.trc中提取action为test.action的信息,输出到test_ora_12489.trc2

trcsess output=test_ora_12489.trc2 action="test.action" test_ora_12489_arer.trc;

下面对跟踪文件进行分析：
把分析结果输出到test_ora_12489.txt.
tkprof test_ora_12489.trc test_ora_12489.txt aggregate=no
tkprof参数：
tkprof tracefile outputfile [explain=<user/passwd@url>] [table=][print=<num 打印SQL条数>][aggregate=yes|no]
aggregate指定是否单独处理同样内容的SQL语句。默认情况下不单独处理，设为no表示进行单独处理。


跟踪文件格式解释：
len : Length of SQL statement
dep: Recursive SQL depth
 0 = External / client-originated SQL
 >0 = SQL originated within database*
uid : Parsing user ID
tim : Relative timestamp
 <Oracle9i = 10 millisecond (=1/100 sec.)
 >Oracle9i = microsecond
hv: Hash value
ad: Address (in V$SQLAREA)
err : Error reported
c: CPU time*
 <Oracle9i = 10 millisecond (=1/100 sec.)
 >Oracle9i = microsecond
e: Elapsed time*
p: Number of physical reads
oct : Oracle command type
 2 = INSERT
 3 = SELECT
 6 = UPDATE
 7 = DELETE
og: Optimizer goal
 1 = All_Rows
 2 = First_Rows
 3 = Rule
 4 = Choose
cr: Logical reads (consistent)
cu: Logical reads (current; e.g., “select for update...”)
mis: Cursor missed in the cache (motivates hard parse)
r: Number of rows processed
p1,p2,p3: Wait event parameters
dty: Bind variable data type
 1 = VARCHAR
 2 = NUMBER
 12 = DATE
 96 = CHAR
--------------------------------------------
查看是否有统计信息：
select * from DBA_tab_col_statistics where table_name='';
select * from dba_indexes where table_owner='' and index_name=upper('');
select * from dba_ind_statistics where table_owner='' and index_name=upper('');

user_tab_col_statistics

select histogram from DBA_tab_col_statistics where table_name='';
取值有：none:表示没有直方图信息，
frequency:使用频度直方图，不同值的数目应该小于254
HEIGHT BALANCED：等高直方图，不同值的数据大于254

直方图最多统计254桶（bucket）
查看直方图信息:
select * from dba_tab_histograms

收集某个表中的统计信息
dbms_stats.gather_table_stats(ownname =>'myname' ,tabname =>'mytable' ,method_opt =>'FOR ALL INDEXED COLUMNS size 254' );


OPTIMIZER_INDEX_CACHING
这个初始化参数代表一个百分比，取值范围在0到99之间。缺省值是0，代表当CBO使用索引访问数据时，在内存中发现数据的比率是0%，这意味着通过索引访问数据将需要产生物理读取，代价昂贵。如果使用缺省设置，Oracle评估成本的时候，很多时候就会错误的选择全表扫描。


OPTIMIZER_INDEX_COST_ADJ
这个初始化参数代表一个百分比，取值范围在1到10000之间。该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等于全表扫描。

这些参数对于CBO的执行具有重大影响，其缺省值对于数据库来说通常需要调整。一般来说对于OPTIMIZER_INDEX_CACHING可以设置为90左右。对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间。对于数据仓库和DSS系统，可能不能简单的把 OPTIMIZER_INDEX_COST_ADJ设置为50，通常我们需要反复调整取得一个合理值。