Oracle Interview Preparation Notes Part 9

Tuning types: Routine, Focused, What-If (test difference configurations).
Tuning session scopes: Instance optimization, SQL reuse, space mgmnt, Optimal data access.
Tuning Data Classes: Schema class, Workload class, Environmental class

Note: Expert cannot collect system data (memory, CPU utilization, etc), you must provide it.

TUNING MULTITHREADED SERVER

· Enables multiple user processes to share a limited number of server processes.

· User processes are dynamically allocated to a server process that can be shared by others.

· Dispatcher gets a request, places it in the request queue, a server process receives it and returns the results via a response queue.

· MTS does NOT increase performance, but allows a greater number of users to connect.

Init.ora parameter	Value
MTS_SERVERS	Initial number of shared servers.
MTS_DISPATCHERS	protocol, number_of_dispatchers.
MTS_MAX_SERVERS	Optional – max number of server processes.
MTS_MAX_DISPATCHERS	Optional – max number of dispatcher processes.

· V$DISPATCHER – use to Monitor contention in Dispatchers (select busy rate).

· V$QUEUE – use to monitor contention for shared server processes (message queues).

· V$SHARED_SERVER – select count to get the current number of active shared servers.

· Oracle8i starts up shared servers dynamically (less need to monitor).

· V$CIRCUIT – gives current server and dispatcher addresses with the session address of the user.

· V$MTS – contains information for tuning the MTS.

Memory Usage:

· Some user information goes into the shared pool.

· Overall memory demand should still decrease.

· Shared servers use the UGA for sorts (consider setting a lower sort_area_retained_size).

· UGA is stored in the Large Pool area if defined.

· Makes sure the DBA has a dedicated connection (cannot startup or shutdown from shared server).

· Shared servers and dispatchers count as processes, size the PROCESSES parameter to handle.

· What view can be used to monitor I/O contention? V$FILESTAT (not V$SESSION_WAIT).

· Use ALTER INDEX REBUILD to organize space in an existing index (not the storage clause).

· Deadlock contention is recorded in the ALERT.LOG and USER process trace file (not PMON trace).

· OPTIMIZER_MODE parameter sets at INSTANCE and SESSION levels (not the system level).

· To evaluate reducing buffer size, use V$CURRENT_BUCKET & set DB_BLOCK_LRU_STATISTICS to TRUE.

· Oracle expert creates an implementation file (.ora) with updates for init.ora.

· Sorts are split into separate runs (SORT RUNS) if they are larger than SORT_AREA_SIZE.

· DB HIT PERCENTAGES (not response time) provide a baseline for determining performance.

· V$LATCH tracks stats used to calculate latch hit ratios, V$LATCHHOLDER identifies processes holding or waiting for latches.

· SQL from explain plan is listed from top to bottom, from inside out.

· Use V$SESSION (or V$SQLAREA) to get user session performance statistics.

· Size the KEEP POOL to hold all tables and their indexes (plus room for growth).

· A high number of wraps indicates that undo info is not fitting into single extents, increase the size of the rollback extents.

Tags