Oracle Performance Analysis and Optimization lecture notes

1. When to consider performance issues.

Performance considerations should run through all stages of System Construction: system design, database design, code development, and system O & M.

1. system design phase:

System Architecture: Layer 2, Layer 3, and Layer 3

System type: OLAP-report generation, mainly considering the processing speed of massive data

Related reading: Oracle queries associated tables based on foreign key names

OLTP-mainly considering memory (cache), concurrency, concurrency, etc.

Estimation of system capabilities in the system life cycle: concurrency, query response time, and so on. Negative example: the ticket booking system for the 2008 Beijing Olympic Games collapsed.

2. In the database design stage, the types and attributes of database objects are highlighted:

Partition.

Indexes and their types (OLTP, generally using B-Tree; OLAP, generally using Bitmap or Text indexes ).

Parallel.

Other Object Attributes (ASSM and concurrency attributes ).

Memory parameters.

Concurrency parameters.

I/O parameters.

Database architecture (single node, RAC, distributed ).

Hardware performance (CPU, memory, etc ).

3. Efficient SQL binding to variables (OLTP)

Misunderstanding: Improper Use of Hint: forced use of index, forced setting of driver table, forced optimizer Mode

4. Object Type: Modification of attributes and Parameters

2. DBA should be involved in all stages of system construction, and developers should consider performance issues as much as possible.

Iii. optimized content:

1. Parameter: OLTP: Memory: SGA, PGA

Parallel: PROCESSES, SESSIONS

2. Object Attributes: partition and parallel

3. Index types: B-Tree, Bitmap, and Text

4. Efficient SQL

5. SQL Execution Plan

6. Analyze the table: histogram, DBMS_STATS package

Iv. optimization tools

1. SQL _Trace

2.10046 events

3. Performance view, such as: V $ SQL, V $ SESSION, V $ LOCK, V $ SESSION_LONGOPS

4.10053 event: displays all detailed operations in the Oracle execution plan

5. Optimization Tool: Hint-optimizer Mode

Access path

Table connection Sequence

Concurrency

Statspack (AWR) report and ASH report