美文网首页我爱编程
ORACLE性能优化之SQL语句优化

ORACLE性能优化之SQL语句优化

作者: Bobby0322 | 来源:发表于2018-01-25 14:01 被阅读307次

    #1 使用EXISTS代替IN

    AND T6.ORG_ID IN 
        (SELECT M1.ORG_ID FROM T_BAS_ORGANIZATION M1        -- 这里改成递归查询
        START WITH M1.ORG_ID = 'E46B7E66F2C54D5EAE31E7C32676FD3A'
        CONNECT BY M1.PARENT_ORG_ID = PRIOR M1.ORG_ID)
    
    AND EXISTS
        (SELECT 1 FROM T_BAS_ORGANIZATION M1        -- 这里改成递归查询
        WHERE T6.ORG_ID = M1.ORG_ID
        START WITH M1.ORG_ID = 'E46B7E66F2C54D5EAE31E7C32676FD3A'
        CONNECT BY M1.PARENT_ORG_ID = PRIOR M1.ORG_ID)
    
    Oracle

    通过使用EXISTS,Oracle会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间。Oracle在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。

    #2 ORACLE查询优化之IS NULL和IS NOT NULL优化

    -- sqlQuery = sqlQuery + $@" AND T5.PAPER_ID IS NULL" -- 这一句会导致查询变慢,执行计划并不会走索引而是全表扫描        
    -- 改为 AND NVL(T5.PAPER_ID,'0') = '0' 
    

    #3 ORACLE查看被锁的表和解锁

    SELECT B.OWNER
          ,B.OBJECT_NAME
          ,A.SESSION_ID
          ,A.LOCKED_MODE 
    FROM V$LOCKED_OBJECT A,DBA_OBJECTS B WHERE B.OBJECT_ID = A.OBJECT_ID;
    

    ORACLE里锁有以下几种模式:
    0:NONE
    1:NULL 空
    2:ROW-S 行共享(RS):共享表锁,SUB SHARE
    3:ROW-X 行独占(RX):用于行的修改,SUB EXCLUSIVE
    4:SHARE 共享锁(S):阻止其他DML操作,SHARE
    5:S/ROW-X 共享行独占(SRX):阻止其他事务操作,SHARE/SUB EXCLUSIVE
    6:EXCLUSIVE 独占(X):独立访问使用,EXCLUSIVE
    数字越大锁级别越高, 影响的操作越多。

    1级锁有:SELECT,有时会在V$LOCKED_OBJECT出现。
    2级锁有:SELECT FOR UPDATE,LOCK FOR UPDATE,LOCK ROW SHARE
    SELECT FOR UPDATE当对话使用FOR UPDATE子串打开一个游标时,所有返回集中的数据行都将处于行级(ROW-X)独占式锁定,其他对象只能查询这些数据行,不能进行UPDATE、DELETE或SELECT FOR UPDATE操作。
    3级锁有:INSERT, UPDATE, DELETE, LOCK ROW EXCLUSIVE
    没有COMMIT之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
    4级锁有:CREATE INDEX, LOCK SHARE
    LOCKED_MODE为2,3,4不影响DML(INSERT,DELETE,UPDATE,SELECT)操作, 但DDL(ALTER,DROP等)操作会提示ORA-00054错误。
    5级锁有:LOCK SHARE ROW EXCLUSIVE
    具体来讲有主外键约束时UPDATE / DELETE ... ; 可能会产生4,5的锁。
    6级锁有:ALTER TABLE, DROP TABLE, DROP INDEX, TRUNCATE TABLE, LOCK EXCLUSIVE

           OWNER   OBJECT_NAME  SESSION_ID LOCKED_MODE
    1   BOEREMS T_RVW_REVIEW_WORK   251 3
    2   BOEREMS T_RVW_REVIEW_WORK   242 3
    3   BOEREMS T_RVW_REVIEW_WORK   241 3
    4   BOEREMS T_RVW_REVIEW_WORK   229 3
    5   BOEREMS T_RVW_REVIEW_WORK   226 3
    6   BOEREMS T_RVW_REVIEW_WORK   218 3
    7   BOEREMS T_RVW_REVIEW_WORK   213 3
    8   BOEREMS T_RVW_REVIEW_WORK   172 3
    9   BOEREMS T_RVW_REVIEW_WORK   156 3
    10  BOEREMS T_RVW_REVIEW_WORK   147 3
    11  BOEREMS T_RVW_REVIEW_WORK   114 3
    12  BOEREMS T_RVW_REVIEW_WORK   98  3
    13  BOEREMS T_RVW_REVIEW_WORK   91  3
    14  BOEREMS T_RVW_REVIEW_WORK   75  3
    15  BOEREMS T_RVW_REVIEW_WORK   69  3
    16  BOEREMS T_RVW_REVIEW_WORK   59  3
    17  BOEREMS T_RVW_REVIEW_WORK   26  3
    18  BOEREMS T_RVW_REVIEW_WORK   19  3
    19  BOEREMS T_RVW_REVIEW_WORK   2   3
    
    
    SELECT T2.USERNAME,
           T2.SID,
           T2.SERIAL#,
           T3.OBJECT_NAME,
           T2.OSUSER,
           T2.MACHINE,
           T2.PROGRAM,
           T2.LOGON_TIME,
           T2.COMMAND,
           T2.LOCKWAIT,
           T2.SADDR,
           T2.PADDR,
           T2.TADDR,
           T2.SQL_ADDRESS,
           T1.LOCKED_MODE
      FROM V$LOCKED_OBJECT T1, V$SESSION T2, DBA_OBJECTS T3
     WHERE T1.SESSION_ID = T2.SID
       AND T1.OBJECT_ID = T3.OBJECT_ID
     ORDER BY T2.LOGON_TIME;
     
     ALTER SYSTEM KILL SESSION 'SID,SEIAL#';
     
     ALTER SYSTEM KILL SESSION '626,315';
    

    #4 ORACLE错误一览

    ORA-12514

    ORA-04030: 在尝试分配 254476 字节 (QERHJ hash-joi,kllcqas:kllsltba) 时进程内存不足

    (内部异常 #7) Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-04030: 在尝试分配 254476 字节 (QERHJ hash-joi,kllcqas:kllsltba) 时进程内存不足

    问题结果:服务器内存过小。
    下面分析了ORA-04030的出现原因及简单解决方法
    ORA-04030出现的基本都是过多的使用memory造成的
    Oracle process使用的内存数量是有一定限制的:

    SQL> show parameter sga;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    lock_sga                             boolean     FALSE
    pre_page_sga                         boolean     FALSE
    sga_max_size                         big integer 2464M
    sga_target                           big integer 0
    
    SQL> show parameter pga;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    pga_aggregate_target                 big integer 0
    
    SQL> show parameter memory;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    hi_shared_memory_address             integer     0
    memory_max_target                    big integer 4G
    memory_target                        big integer 4G
    shared_memory_address                integer     0
    
    SQL> show parameters area_size;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    bitmap_merge_area_size               integer     1048576
    create_bitmap_area_size              integer     8388608
    hash_area_size                       integer     131072
    sort_area_size                       integer     65536
    workarea_size_policy                 string      AUTO
    
    SQL> select * from v$pgastat;
     
    NAME                                                                  VALUE UNIT
    ---------------------------------------------------------------- ---------- ------------
    aggregate PGA target parameter                                   1795162112 bytes
    aggregate PGA auto target                                         631001088 bytes
    global memory bound                                               150930432 bytes
    total PGA inuse                                                  1107343360 bytes
    total PGA allocated                                              1160961024 bytes
    maximum PGA allocated                                            1166142464 bytes
    total freeable PGA memory                                                 0 bytes
    process count                                                            71 
    max processes count                                                      75 
    PGA memory freed back to OS                                               0 bytes
    total PGA used for auto workareas                                  13237248 bytes
    maximum PGA used for auto workareas                               556406784 bytes
    total PGA used for manual workareas                                       0 bytes
    maximum PGA used for manual workareas                                     0 bytes
    over allocation count                                                     0 
    bytes processed                                                  5267555635 bytes
    extra bytes read/written                                         2914225152 bytes
    cache hit percentage                                                  94.75 percent
    recompute count (total)                                                 393 
     
    19 rows selected
    

    估计pga突破了限制,导致总内存超过了1.7G,32bit操作系统最多使用1.7G

    SQL> show parameter pool;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    buffer_pool_keep                     string      
    buffer_pool_recycle                  string      
    global_context_pool_size             string      
    java_pool_size                       big integer 0
    large_pool_size                      big integer 0
    olap_page_pool_size                  big integer 0
    shared_pool_reserved_size            big integer 41104179
    shared_pool_size                     big integer 0
    streams_pool_size                    big integer 0
    
    • 设置rman从SGA取内存
    SQL> alter system set dbwr_io_slaves=2 scope=spfile;
    System altered 
    SQL> alter system set backup_tape_io_slaves=true scope=spfile; 
    System altered
    
    • 调整SGA大小
    SQL> alter system set sga_target=1200m; 
    System altered
     
    SQL> alter system set sga_max_size=1200m scope=spfile; 
    System altered
    
    • 设置使用内存最大大小
    SQL> alter system set large_pool_size=80m; 
    System altered
    
    • 重启oracle service。

    • 查看sga,pga,pool的大小。

    SQL> show parameter pool;
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    buffer_pool_keep                     string      
    buffer_pool_recycle                  string      
    global_context_pool_size             string      
    java_pool_size                       big integer 0
    large_pool_size                      big integer 80M
    olap_page_pool_size                  big integer 0
    shared_pool_reserved_size            big integer 41104179
    shared_pool_size                     big integer 0
    streams_pool_size                    big integer 0
    

    相关文章

      网友评论

        本文标题:ORACLE性能优化之SQL语句优化

        本文链接:https://www.haomeiwen.com/subject/qfjvaxtx.html