Exforsys.com
 

Sponsored Links

 

Interview Notes Tutorials

 
Home Career Center Interview Notes
 

Oracle Interview Preparation Notes Part 5

 

In this section we will be covering Performance and Tuning. Please use this as reference before going to any interview and Certification exams.

Tuning Overview

  • Tuning Order: 1) Design 2) Application 3) Memory 4) I/O 5) O.S.

Alert and Tracefiles

  • 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.


Read Next: Oracle Interview Preparation Notes Part 6



 

 

Comments


tejinder said:

  Want to appreciate the effort and thought behind it.
Keep up the good work!!
August 23, 2004, 8:03 am

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2009 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape