OracleGSQLTuning实用教案

1、目录(ml) 1、Oracle数据库结构 2、SQL调优的主要方法 搜集统计信息 执行(zhxng)计划的查看 确定运行速度慢的语句 使用索引 使用hints 使用物化视图第1页/共29页第一页，共30页。Oracle Database ArchitectureTwo main components: The database:physical structures The instance:memory structuresThe size and structure of these components impact perfomance.第2页/共29页第二页，共30页。Oracle

2、Database Architecture The database:-The control file-The redo log files-The data files-The parameter file-The password file The instance:-SGA-PGA-background processes-server processes第3页/共29页第三页，共30页。Oracle Database Architecture第4页/共29页第四页，共30页。SGAnDatabase buffer cache: Caches blocks of data retrie

3、ved from the data filesnRedo log buffer: Caches redo information (used for instance recovery) until it can be written to the physical redo log files stored on disknShared pool: Caches various constructs that can be shared among users第5页/共29页第五页，共30页。SGALarge pool: Optional area in the SGA that provi

4、des large memory allocations for Oracle backup and restore operations, I/O server processes, and session memory for the shared serverJava pool: Used for all session-specific Java code and data within the Java Virtual Machine (JVM) Streams pool: Used by Oracle Streams第6页/共29页第六页，共30页。SGA Oracle 10g 中

5、可以选择手动或者自动的方式来管理(gunl)SGA及各部分的大小，默认为自动管理(gunl)（ASSM） Oracle Database 10g uses an SGA size parameter (SGA_TARGET) that includes all the memory in the SGA, including all the automatically sized components, manually sized components, and any internal allocations during startup. ASMM simplifies the conf

6、iguration of the SGA by enabling you to specify a total memory amount to be used for all SGA components. The Oracle Database then periodically redistributes memory between these components according to workload requirements.第7页/共29页第七页，共30页。PGAThe Program Global Area (PGA) is a memory region, which

7、contains data and control information for each server process. A server process is a process that services a clients requests. Each server process has its own private PGA that is created when the server process is started. Only a server process can access its own PGA.第8页/共29页第八页，共30页。搜集(suj)统计信息 Ora

8、cle优化器会根据系统表中搜集到的统计信息生产(shngchn)执行计划，所以需要对数据库对象定期进行搜集统计信息 统计信息包括： 对象的统计信息： 表 索引 列 系统的统计信息： I/O性能 CPU性能第9页/共29页第九页，共30页。搜集统计(tngj)信息Oracle 10G有个自动搜集统计信息的作业：gather_stats_job每天自动运行对数据库中的每个对象都进行搜集可以通过系统视图查看是否(sh fu)启用：SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = GATHER_STATS_JOB;可以修改成启用或者不启用：BEGIN

9、DBMS_SCHEDULER.DISABLE(GATHER_STATS_JOB);END;/BEGIN DBMS_SCHEDULER.ENABLE(GATHER_STATS_JOB);END;/可以设置搜集的频率可以设置搜集的方式：比如设置成抽样搜集或者DML_monitoring等第10页/共29页第十页，共30页。搜集统计(tngj)信息手工搜集统计信息(xnx)：使用dbms_stats包 搜集表的统计信息(xnx)： BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNER, TABNAME);END;/ 锁定表的统计信息(xnx)： BEGIN DBMS_S

10、TATS.LOCK_TABLE_STATS(OWNER, TABNAME);END;/锁定schema的统计信息(xnx)： BEGIN DBMS_STATS.LOCK_SCHEMA_STATS(OWNER, TABNAME);END;/查看表的搜集统计信息(xnx)状态：SELECT * FROM DBA_TAB_STATISTICS;第11页/共29页第十一页，共30页。执行(zhxng)计划 执行计划(jhu)是执行SQL语句的一些步骤 查看执行计划(jhu)： 1、explain plan 2、SQL trace 3、Statspack 4、Automatic Workload Rep

11、ository 5、v$sql_plan 6、SQL*Plus AUTOTRACE第12页/共29页第十二页，共30页。EXPLAIN PLAN 产生(chnshng)执行计划 执行计划存储在plan表里 并没有真正执行语句 执行完explain plan语句之后要用dbms_xplan包的方法获取执行步骤 执行：Explain plan for SQL语句 查看：select plan_table_output from ();第13页/共29页第十三页，共30页。V$SQL_PLAN V$sql_plan视图用来查看执行过的SQL语句的执行计划，可以查看不同用户下执行的相同(xin tn)

12、语句的执行计划。 V$SQL_PLAN视图包括adress，hash_value等跟explain plan相同(xin tn)的值，要查看完整的执行计划数也需要调用dbms_xplan包。 select plan_table_output from ( ); V$sql_plan_statistics：存储生成执行计划时的统计信息第14页/共29页第十四页，共30页。AWR AWR报告包括对象的统计信息，系统和会话的统计信息等 通过快照来记录 可以调用dbms_workload_repository包来产生快照和管理快照 可以通过系统视图(sht)查看快照的情况： Dba_hist_snap

13、shot Dba_hist_sql_plan Dba_hist_database_instance 查询AWR：select plan_table_output from table(dbms_xplan.display_awr(sql_id);第15页/共29页第十五页，共30页。SQL*Plus Autotrace Set autotrace off Set autotrace on Set autotrace traceonly Set autotrace traceonly explain：不显示statistics Set autotrace traceonly statistics

14、:只显示statistics 可以在会话级别开启sql trace功能，产生(chnshng)trace文件 Alter session set sql_trace true; 格式化trace文件：TKPROF Tkprof tracefile outputfile;第16页/共29页第十六页，共30页。索引(suyn)1、索引类型： unique 和nounique索引 复合索引 按存储(cn ch)分类： B*_tree Bitmap(不能用merge) Domain indexes Key compression第17页/共29页第十七页，共30页。索引(suyn)一般何时需要建立索引

15、：1、经常在检索和查询语句中出现（where子句）2、连接表的关键字3、外键4、高选择(xunz)度的关键字（此字段相同的值多）5、经常被update的关键字不适合建立索引注：索引里面不记录空值，即如果查询is null 时不会走索引第18页/共29页第十八页，共30页。DML对索引(suyn)的影响1、insert:可能引起分列，如果insert的值超过block的最大值，则要用另一个block存储，用第一个block指向(zh xin)新的block2、delete:索引相应删除3、先delete后insert，对索引的影响最大第19页/共29页第十九页，共30页。索引(suyn)监控 索

16、引扫描只能走索引查询，根据rowid查询数据，而全表扫面可以多块读，即可以并行。 监控索引是否使用(shyng)： alter index index_name monitoring usage; alter index index_name nomonitoring usage; 查看系统视图： select * from v$object_usage where index_name=;第20页/共29页第二十页，共30页。Hints 1、Hints用来影响优化器的决定，改变执行计划。 2、Hints的种类： 单个表（use_index） 多个表(use NL(table1 table2)

17、 整个语句（ALL_ROWS） 3、语法：merge/select/update/delete/insert /*+ */ 或者：merge/select/update/delete/insert -+ 注：1、不会提示错误，如果书写错误，则不执行 2、如果对象有别名(bimng)，一定要使用别名(bimng) 3、视图一般不要使用hints（如果要加可以加上视图名称）第21页/共29页第二十一页，共30页。物化(whu)视图 物化视图是一组查询结果集，有自己的存储数据段，可以建立索引。一般用来建议复杂(fz)的查询和聚合数据。 使用物化视图的好处： 1、节省存储 2、支持查询重写，效率高 3

18、、可以自动刷新第22页/共29页第二十二页，共30页。物化(whu)视图第23页/共29页第二十三页，共30页。物化(whu)视图 Build immediate：创建视图的时候生成数据，build deferred:创建视图的时候只创建结构，并不生成数据，直到第一次更新，默认为build immediate On prebuilt table：可以用之前已经存在(cnzi)的表作为数据源，表名要跟视图名一样 Enable/disable query rewrite:是否支持查询重写，Oracle 10G默认为enable第24页/共29页第二十四页，共30页。物化(whu)视图 物化视图刷新(shu xn)方式： COMPLETE：全量刷新(shu xn) FAST：增量刷新(shu xn) FORCE：如果可以进行增量刷新(shu xn)则使用增量刷新(shu xn)，不然就用全量刷新(shu xn) NEVER：从不刷新(shu xn) 注：可以通过系统视图ALL_MVIEWS的refresh_method查看某个视图的刷新(shu xn)方式第25页/共29页第二十五页，共30页。物化(whu)视图 物化视图刷新形式： 手动：on demand(default) 自动同步：on commit 自动异步：start with next 注：可以通过系