Career Center Interview NotesIn this section we will be covering Performance and Tuning. Please use this as reference before going to any interview and Certification exams.
Tuning Overview
Alert and Tracefiles
Dynamic Performance Views
|
Instance / Database |
|
|
V$PROCESS |
Info about currently active processes. |
|
V$WAITSTAT |
Contention statistics on the blocks |
|
V$SYSTEM_EVENT |
Total waits for particular events, all sessions together. |
|
|
|
|
Pertaining to Memory |
|
|
V$BUFFER_POOL_STATISTICS |
Buffer pools allocation on the instance. (list stats for multiple pools) |
|
V$DB_OBJECT_CACHE |
List of DB objects 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 |
Gives sizes of all SGA structures. |
|
|
|
|
Disk Performance |
|
|
V$FILESTAT |
Data file read/write statistics (look for I/O dist and bottlenecks) |
|
V$TEMPSTAT |
Info about read/write stats on temporary tablespace data files. |
|
Contention |
|
|
V$LATCH |
Statistics for each type of latch. |
|
V$ROLLSTAT |
Statistics for all online rollback segments. |
|
V$WAITSTAT |
Block contention status (if TIMED_STATISTICS=TRUE) |
|
V$EVENT_NAME |
List of more than 100 wait events. |
|
Session Related |
|
|
V$LOCK |
Locks currently held by the server & outstanding requests for a lock or latch |
|
V$OPEN_CURSOR |
Cursors currently opened and parsed by each session. |
|
V$SORT_USAGE |
Size of temporary segments and sessions creating them. |
|
V$SESSTAT |
User session statistics. |
|
V$SESSION_EVENT |
Information on waits for an event for each session that had to wait. |
|
V$SESSION_WAIT |
Resources or events for which active sessions are currently waiting. |
|
V$SESSION_LONGOPS |
Use to monitor Long running full table scans. |
|
V$PX_SESSTAT |
Information about sessions running parallel execution. |
Utilities
Utlbstat – creates begin tables
Utlestat – creates end tables, difference tables, writes to report.txt.
• Run as SYS, should change SYS default tablespace first.
Latch = used to protect access to internal structures.
Three that can be tuned by DBA: Redo allocation, Redo copy, and LRU latch.
Enterprise Manager – Event Management (EM) System – monitor for unusual conditions by creating events for them – can define fixit routines or be paged to manually fix.
Performance Manager – captures performance data and displays in graphical form in real time.
TOPSESSIONS – monitor sessions ordered by a chosen statistic & identify their SQL.
Oracle EXPERT – Collects data, makes recommendations & creates SQL.
Creates 3 files: .ORA (init params), .SQL (sql to execute), .TXT (sql needs completed <tbs>)
Tuning Scopes: Instance – SGA size, I/O distribution, & sorts.
SQL Reuse – Shared pool analysis.
Space Management – Determines efficient storage definitions.
Optimal Data Access – analysis of index use, unused indexes, need rebuilt.
|
Want to appreciate the effort and thought behind it. Keep up the good work!! |
