oracle is null效率,ORACLE性能优化之SQL语句优化

#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