Oracle Interview Preparation Notes Part 8

Have enough rollback segments so that transactions never wait for access to rollbacks.
Avoid frequent dynamic extention of rollback segments (overhead).

V$ROLLNAME	displays name and number of online rollback segments.
V$ROLLSTAT	Displays stats (waits, bytes, extends, etc) for each online rollback segment.
V$SYSTEM_EVENT	“Undo Segment Tx Slot” event shows rollback contention.
V$WAITSTAT	displays cumulative wait stats on header and data blocks.
V$TRANSACTION	displays the current transactions using rollback segments.

How many?

For OLTP – recommended 1 for every 4 transactions.
For Batch – recommended 1 for each concurrent job. (assign larger rollback segments to by using the SET TRANSACTION USE ROLLBACK SEGMENT)

Create more if The ratio of sum of WAITS to the sum of GETS > 1% (from V$rollstat).

Sizing:

· Make all the same size.

· Use same initial and next, with a minextents of 20.

Use less rollback by…

· Committing often.

· Using CONSISTENT = N on exports.

· Using COMMIT = Y on imports.

· Using ROWS keyword on SQL*Load to set commit interval.

Snapshot too old – the rollback image needed for read consistency has been overwritten by an active transaction. – you need more or bigger rollback segments.

Oracle will only use TRANSACTIONS / TRANSACTION_PER_ROLLBACK_SEGMENT.

(Ex: transactions = 1000, t.p.r.s. = 150, 10 online segments, Oracle will use 7 (1000 / 150).)

V$WAITSTAT: Classes = system undo header, system undo block, undo header, undo block

Monitoring and Detecting LOCK CONTENTION

DML locks – guarantee integrity of data accessed concurrently by multiple users.

· Exclusive row locks (RX) are used for DML.

· Share locks are used on tables (TM) for DML.

· Row share (RS) are used for SELECTs.

DDL locks – protect definition of a schema object during DDL operations.

· Exclusive - DROP TABLE, ALTER TABLE

· Shared DDL – CREATE PROCEDURE, AUDIT.

Notes:

· Enqueue = Oracle maintains all locks as enqueues, the enqueue mechanism tracks: Users waiting for locks held by others, the lock mode these users require, the order of requests.

· To avoid table locks in child tables of a foreign key, index the foreign key columns in the child table.

· To manually lock tables: LOCK TABLE x IN EXCLUSIVE MODE.

Lock Contention:

V$LOCK – provide info about the currently held locks

V$LOCKED_OBJECT – provides the object id and session that is locking.

V$SESSION_WAIT – queried by the UTLLOCKT.SQL utilitity.

Deadlock:

· Caused when 2 or more users are waiting for data locked by the other.

· Oracle automatically detects and resolves by rolling back the statement that detected it.

· A trace is recorded in SID_ora_PID.trc in the user_dump_dest.

SQL ISSUES and TUNING APPLICATIONS:

Most important part of tuning!

EXPLAIN plan: Used to check the execution plan of a statement.

· Can be used at the session level (SQL*Plus) without tracing.

SQL Trace and TKPROF:

· SQL Trace must be invoked at the instance (init.ora) or session (alter session) level.

· Use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to set for another user session.

· Set TIMED_STATISTICS to TRUE and set user_dump_dest.

· Use TKPROF (command line utility) to format the Oracle trace file.

· Use SYS=NO to suppress the recursive SQL output.

· Use EXPLAIN=username/passwd to get the execution plans.

Data gathered includes: Count (# of executions), CPU used, Elapsed time, Disk (physical reads),

Query (Logical reads for consistency), Current (logical reads in current mode), Rows processed.

AUTOTRACE (SQL*Plus):

· Must create plan table, run plustrce.sql, and grant plustrace to user.

· SET AUTOTRACE [OFF | ON | TRACEONLY] [ EXPLAIN | STATISTICS]

· ON = stats, explain plan, and results / TRACEONLY = explain plan and stats (no results)

· ON EXPLAIN = result set with explain plan / TRACEONLY STATISTICS = just stats.

Optimizer Mode:

· Alter session set optimizer_mode or set optimizer_mode=value in init.ora

· Using a HINT: SELECT /*+ FIRST_ROWS */

· Modes = CHOOSE | RULE | FIRST_ROWS | ALL_ROWS

ANALYZE table|index|cluster name {COMPUTE|ESTIMATE|DELETE} STATISTICS

[FOR [SIZE n] [SAMPLE n {ROWS | PERCENT}]

(Use DBMS_STATS package procedures to copy stats from one database to another.)

· Table stats - # rows, #blocks, #empty blocks, chained rows, average row length.

· Index stats – Index level (height), leaf blocks, distinct keys, clustering factor, avg blocks per key.

· Column stats - # distinct values, lowest and highest value.

STORED OUTLINES:

· Allow you to store the execution plan for queries (do not change as data changes).

· CREATE OUTLINE FOR CATEGORY name ON (sql statements).

· Alter session CREATE_STORED_OUTLINE = TRUE | name; (sql statements).

· Alter session set USE_STORED_OUTLINE = name.

· The OUTLN_PKG (DROP_UNUSED | DROP_BY_CAT | UPDATE_BY_CAT).

Indexes:

B-TREE

· Intended for high cardinality columns.

· Relatively inexpensive updates on keys.

· Improve performance of queries that select a small percentage (<5%) of the rows in a table.

· Locking at the row level.

BITMAP

· Intended for low cardinality columns.

· Use little storage space (one entry per key value).

· Work very well with multiple predicates (especially using OR).

· DML is slower, so bitmaps are best suited for DSS systems.

· Locks are by segment of key value (range of rows are locked).

· CREATE_BITMAP_AREA_SIZE – (init.ora) amount of memory allocated for creation of.

· BITMAP_MERGE_SIZE = amount of memory to use to merge bitmaps from a range scan.

REVERSE KEY

· Suitable for keys with sequential numbers – reverses order of the column bytes (less levels).

· Not suitable for a range scan – plan produces a full table scan.

INDEX-ORGANIZED TABLES:

· 1 single B-Tree structure is maintained for primary key value and all other values.

· Take up less storage and provide faster key-based access.

CLUSTERS:

Group of 1 or more tables that share the same data blocks (share common columns) and are often

used together in join queries.

· Disk I/O is reduced for joins of clustered tables.

· Full table scans are generally slower.

Index cluster – Uses an index to maintain the data (can store NULL values).

Hash cluster – Uses a hash algorithm to calculate the location of a row. Better performance for an

equality predicate on the key (no index scan needed).

MATERIALIZED VIEWS:

Stores both the definition of a view and the rows from its execution.

· Can be indexed and /or partitioned.

· Must be refreshed manually (DBMS_MVIEW.REFRESH) or automatically (on COMMIT) or at defined intervals (using JOB_QUEUE_INTERVAL).

· Oracle will automatically rewrite a query to use a materialized view when appropriate. Init.ora or session parameter QUERY_REWRITE_ENABLED = TRUE or use hint /*+REWRITE*/

OLTP:

High throughput, insert & update intensive, continuously growing data volume, concurrent access.

Tuning Goals: Availability – Speed – Concurrency - Recoverability

· B-Tree indexing is preferred, with frequent rebuilds.

· Higher number of rollback segments (min extents of at least 20).

DSS:

Large data queries, heavy use of full table scans, Parallel query should be used.

Tuning Goals: Speed – Accuracy – Availability (daytime).

· Run analyze frequently.

· Set a high block size (for table scans) and db_file_multiblock_read_count.

Use bitmap indexes, index-organized tables, and hash clusters whenever possible.