sql优化实例oracle,Oracle性能优化-SQL优化(案例三)

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"，共同学习，共同成长！！！