Troubleshooting and Performance Tuning Main

Troubleshooting and performance tuning main :

Ø  What to tune and tools used for tuning

Ø  Logs to be uploaded to oracle corp

Ø  Suggestion to be made to client **

Ø  11g automatic maintenance task

Ø  11g health check

Ø  11g Virtual columns

Ø  11g Database replay - Work load replay

Ø  deleting duplicate rows

Ø  PL/SQL to huge deletion

Ø  deciding value of session cache cursor and open cursor

Ø  11g Instance caging

Ø  ORA - ERRORS

Ø  troubleshoot checks list **

troubleshoot checks :

Ø  Tune wait event

Ø  Logs to minotor

Ø  OEM queries

Ø  smon recovery

Ø  session hung / database hung

Ø  Archive generation

Ø  Row chaining

Ø  Fragmentation

Ø  Parallel executions , Parallelism Notes , 11g automatic degree

Ø  Unindexes foreign keys

Ø  Long operation

Ø  Locks

Ø  Undo usage and calculation

Ø  Temp usage

Ø  Hot block

Ø  Latch and mutex contention

Ø  11g read alert log -- ARDCI

Ø  Top resources

Ø  Detect Parameter changed

Ø  I/O utilization check

Ø  General database check queries

Ø  Compile invalid objects

Ø  Get SCN

Ø  Invalidate cursor

Ø  Resource limit and registry status

Ø  OS statistics

Ø  Uncommited transaction

Ø  Increasing Priority of lgwr Process

Ø  Pending transaction

Ø  iostat , vmstat

SQL :

Ø  SQLTEXT for sid

Ø  Sql ID details

Ø  SQL performance details ** + top sql

Ø  check sql id with multiple plan hash value ( sql that changed plan )

Ø  Manage Sql plan ****

Ø  11g Sql tuning sets and Sql plan baselines --> force plan

Ø  Manage Sql outlines --> force plan

Ø  Manage Sql profiles --> force plan

Ø  11g purge sql id from shared pool

Ø  10g sql tuning advisor

Ø  10g sql access advisor

Ø  11g Sql Perforamance analyzer – for upgrade

Ø  11g automatic sql tuning

Ø  Bind peeking / Column usage / skew columns- histogram

Ø  Check sql with bind aware and bind sesntive – all for bind variable

Ø  11g adaptive cursor sharing

Ø  11g result cache

Ø  Sql tuning notes

Ø  Parsing

Sessions :

Ø  Session info ibm

Ø  Session info

Ø  List connected session

Ø  who is blocking my session

Ø  check SPID of session

Ø  check SID from SPID

Ø  Check last call et of session

Ø  Killing session

Ø  Stop current transaction in another session

Ø  Dump session with modified parameters + list all session parameters

Ø  making prelimary connection to database

Ø  Set current schema

Ø  Session Is waiting on which object

Ø  what operation session did in past and which block accessed

Ø 

Objects :

Ø  Indexes – notes

Ø  Tables types -- notes

Ø  11g Invisible index

Ø  Partitioning

Ø  Compression ( advance compression , tables , tablespace , etc)

Ø  LOB -- move LOB

Ø  Materializez views and materialized view groups -- QUERY_REWRITE

Ø  Find dependant objects

Ø  Foreign key with no index

Ø  Foreign key referring my table

Ø  Object using db links

Ø  Which block session is trying to access

Ø  Block information

Ø  Dump oracle block

Ø  Segment space advisor

Ø  Segment statistics

Ø  Segment usage history

Ø  Oracle data types

Ø 

Maintainence activity :

Ø  Index rebuild

Ø  Colease index

Ø  Monitoring index usage

Ø  Table reorg

Ø  Analyzing / stats gather / system statistics collection

Ø  Clean failed index rebuild.

Ø  Validate index and tables

Notes :

Ø  Oracle Error code descriptions

Ø  Optimizer

Ø  Cursor sharing

Ø  Type of hints

Ø  Major issues faced and how it was resolved .

Ø  Lock modes

Ø  Background processes

Ø  Initialization parameters

Ø  Other tuning notes

Ø  Hidden parameter

Ø  Dynamic Sampling

Ø  Joins

####################################################################################

troubleshoot checks list

####################################################################################

database , os performance reports , os logs ,

Session :

------

-- wait event , blocking , multiple plan , historical excution , purge sql id , invalidate cursor ,

-- sql diag , ash , session trace and tkprof, expalin plan , Sql monitoring report , sqlt

-- stale stats , full table scan in explain plan and wait event, purge sql id, invalidate cursor

-- sql statistics , v$segment statistics , sql execution history – data fetched / time ,

-- missing index , missing stats , Un-indexed foreign keys , index on join

-- plan change , initialization parameter change ,table fragmentation , index fragmentation , last analyzed ,

-- recently analyzed – restore old stats , session optimizer environments

-- invalid objects , last ddl on objkect ,

-- oem : which object session is waiting on , event , lock , rows fetched

-- segment advisor , sql tuning advisor , segsize history :

-- histogram , OS STATS , lead columns in composite index ( cardinality) ,

-- table fragmentation and index cluster factor ,

-- session waiting on which segment

-- check if any profile or baseline is created .

Compare :

-- nls environment , optimizer environment

Notes :

-- why index not used , sql tuning notes ,

database :

------

-- O/S : cpu , memory , archive backup , os logs

-- blocking tree , locks , concurrency , wait events , oem : concurrency

-- invlaid objects , new object created , foreign key with no indexes

-- alert log , listener log , os logs , topas , trace files udump/bdump/cdump

-- statspack , ash , awr , awr sql report , awr compare report , addm , segment advisor

Rda , hanganalysis and system state , os watcher , diagcollection ,

-- Checking listener connectivity

-- session or db hung , connected sessions

-- temp and undo usage , archive generation ,

-- pArallel query processes , longops . , degree of table

-- V$resource_limit , registry status , number of sessions

-- tablespace status /// next extent

-- rman/ exp / user managed hot backup

-- smon recovery , files in recovery

-- truss """truss -d -o truss.out sqlplus "/ as sysdba""

-- defunct process

-- segments statistics , segment history , tablespace history , segment usage hist

-- runStats_pkg

-- top session , top session in toad , top session on server , top sql

-- DB upgrades, patchsets,

-- hit ratios , parsing

-- smon recovery , db or session hung ,

######### tuning using OEM :

concurreny

explain plan

segment advisor

sql moitoring

ash and awr report

blocking session

sql awr report

sql ash report

Get Proactive - Oracle Health Checks - Installation, troubleshooting, catalog and more. (Doc ID 868955.1) -- OCM

GO TO TOP

####################################################################################

General database check queries

####################################################################################

GENERAL

set arraysize 5000

set line 200

set pagesize 5000

col owner for a20

show parameter

select * from v$version;

select * from v$license;

select action,namespace,action_time from DBA_REGISTRY_HISTORY;

select parameter,value from v$option;

select type,RECORD_SIZE,RECORDS_TOTAL,RECORDS_USED from v$CONTROLFILE_RECORD_SECTION;

select resource_name,CURRENT_UTILIZATION,MAX_UTILIZATION,limit_value from v$resource_limit;

select stat_name,value from v$osstat;

select metric_name,value from V$SYSMETRIC where value >0;

NLS PARAMETERS :

col parameter for a40

col value for a40

select parameter,value from NLS_DATABASE_PARAMETERS where parameter in('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

col “SESSION” format a30

col DATABASE format a30

col INSTANCE format a30

select * from

(select ‘SESSION’ SCOPE,nsp.* from nls_session_parameters nsp

union

select ‘DATABASE’ SCOPE,ndp.* from nls_database_parameters ndp

union

select ‘INSTANCE’ SCOPE,nip.* from nls_instance_parameters nip

) a

pivot (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)

FOR SCOPE

in (‘SESSION’ as “SESSION”,’DATABASE’ as DATABASE,’INSTANCE’ as INSTANCE));

And the output:

select ndp.parameter

, max(nsp.value) Sesssion

, max(nip.value) Instance

, max(ndp.value) Database

FROM nls_session_parameters nsp

, nls_instance_parameters nip

, nls_database_parameters ndp

WHERE ndp.parameter = nsp.parameter (+)

AND ndp.parameter = nip.parameter (+)group by ndp.parameter

;

show parameter nls

GO TO TOP

####################################################################################

Compile invalid objects

####################################################################################

EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('BMR','COMPUTE');

BEGIN DBMS_UTILITY.ANALYZE_SCHEMA('BMR','COMPUTE'); END;

------compile a schema ------

SELECT 'exec dbms_utility.compile_schema('''||username||''')'

FROM DBA_USERS WHERE DEFAULT_tablesPace NOT LIKE 'SYSTEM';

spool compile.sql

select 'alter ' || DECODE(object_type,'PACKAGE BODY','PACKAGE',OBJECT_TYPE)|| ' ' ||

object_name || ' compile ' || DECODE(OBJECT_TYPE,'PACKAGE BODY','BODY') || ';' from

user_objects where status = 'INVALID' order by object_type;

@compile.sql

SELECT 'alter ' || DECODE(object_type,'PACKAGE BODY','PACKAGE',OBJECT_TYPE)|| '

'||owner||'.' || object_name || ' compile ' || DECODE(OBJECT_TYPE,'PACKAGE BODY','BODY') ||

';' FROM

dba_objects WHERE status = 'INVALID' ORDER BY object_type;

GO TO TOP

####################################################################################

Get SCN

####################################################################################

Select to_char(CURRENT_SCN) from v$database; -- oracle Ver. 10g or above

select dbms_flashback.get_system_change_number from dual;

Select current_scn, dbms_flashback.get_system_change_number from v$database; --standby case

select scn_to_timestamp(33362000) from dual;

select file#,status, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, count(*) from v$datafile_header

group by status, checkpoint_change#, checkpoint_time, file#

order by status, checkpoint_change#, checkpoint_time;

GO TO TOP

####################################################################################

Resource limit and registry status

####################################################################################

select * from v$resource_limit ;

select * from DBA_HIST_RESOURCE_LIMIT ;

select * from dba_registry ;

select * from dba_registry_history ;

GO TO TOP

####################################################################################

OS statistics

####################################################################################

OS stats :

select to_char(BEGIN_INTERVAL_TIME , 'DD-MON-YYYY HH24:MI:SS' ) , to_char(END_INTERVAL_TIME , 'DD-MON-YYYY HH24:MI:SS' ) , SNAP_ID

from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME , 'DD-MON-YYYY HH24:MI:SS' ) >= '31-OCT-2012 08:30:00'

and to_char(END_INTERVAL_TIME , 'DD-MON-YYYY HH24:MI:SS' ) <= '31-OCT-2012 10:30:00'

order by snap_id;

select * from dba_hist_osstat where snap_id between 53356 and 53358;

Select * from v$osstat;

GO TO TOP

####################################################################################

Invalidate cursor

####################################################################################

confirm if cursor is invalidated :

select hash_value, address, child_number, executions, plan_hash_value, invalidations from

v$sql where sql_text like 'select%denis%' and sql_text not like 'select hash_value%';

- flush the shared_pool

- grant: i.e. grant select on t to system;

- comment: i.e. comment on table t is 'I am a table';

- dbms_stats.gather_table_stats (no_invalidate should not be true);

- add a column

- 11g purge sql id from shared pool

GO TO TOP

####################################################################################

Logs to minotor

####################################################################################

listener logs

database alert logs

files in dump areas

è Content of alert logs :

Ø  Error codes

Ø  Administrative task

Ø  archival information

Ø  parameter change

Ø  recovery information

Ø  deadlocks

Ø  checkpoints

è 11g diagnostic destination

Show parameter diag

Select * from v$diag_info

GO TO TOP

####################################################################################

11g read alert log

####################################################################################

x$dbgalertext;

create view v$alert_log as select * from x$dbgalertext;

create public synonym v$alert_log for v$alert_log;

grant select on v$alert_log to jay;

set lin 180

col ORIGINATING_TIMESTAMP format A20

col MESSAGE_TEXT format A90

select MESSAGE_TEXT from x$dbgalertext WHERE MESSAGE_TEXT LIKE 'ORA-%' ;

set lin 180

col ORIGINATING_TIMESTAMP format A20

col MESSAGE_TEXT format A90

select ORIGINATING_TIMESTAMP,NORMALIZED_TIMESTAMP,MESSAGE_TEXT from x$dbgalertext WHERE mESSAGE_TEXT LIKE 'ORA-%' ;

select DISTINCT ORIGINATING_TIMESTAMP,MESSAGE_TEXT from x$dbgalertext WHERE MESSAGE_TEXT LIKE 'ORA-%' or MESSAGE_TEXT LIKE '%Fatal%' AND ORIGINATING_TIMESTAMP > SYSDATE-4;

select rownum “line”, message_text “error” from x$dbgalertext where originating_timestamp > (sysdate – 5/1440) and message_text like ‘%ORA-%’ order by originating_timestamp;

GO TO TOP

####################################################################################

What to tune and tools used for tuning

####################################################################################

What to tune :

è application

Ø  SQL

Ø  Storage structures

Ø  Access paths

Ø  Parsing

Ø  Wait event

è Instance

Ø  memory

sga / pga / amm / asmm / shared pool

hit ratios /

Ø  sorting

Ø 

è OS

Ø  I/O

Ø  Network

è Hardware

Tools used for tuning :

Ø  ADDM , AWR , statspack , sql report ,

Ø  OEM

Ø  Dynamic performance views

Ø  Time model statistics Statistics based on db time

Ø  System metrics

Ø  Wait events -- wait time / cpu time

Ø  Session tracing , tkprof ,

Ø  OS tools : topas , nmon

Ø  Database and os logs

Main goal :

Ø  decrease downtime

Ø  decrease system response time

Ø 

GO TO TOP

####################################################################################

Tune Wait events

####################################################################################

è v$session

è v$session_wait

è v$session_wait_history

è v$session_event

è v$system_event

è V$statname

è v$enqueue_statistics

è v$event_histogram

è v$active_session_history

è dba_hist_active_sess_history

è DBA_HIST_SYSTEM_EVENT

è session wait on object

è session wait on file

è object having wait event ( object that are waiting for that wait event )

è check buffer busy wait

Ø  Wait event descriptions

Ø  Wait event List

######################## v$session:

select username, SQL_ID, SID, event , status from v$session where EVENT='cr request retry'

;

select event, count(*) from v$session group by event order by 2 ;

select sql_id, count(*) from v$session where EVENT='cr request retry' group by sql_id ;

select sum (wait_time+ SECONDS_IN_WAIT) , event from v$session group by event order by 1 ;

select sum (SECONDS_IN_WAIT) , event from v$session group by event order by 1 ;

select sum (wait_time) , event from v$session group by event order by 1 ;

select p1,p2, event , sum(wait_time) from v$session

group by p1,p2,event

order by sum(wait_time);

SELECT a.sid, c.pid, c.spid, a.username, b.event, b.wait_time, b.seconds_in_wait, b.p1, b.p2, b.p3

FROM v$session a, v$session_wait b, v$process c

WHERE a.sid = b.sid

AND a.paddr = c.addr

AND b.event LIKE 'enq: RO%';

######################## v$session_wait

SELECT p1 "file#", p2 "block#", p3 "class#" FROM v$session_wait WHERE event = 'read by

other session';

with temp as (select sid,event,WAIT_TIME+SECONDS_IN_WAIT "TIME_WAITED" from v$session_wait order by 3 desc) select * from temp where rownum < 50;

column sid format 990

column seq# format 99990

column wait_time heading 'WTime' format 99990

column event format a30

column p1 format 9999999990

column p2 format 9999999990

column p3 format 9990

select sid,event,seq#,p1,p2,p3,wait_time

from V$session_wait

order by sid

/

######################## v$session_wait_history

--> gives last ten waits for all active sessions

select seq# , event , wait_time , p1,p2,p3

from v$session_wait_history where sid=988

order by seq# ;

###################### v$session_event

select event,count(*) from v$session_event where event='cr request retry' group by event ;

select event, sum( time_waited) from v$session_event group by event ;

select event, total_waits from v$session_event where event = 'enq: TM - contention';;

select event,sum(TOTAL_WAITS) "TOTAL_WAITS" , sum(TOTAL_TIMEOUTS) "TOTAL_TIMEOUTS" ,sum(TIME_WAITED) "TIME_WAITED",avg (AVERAGE_WAIT) "AVERAGE_WAIT" from v$session_event where event is not null and wait_class >'Idle' group by event order by 4;

###################### v$system_event

with temp as (select EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT from v$system_event where wait_class > 'Idle' order by 4 desc ) select * from temp where rownum < 50;

select event, total_waits from v$system_event where event = 'enq: TM - contention';;

select event,count(*) from v$system_event where event='cr request retry' group by event ;

select event, sum( time_waited) from v$system_event group by event ;