Oracle 9i BUG导致statspack无法收集快照

说明：

文章整理自2014-08-30我在ITPUB博客发布的《Oracle statspack无法收集快照，及解决办法》

https://blog.itpub.net/29785807/viewspace-1260544/

注意：文章发布时间较久，可能存在错误，仅供参考，请勿用于生产环境。

环境说明：

Oracle 9.0.1.0.0 RoseHA

问题现象：

手动收集一次快照，产生如下错误

SQL> show user
USER is "PERFSTAT"


SQL> execute statspack.snap


BEGIN statspack.snap; END;


*
ERROR at line 1:
ORA-01401 : inserted value too large for column
ORA-06512 : at "PERFSTAT.STATSPACK", line 1148
ORA-06512: at "PERFSTAT.STATSPACK", line 2134
ORA-06512: at "PERFSTAT.STATSPACK", line 72
ORA-06512: at line 1

解决方案：

尝试重新创建statspack

注意：此方法不生效

1.备份 perfstat 用户下所有表

exp perfstat/perfstat file='/home/oracle/perfstat_tab/perf.dmp' owner=perfstat


cjc-db-01$ cd perfstat_tab/
cjc-db-01$ ll -rth
总计 4.9M
-rw-r--r-- 1 oracle oinstall 4.9M 08-30 09:47 perf.dmp

2.删除用户及用户下所有表

SQL> drop user perfstat cascade;
User dropped.

3.执行脚本创建用户，表，指定表空间，产生如下错误

SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
ERROR at line 1:
ORA-00955 : name is already used by an existing object --- 说明 statspack 信息没有完全卸载

4.删除收集信息的表空间

SQL> select file_name,tablespace_name,bytes/1024/1024||'M' M from dba_data_files;
/home/oracle/oradata/orcl/statspack01.dbf STATSPACK 100M

备份表空间

RMAN> run{
2> allocate channel d1 type disk format '/home/oracle/perfstat_tab/tbs_%s_%p_%T.sp';
3> backup tablespace statspack filesperset 3;
4> }

删除表空间

SQL> drop tablespace statspack including contents and datafiles;
Tablespace dropped.

5.重新创建表空间

SQL> create tablespace statspack_chen datafile '/home/oracle/oradata/orcl/statspack_chen01.dbf' size 150M;
Tablespace created.

6.重新执行脚本，仍然显示同样的错误？

SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
ERROR at line 1:
ORA-00955: name is already used by an existing object

7.执行脚本 spdrop.sql ，删除信息

SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql

8.在重新执行 spcreate.sql 脚本，创建用户，表，指定表空间

SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql

9.检查用户权限等信心

SQL> show user
USER is "PERFSTAT"


SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE SEQUENCE
CREATE PROCEDURE
7 rows selected.


SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS SYSTEM
SYSTEM SYSTEM
OUTLN SYSTEM
DBSNMP SYSTEM
CJC CJCTBS
PERFSTAT STATSPACK_CHEN


6 rows selected.

10.重新收集快照，仍然报相同的错误，看来只重建解决不了此问题

SQL> execute statspack.snap
BEGIN statspack.snap; END;


*
ERROR at line 1:
ORA-01401 : inserted value too large for column
ORA-06512 : at "PERFSTAT.STATSPACK", line 1148
ORA-06512: at "PERFSTAT.STATSPACK", line 2134
ORA-06512: at "PERFSTAT.STATSPACK", line 72
ORA-06512: at line 1

SQL> select 1 - (phy.value (cur.value + con.value)) "HIT RATIO"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads';
  2    3    4    5 


HIT RATIO
----------
.72201289

难道是BUG？

 11.查询MOS

资料内容：Run statspack.snap report error ora-1401

fact: Oracle Server - Enterprise Edition 8.1.7.2
fact: PL/SQL
fact: SYSPKG - SYSTEM PACKAGES
symptom: Error running STATSPACK report
symptom: Execute STATSPACK.SNAP fails
symptom: ORA-01401 inserted value too large for column
cause: :
ORA-1401 WHEN STATPACK.SNAP IS EXECUTED


If a sql statement contains Multibyte characters, and STATSPACK.SNAP needs to
store information about the sql statement, an ORA-01401 may occur.


fix:


is fixed in 8.1.7.3, 9.0.1.2 and 9.0.2.


Workaround:


Edit $ORACLE_HOME/rdbms/admin/spcpkg.sql, and change the one occurance of
"substr" to "substrb".
Rerun spcpkg.sql to apply changes.

12.修改脚本 spcpkg.sql

cjc-db-01$ vim spcpkg.sql

将下面内容：

select l_snap_id
, p_dbid
, p_instance_number
, substr (sql_text,1,31)

改成：

select l_snap_id
, p_dbid
, p_instance_number
, substrb (sql_text,1,31)

原因：这个问题只会出现在多位的字符集 ,substr 会将多位的字符 , 当作一个 byte。substrb 则会当作多个 byte。因位 statpack 会将   top 10 的 sql 前 31 个字 存入 table 中 ,若在 SQL 的前 31 个字有中文，则会有此错误。

13.再次执行脚本，删除重建

SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql

解决：

收集快照成功

SQL> execute statspack.snap
PL/SQL procedure successfully completed.

SQL> select snap_id,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') time from stats$snapshot;
SNAP_ID TIME
---------- -------------------
1 2014-08-30 10:55:48

SQL> execute statspack.snap
PL/SQL procedure successfully completed.

SQL> select snap_id,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') time from stats$snapshot;
SNAP_ID TIME
---------- -------------------
1 2014-08-30 10:55:48
2 2014-08-30 11:04:47

SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
1
2
20140830chen_report.txt

cjc-db-01$ cd perfstat_tab/
cjc-db-01$ ls
20140830chen_report.txt spcpkg.lis spcusr.lis spdusr.lis
perf.dmp spctab.lis spdtab.lis tbs_45_1_20140830.sp

cjc-db-01$ vi 20140830chen_report.txt
STATSPACK report for


DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORCL          3278851613 orcl                1 9.0.1.0.0   NO      server1


Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 1 30-Aug-14 10:55:48 48 5.9
End Snap: 2 30-Aug-14 11:04:47 48 6.3
Elapsed: 8.98 (mins)


Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 92M Std Block Size: 4K
           Shared Pool Size:       128M          Log Buffer:       400K


Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.15 In-memory Sort %: 99.61
Library Hit %: 99.33 Soft Parse %: 97.63
         Execute to Parse %:   21.12         Latch Hit %:  100.00
......

###chenjuchao 20240224###