SQL_TRACE set linesize 10000; set pagesize 20000; set serveroutput on; alter session set sql_trace=true; select count(*) from firefox; alter session set sql_trace=false; --查看sql_trace trace file select * from v$diag_info where name like 'Default%'; select sid,serial# from v$session ; variable x number; exec :x:=1; alter session set events '10046 trace name context forever,level 12'; select count(*) from firefox where object_id=:x; alter session set events '10046 trace name context off'; select object_name from dba_objects where object_id=426 or object_id=427 SQL> select * from aux_stats$; 收集系统信息 exec dbms_stats.gather_system_stats(gathering_mode=>'start'); alter system flush_cache; select count(*) from fire_t01; select * from aux_stats$; 初始化参数; db_file_multiblock_read_count 查看数据表一共有多少个块; select blocks from user_tables where table_name='FIREFOX'; set autotrace on; SELECT * FROM FIREFOX; SELECT /*+ blocks/k=cost */ 1029/10.4 from dual; 查看数据块大小 show parameter db_block_size; select * from sys.aux_stats$; SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN CPUSPEEDNW 2657.0122 #单块读的时间 sreadtim = IOSEEKTIM+(size of one block/IOTFRSPEED). =10+(8192/4096)=12 #多块数据块读取的时间 mreadtim=IOSEEKTIM+(MBRC * SIZE OF one block) / IOTFRSPEED) =10+((16*8192)/4096)=42 io_cost=(1046(blocks)/16)*(42/12)=232.75 cpu_cost=cpu_cost=cpucycles/(cpuspeed*sreadtim) =116192792/(2657.0122*12*1000)=4.48 cost=io_cost+cpu_cost = 232.75 + 4.48 = 如何查看cpucycles:PLAN_TABLE、PLAN_TABLE$; create table firefox1 as select * from firefox where rownum<100; create index idx_firefox on firefox(object_id); create index idx_firefox1 on firefox1(object_id); begin dbms_stats.gather_table_stats( user, 'firefox', cascade => true, estimate_percent => null, method_opt =>'for all columns size 1' ); end; / begin dbms_stats.gather_table_stats( user, 'firefox1', cascade => true, estimate_percent => null, --完全精确地分析; method_opt =>'for all columns size 1' ); end; / alter session set events '10053 trace name context forever,level 12'; select * from firefox,firefox1 where firefox.object_id = firefox1.object_id; alter session set events '10053 trace name context off'; select * from v$diag_info where name like 'Default%'; 对SGA区的动态调整; SQL> show sga; 网络连接 --session &processes; SQL> select username,paddr from v$session where username is not null; USERNAME PADDR ------------------------------ ---------------- SYS 00000000F04DAE60 SYS 00000000F04DAE60 C##SCOTT 00000000F04DBEA0 select addr from v$process where background is null and addr='00000000F04DAE60'; select * from v$process where background is null and addr='00000000F04DAE60'; set autotrace on stat; select username,paddr from v$session where username is not null; select addr from v$process where background is null and addr='00000000F04DAE60'; CBO计算成本时索引的权重修正值. show parameter OPTIMIZER_INDEX_COST_ADJ; alter session set OPTIMIZER_INDEX_COST_ADJ=10; 优化器--optimizer_mode first_rows [1|10|100|1000] first 优化器--optimizer_dynamic_sampling 动态采样的级别0to10 默认值(9i 1, >= 10g 2) 查看当前sql/plus 的SID SELECT sid, serial# FROM v$session WHERE audsid=SYS_CONTEXT('USERENV','SESSIONID'); select a.name,b.value from v$sesstat b.v$statname a where a.static#=b.statistic# and a.name ='CPU used by this session' and sid=69; 显示各种文件的I/O统计信息 - 数据文件,临时文件,控制文件,日志文件,归档文件... select file_no,filetype_name,LARGE_READ_MEGABYTES,SMALL_READ_MEGABYTES,SMALL_SYNC_READ_LATENCY from v$iostat_file; 对象--v$segstat 获得某对象的各类统计信息; select * from v$segstat where obj#=79605 and statistic_name='physical_reads';