# Oracle性能調整思考方向筆記
###### tags: `Oracle` `Performance Tuning` `DataBase`

## 定義問題
1. 確定性能目標
2. 找出問題的範圍
3. 確定發生問題時的時間
4. Quantify the slowdown
5. Identify any changes
> 識別任何變化
## 檢查主機系統
* CPU Usage
1. Non-Oracle Processes
2. Oracle Processes
3. Oracle CPU Statistics
4. Interpreting CPU Statistics
> 解釋cpu統計
* 檢測 i/o 問題
* Network
## 研究Oracle統計資訊
* Setting the Level of Statistics Collection
> 設置統計數據的收集等級
* V$STATISTICS_LEVEL
* BASIC
* TYPICAL
* ALL
* Wait Events
* Dynamic Performance Views Containing Wait Event Statistics
* V$ACTIVE_SESSION_HISTORY
* V\$SESS_TIME_MODEL and V$SYS_TIME_MODEL
* V$SESSION_WAIT
* V$SESSION
* V$SESSION_EVENT
* V$SESSION_WAIT_CLASS
* V$SESSION_WAIT_HISTORY
* V$SYSTEM_EVENT
* V$EVENT_HISTOGRAM
* V$FILE_HISTOGRAM
* V$SYSTEM_WAIT_CLASS
* V$TEMP_HISTOGRAM
* System Statistics
* V$ACTIVE_SESSION_HISTORY
* V$SYSSTAT
* V$FILESTAT
* V$ROLLSTAT
* V$ENQUEUE_STAT
* V$LATCH
* Segment-Level Statistics
## SQL Tuning
* Goals for Tuning
> 確認調整的目標方向
* 減少工作量
* Balance the Workload
> 平衡工作量
* Parallelize the Workload
> 並行工作量
* Identifying High-Load SQL
> 找出高負載的SQL
* Identifying Resource-Intensive SQL
> 查明資源密集的sql
* Tuning a Specific SQL
> 調整SQL效能
* Reducing Load
> 降低負載
* Gathering Data on the SQL Identified
> 依照SQL定義收集數據
* Automatic SQL Tuning Features
1. ADDM
2. SQL Tuning Advisor
3. SQL Tuning Sets
4. SQLAccess Advisor
* Developing Efficient SQL Statements
> 開發高效的sql語句
* Verifying Optimizer Statistics
> it is probably best to gather statistics for all tables
> 最好儘可能收集統計所有表
* Reviewing the Execution Plan
1. 使用正確的INDEX
2. 避免大表全表掃描
* Restructuring the SQL Statements
> 調整sql語句的結構
* Compose Predicates Using AND and =
> 為了提高SQL效率，盡可能使用等值連接
* Avoid Transformed Columns in the WHERE Clause
> 儘可能不要去轉換欄位值在WHERE語句
` where log_date = to_date('20221109','yyyymmdd') `
rather then
` where to_char(log_date,'yyyymmdd') = '20221109' `
* Write Separate SQL Statements for Specific Tasks
> SQL是不是程序語言。使用一塊的SQL做很多不同的事情通常會導致低於最優結果為每個任務。如果你希望SQL來完成不同的東西，然後寫各種報表，而不是寫一個語句做不同的事情取決於你給它的參數。
* Use of EXISTS versus IN for Subqueries
> 一般來說，如果選擇性謂詞是在子查詢使用IN。如果選擇性謂詞是在父查詢使用EXISTS。
* Controlling the Access Path and Join Order with Hints (使用HINT控制訪問路徑和連接順序)
* Use Caution When Managing Views
1. Use Caution When Joining Complex Views
2. Do Not Recycle Views
3. Use Caution When Unnesting Subqueries
4. Use Caution When Performing Outer Joins to Views
* Store Intermediate Results
1. USing DSA Table
2. Using View
3. Using MATERIALIZED VIEW
* Restructuring the Indexes (重組索引)
* Analyze Index
* Index rebuild
1. B-Tree
2. Bitmap
3. Function
4. Join
5. Unique
* Index-only tables

        CREATE TABLE emp_iot (
        emp_id number,
        ename varchar2(20),
        sal number(9,2),
        deptno number,
        CONSTRAINT pk_emp_iot_index PRIMARY KEY (emp_id) )
        ORGANIZATION index
        TABLESPACE spc_demo_ts_01
        PCTHRESHOLD 20 INCLUDING ename;

* Guidelines for Managing Indexes
> Index管理方針
* Create Indexes After Inserting Table Data
* Index the Correct Tables and Columns
> 建立正確的Index
* Limit the Number of Indexes for Each Table
> 減少Index數量
* Drop Indexes That Are No Longer Required
* Consider Creating Indexes with NOLOGGING
* Consider Costs and Benefits of Coalescing or Rebuilding Indexes
> 考慮索引建立與維護成本與效益
* Consider Cost Before Disabling or Dropping Constraints
* 選擇Global Index or Local Index
* Restructuring the Data
> 重組數據
* Analyze Table
* 大表建立PARTITION
1. Range Partitioning
2. Hash Partitioning
3. List Partitioning
4. Composite Partitioning
* Index-Organized Tables

        CREATE TABLE kk(
        project_name ,
        job_name ,
        job_desc ,
        CONSTRAINT pk_kk PRIMARY KEY (project_name, job_name)
        )
        ORGANIZATION INDEX
        AS
        SELECT project_name , job_name , job_desc
        FROM etl_job;

* Modifying or Disabling Triggers and Constraints
> 修改或禁用的觸發器和約束
* Maintaining Execution Plans Over Time
> 隨著時間的變化保持執行計劃
* Using Hints
* Using Stored Outlines
* Visiting Data as Few Times as Possible
> 減少資料訪問次數
* Combine Multiples Scans with CASE Statements
* Use DML with RETURNING Clause
* Modify All the Data Needed in One Statement