OracleTuning笔记

#oracle tuning
#new tools AWR ADDM
SLA : service level agreements
Baseline os Statistics (statpack)
alert log & trace files
top vmstat
service time & wait time
Trade-Offs(权衡) of performance & safety


#Diagnostic and Tuning Tools
Alert Log
show parameter dump
view #linux shell
ORA-600 internal errors #一般在metalink找
checkpoint : ALTER SYSTEM SET LOG_CHECKPOINTS_TO_ALERT=TRUE SCOPE=BOTH;
#Trace Files : backupground process  #oracle support
#User Trace Files
alter session set sql_trace=true;
EXECUTE dbms_system.set_sql_trace_in_session(12,167,true);  #as sysdba run
v$session
show parameter sql_trace
OEM & PL/SQL packs & statspack & v$xxx & dba_xxx & oracle wait events & utlbstat.sql utlestat.sql
#statspack
spcreate.sql spauto.sql spreport.sql
execute statspack.snap
TIMED_STATISTICS=true
spdoc.txt
####
dbms_stats
dba_tables ...index_histogram
v$sysstat v$sgastat v$event_name v$system_event
v$statname v$sesstat v$session v$session_event v$event_name v$session_wait
X$ v$fixed_table ...
#Level of Statics
STATISTICS_LEVEL TIMED_STATISTICS TIMED_OS_STATISTICS DB_CACHE_ADVICE
#database configuration & I/O issus
File system & RAW & ASM
v$filestat v$tempstat v$datafile v$tempfile dba_data_files statspack
File Striping : RAID等 \ alter table xxx ALLOCATE ...
Full Table Scans: DB_FILE_MULTIBLOCK_READ_COUNT(defautl 128 block , 1MB)  #跟OS也有关系,操作系统一次最大能读多大KB
v$session_longops
select * from v$sysstat where name like '%table scan%'; #table<4block short table , table>4block long table
dbms_application_info.set_session_longops;
checkpoint 更新数据写到数据文件上
Incremental checkpints (数据库空闲时DBWn干活、oracle一般用不完全检查点) & Full checkpoints (DBWn干活)
Full checkpoints : Complete & Tablesapce (tablespace begin backup, tablespace offline & online)
ALTER SYSTEM CHECKPOINT;
fast_start_mttr_target
fast_start_io_target & fast_start_mttr_target & log_checkpoint_interval & log_checkpoint_timeout
v$instance_recovery
Redo Groups & Members
v$logfile v$log v$log_history
Archivng Performance
ALTER SYSTEM ARCHIVE LOG ALL TO <log_archive_dest>
log_archive_dest_n
log_archive_dest_state_n
log_archive_max_processes
v$archive_dest v$archived_log v$archive_processes


###########
#Tuning the Shared Pool
Shared Pool : Library cache & Data dictionary cache & User global area(共享服务器模式，现在很少用，如果要用最好用large_pool)
SHARED_POOL_SIZE
SGA_TARGET & MEMORY_TARGET
ORA-04031
v$sgastat
hard parse & soft parse
SQL Sharing Criteria : SQL语句先hash比较，再逐个字符比较(包括空格，大小写，以及注释) 跟用户环境也有关系 优化器也有关系
CURSOR_SHARING
v$sysstat parse count (hard) ..
Bind variables : :变量名
execute immediate 'sql' #执行动态SQL
execute immediate 'sql :x' using 变量;
set timing on
Shared Pool Latches
hard parse还消耗其他大量的资源 : Latches ...
v$librarycache
Gets Pins Reloads Invalidations gethitratio pinhitratio
v$sgastat : shared pool : free memory
v$sql v$sqlarea v$sqltext v$db_object_cache
statspack
SHARED_POOL_SIZE OPEN_CURSORS SESSION_CACHED_CURSORS CURSOR_SPACE_FOR_TIME 
CURSOR_SHARING SHARED_POOL_RESERVED_SIZE
data warehousing : 使用bind variables反而慢 , OLTP shared_pool_size优化不适用OLAP
data warehousing : result cache
v$SQL_SHARED_CURSOR
package以所有者执行而不是调用者
避免高峰做DDL操作 : DDL使对象invalidated 需要重新解析
Reloads should be less than 1% of the pins : select sum(reloads)/sum(pins) from v$librarycache;
execute dbms_stats.gather_table_stats('HR','EMPLOYEES');
v$shared_pool_advice
Cached Execution Plans
v$sql_plan hash_value
----
#library cache size 估算（要有负荷）
select sum(sharable_mem) from v$db_object_cache;  #非SQL对象占的共享内存
select sum(sharable_mem) from v$sqlarea;  #SQL对象占的共享内存
----
Reserved Pool : SHARED_POOL_RESERVED_SIZE (share_pool_size 5%--10% 最大50%)
dbms_shared_pool.aborted_request_threshold
v$shared_pool_reserved
REQUEST_FAILURES is more than zero and increasing : SHARED_POOL_SIZE & SHARED_POOL_RESERVED_SIZE太小
REQUEST_FAILURES is zero or not increasing & FREE_MEMORY is greater then or equal to of 50% of SHARED_POOL_RESERVED_SIZE: SHARED_POOL_RESERVED_SIZE太大
REQUEST_FAILURES>0 and increasing & Request_misses>0 : increase SHARED_POOL_RESERVED_SIZE
REQUEST_FAILURES>0 and increasing & Free_memory=>50% of SHARED_POOL_RESERVED_SIZE : increase SHARED_POOL_SIZE
REQUEST_FAILURES=0 or Free_memory=>50% of SHARED_POOL_RESERVED_SIZE : Decrease SHARED_POOL_RESERVED_SIZE
#Keeping Large Objects
Pin large packages
execute dbms_shared_pool.keep('package_name');  #最好在startup database后执行 可以用trigger
ALTER SYSTEM FLUSH SHARED_POOL;
select sql_text from v$sqlarea where command_type=47 and length(sql_text)>500; #查询大的匿名块
#Tuning the DD Cache
v$rowcache : gets , getmisses
#Large Pool : 仅使用一次，使用完就归还，不参与LRU算法
I/O server process : DBWR_IO_SLAVES
Backup and restore operations
Session memory for the shared servers
Parallel query messaging
LARGE_POOL_SIZE
select * from v$sgastat;


############
#Tuning the Buffer Cache  (LRU lists [MRU,LRU]) (单位Block)
Default buffer cache, Keep buffer cache, Recycle buffer cache, nk_buffer_caches #相互独立
User's server process(从磁盘读数据), DBWn(写buffer中的数据到磁盘)
SYSTEM, SYSAUX, Undo等Data file
LRU lists, Checkpoint Queue(dirty block)
DB_BLOCK_SIZE DB_CACHE_SIZE DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE DB_BLOCK_CHECKSUM DB_FILE_MULTIBLOCK_READ_COUNT
读一致性 : 一个block可以在buffer cache中有多个copies, 但最新的copy只有一个
Buffers in the buffer cache can be in one of four stats : Pinned Clean Free/unused Dirty
DB_BLOCK_CHECKSUM : block校验码检查 增加1%--2%的负荷
DB_CACHE_ADVICE v$db_cache_advice
statistics_level
oracle phyical reads不一定就读磁盘，有时用file system cache，但不推荐用file system cache(OS)
statspack
v$sysstat v$sesstat v$system_event(没有记录就说明没有这个event) v$session_wait v$session_event v$buffer_pool_statistics v$buffer_pool v$db_cache_advice v$bh v$cache
Tuning techniques : Bypass the cache for sorting and parallel reads(使用pga或user's server process，并不使用buffer cache) ...
Free Buffer Inspected, Free Buffer Waits, Buffer Busy Waits
---------
#命中率SQL(注意版本) or statspack  [buffer cache的命中率不是越高越好，要参考其他event，wait事件]
select 1-(phy.value-lob.value-dir.value)/ses.value "CACHE HIT RATIO"
from v$sysstat ses, v$sysstat lob, v$sysstat dir, v$sysstat phy
where ses.name='session logical reads' and dir.name='physical reads direct'
and lob.name='physical reads direct (lob)' and phy.name='physical reads';
#11g 算法 1-(('physical reads cache')/('consistent gets from cache'+'db block gets from cache'))
---------
Increase the Cache Size #以下先优化在增加buffer cache
--------
Any wait events have been tuned
SQL statements have been tuned
There is no undue page faulting
The previous increase of the buffer cache was effective
Low cache hit ratio
------
alter system set db_cache_size=128M;
DB_nK_CACHE_SIZE
ALTER TABLE table_name STORAGE (BUFFER_POOL RECYCLE);
ALTER INDEX index_name STORAGE (BUFFER_POOL KEEP);
v$bh(x$bh) dba_objects  #The v$bh view shows the data object ID of all blocks that currently reside in the SGA.
----------
select o.object_name, count(*) "Num of Blks"
from dba_objects o, v$bh bh
where o.data_object_id=bh.objd and o.owner!='SYS'
group by o.object_name order by count(*);
----------
v$buffer_pool   select name, block_size, sum(buffers) from v$buffer_pool group by name, block_size having sum(buffers)>0;
v$cache #可能不存在，运行一个脚本就可以有
v$sess_io
v$buffer_pool_statistics
--------
select name, 1- (physical_reads/(db_block_gets+consistent_gets)) "HIT_RATIO"
from v$buffer_pool_statistics where db_block_gets+consistent_gets>0;
--------
Keep pool : Blocks are accessed repeatedly & segemnt size is less than 10% of the default buffer pool size
Recycle pool : Blocks are not reused outside of transaction & segemtn size is more than twice the default pool size
Caching Tables : creating table with CACHE clause & altering table with CACHE clause & using CACHE hint in a query   #Use a keep pool不要放太多的表
alter table table_name {CACHE|NOCACHE};
select /*+ CACHE*/ last_name, first_name from employee;
CREATE TABLESPACE ... SEGMENT SPACE MANAGEMENT AUTO;  #使用bitmap管理而非free list(老技术)方式
Free list Contention(竞争) : v$waitstat v$system_event v$session_wait dba_segments
Multiple DBWn Processes(多个CPU非常有用) : DB_WRITER_PROCESSES disk_asynch_io dbwr_io_slaves(操作系统不支持异步IO用)
libaio库 : linux支持异步IO的库
FREE BUFFER WAITS event多就可以考虑增加DB Writer processes


##############
#Dynamic Instance Resizing
SGA_TARGET SGA_MAX_SIZE
Granule : SGA调整时最小单位 SGA<128MB时Granule=4MB，SGA>128MB时Granule=16MB
A granule is a unit of contiguous(连续的) virtual memory allocation.
v$buffer_pool
fixed SGA & buffer cache & shared pool ...


##############
#Sizing Other SGA Structures
redo log buffer & java pool
redo log buffer : user server process, LGWR, ARCn #LGWR只有一个进程
Oracle server processes copy redo entries from the user's memory space to the redo log buffer for each DML or DDL statement.
The redo log buffer is a circular buffer.
LOG_BUFFER : Default value : Either 512K or 128K* the value of CPU_COUNT,whichever is greater.
redo log 可以放在最快的磁盘上，专用redo log，容量不一定大
v$sgastat v$session_wait(log buffer space event) v$sysstat(redo entries,redo buffer allocation retries)
There should be no Log Buffer Space waits.
Redo Buffer Alocation Retries value should be near 0 and should be less than 1% of redo entries.
select * from v$system_event where event like 'log file switch completion%';
select * from v$system_event where event like 'log file switch (check%';
select * from v$system_event where event like 'log file switch (arch%';
FAST_START_MTTR_TARGET
LOG_ARCHIVE_MAX_PROCESSES
Reducing Redo Operations
--------
Direct Path loading without arhiving does not generate redo
Direct Path loading with archiving can use Nologging mode
Direct Load Insert can use Nologging mode
Some SQL statements can use Nologging mode
--------
#JAVA Pool
JAVA_SOFT_SESSIONSPACE_LIMIT  #软限制 超过了报warning
JAVA_MAX_SESSIONSPACE_SIZE
select * from v$sysstat where pool='java pool';
SHARED_POOL_SIZE : 8KB per loaded class, 50MB for loading large JAR files
JAVA_POOL_SIZE : 24MB default , 50MB for medium-sized java application


############
#Tuning the Oracle Shared Server  #3层架构模式，shared server一般不用
Listener , Dispatcher(D000,D001,D002) , Shared Server(S000,S001,S002), Request Queue, Response Queue 0..
PGA(UGA，session) 有Larger_pool在Larger_pool里，否则放在Shared Pool
Dedicated server connection configurations can perform well even at 1,000 concurrent connections.
DISPATCHERS MAX_DISPATCHERS SHARED_SERVERS MAX_SHARED_SERVERS CIRCUITS(同时有多少网络连接，逻辑上的电路，网络的知识) PROCESSES
tnsname.ora : SERVER=dedicated(dedicated模式连接) & SERVER=shared(shared模式连接)
一个Dispatcher一般可以负责15--20个用户连接
v$shared_server_monitor v$shared_server v$dispatcher v$dispatcher_rate v$queue
Busy rates & Dispatcher waiting time  #busy rates>50%可以考虑增加dispatcher
select sum(owned) "Clients", sum(busy)*100/(sum(busy)+sum(idle)) "Busy Rate" from v$dispatcher;
alter system set dispatchers='(pro=tcp)(dis=5)';
select * from v$queue;
alter system set shared_servers=3; #shared_servers一般可以自动调整，不需要手动操作，但也可以手动增加或较少
v$circuit


############
#Optimizing Sort Operations  #消耗memory CPU...
Memory-intensive SQL operators : Sort-based(sort,group-by,rollup,window,and so on) & Hash-join & Bitmap operators(merger and inversion)
Concept of work area : Memory allocated by a memory-intensive operator to process its input data
Performance impact of memory
-------
Optimal : Input data fits into the work area(cache)
One-Pass : Perform one extra pass over input data
Multi-pass : Perform serveral extra passes over input data
-------
The goal is to have most work areas running with an optimal size(for example,more then 90% or even 100% for pure OLTP systems),while a smaller fraction of them are running with a one_pass size(for example, less than 10%).Multi-pass execution should be avoided.
pga_aggregate_target #自动管理
*_area_size          #手动管理
WORKAREA_SIZE_POLICY  AUTO | MANUAL
alter session xxx 可以临时修改pga管理方式
-------
#初始设置大小参考，以后可以根据负荷调整
Leave 20% of the available memory to other applications
Leave 80% of memory to the Oracle instance
For OLTP : PAG_AGGREGATE_TARGET=(total_mem*80%)*20%
For DSS(OLAP) : PAG_AGGREGATE_TARGET=(total_mem*80%)*[50%--70%]
-------
v$sql_workarea_histogram v$pgastat v$sql_workarea_active v$sql_workarea v$tempseg_usage v$sql v$sql_plan v$sysstat v$process_memory
v$pga_target_advice v$pga_target_advice_histogram
STATISTICS_LEVEL
statspack 取差值，意义更大
v$pgastat : over allocation count & cache hit percentage & extra bytes read/written & total PGA used for auto workareas & bytes processed
cache hit percentage = bytes processed/(bytes processed + extra bytes read/written)*100%
The PGA cache hit percentage should be higher than 60%.
v$sysstat : workarea executions - optimal & workarea executions - onepass & workarea executions - multipass
#batch批量处理可以设置为手动管理 设置*_area_size参数
alter session set workarea_size_policy=manual;
alter session set sort_area_size=xxx;
SORT_AREA_SIZE sort_area_retained_size
内存放不下，放在Temporary segment里
Temp Space : One single sort segment
CREATE TEMPORARY TABLESPACE xxx TEMPFILE xxx SIZE 1024M;
-------
#Temp Space Segments
Is created by the first sort
Extends as demands are made on it
Comprises extents, which can be used by different sorts
Is described in the sort extent pool(SEP)
-------
-------
#Operations Requiring Sorts
Index creation
Parallel insert operations involving index maintenance
ORDER BY or GROUP BY clauses
DISTINCT values selection
UNION, INTERSECT, or MINUS operators (UNION ALL no sort)
Sort-merger joins
ANALYZE command execution
-------
-------
#Avoiding Sort
Use NOSORT to create indexes
Use UNION ALL instead of UNION
use index access for table joins
create indexes on columns referenced in the ORDER BY clause
Select the columns for analysis
Use ESTIMATE rather than COMPUTE for large objects
-------
select * from v$sysstat where name like '%sort%';  #In a OLTP system [sorts (disk)/sorts (memory)]*100% < 5% #Increase SORT_AREA_SIZE or PGA_AGGREGATE_TARGET
v$sort_usage v$sort_segment v$tempfile dba_temp_files dba_tablespaces


###############
#Using Resource Manager  #最好不用
DBMS_RESOURCE_MANAGER   #desc DBMS_RESOURCE_MANAGER
DBMS_RESOURCE_MANAGER_PRIVS
resource_manager_plan
dbms_session
dba_rsrc_plans
dba_rsrc_plan_directives
...
v$session v$rsrc_plan v$rsrc_consumer_group


###############
#SQL Statement Tuning
Optimizer Modes : Rule-based & Cost-based
best throughput : For applications performed in batch
best response time : For interactive applications
------
SQL statement is affected by the following factors :
OPTIMIZER_MODE Initialization Parameter
Optimizer SQL Hints for Changing the Query Optimizer Goal
Query Optimizer Statistics in the Data Dictionary
------
OPTIMIZER_MODE : ALL_ROWS FIRST_ROWS_n 
The statistics used by the query optimizer are stored in the data dictionary.
DBMS_STATS
optimizer_dynamic_sampling #局部取样，而且影响性能
Hint : /*+ ... */ : FIRST_ROWS ALL_ROWS ...
optimizer_features_enable #enables the optimizer to retain CBO features of previous versions
SQL Plan Management  #替代Stored outlines
DBMS_SPM
-------
Diagnostic Tools :
Statspack
EXPLAIN PLAN
SQL trace and TKPROF
SQL*Plus autotrace feature
Oracle SQL Analyze
-------
Viewing Execution Plan : EXPLAN PLAN & SQL Trace & Automatic Workload Repository & v$SQL_PLAN & SQL*Plus AUTOTRACE
plan_table : utlxplan.sql
EXPLAN PLAN FOR SELECT ...
select * from table(dbms_xplan.display);   #utlxpls.sql utlxplp.sql
ALTER SESSION SET sql_trace = True;  #Format the trace file with TKPROF
dbms_session.set_sql_trace({True|False})
dbms_system.set_sql_trace_in_session(session_id, serial_id, {True|False});
tkprof xxx.trc output.txt [options]
USER_DUMP_DEST
show user parameter
$ORACLE_HOME/sqlplus/admin/plustrce.sql
GRANT plustrace TO PUBLIC;
SET AUTOTRACE {Off | On | Traceonly } [Explain | Statistics ]


#############
#Managing Statistics
Table statistics & Column statistics & index statistics & system statistics(I/O,CPU)
When statistics are updated for a database object, Oracle invalidates any currently parsed SQL statements than access the object.  #re-parsed
DBMS_STATS  #analyze老技术
-----
gather_table_stats
gather_index_stats
gather_schema_stats
gather_database_stats
gather_stale_stats
-----
user_tab_statistics user_tab_col_statistics user_ind_statistics ...
手动统计 & 自动统计
大批量的操作表后收集统计信息
分区表可以只统计修改的分区的统计信息
Segment-Level statistics : v$segstat_name v$segstat v$segment_statistics
Dynamic Sampling : OLAP or 批处理用,OLTP一般不用
Enable Dynamic Sampling : OPTIMIZER_DYNAMIC_SAMPLING [1-10] & use hint也可以
Histograms : dba_histograms dba_tab_histograms
Skewed Data
Height-balanced Histogram(oracle采用的) & Width-balanced Histogram  #oracle缺省为75个桶(buckets)，最大是256个桶
execute dbms_stats.gather_table_stats('HR','EMPLOYEES', METHOD_OPT=>'FOR COLUMNS SIZE 10 salary');
METHOD_OPT : FOR ALL COLUMNS SIZE AUTO
estimate_percent : dbms_stats.auto_sample_size
METHOD_OPT : REPEAT AUTO SKEWONLY
dbms_stats.gather_schema_stats : gather stale & gather empty & gather auto
*_tab_modifications
alter table table_name monitoring; or dbms_stats
system statistics : plan_table : cpu_cost io_cost temp_space
dbms_stats : gather_system_stats set_system_stats get_system_stats
------
Automatic Gathering
execute dbms_stats.gather_system_stats(interval=>120, stattab=>'mystats', statid=>'OLTP');  #statstab : table_name, statid : 标签
execute dbms_stats.gather_system_stats(interval=>120, stattab=>'mystats', statid=>'OLAP');
Manual Gathering
execute dbms_stats.gather_system_stats(gathering_mode=>'START');
execute dbms_stats.gather_system_stats(gathering_mode=>'STOP');
------
execute dbms_stats.import_system_stats(stattab=>'mystats', statid=>'OLAP');
Copy Statistics Between DataBases
dbms_stats.create_stat_table
dbms_stats.export_table_stats
dbms_stats.import_table_stats


#######################
#Using Oracle Blocks Efficiently


#######################
#Using Oracle Data Storage Structures Efficiently
Cluster Table
Partitioning Table
Partition Pruning : Only the relevant partitions are accessed.


#######################
#Application Tuning
IOT Table


#######################
#Using Materialized Views
CREATE MATERIALIZED VIEW
dbms_mview.refresh
push_deferred_rpc
MATERIALIZED VIEW LOG
Query Rewrite
query_rewrite_enabled query_rewrite_integrity
OPTIMIZER_MODE (CBO)
Hints : REWRITE and NOREWRITE
grant query rewrite privileges
dbms_olap


####################
#Monitoring and Detecting Lock Conterntion
Table-lock & Row-lock
Exclusive-lock & Share-lock
v$transaction v$lock v$session v$mystat
LOCK TABLE ...
DML Locks in Blocks : create table ... INITRANS ... MAXTRANS...
v$lock v$locked_object dba_waiters dba_blockers
Deadlocks


####################
#Tuning the Operating System
调优顺序 : 1.MEM 2.I/O 3.CPU
vmstat iostat top
LOCK_SGA
Process & Thread

ps -ef | grep java  &  ps -efL | grep java

-The End-