Oracle 问题分析( 36 ).pptVIP

DB Issue Trouble Shooting Guideline Edgar Huang DB Issues Performance Issues DB Hung Internal Error Raised Lock / Dead Lock Other Issues Performance Issues Most of (more 90%) performance issues are caused by Application. Few (less than 10%) of issues are caused by resource limitation, OS/RDBMS configuration and other reasons. Work flow of identifying/solving Performance Issues. Identifying Solving Latent Reasons Bottleneck in Client Program PLSQL Issue Unreasonable configuration in RDBMS Network Issue Memory Issue CPU Issue IO Issue Top Processes TOP command in UNIX Glance command in HP-UX Task Manager in Windows Top Sessions in DB Find out the PID of top process from OS In dedicated server, find out the mapping session info in DB by the PID select b.spid, a.sid, a.username, s.sql_text from v$session a , v$process b, v$sqlarea s where a.PADDR = b.ADDR and a.sql_hash_value = s.hash_value and b.spid=PID; Find out the long operation in the transaction select username,sid,opname, round ( sofar *100 / totalwork,0)||% as progress, time_remaining,sql_text from v$session_longops , v$sqlarea where time_remaining 0 and sql_address = address and sql_hash_value = hash_value; Related View V$PROCESS V$SESSION V$SQLAREA v$session_longops Find more from Statspack Report Generate the report during peak time Analyze the report Important Sections in the report Instance Efficiency Percentages Top 5 Wait Events SQL ordered by Gets for DB SQL ordered by Reads for DB Tablespace IO Stats for DB File IO Stats for DB Instance Efficiency Percentages Buffer Nowait Ratio Buffer Hit Ratio Library Hit Ratio Redo no-wait Ratio In-memory Sort Ratio Instance Efficiency Percentages Top 5 Wait Events DB File Scattered Read. DB File Sequential Read Free Buffer Enqueue Log Buffer Space Log File Sync log file parallel write Top 5 Wait Events SQL ordered by Gets for DB SQL ordered by Reads for DB Tablespace IO Stats for DB File IO Stats for DB Generate SQL repor