上周生产线报告客户端无法连接应用服务器，查看应用服务器，所有进程运行正常。查看应用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 Name | DB Id | Instance | Inst num | Startup Time | Release | RAC |
|---|---|---|---|---|---|---|
| DB | 2594061185 | DB1 | 1 | 11-Feb-13 00:02 | 11.2.0.3.0 | YES |
| Host Name | Platform | CPUs | Cores | Sockets | Memory (GB) |
|---|---|---|---|---|---|
| xxdb001 | Linux x86 64-bit | 8 | 8 | 2 | 47.13 |
先看一下SYSAUX里每个component所占的空间
SELECT
occupant_name,
ROUND( space_usage_kbytes/1024,2) "Space (M)",
schema_name
FROM
v$sysaux_occupants
ORDER BY
1;
| OCCUPANT_NAME | Space (M) | SCHEMA_NAME |
|---|---|---|
| AO | 1.38 | SYS |
| AUDIT_TABLES | 0 | SYS |
| AUTO_TASK | 0.38 | SYS |
| EM | 126.31 | SYSMAN |
| EM_MONITORING_USER | 3.56 | DBSNMP |
| EXPRESSION_FILTER | 0 | EXFSYS |
| JOB_SCHEDULER | 3.94 | SYS |
| LOGMNR | 91.63 | SYSTEM |
| LOGSTDBY | 1.38 | SYSTEM |
| ORDIM | 0 | ORDSYS |
| ORDIM/ORDDATA | 0 | ORDDATA |
| ORDIM/ORDPLUGINS | 0 | ORDPLUGINS |
| ORDIM/SI_INFORMTN_SCHEMA | 0 | SI_INFORMTN_SCHEMA |
| PL/SCOPE | 1.56 | SYS |
| SDO | 0 | MDSYS |
| SM/ADVISOR | 187.5 | SYS |
| SM/AWR | 1903.5 | SYS |
| SM/OPTSTAT | 756.94 | SYS |
| SM/OTHER | 73.38 | SYS |
| SMON_SCN_TIME | 3.31 | SYS |
| SQL_MANAGEMENT_BASE | 53539.06 | SYS |
| STATSPACK | 0 | PERFSTAT |
| STREAMS | 1 | SYS |
| TEXT | 0 | CTXSYS |
| TSM | 0 | TSMSYS |
| ULTRASEARCH | 0 | WKSYS |
| ULTRASEARCH_DEMO_USER | 0 | WK_TEST |
| WM | 7.38 | WMSYS |
| XDB | 0 | XDB |
| XSAMD | 0 | OLAPSYS |
| XSOQHIST | 1.38 | SYS |
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
Name Null? Type
----------------------------------------- -------- -------------------------
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_HANDLE和SQL_TEXT；
sys.sqlobj$表主要记录NAME, FLAG和LAST_EXECUTED；
sys.sqlobj$auxdata表记录执行环境，绑定变量，执行计划等所有信息；
那么当同一条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
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表也可以看到这些信息：
结论，为了避免上述问题，建议将参数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/
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
