ORA-01653: unable to extend table SYS.SQL$TEXT in tablespace SYSAUX

上周生产线报告客户端无法连接应用服务器,查看应用服务器,所有进程运行正常。查看应用log,发现如下报错:

?SQ-F-SQL error : SQLCA.Code -1653
ORA-01653: unable to extend table SYS.SQL$TEXT by 8192 in tablespace SYSAUX

select xxxx from t1 cont where  cont.pid = 'Bxxxxx'  and cont.pid_type = 'xx_ID'

查看数据库alert.log,发现大量ORA-1653的告警

Tue Mar 19 15:45:13 2013
ORA-1653: unable to extend table SYS.SQL$TEXT by 128 in                 tablespace SYSAUX
ORA-1653: unable to extend table SYS.SQL$TEXT by 8192 in                 tablespace SYSAUX
Tue Mar 19 15:45:13 2013
ORA-1653: unable to extend table SYS.SQL$TEXT by 128 in                 tablespace SYSAUX
ORA-1653: unable to extend table SYS.SQL$TEXT by 8192 in                 tablespace SYSAUX
Tue Mar 19 15:45:16 2013
ORA-1653: unable to extend table SYS.SQL$TEXT by 128 in                 tablespace SYSAUX
ORA-1653: unable to extend table SYS.SQL$TEXT by 8192 in                 tablespace SYSAUX
Tue Mar 19 15:45:16 2013
ORA-1653: unable to extend table SYS.SQL$TEXT by 128 in                 tablespace SYSAUX
ORA-1653: unable to extend table SYS.SQL$TEXT by 8192 in                 tablespace SYSAUX
ORA-1653: unable to extend table SYS.SQL$TEXT by 128 in                 tablespace SYSAUX
ORA-1653: unable to extend table SYS.SQL$TEXT by 8192 in                 tablespace SYSAUX
Tue Mar 19 15:45:17 2013
ORA-1653: unable to extend table SYS.SQL$TEXT by 128 in                 tablespace SYSAUX
ORA-1653: unable to extend table SYS.SQL$TEXT by 8192 in                 tablespace SYSAUX
Tue Mar 19 15:45:18 2013
ORA-1653: unable to extend table SYS.SQL$TEXT by 128 in                 tablespace SYSAUX
ORA-1653: unable to extend table SYS.SQL$TEXT by 8192 in                 tablespace SYSAUX
ORA-1653: unable to extend table SYS.SQL$TEXT by 128 in                 tablespace SYSAUX
ORA-1653: unable to extend table SYS.SQL$TEXT by 8192 in                 tablespace SYSAUX

查看TABLESPACE,SYSAUX确实满了

Status       Tablespace Name                        Used (Bytes)           TBS Size (Bytes)  Used1%     MAX Size (Bytes)  Used2%
ONLINE    SYSAUX                                          60,596,486,144       60,596,486,155   99.99           60,596,486,155   99.99

解决办法,为SYSAUX增加新的datafile,问题解决。

事情到此似乎圆满解决,当然表空间使用状况应该平日监控,这是教训。

但仔细想想,我们每个应用程序都有独立的SCHEMA,为什么一条应用程序的SQL语句会用到SYSAUX表空间中的SYS.SQL$TEXT表?

先简单介绍一下环境,应用程序是三层架构,CLIENT-APP_SERVER-DB_SERVER


数据库环境:

DB NameDB IdInstanceInst numStartup TimeReleaseRAC
DB2594061185DB1111-Feb-13 00:0211.2.0.3.0YES

Host NamePlatformCPUsCoresSocketsMemory (GB)
xxdb001Linux x86 64-bit88247.13


先看一下SYSAUX里每个component所占的空间

SELECT
  occupant_name,
  ROUND( space_usage_kbytes/1024,2) "Space (M)",
  schema_name
FROM
  v$sysaux_occupants
ORDER BY
  1;

OCCUPANT_NAMESpace (M)SCHEMA_NAME
AO1.38SYS
AUDIT_TABLES0SYS
AUTO_TASK0.38SYS
EM126.31SYSMAN
EM_MONITORING_USER3.56DBSNMP
EXPRESSION_FILTER0EXFSYS
JOB_SCHEDULER3.94SYS
LOGMNR91.63SYSTEM
LOGSTDBY1.38SYSTEM
ORDIM0ORDSYS
ORDIM/ORDDATA0ORDDATA
ORDIM/ORDPLUGINS0ORDPLUGINS
ORDIM/SI_INFORMTN_SCHEMA0SI_INFORMTN_SCHEMA
PL/SCOPE1.56SYS
SDO0MDSYS
SM/ADVISOR187.5SYS
SM/AWR1903.5SYS
SM/OPTSTAT756.94SYS
SM/OTHER73.38SYS
SMON_SCN_TIME3.31SYS
SQL_MANAGEMENT_BASE53539.06SYS
STATSPACK0PERFSTAT
STREAMS1SYS
TEXT0CTXSYS
TSM0TSMSYS
ULTRASEARCH0WKSYS
ULTRASEARCH_DEMO_USER0WK_TEST
WM7.38WMSYS
XDB0XDB
XSAMD0OLAPSYS
XSOQHIST1.38SYS

SQL_MANAGEMENT_BASE占用了53G,这个component是干什么用的?
按照ORACLE官方文档的说法,
SMB(SQL management base): is part of the data dictionary that resides in the SYSAUX tablespace; stores
    a. statement logs
    b. plan histories
    c. SQL plan baselines
    d. SQL profiles


目前的DB中应该有大量的SPM相关信息,这些信息是如何产生的呢?
查看系统参数optimizer_capture_sql_plan_baselines,发现状态是TRUE,说明自动生成baseline的功能已经打开。

SQL> show parameter baseline;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE

这一打开不要紧,造成了数据库自动收集sql,不断生成baselines,且数据库中已经产生了大量的baselines

SQL> select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
   5536248

目前有50多万条记录,由于担心贸然删掉这些baseline会影响效率,因此目前仍然保留。

有人会问,这些baseline到底是如何产生的?

按照ORACLE官方文档的说法
SPM(SQL Plan Management): 11G new feature
 1. statement log: contains the SQL Signature of repeatable statements; in order to recognize repeatable SQL statements; a SQL statement is recognized as repeatable when it is parsed or executed again after it has been logged;
 2. SQL plan history:  contains all plans generated by the optimizer(both accepted and not accepted); is maintained only for repeatable SQL statements.
 3. SQL plan baseline: is really nothing more than a collection of hints to recreate a precise Execution Plan; a set of all accepted plans for a SQL statement in the plan history; contains
     a. set of hints
     b. plan hash value
     c. plan-related info

According to catsmbvw.sql, the view dba_sql_plan_baselines is populated from 
    a. sys.sqlobj$
    b. sys.sqlobj$auxdata
    c. sys.sql$text

NOTE:
sql$: SQL statement table
sql$text: SQL statement text table
sqlobj$: SQL management object table
sqlobj$data: SQL management object data table
sqlobj$auxdata: SQL management object auxiliary data table

SELECT
  dl.table_name,
  ds.segment_name,
  ROUND(ds.bytes/1024/1024,2) "Space (M)"
FROM
  dba_segments ds
JOIN dba_lobs dl
ON
  ds.segment_name=dl.segment_name
WHERE
  dl.table_name LIKE 'SQL%';

TABLE_NAME SEGMENT_NAME Space (M)
SQLOBJ$ SYS_LOB0000305396C00009$$ 0.06
SQLOBJ$DATA SYS_LOB0000305401C00005$$ 43397
SQLOBJ$DATA SYS_LOB0000305401C00007$$ 0.06
SQLOBJ$AUXDATA SYS_LOB0000305407C00036$$ 0.06
SQL$ SYS_LOB0000309320C00005$$ 0.06
SQL$TEXT SYS_LOB0000309324C00003$$ 0.19
SQL$TEXT SYS_LOB0000309324C00005$$ 0.06

那么baseline到底是什么东西,里面包含哪些信息?最好的办法就是生成staging表,然后把某条baseline解压缩到该表中。

select sql_handle from dba_sql_plan_baselines where rownum<5;

SQL_HANDLE
------------------------------
SQL_033b7226aa41767c
SQL_0398f5dac9a26bd2
SQL_06e57a37e1272ec9
SQL_073f1275ad6ad129

随便选取一个sql_handle

var  nRet number;
EXEC :nRet :=dbms_spm.pack_stgtab_baseline(table_name=>'stage1',sql_handle=>'SQL_033b7226aa41767c');

SET long 1000000
SET longchunksize 30
colu sql_text format a30
colu optimizer_cost format 999,999 heading 'Cost'
colu buffer_gets    format 999,999 heading 'Gets'

SELECT sql_text, OPTIMIZER_COST, CPU_TIME, BUFFER_GETS, COMP_DATA FROM stage1;

SQL_TEXT                           Cost   CPU_TIME     Gets COMP_DATA
------------------------------ -------- ---------- -------- ------------------------------
SELECT DECODE(PARAMETER_VALUE,        1          0        0  :B3 , 3, :B2 , 2, :B1 , 1, 0)                              NDEX(@"SEL$1" "MGMT_PARAMETERS
 FROM MGMT_PARAMETERS WHERE PA                              "@"SEL$1" ("MGMT_PARAMETERS"."
RAMETER_NAME = 'system_error_l                              PARAMETER_NAME" "MGMT_PARAMETE
og_level'                                                   RS"."PARAMETER_VALUE"))]]>                                                            nt>                                                            (@"SEL$1")]]>                                                            DATA[ALL_ROWS]]><
                                                            ![CDATA[DB_VERSION('11.2.0.2')
                                                            ]]>                                                            IZER_FEATURES_ENABLE('11.2.0.2
                                                            ')]]>                                                            ORE_OPTIM_EMBEDDED_HINTS]]>                                                            int>


可以看到baseline无非是一些hint的集合,用来还原或者说锁定SQL语句的执行计划,另外还包含一些帮助信息,比如估算的CBO的开销(不太重要),和实际执行产生的统计信息(更为重要)。另外,还包含一些技术细节,比如baseline是何时以何种方式声场的,目前的状态,SQL执行环境的信息。

As you can probably see now, SPM Baseline is really nothing more than a collection of hints (this is what “freezes” SQL statement execution) and some supporting information such as estimated optimizer cost (less important) and real execution statistics (more important). Plus, there is, of course, a number of technical details, such as when and how a baseline was built, its current status as well as what was SQL environment at the time.

SPM获取由初始参数optimizer_capture_sql_plan_baselines决定,默认FALSE;
SPM使用由初始参数optimizer_use_sql_plan_baselines决定,默认TRUE;

SPM的清除规则(purging rule)

SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM   DBA_SQL_MANAGEMENT_CONFIG;

PARAMETER_NAME PARAMETER_VALUE
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53

参数optimizer_capture_sql_plan_baselines为TRUE时,即自动获取sql plan baseline。每条SQL生成执行计划之后,执行之前,会查询statement log中是否存在相同SIGNATURE的记录,如果没有则向statement log插入一条(我个人猜想是sql$表应该是sys.sqllog$表)。SQL第一次执行时并不会生成plan history,是为了防止ad-hoc查询,即任意查询,这种查询一般只会执行一次,所以没有必要产生baseline。

SQL> desc sys.sqllog$
Name                                      Null?    Type
----------------------------------------- -------- -------------------------
SIGNATURE                                 NOT NULL NUMBER
BATCH#                                    NOT NULL NUMBER


当同一条SQL再次执行时,SQL生成执行计划之后,执行之前,会查询statement log中是否存在相同SIGNATURE的记录,如果存在则向plan history插入记录(which will include information used by the optimizer to reproduce the execution plan, such as the SQL text, outline, bind variables, and compilation environment. )(我猜想是sys.sql$表,sys.sql$text表,sys.sqlobj$表,sys.sqlobj$data表和sys.sqlobj$auxdata表),且ACCEPTED状态为YES,ENABLED状态为YES,该执行计划也就成为该SQL的PLAN BASELINE,一条新的baseline就这样诞生了,然后按照执行计划执行该SQL。注意只有ACCEPTED的plan hisotory才是plan baseline,否则只是plan history。

note:
    sql$text表主要记录SQL_HANDLESQL_TEXT;
    sys.sqlobj$表主要记录NAME, FLAGLAST_EXECUTED;
   
sys.sqlobj$auxdata表记录执行环境,绑定变量,执行计划等所有信息;

这也是statement log, plan history, plan baseline这三者的关系。

那么当同一条SQL以后再次被执行时,如果有新的执行计划,仍然会向plan history表中插入一条记录,此时ACCEPTED状态为NO,ENABLED状态为YES,说明它只是一条plan history,而不是baseline,所以也不会被使用到。通过dba_sql_plan_baselines中的LAST_EXECUTED字段也可以证明这点,该地段值为NULL。只有当DBA确认该条计划确实比当前baseline优化时,使用dbms_spm.evolve_sql_plan_baseline演化这条baseline,ACCEPTED状态将演化为为YES,LAST_VERIFIED字段值也会被更新为演化时间。此时同一条SQL会有两条对应的baseline,那么到底是哪一条baseline会被使用呢?答案是COST最低的那条,也就是dba_sql_plan_baselines中optimizer_cost低的那条。

note: plan evolution: the process of adding plans to a SQL plan baseline

 

SQL> desc sys.sql$;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 SIGNATURE                                 NOT NULL NUMBER
 INUSE_FEATURES                            NOT NULL NUMBER
 FLAGS                                     NOT NULL NUMBER
 SPARE1                                             NUMBER
 SPARE2                                             CLOB

SQL> desc sys.sql$text;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 SIGNATURE                                 NOT NULL NUMBER
 SQL_HANDLE                                NOT NULL VARCHAR2(30)
 SQL_TEXT                                  NOT NULL CLOB
 SPARE1                                             NUMBER
 SPARE2                                             CLOB

SQL> desc sys.sqlobj$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SIGNATURE                                 NOT NULL NUMBER
 CATEGORY                                  NOT NULL VARCHAR2(30)
 OBJ_TYPE                                  NOT NULL NUMBER
 PLAN_ID                                   NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 FLAGS                                     NOT NULL NUMBER
 LAST_EXECUTED                                      TIMESTAMP(6)
 SPARE1                                             NUMBER
 SPARE2                                             CLOB

note: 这个FLAGS很关键,它决定了这条plan的状态,只有ACCEPTED为的YES的记录才是plan baselines,才会被用到,其他的只是plan histories;

    enabled,
    accepted,
    fixed,
    reproduced,
    autopurge,

    DECODE(BITAND(so.flags, 1), 1, 'YES', 'NO'),
    DECODE(BITAND(so.flags, 2), 2, 'YES', 'NO'),
    DECODE(BITAND(so.flags, 4), 4, 'YES', 'NO'),
    DECODE(BITAND(so.flags, 64), 64, 'NO', 'YES'),
    DECODE(BITAND(so.flags, 8), 8, 'YES', 'NO'),

SQL> desc sys.sqlobj$auxdata;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SIGNATURE                                 NOT NULL NUMBER
 CATEGORY                                  NOT NULL VARCHAR2(30)
 OBJ_TYPE                                  NOT NULL NUMBER
 PLAN_ID                                   NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(500)
 CREATOR                                            VARCHAR2(30)
 ORIGIN                                    NOT NULL NUMBER
 VERSION                                            VARCHAR2(64)
 CREATED                                   NOT NULL TIMESTAMP(6)
 LAST_MODIFIED                                      TIMESTAMP(6)
 LAST_VERIFIED                                      TIMESTAMP(6)
 PARSE_CPU_TIME                                     NUMBER
 OPTIMIZER_COST                                     NUMBER
 MODULE                                             VARCHAR2(64)
 ACTION                                             VARCHAR2(64)
 PRIORITY                                           NUMBER
 OPTIMIZER_ENV                                      RAW(2000)
 BIND_DATA                                          RAW(2000)
 PARSING_SCHEMA_NAME                                VARCHAR2(30)
 EXECUTIONS                                         NUMBER
 ELAPSED_TIME                                       NUMBER
 CPU_TIME                                           NUMBER
 BUFFER_GETS                                        NUMBER
 DISK_READS                                         NUMBER
 DIRECT_WRITES                                      NUMBER
 ROWS_PROCESSED                                     NUMBER
 FETCHES                                            NUMBER
 END_OF_FETCH_COUNT                                 NUMBER
 TASK_ID                                            NUMBER
 TASK_EXEC_NAME                                     VARCHAR2(30)
 TASK_OBJ_ID                                        NUMBER
 TASK_FND_ID                                        NUMBER
 TASK_REC_ID                                        NUMBER
 FLAGS                                              NUMBER
 SPARE1                                             NUMBER
 SPARE2                                             CLOB

这三张表以SIGNATURE连接,生成了dba_sql_plan_baselines。也是我们平时经常使用的VIEW。

通过staging表也可以看到这些信息:

补充一点,对于没有使用绑定变量的SQL语句,每次谓语词取值发生变化时,在optimizer_capture_sql_plan_baselines设置为true时,每次执行时(确切的说是>2次执行时)均会向plan history(前面提到的那5个表)插入一条记录,这也是为什么SYSAUX表空间越来越大的原因。

结论,为了避免上述问题,建议将参数optimizer_capture_sql_plan_baselines设置为FALSE,每个版本的新特性只有在了解到他的优缺点之后才能加以利用,不要贸然使用。

SQL> show parameter baseline;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE


SQL> alter system set optimizer_capture_sql_plan_baselines=false scope=both sid='*';

SQL> show parameter baseline;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

reference:

TODO:

整理笔记,增加SPM具体内容

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7314577/viewspace-757248/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/7314577/viewspace-757248/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值