Chapter1Overview of Oracle9i Database Performance Tuning
Tuning goals
minimizing response time
increasing throughput
increasing load capabilities
decreasing recovery time
Tuning steps during development
1design
2application
3memory
4input/output(I/O)
5contention
6opersting system
Chapter2Diagnostic and Tuning Tools
v$sysstat(statistic#,name,class,value)
v$sgastat(pool,name,bytes)
v$event_name(event number,name,parameter1,parameter2,parameter3)
v$system_event(event,total_waits,total_timeouts,time_waited,average_wait)
SQL>select name,class,value from v$sysstat;
SQL>select*from v$sgastat;
Displaying session-related statistics
v$statname(statistic#,name,class)
v$sesstat(sid,statistic#,value)
v$session(sid,serial#,username,osuser)
v$session_event(sid,event,total_waits,total_timeouts,time_waited,average_wait,max_wait)
v$event_name(event#,name,parameter1,parameter2,parameter3)
v$session_wait(sid,seq#,event,ps/2/3,p1/2/3text,p1/2/3raw,wait time,seconds_in_wait,state)
SQL>select sid,username,type,server from v$session;
SQL>select username,name,value from v$statname n,v$session s,v$sesstat t where s.sid=t.sid and n.statistic#=t.statistic#pe='user' and s.username is not null and n.name='session pga memory'and t.value>30000;
SQL>select sid,event from v$session_wait where wait_time=0;
SQL>select name,parameter1,parameter2,parameter3from v$event_name;
SQL>select sid,event,total_Waits,average_wait from v$session_event where sid=10;
SQL>select sid,seq#,event,wait_time,state from v$session_wait;
wait_time{>0(the session's last wait time);=0(the session is currently waiting)=-1(the value was less than1/100of a second)=-2(the system cannot provide timing information)}
SQL>select event,total_waits,total_timeouts,time_Waited,average_wait from v$system_event order by time_waited desc; Troubleshooting and tuning views
Instance/Database
v$database
v$instance
v$option
v$parameter
v$backup
v$px_process_sysstat:parallel query system statistics
v$process:information about currently active process
v$waitstat:connection statistics
v$system_event:total waits for particular events
Disk
v$datafile
v$filestat:data file read/write statistics
v$log
v$log_history
v$dbfile
v$tempfile
v$tempstat:information about file read/write statistics for temporary tablespace data files
v$segment_statistics:offers statistics on I/O segment
Connection
v$lock
v$rollname
v$rollstat:statistics for all online rollback segments
v$waitstat:block contention statistics(the timed_statistics parameter should be set to true)
v$latch:statistics for each types of latch
Memory
v$buffer_pool_statistics:buffer pools allocation on the instance
v$db_object_cache:database objects cached in the library cache
v$librarycache:library cache performance and activity statistics
v$rowcache:data dictionary hits and misses activity
v$sysstat:basic instance statistics
v$sgastat
User/Session
v$lock:locks currently held by the server and outstanding requests for a lock or latch
v$open_cursor:cursors currently opened and parsed by each session
v$process
v$transaction
v$px_sesstat:information about the session executing SQL statements in parallel
v$px_session
v$sesstat:user session statistics
v$session_event:information on waits for an event by a session
v$sort_usage:seze of temporary segment and sessions creating them;identification of process doing disk sorts
v$session_wait
v$session
v$session_object_cache
test
SQL>show parameter timed_statistics
SQL>altere system set timed_statistics=true;
SQL>alter session set SQL_TRACE=true;
SQL>select count(*)from dba_tables;
SQL>alter session set SQL_TRACE=false;
$cd$HOME/admin/udump
$ls-l
$sqlplus hr/hr
$sqlplus sys/oracle as sysdba
SQL>select username,sid,serial#from v$session where username='hr';
SQL>begin
dbms_system.set_sql_trace_in_session(&SID,&SERIALNUM,true);
end;
/
SQL>select*from employees;
SQL>begin
dbms_system.set_sql_trace_in_session(&SID,&SERIALNUM,false);
end;
/
$cd$HOME/admin/udump
$ls-l
SQL>connect sys/oracle as sysdba
SQL>create tablespace tools datafile'$HOME/oradata/u05/tools.dbf'size100M extent management dictionary;
SQL>select tablespace_name,sum(types)from dba_free_space where tablespace_name='tools'group by tablespace_name; SQL>@$HOME/student/labs/spcreate.sql
SQL>@$HOME/student/labs/snap.sql
SQL>@$HOME/student/labs/spauto.sql
SQL>select job,next_date,next_sec,last_sec from user_jobs;
SQL>select sanp_id,to_char(startup_time,'dd Mon"at"HH24:mi:ss')instart_fm,to_char(snap_time,'dd Mon YYYY HH24:mi') snapdat,snap_level"level"from stats$snapshot order by snap_id;
SQL>@$HOME/student/labs/spreport.sql
$vi sp_x_y.1st
where x is the starting snapshot,and y is the ending snapshot(this is true if the default report filename was used)
SQL>connect sys/oracle as sysdba
SQL>select event,total_waits,time_waited from v$system_event;
SQL>select sid,event,p1text,wait_time,state from v$session_wait;
SQL>connect perfstat/perfstat
SQL>select job,log_user from user_jobs;
SQL>execute ve($job);
SQL>alter session set sql_trace=true;
SQL>select sid,serial#,username from v$session;
SQL>execute dbms_system.set_sql_trace_insession(ue);
statspack install
先阅读/u010g/oracle/rdbms/这文档
SQL>create tablespace perfstat datafile'/u01/oradata/pitts/perfstat_01.dbf'size512M;
SQL>@?/rdbms/admin/spcreate.sql
SQL>execute statspack.snap;
SQL>execute statspack.snap;
SQL>@?/rdbms/admin/spreport
********************************************************************************
德哥statspack讲解
SQL>drop user perfstat cascade;
SQL>drop tablespace perfstat including contents and datafiles;
SQL>create tablespace perfstat datafile'/oradata/clonedb/perfsata01.dbf'size500M autoextend on;
SQL>@?/rdbms/admin/spcreate.sql
如过运行出错
SQL>@?/rdbms/admin/spdrop.sql
然后在安装
SQL>@?/rdbms/admin/spcreate.sql
查看相关文档
cat spcusr.list
SQL>connect perfstat/perfstat
SQL>execute statspack.snap;
SQL>select*from stats$statspack_parameter;
SQL>select*from stats$snapshot;
然后在执行一次
SQL>execute statspack.snap;
vi spauto.sql
variable jobno number;
variable instno number;
begin
select instance_number into:instno from v$instance;
dbms_job.submit(:jobno,'statspack.snap;',trunc(sysdate+1/24,'HH'),'trunc(SYSDATE+1/24,''HH'')',TRUE,:instno);
commit;
end;
/
SQL>execute dbms_job.interval(62,'sysdate+1/(24*60)');
SQL>select*from user_jobs;
report
1、instance report
spreport.sql
sprepins.sql(multi-instance)
2、sql report
sprepsql.sql
sprsqins.sql(multi-instance)
vi$HOME/rdbms/admin/sprepcon.sql
define num_days='';
SQL>@?/rdbms/admin/spreport.sql(生成报告)
SQL>@?/rdbms/admin/sprepins.sql
SQL>@?/rdbms/admin/sprepsql.sql
Configure
1database parameters
2snapshot parameters
3instance report parameters
4sql report parameters
SQL>desc stats$statspack_parameter;
SQL>select SNAP_LEVEL from stats$statspack_parameter;
SQL>execute statspack.snap<i_snap_level=>7>;
SQL>execute statspack.snap<i_snap_level=>7,i_modify_parameter=>'true'>;
使用包更改
SQL>dify_statspack_parameter<i_snap_level=>6>;
SQL>select SNAP_LEVEL from stats$statspack_parameter;
停止脚步执行
SQL>execute ve(62);
SQL>select*from user_jobs;
maintenance
Make_baseline&clear_baseline
Purge snapshot except baseline
Truncate all tables with perfstat schema
Drop perfstat schema;
SQL>define i_begin_snap=1
SQL>define i_end_snap=16
SQL>execute statspacl.make_baseline(i_begin_snap=>1,i_end_snap=>16);
SQL>execute statspack.purge(i_begin_snap=>1,i_end_snap=>73);
SQL>execute statspace.clear_baseline(i_begin_snap=>1,i_end_snap=>16,i_snap_range=>false);
SQL>execute statspack.purge(i_begin_snap=>1,i_end_snap=>16);
SQL>select snap_id from stats$snapshot;
********************************************************************************
Chapter3Database Configuration and I/O Issues
Using the v$filestat view
SQL>select phyrds,phywrts,d.name from v$datafile d,v$filestat f where d.file#=f.file#order by d.name;
I/O statistics
SQL>select d.tablespace_name tablespace,d.file_name,f.phyrds,f.phywrts from v$filestat f,dba_data_files d where f.file#=d.file_id; Table Scan statistics
SQL>select name,value from v$sysstat where name like'table scan%';
Determine the progress of long operations using
SQL>select sid,serial#,opname,to_char(start_time,'HH24:MI:SS')as"start",(sofar/totalwork)*100as percent_complete from v$session_longops;
Use set_session_longops to populate v$session_longops
dbms_application_info.set_session_longops(rindex,slno,"Operation X",obj,0,sofar,totalwork,"table","tables");
Monitoring full table scan operations
declare
rindex binary_integer;
slno binary_integer;
totalwork number;
sofar number;
obj binary_integer;
begin
rindex:=dbms_application_info.set_session_longops_nohint;
sofar:=0;
totalwork:=10;
while sofar<10loop
--update obj based on sofar and perform task on object target
sofar:=sofar+1;
dbms_application_info.set_session_longops(rindex,slno,"Operation X",obj,0,sofar,totalwork,"table","tables");
end loop;
end;
DB_FILE_MULTINLOCK_READ_COUNT：是指oracle一次IO读出的最大块数，同时也受操作系统限制
如：oracle一次IO读64K，但是，操作系统只能读32K，这样就受到限制。
v$session_longops是指每次执行时间超过6秒都被记录在这个视图里面（6秒是缺省的值）同时要把sql_truce
test
SQL>connect system/oracle
SQL>select name from v$datafile union select member from v$logfile union select name from v$controlfile union select value from v$parameter where(name like'log_archive_dest%'and name not like'log_archive_dest_state%')or name in ('log_archive_dest','log_archive_duplex_dest');
SQL>select phyrds,phywrts,d.name from v$datafile d,v$filestat f where d.file#=f.file#;
SQL>select event,total_waits,time_waited,average_wait from v$system_event where event='log file sysnc'or
event='log file parallel write';
SQL>connect system/oracle
SQL>alter system set log_checkpoint_to_alter=true;
SQL>connect sh/sh
SQL>@$HOME/student/labs/lab03_06.sql
表占一块盘
索引占一块盘
undo自己占一块盘
redo要是IO最快的盘
system自己占一块盘
temporary自占一块盘
Chapter4Tuning the Shared Pool
Unshared SQL
SQL>select sql_text from v$sqlarea where executions<5order by UPPER(sql_text);
Reparsed Sharable SQL
SQL>select sql_text,parse_calls,executions from v$sqlarea order by parse_calls;
truncated命令不记录日志
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论