Oracle
性能优化
-SQL
优化
(
案例三
)
问题：
客户反馈取消记账耗时太长，10
分钟也结束不了。
问题分析：
问题重现后，远程查看主要慢在一条查询SQL
。
查看执行计划gl_voucher
大表全表扫描，实际上
SQL
中谓词字段上有特别高效的索引，怀疑统计信息不准确。
查看gl_voucher
统计信息记录行数
0
行，显然
oracle
认为扫描一个
0
行的表不需要走索引。
SQL> Select * from user_tables where table_name in (
‘
GL_VOUCHER
’
,
’
GL_TMP_TABLE
’
);
解决方案：
手动重新收集gl_voucher
表统计信息
Analyze table gl_voucher compute statistics;
SQL> Select * from user_tables where table_name in (
‘
GL_VOUCHER
’
,
’
GL_TMP_TABLE
’
);
再次执行速度有很大提升。
注意：
统计信息不准确经常会导致性能问题，如果数据库自带收集统计信息的任务已经停了，可以考虑使用如下存储过程定期收集统计信息，此存储过程并不适用于所有场景，根据实际情况进行调整。
---
创建收集统计信息的存储过程：
---需要显式地赋予用户建表权限
grant
create
any
table
to
chen
;
---创建收集统计信息的存储过程
CREATE
OR
REPLACE
PROCEDURE
ANALYZE_TB
AS
OWNER_NAME
VARCHAR2
(
100
);
V_LOG
INTEGER
;
V_SQL1
VARCHAR2
(
800
);
V_TABLENAME
VARCHAR2
(
50
);
CURSOR
CUR_LOG
IS
SELECT
COUNT
(*)
FROM
USER_TABLES
WHERE
TABLE_NAME
=
'ANALYZE_LOG'
;
--1
BEGIN
--DBMS_OUTPUT.ENABLE (buffer_size=>100000);
--1.1
BEGIN
OPEN
CUR_LOG
;
FETCH
CUR_LOG
INTO
V_LOG
;
IF
V_LOG
=
0
THEN
EXECUTE
IMMEDIATE
'CREATE TABLE ANALYZE_LOG (USER_NAME VARCHAR(20),OP_TIME CHAR(19) DEFAULT to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),ERROR_TEXT VARCHAR(200),TABLE_NAME VARCHAR(40))'
;
END
IF
;
END
;
SELECT
USER
INTO
OWNER_NAME
FROM
DUAL
;
V_SQL1
:=
'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES ('''
||
OWNER_NAME
||
''',''ANALYZE BEGIN'',''ALL'')'
;
EXECUTE
IMMEDIATE
V_SQL1
;
sys.dbms_stats.gather_schema_stats
(
ownname
=>
UPPER
(
OWNER_NAME
),
estimate_percent
=>
100
,
method_opt
=>
'FOR ALL INDEXED COLUMNS'
,
cascade
=>
TRUE
);
V_SQL1
:=
'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES ('''
||
OWNER_NAME
||
''',''ANALYZE END'',''ALL'')'
;
EXECUTE
IMMEDIATE
V_SQL1
;
commit
;
--1.2 delete tmptb statitics and lock statistics
BEGIN
for
x
in
(
select
a.table_name
,
a.last_analyzed
,
b.stattype_locked
from
user_tables a
,
user_tab_statistics b
where
a.temporary
=
'Y'
and
a.table_name
=
b.table_name
and
(
b.STATTYPE_LOCKED
is
null
or
a.last_analyzed
is
not
null
))
LOOP
IF
x.last_analyzed
IS
NOT
NULL
THEN
--delete stats
dbms_stats.delete_table_stats
(
ownname
=>
user
,
tabname
=>
x.table_name
,
force
=>
TRUE
);
END
IF
;
IF
x.stattype_locked
IS
NULL
THEN
--lock stats
dbms_stats.lock_table_stats
(
ownname
=>
user
,
tabname
=>
x.table_name
);
END
IF
;
END
LOOP
;
end
;
EXCEPTION
WHEN
OTHERS
THEN
IF
CUR_LOG
%
ISOPEN
THEN
CLOSE
CUR_LOG
;
END
IF
;
commit
;
end
;
---
创建
job,
当天的凌晨2点开始更新统计信息，以后每2天的凌晨2点更新统计信息。
根据实际情况调整时间。
SQL
>
VARIABLE
JOBNO
NUMBER
;
SQL
>
VARIABLE
INSTNO
NUMBER
;
SQL
>
SQL
>
BEGIN
2
SELECT
INSTANCE_NUMBER
INTO
:
INSTNO
FROM
V$INSTANCE
;
3
DBMS_JOB.SUBMIT
(:
JOBNO
,
4
'ANALYZE_TB; '
,
5
TRUNC
(
SYSDATE
)
+
1
+
2
/
24
,
6
'TRUNC(SYSDATE)+2+2/24'
,
7
TRUE
,
8
:
INSTNO
);
9
COMMIT
;
10
END
;
11
/
PL
/
SQL
procedure
successfully completed
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
