暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / ORACLE优化

ORACLE优化

木底木叉 2020-09-17
683


Oracle Resource Manager 基本概念

Resource consumer group

   一个资源使用组由一组具有相似请求的用户组成，一个组可以包含许多用户，一个用户又可以是多个组的成员（这个很重要），实际中多个 session 使用一个用户名访问数据库是很普遍的，但是同一时刻，每个 session 只能有一个组作为这个 session 的有效使用者组。

当新创建一个 session 时， oracle 会根据你的设定自动把它分配到某个组（初始化组）。如果以某个用户登录的 session，它的用户名是属于多个组的，数据库管理员还可以手动的切换这个 session 所属的组。 

sys_group sys 和 system 属于这个组

default_consumer_group  没有指定用户属于哪个组时缺省放在这个组里

other_group 在资源计划里除了指定组以外的用户都默认在这个组里

 OTHER_GROUPS是绑定在资源计划中的，所有资源计划必须要包括OTHER_GROUPS 组，这个组的作用就是作为一个后选项，当一个没有匹配到任何资源组的SESSION 连接到数据库的时候会自动的匹配到 OTHER_GROUPS 下面，受制于 OTHER_GROUPS 的资源限定。

Resource plan

 在一个数据库中同一时间只能有一个资源计划 active。(也可以无任何资源计划 active)。一个资源计划还可以包含子资源计划  

查看当前被激活的资源计划的三个方法：

  • show resoure_manager_plan 参数  

  • select name,is_top_plan from v$rsrc_plan;  

  • OEM

Resource plan directives

 资源管理的目的是实现组或 session 对于资源分配的优先级，一部分用户在某时间内可以更多的享受资源，而另一部分则不能。 

 资源计划指令就是给出各种限定的条件，例如给某个组分配一定百分比的 CPU 时间，或者限制一个组内最大活动的会话数等等。 

exec dbms_resource_manager.set_initial_consumer_group('TIM','OLTP');
exec dbms_resource_manager.set_initial_consumer_group('MIKE','DSS');
exec dbms_resource_manager.set_initial_consumer_group('MGR','OLTP');

select username,INITIAL_RSRC_CONSUMER_GROUP from dba_users;


alter system set resource_manager_plan='DAYTIME'
select resource_consumer_group from v$session where username='MGR';


declare old_grp varchar2(30);begin
dbms_session.switch_current_consumer_group('DSS',old_grp,TRUE);
end;
/

select resource_consumer_group from v$session where username='MGR';

execute dbms_resource_manager.switch_consumer_group_for_user('MGR','OLTP');

其他资源指令和阈值

  • 以上介绍的 cpu 分配方法在原文中叫 EMPHASI 方法(按%分），还有一种叫 RATIO（比例）的方法，两种性质相同，但表达方式不同，前者有 8 个 level,每个 level 中合计值不能超过100，而后者是一种比例关系，只有一个 level，合计值可以超过 100。

  • 其它几种指令

    • 活动会话池：目的是限制一组同时运行的 SESSION 数量，假设 DSS 组有 8 个用户，如果就让 3 个可以运行（ active session)。可将该组最大激活数设为 3，那么另外的 5 个可以连接上来，但要排队等着激活。而排队也可设延迟时间，超时后就会报错  

    • 限制并行度：  Oracle 可通过参数设置并行度，如：parallel_max_servers,（创建一个并行执行服务器池），但是无法阻止任何人使用它，于是我们可以通过 Resource Management 加以限制 。

    •    通过执行时间控制作业 ：  数据库中一个大型作业会挤掉其他用户性能， Threshold 指令可以解决这个问题。到了时间阀值，按 action 的规定去做  

    • 依据空闲时间终止 session ： 不做任何事情的 session 浪费服务器资源，如 PGA 白白占用， idle time 指令从两个方面限制这样的情况，比如某组的 max idle time(秒） =1800，表示空闲了 3 分钟， block anothersession(秒） =30,表示把别人锁了 30 秒，这两种情况都会终止该 session。

    • 限制 undo 数据的产生 ： 某些用户的大型事务可能填满 undo 表空间，如批处理事务不定期的提交，为了安全起见，可以对这里潜在的用户设置 undo 表空间使用上限，比如某组其 undo pool 设置为 6G，当到达这个上限值后该组中的所有 session 都会被挂起，直至事务提交后释放池中的空间  

配置使用者组自动切换

select resource_consumer_group from v$session where username='MGR';
select count(*) from all_object;
select resource_consumer_group from v$session where username='MGR';

 若选择 revert after call=yes, MGR 任务结束后又返回到 OLTP 组  

 激活的资源计划有一个参数：resource_manager_plan，但是 resource_limit 这个初始化参数与资源计划毫无关系。 

  • 赋给一个用户管理资源管理器的能力，需要系统权限 ADMINISTER RESOURCE MANAGER,而这个权限不是使用通常的 grant 方法授予的 ，只能使用 oracle 提供的程序包。DBMS_RESOURCE_MANAGER 和 DBMS_RESOURCE_MANAGER_PRIVS 是有关资源管理器的 PL/SQL API形式的最有用的两个包  

  • DBMS_RESOURCE_MANAGER_PRIVS 包负责：授予管理 Resource Manager 的权限，将用户放置到组中，从组中删除用户。DBMS_SESSION 和 DBMS_RESOURCE_MANAGER 包 负责切换会话的有效组，创建使用者组，配置会话映射到组的方式。 

  • 在每个级别的总 CPU 使用率不能超过 100%，如果超过的话，则挂起区域将无法验证并且其资源计划不能保存到数据字典，资源计划容许在一个级别分配的资源<100%,但这样做没有什么意义  

  • 每个资源计划都必须包含一条针对 OTHER_GROUPS 组的指令。 

  • 会话池不限制连接会话的数量，限制的是活动会话的数量。活动会话如果没有提交，该会话仍然对该组的活动会话池计数有效。 

  • 每个组的 undo pool 与 undo 表空间配额无关，你甚至不可能授予 undo 表空间上的配额。

    ORACLE自动任务调度

      在 Oracle 中任务调度指某一（组）执行程序在特定的时间被周期性的执行。Oracle 把任务调度称为 job（作业）。 

    概念

     JOB 翻译为作业，一个基本的 JOB 由两方面组成：program 和 schedule。JOB 总体上可分为两大类，基于时间的 JOB 和基于事件的 JOB。在 Oracle 10g 之前，采用 dbms_job 程序包来完成任务调度的相关工作。在 Oracle 10g 之后， Oracle 推出了功能更加强大的dbms_scheduler 来完成作业调度工作。

     必须将 JOB_QUEUE_PROCESSES 实例参数设置为>0 的值，否则调度程序将无法运行，默认值为 1000。如果有任何定义的、活动的作业，那么总是运行作业队列协调器 。

    show parameter job_queue_processes;
    select program from v$process where program like '%J%';

     PL/SQL BLOCK:标准的 pl/sql 代码块； 

     STORED PROCEDURE :编译好的 PL/SQL 存储过程，或者 Java 存储过程，以及外部的 c 子程序  

     EXECUTEABLE :ORACLE 数据库之外的应用，比如操作系统命令等等。 

     Scheduler 有的资料翻译为调度，有的翻译成时间表，简单来说指的就是作业运行周期，即时间和频率  

     相当于创建了一个 job 组，可以将那些具有相同特性的 job，统统放到相同的 Job Classes中，在 Job Classes 中的 Jobs 可以指派优先级（ 1-5）， ,默认优先级是 3，而 1 的优先级最高，然后让 Job Classes 与资源计划器结合进行管理  

     窗口指定了作业运行的起始时间(START DATE)、终止时间(END DATE)，运行时间(DURATION)，以及重复间隔(REPEAT INTERVAL)等，它实际上扩展了 SCHEDULER(时间表）的概念，所谓扩展是指窗口可以激活资源管理计划，当窗口打开时，数据库自动切换到相关的 resource plan上，此时这个 resource plan 便处于活动状态（ ACTIVE),当窗口关闭时，将切换回原来的resource plan,前提是此时没有其他窗口打开,  窗口对于 job class 也有着特殊的意义，通过 job class 和 resource plan 联系，这就使窗口下被激活的 resource plan 中所关联的 job class 中的所有 job 被激活了,这些 job 会根据优先权调度运行。 

     CHAIN 可以被视做一组 Programs 的组合，举个简单的例子：运行 PROGRAM:A 以及 PROGRAM:B，如果成功的话继续运行 PROGRAM:C，否则的话运行 PROGRAM:D。Programs:A、 B、 C、 D 以及执行的逻辑关系就构成了一个最简单的 CHAIN。 

     轻型作业不显示在 DBA_SCHEDULER_JOBS 视图中，因为轻型作业不是模式对象，与普通作业相比创建和删除轻型作业的开销非常小。使用轻型作业有两种情形：1）作业成百上千次的批量执行， 2）单个作业运行时间较短。   DBMS_SCHEDULER 程序包可以使用最少的语法来创建轻量作业。轻量作业只有很少的参数可以指定：作业参数和计划。作业的其余元数据（包括权限）都是从作业模板继承来的。轻量作业的模板必须是 PL/SQL 块或存储过程。轻量作业必须使用 DBMS_SCHEDULER 程序包在 PL/SQL中创建。JOB_STYLE 参数在 EM 中是不可见的。 

  1. 轻型作业  

  1. JOB CHAIN  

  1. WINDOW  

  1. JOB CLASSES  

  1. Advanced SCHEDULER 下的 schedule  

  1. Advanced SCHEDULER 下的 program  

  1. Advanced Scheduler下的job

dbms_scheduler.create_job(
job_name =>'test_ltwtjob1',
program_name =>'test_prog',
repeat_interval =>'freq=hourly',
end_date =>to_timestamp(sysdate+1),
job_style =>'lightweight'
    
    
    
dbms_scheduler.create_job(
job_name =>'test_ltwtjob1',
program_name =>'test_prog',
shedule_name =>'test_sched'
job_style =>'lightweight',

  1. 基于事件(Event)的作业  

  • Scheduler 触发的 Events  

    Scheduler 中触发的 Events，一般是说当前 schduler 中 job 的状态发生修改，类似 job 启动，或者运行结束，或者达到运行时间等诸如此类的动作，都能够抛出一个 EVENT，接收到EVENT 的 application 就可以根据这些信息进行适当的处理 。

  • Application 触发的 Events  

    外部的应用也可以触发 Events，并且由 Scheduler 来接收并处理这一类型的 Events。所谓Scheduler 处理 EVENT 就是指 Scheduler 启动相应的 job 来执行相关操作，这类 job 在创建时专门声明了 event 的处理，这样当接收到 EVENT 时，这类 job 就会启动  

JOB相关的视图

DBA_SCHEDULER_JOBS
DBA_SCHEDULER_RUNNING_JOBS; //这两个都是查看当前 job 运行状态的(考点）
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOB_RUN_DETAILS
                                                  
select log_id, log_date, status, additional_info fromuser_scheduler_job_run_details where job_name = 'MY_JOB1';                                                  

 exec dbms_scheduler.enable('my_job1');
                                                  
exec dbms_scheduler.disable('my_job1');                                                  

exec dbms_scheduler.drop_job('my_job1');

exec dbms_scheduler.run_job('my_job1');
                                                  
exec dbms_scheduler.stop_job('my_job1');                                                  
DBMS_SCHEDULER.set_attribute管理job属性                                                  
                                                  

Oracle 数据库采样ASH和AWR

ASH（Active Session History）

 ASH 收集的是活动会话的样本数据， Oracle 的会话状态可以分为 3 种， 1） on cpu,2)waiting,3)idle. 前两种都是活动会话（Active Session）而 v$session 包括了所有的（三种状态）当前会话，它每秒采样一次，那么 ASH 就以 v$session 为数据源，只记录活动会话信息，不活动的会话不会记录，记录数据在 SGA 缓冲区中。最终将 ASH 信息存入了 AWR 库。有关 ASH数据采集和存入 AWR 库的过程都由后台进程 MMNL 来完成的。 

@/u01/oracle/rdbms/admin/ashrpt.sql--生成ASH报告

AWR(Automatic Workload Repository)

 AWR 架构是从 oracle 10g 开始的，它以快照形式自动收集并保存和数据库有关性能统计数据，它的前身 Statspack， AWR 的作用是提供一个时间段内整个系统资源使用情况的报告，它存储重要的累计统计信息。通过这个报告，我们就可以了解一个系统的整个运行情况。 

 AWR 工作时是由后台进程 MMON 负责，于每 1 小时生成一个内存统计的快照,并写入磁盘上的sysaux 表空间，快照不能移动到其他位置，快照也会作为 ADDM 的原始数据，缺省情况下，Oracle 将快照保留 8 天。 

 @/u01/oracle/rdbms/admin/awrrpt.sql

  • AWR 收集数据库有关性能信息：它是新的数据库自动调优机制的核心，结果是以每小时一次快照的形式将关键数据的写入 SYSAUX 表空间。 

    • 基本统计数据，也是 v$sysstat 和 v$sesstat 视图中收集的系统和会话的统计信息； 

    • SQL 统计数据，分别按执行时间、 cpu 时间、执行次数等标准来统计  

    • 对象的统计信

    • 时间模型统计信息，告知每个数据库活动要花多长时间。(在 v$sys_time_model 和v$sess_time_model 视图中查看)；

    • 等待统计数据（来自 V$session 视图中的几个新添加的字段）

    • ASH 统计信息，包含近期会话活动的历史记录  

    • 数据库特性利用的统计数据

    • 各种管理顾问会话的结果，如 ADDM、 Segment Advisor、 Sql Access Adivisor 等  

    • 操作系统的统计数据，如 I/O 和内存的利用率

  • AWR 度量（metric)：两个或多个统计数据综合的结果。它是衡量累计性能统计数据变化率的统计指标。度量有两个主要作用， 1）所有管理顾问都使用 metrics 诊断性能问题并给出调优建议。2） metrics 是服务器产生预警特性的基础。 

  • AWR 基准线（baseline)。是一种快照集，由多个快照组成。通过把当前性能与基本阶段的性能进行比较，可以检验数据库运行的优劣。基线可以被无限期保留，除非你设定删除，因此基线总是拿来比较性能，而不像快照有 retention 限制。oracle数据库中包含了三种类型的基线

    • 静态基准线(Fixed Baselines)  

    • 移动窗口基线(Moving Window Baseline)  

  • 基线模板(Baseline Templates)  

  begin-- singe 基线模板
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
  START_TIME => TO_TIMESTAMP(SYSDATE+2),
  END_TIME => TO_TIMESTAMP(SYSDATE+10),
  BASELINE_NAME => 'Mybase4',
  TEMPLATE_NAME => 'Mytemp4',
  EXPIRATION => NULL);
  end;
  /
  BEGIN-- repeat 基线模板
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
  day_of_week => 'monday', hour_in_day => 17,
  duration => 3, expiration => 30,
  start_time => '2014-01-01 17:00:00',
  end_time => '2014-12-31 20:00:00',
  baseline_name_prefix => 'baseline_2014_mondays_',
  template_name => 'template_2014_mondays');
  END;
  /

  • 自适应阈值(Adaptive Thresholds)  

    • 最大值的百分比：阈值以最大值的百分比倍数的方式设计，  

    • 重要性级别：阈值被设为一个统计学中的百分位来观察基于移动窗口基线数据的阈值以上的值，来体现异常程度。百分位能指定为以下几种：高(0.95)， 100 个中只有 5 个能超过这个值；非常高(0.99)：100 个中只有 1 个能超过这个值；严重的(0.999)：1000 个中只有 1 个能超过这个值；极端的(0.9999)：10000 个里只有 1 个能超过这个值  

  • 与 AWR 有关参数  

show parameter statistics_level
若参数 STATISTICS_LEVEL 设置为 TYPICAL 或 ALL 将默认启用 AWR 来采集数据库统计信息。ALL 参数收集信息最全，参数的默认值是 TYPICAL。
如果 STATISTICS_LEVEL 设为 BASIC，将禁用收集快照和运行顾问。但你仍可以通过DBMS_WORKLOAD_REPOSITORY 包来手动获得 AWR 统计信息。

ADDM(  Automatic Database Dianostic Monitor )

 ADDM 相当于 Oracle 内部的一个顾问系统，它能够自动的完成对数据库的一些优化建议，它是根据 AWR 每小时采集的数据，看看有没有性能问题，如果有就给出建议调用各个相关的指导（Advisor)，比如建议做 SQL Tuning Advisor, 或 SQL Access Advisor，或者建议创建相关索引，总之， 给出建议是 ADDM 作为一个顾问的特色  

 ADDM的另一个特点是自动生成的 ADDM报告，默认它会包括当前快照和前一个快照的时间段，如果想要ADDM跨越更长的时间段，也可以手动调用ADDM生成包括任意两个快照间的时间段。与 ADDM 有关的参数：control_management_pack_access 缺省是 DIAGNOSTIC+TUNING，如果设成 NONE，则 ADDM 关闭  。

 收集 AWR 快照时自动运行 ADDM，根据情况决定是否产生报告，也可以根据需要手动生成 ADDM 报告，并将其结果保存到 AWR 中。默认情况下 ADDM 报告保存 30 天。 

@/u01/oracle/rdbms/admin/addmrpt.sql
exec dbms_workload_repository.create_snapshot;
业务活动
exec dbms_workload_repository.create_snapshot;
OEM：Server-->Advisor Central-->Advisor Task Results（画面下方）已经显示了一个 ADDM结果

其他顾问 （Advisor）  

 SQL Advisors   :

SQL Tuning Advisor: 对单个 SQL 语句提供调优建议,生成 sql profile， sql 语句执行路径分析， sql 语句结构分SQL Access Advisor: 评估 SQL 语句对数据库负荷的影响，提供建议。如 index,partition,materilizer view 等SQL Repair Advisor: 对可能的 oracle 内部错误，如 ORA-600 需要的 patch(补丁）提出建议  

Memory Advisors:

  可以对 Oraclen 内存结构（SGA+PGA）做自动调整，以适应数据库在不同时间段的工作量变化。

 Segment Advisor:    提供段收缩命令（shrink)。释放未使用的空间。

 Undo Advisor  :  为 undo 表空间的大小提供建议，如避免快照太旧的问题。 

 MTTR Advisor：  为实例恢复的时间提供建议。 

关于 Advisor 的 API's 程序包  

 首先是 DBMS_ADVISOR: 不过，它只是顾问管理中一部分包的套件，有一些 Advisor 有它们自己的包，如：Automatic Database Diagnostic Monitor (DBMS_ADDM)SQL Performance Analyzer (DBMS_SQLPA)SQL Repair Advisor (DBMS_SQLDIAG)SQL Tuning Advisor (DBMS_SQLTUNE)  

优化器（CBO）和优化器统计信息

show parameter optimizer_dynamic_sampling;

CBO 优化器得出执行计划，依据的是优化器统计数据(optimizer statistics), 而优化器统计数据又是怎么产生的？由 DBMS_STATS 程序包派生  

优化器有两种工作模式 tuning mode 和 normal mode.  

 tuning 模式下就是 SQL Tuning Advisor.可以有充足的时间完成下面四个方面的工作：1）统计信息分析， 2）产生 SQL profile ， 3）访问路径分析， 4） SQL 结构分析。normal 模式下，对一个新的 sql（查询）语句，优化器要产生对应的执行计划，没有办法细致的分析，即可能的得到执行计划不是最优的。 

预警及诊断系统  

 注 意 两 个 视 图 ：dba_outstanding_alerts 和dba_alert_history 的关系。

create tablespace small datafile '/u01/oradata/timran11g/small01.dbf' size
10m extent management local uniform size 3m;
create table scott.test_table(id int) tablespace small storage(minextents
3);
select reason from dba_outstanding_alerts;
alter database datafile '/u01/oradata/timran11g/small01.dbf' resize 20m;
select reason from dba_outstanding_alerts;
select reason,resolution from dba_alert_history where reason like '%表空间%';
drop tablespace small including contents and datafiles;

oracle 性能调优

硬解析和软解析

解析过程

  • parse 分析语法语义

    • 从共享池的库缓冲区搜索，该语句是否执行过，凡是执行过的 sql 语句， oracle 会使用 HASH 函数计算，产生一个很小的文本记录， 如果是第一次执行则进入第二步。

    • 检查语法，权限（权限的信息放在 oracle 的数据字典当中。oracle 先从共享池的数据字典缓冲区中搜素，如果没有，再从数据文件 （system 表空间的数据文件）当中读取， 然后，存放在数据字典缓冲区，以便共享

    • 分析过程中，对访问到的表进行锁操作，目的是保护表的结构不被修改，优化器会根据数据的存储结构（表的存储结构），统计 信息，计算读取的代价，生成执行计划同时编译并存储在共享池的缓冲区中

  • BIND 变量，优化器会考虑绑定变量来确定执行计划。 

  • 查询优化器建立执行计划  

  • 执行库池里的执行计划， 返回结果  

硬解析从1开始 软解析从4开始

共享游标

 父游标和子游标同属共享游标范畴，通过视图 V$sqlare 和 V$sql 可以得到具化。父游标在进行硬解析时产生，当产生父游标的同时跟随父游标会产生相应的子游标，此时 v$sql 中的child_number 的值为 0。同样的父游标由于不同的运行环境会产生不同的子游标。如果一条sql 语句不但与 library cache 中的父游标匹配，同时又和其下的子游标匹配，那么这个 sql语句就称为共享游标匹配  。

1.与父游标 SQL 文本完全一致的情形下，多个相同的 SQL 语句可以共享一个父游标。2.当 SQL 文本，执行环境完全一致的情形下，子游标能够被共享，否则，当环境不一致时，将在父游标下生成新的子游标  

使用 BIND 变量减少硬解析：

create table scott.m1(x int);
create or replace procedure proc1
as
begin
for i in 1..10000
loop
execute immediate
'insert into scott.m1 values(:x)' using i;
end loop;
end;
/
---------不带绑定变量的查询
create table scott.m2(x int);
create or replace procedure proc2as
begin
for i in 1..10000
loop
execute immediate
'insert into scott.m2 values('||i||')';
end loop;
end;
/

 1，硬解析通常是由于不可共享的父游标，如经常变动的 SQL 语句，或动态 SQL 或未使用绑定变量等。  2，为了减少硬解析，通常是考虑使用绑定变量的办法  


 当一个查询执行时，数据库在 cache memory 查找是否存在结果集，如果有就从内存中取出而不再执行查询，如果没有则执行查询，返回结果， RESULT CACHE 这个新特性可以看成是执行计划的一个补充内容，利用内存中保存的结果集，可以避免再次软解析 。 

进一步说， Result Cache 又可以分为：Server Result Cache 和 Client Result Cache。前者通过服务器端 SGA 来缓存结果集保存 shared pool 中，后者通过客户端来缓存结果集。客户端结果集缓存针对使用 OCI 应用程序(考点), 并不使用服务器端的内存，不会对服务器的内存使用造成影响  

 （Oracle Call Interface， OCI）是 Oracle 数据库访问的一种底层接口，通过它可以高效地访问 Oracle 数据源，性能优于 ODBC, 是开发大型 Oracle 数据库应用程序的利器。 

show parameter result;
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 1056K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0



前两行是 client_result_cache 的参数
后四行是 server result cache 的参数



result_cache_max_size=0 则表示禁用该特性。result_cache_max_result 则控制单个缓存结果可以占总的 Server Result Cache 大小的百分比。
参数 result_cache_mode 用于控制 Server Result Cache 的模式，有 3 个可选设置。
result_cache_mode=AUTO：则优化器自动判断是否将查询结果缓存。
result_cache_mode=MANUAL：则需要通过查询提示来告诉优化器是否缓存结果。
result_cache_mode=FORCE：则尽可能地缓存查询结果，通过提示：no_result_cache 可以拒绝缓存。

内存管理

memory_max_target:    该参数设置 Oracle 实例可以使用的最大内存量。memory_target<=memory_max_target.这个参数是静态初始化参数。 

memory_target:该参数设置整个 oracle 数据库实例可以使用的内存量，自动的调节 SGA 与 PGA 的大小。该参数是可以动态调整的初始化参数  

 如果你的初始化参数文件里没有指定 memory_max_target，即使用它的缺省值，那么memory_max_target=memory_target  

 如果你不想设置 SGA 与 PGA 的最小值，可以把 sga_target 与 pag_aggregate_target 初始化参数都设置为 0。 

 11g 的 参 数 memory_max_target 参 数 如 果 不 设 ， 数 据 库 将 自 动 使memory_max_target=memory_target.  

 SGA 中可以自动调整的只有 5 个池分别是：DB_CACHE_SIZESHARED_POOL_SIZELARGE_POOL_SIZEJAVA_POOL_SIZESTREAMS_POOL_SIZE还有几个池是不能自动调整的分别是：LOG_BUFFERDB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZEDB_nK_CACHE_SIZE  

执行计划

使用 AUTOTRACE  

 autotrace 的结果将放在一个叫 plan_table 的表中  

desc plan_table;
drop table plan_table;
@$ORACLE_HOME/rdbms/admin/utlxplan
@$ORACLE_HOME/sqlplus/admin/plustrce
grant plustrace to scott;
set autotrace on;
set autotrace traceonly explain;--仅显示执行计划策略。
set autotrace traceonly statistics;--仅显示统计信息

nested_loop join (嵌套循环连接）  

 指定一个表作为驱动表（ driving table),也叫外部表（ outer table), 连接中的另一表被称为内部表（ inner table),Oracle 对驱动表中的每行数据都要读取内部表的所有行。

 适用：driving table 比较小，而 inner table 较大且有索引，此法效率高。使用较小内存，第一次返回结果较快  

hash join (散列连接）  

 在连接两个表时， Oracle 用其较小的表在连接键上构建一个 hash 表，然后,Oracle 对较大的表进行搜索，并从 hash 表中返回被连接的行。 

 适用：大数据连接，连接字段缺乏索引， hash 连接比嵌套循环有效。但需要大内存。第一次结果返回较慢  

sort_merge join（分类合并连接）  

 分类连接操作将连接键上的输入值进行分类，合并连接操作将已分类的表进行合并，如果输入值已经按照连接列进行了分类，那么没必要为每个行源执行分类连接操作。 

 适用：两个大表，连接字段缺乏索引，但已有排序。此法为最优化吞吐量设计，并且在结果没有全部找到前不返回数据  

可以使用 hints 强制优化器使用以下连接：
/*+ use_nl（ emp1,dept1） */ 表示采用嵌套循环连接。
/*+ use_merge（ emp1,dept1) */ 表示采用排序合并连接。
/*+ use_hash（ emp1,dept1） */ 表示采用哈希连接。
/*+ leading(emp1) */ 表示选择 emp 为驱动表。
/*+ ordred */ 按照 from 列出的表顺序进行连接

SQL 计划管理 SPM  

 SQL 的一些顾问可以调优 SQL 语句，但最多是一种被动的机制，而且需要 DBA 干预。而 SPM（ SQL Plan Management)可以提供一种预防性的机制，为保持执行计划始终处于最优状态。Oracle 引入 SQL 计划基线的概念，目的是使 SQL 执行计划得以进化。 

 dba_sql_plan_baselines  




oracle
文章转载自木底木叉，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论