Oracle Interview Preparation Notes Part 5

Background_dump_dest, User_dump_dest, Max_dump_file_size.
SQL> Execute dbms_system.set_sql_trace_in_session(8,12,TRUE); or Alter session set sql_trace=TRUE;

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.

Tags

tejinder said: