oralce优化之10大要点九十

 九、SQL优化

    1、优化器模式

    Oracle9i有两种优化器模式可以选择：
    • Rule-based:
    – Uses a ranking system
    – Syntax- and data dictionary–driven
    • Cost-based:
    – Chooses least-cost path
    – Statistics-driven
    Rule-based模式满足向后兼容，而Cost-based模式中的成本大部分来自于逻辑读的次数，推荐使用Cost-based模式。

    2、固定optimizer plan

    1）概念
    对于每一个查询，optimizer都会准备一个定义了操作执行顺序和方法的操作树（执行计划），oracle server根据这个执行计划执行语句。通过固定执行计划，可以强制应用通过一种理想的方式访问数据，并且一个稳定的执行计划可以经历数据库的变化而保持不变。固定执行计划通过创建stored outline实现，outline使用cost-based的optimizer，因为其由一系列的hints组成。
    执行计划的固定依赖于当判定一个查询是否存在stored outline时查询语句是否完全一致，与判定shared pool里一个执行计划是否可以重用时的匹配方式是一致的。
    Outline被保存在outln schema中。

    2） 创建stored outline
    alter session set CREATE_STORED_OUTLINES = train;
    create or replace OUTLINE co_cl_join
    FOR CATEGORY train ON
    select co.crs_id, ...
    from courses co，classes cl
    where co.crs_id = cl.crs_id;

    stored outline通过category组织，相同的sql语句可以在多个category同时拥有stored outline，如果categoey没有指定，缺省是default category。
    当CREATE_STORED_OUTLINES等于true或category名时，oracle会为所有被执行的sql语句创建stored outline，也可以通过create outline手工创建。

    3） 使用stored outline
    将USE_STORED_OUTLINES设置为true或category名。
    alter session set USE_STORED_OUTLINES = train;

    当为一个查询寻找stored outline时，查询语句与stored outline里的语句必须完全一致，在outline里的hints也必须在查询语句中出现。

    3、private outline

    Private outline是当前保存的stored outline的副本，可以被编辑而不影响正在运行的系统，一个private outline只能被当前session看到，它的数据被保存在当前被解析的schema里。，知道显示的将其公布。
    当USE_PRIVATE_OUTLINES=TRUE时，一个已有outline的sql被提交时，optimizer会检查是否存在private outline，如果不存在，optimizer就不使用optimizer编译语句，而不会去检查公布的stored outline。

    4、在sql中使用hints
    Create index gen_idx on customers(cust_gender);
    Select /*+ index(customers gen_idx)*/
    Cust_last_name,cust_street_address,cust_postal_code
    From sh.customers where upper(gender)=’M’;

    5、EXPLAIN PLAN
    可以不通过tracing，需要建立plan_table表：
    Sql>@oracle_home/rdbms/admin/utlxplan;
    建立explain plan：
    Explain plan for select last_name from hr.emp;
    查询plan_table中的explain plan，可以直接查询，也可以通过脚本utlxplx.sql(隐藏并行查询信息)、utlxplp.sql（显示并行查询信息）查询。

    6、管理统计信息
    利用analyize命令收集或删除信息。
    参数：
    Compute：统计精确的数据；
    Estimate：估计的统计数据。

    各类统计数据的位置：
    表：dba_tables;
    索引：dba_indexes;
    列：user_tab_col_statistics;
    柱状图（histogram）详细的描述了一个特定列中数据的分布情况，可以通过analyize table ... for columns... 命令创建，保存在dba_histogram/dba_tab_histograms中。

 

十、操作系统优化和使用资源管理器

    1、操作系统优化
    1）概念
    操作系统优化时应该考虑的因素有：内存的使用；Cpu的使用；IO级别；网络流量。各个因素互相影响，正确的优化次序是内存、IO、CPU。
    操作系统使用了虚拟内存的概念，虚拟内存使每个应用感觉自己是使用内存的唯一的应用，每个应用都看到地址从0开始的单独的一块内存，虚拟内存被分成4K或8K的page，操作系统通过MMU（memory management unit）将这些page与物理内存映射起来，这个映射关系通过page table控制。
    Raw device是没有文件结构或目录结构的磁盘或磁盘分区，由于它忽略了操作系统缓存，在某些情况下可以显著提升性能，但是在windows NT下，由于操作系统IO操作本身不使用文件系统缓存，所以raw device不能显示性能上的优点。

    2）Guideline
    CPU的最高使用率：90％；
    OS/USER进程数之比：40/60；
    各个CPU的负载应该大致均衡。

    3）服务器安全性检查
    A、检查UNIX系统用户口令
    检查：/etc/passwd、/etc/shadow，UNIX密码采用了shadow机制，安全性能高
    建议：参考UNIX命令passwd，修改/etc/default/passwd文件的某些设置如MAXWEEKS、MINWEEKS、PASSLENGTH使口令修改更加合理化。
    建议：定期更改UNIX系统的如下用户口令：
    root、oraprod、applprod、appprod

    B、检查 Remote Login
    启动了rlogin，服务器数据库a、数据库b、数据库c，终端console1、console2、console3及T3形成相互非常信任的关系，用户只要拥有一个服务器的超级权限就可以rlogin到.rhosts指明的任一主机而无需要口令。
    建议：非常不安全，参考UNIX命令rlogin和/目录下的文件.rhosts。在正式环境服务器和测试环境服务器之间不要建立这种远程信任的机制。

    C、检查FTP服务
    检查可以FTP到服务器的用户（/etc/ftpusers），注释了root用户，就是说用户可以用root权限FTP到服务器上。权限太大。
    建议：把这种权力取消，将/etc/ftpusers中root的注释符号（#）去掉，在列表中添加oraprod、applprod、appprod等用户使之不能FTP服务器。必要时（如上传PATCH时）再打开applprod的FTP权限。
    D、建议：UNIX系统管理员定期检查/var/adm下的messages、sulog；/etc/syslog.conf 等信息。检查是否有非法用户登陆UNIX。
    建议：与UNIX工程师探讨更好的监控方式

    4）数据库与应用产品安全性检查
    A、建议：修改oracle用户根目录下的.profile文件，修改该文件的权限为500。即使得用户登陆时并不执行和数据库或应用相关的环境变量，增加安全性。
    B、检查数据库DBA权限的用户密码和应用系统用户密码：SYSTEM、APPS密码都已经改变，SYS密码还是初始安装密码Change_on_install
    建议：立即修改SYS用户密码，定期更改APPS、SYSTEM、SYS密码。
    C、定期检查并清除$ORACLE_HOME/admin/bdump目录下的alert_PROD.log文件和后台进程trace文件。定期清除$ORACLE_HOME/admin/udump目录下的trc文件。
    D、建议：给应用产品登陆的用户设置口令过期限制，如口令访问次数限制或时间（天数）限制。
    建议：不要给使用应用产品的用户共享用户名和口令，每个用户分配一个应用产品用户名。
    建议：对有应用系统管理员权限的用户登记，不适合有系统管理员权限的用户要把权限回收，统一管理。
    E、定期检查并清除与Apache Server有关的log文件，目录为:
    /u01/prodora/iAS/Apache/Apache/logs/acccess_log、error_log
    /u01/prodora/iAS/Apache/Jserv/logs/jserv.log、mod_jserv.log
    F、定期检查清除listener、tnsname的log文件，文件存放在:
    /u01/prodora/8.0.6/network/admin/apps_prod.log、
    /u01/proddb/8.1.7/network/admin/prod.log
    /u01/proddb/8.1.7/network/log/listener.log、sqlnet.log…
    G、数据库控制文件做多个镜像，放在多个磁盘位置，提高安全性。

    5）网络安全性检查
    检查$ORACLE_HOME/dbs/initPROD.ora文件
    #remote_login_passwordfile=EXCLUSIVE
    设置为REMOTE_LOGIN_PASSWORDFILE=NONE，不允许远程客户用INTERNAL方式登陆。
    2、资源管理器（Resource Manager）
    通过资源管理器可以管理混合工作负载，控制系统性能。数据库资源管理器包括：
    • Resource plans：包括 resource plan directives, 它指定了被分配到各个 resource consumer group的资源。
    • Resource consumer groups：定义了具有类似资源使用需求的一组用户。
    • Resource plan directives：包括下列内容:为consumer groups 或 subplans 指定resource plans；在各个 consumer groups 或资源计划的subplans 分配资源。