Sql_trace of Oracle DBA learning article

Sql_traceSetLinesize10000;SetPageSize20000;SetServeroutput on;AlterSessionSetSql_trace=true;Select Count(*) fromFirefox;AlterSessionSetSql_trace=false;--view Sql_trace trace fileSelect *  fromV$diag_infowhereName like 'default%';Selectsid,serial# fromv$session; variable x Number;exec: x:=1;AlterSessionSetEvents'10046 Trace Name Context Forever,level';Select Count(*) fromFirefoxwhere object_id=: x;AlterSessionSetEvents'10046 Trace name context off';Select object_name  fromDba_objectswhere object_id=426 or object_id=427SQL> Select *  fromaux_stats$; collect System InformationexecDbms_stats.gather_system_stats (Gathering_mode='Start');Altersystem Flush_cache;Select Count(*) fromfire_t01;Select *  fromaux_stats$; initialize parameters; db_file_multiblock_read_count See how many blocks there are in a data table;SelectBlocks fromUser_tableswheretable_name='FIREFOX';SetAutotrace on;SELECT *  fromFIREFOX;SELECT /*+ blocks/k=cost*/ 1029/10.4  fromdual; View data block size show parameter db_block_size;Select *  fromsys.aux_stats$; Sysstats_main IoseektimTenSysstats_main iotfrspeed4096Sysstats_main CPUSPEEDNW2657.0122#单块读的时间sreadtim=Ioseektim+(Size ofOne block/iotfrspeed).=Ten+(8192/4096)= A#多块数据块读取的时间mreadtim=Ioseektim+(MBRC*SIZE ofOne block)/iotfrspeed)=Ten+(( -*8192)/4096)= theIo_cost=(1046(blocks)/ -)*( the/ A)=232.75Cpu_cost=Cpu_cost=Cpucycles/(Cpuspeed*Sreadtim)=116192792/(2657.0122* A* +)=4.48 Cost=Io_cost+Cpu_cost= 232.75 + 4.48 =How to view cpucycles:plan_table, plan_table$; Create TableFirefox1 as Select *  fromFirefoxwhereRowNum< -;Create IndexIdx_firefox onFirefoxobject_id);Create IndexIdx_firefox1 onFirefox1 (object_id);beginDbms_stats.gather_table_stats (User,'Firefox',Cascade =true,estimate_percent= NULL, Method_opt='For all columns size 1');End;/beginDbms_stats.gather_table_stats (User,'Firefox1',Cascade =true,estimate_percent= NULL,--complete and accurate analysis;Method_opt='For all columns size 1');End;/AlterSessionSetEvents'10053 Trace Name Context Forever,level';Select *  fromFirefox,firefox1whereFirefox.object_id =Firefox1.object_id;AlterSessionSetEvents'10053 Trace name context off';Select *  fromV$diag_infowhereName like 'default%'; The dynamic adjustment of the SGA area; SQL>show SGA; network connection--Session &processes;Sql> SelectUsername,paddr fromV$sessionwhereUsername is  not NULL; USERNAME paddr------------------------------ ----------------SYS 00000000f04dae60sys 00000000f04dae60c# #SCOTT 00000000f04dbea0SelectAddr fromV$processwhereBackground is NULL  andAddr='00000000f04dae60';Select *  fromV$processwhereBackground is NULL  andAddr='00000000f04dae60';SetAutotrace onStat;SelectUsername,paddr fromV$sessionwhereUsername is  not NULL;SelectAddr fromV$processwhereBackground is NULL  andAddr='00000000f04dae60'; When the CBO calculates the cost, the weight correction value of the index. Show parameter Optimizer_index_cost_adj;AlterSessionSetOptimizer_index_cost_adj=Ten; optimizer--Optimizer_modeFirst_rows[1|10|100|1000]First optimizer--optimizer_dynamic_samplingDynamic Sampling level 0TO10 default value (9i1,>=10g2) to view the current SQL/Plus's SIDSELECTSid, Serial# fromV$sessionWHEREAudsid=Sys_context ('USERENV','SESSIONID');SelectA.name,b.value fromV$sesstat B.v$statname Awherea.static#=b.statistic# andA.name='CPU used by this session'  andSid= thedisplays the various files I/o statistical Information-data files, temporary files, control files, log files, archive files ...SelectFile_no,filetype_name,large_read_megabytes,small_read_megabytes,small_sync_read_latency fromV$iostat_file; object--V$segstatObtain the statistical information of an object;Select *  fromV$segstatwhereobj#=79605  andStatistic_name='physical_reads';

Sql_trace of Oracle DBA learning article