十一长假期间也不得轻松,某日接到业务保障,数据库报错,导致某关键业务不能正常执行,需要立即处理
原因分析
1,登录数据库,查看主机日志,报错内容为ORA-01652,temp表空间不足
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01
2,让业务部门重新执行相关脚本,发现占用temp表空间的具体语句如下,目前temp表空间96GB,大约1个小时会被该sql使用满,sql异常退出
Sql具体如下
INSERT INTO www.WWW_BILL_DTL_TEMP_0101(ACCT_ID,SERV_ID,FEE,BRAND,
PHONE_ID,USER_TYPE) SELECT ACCT_ID, SERV_ID, sum(FEE) FEE,BRAND
,PHONE_ID,USER_TYPE FROM ( SELECT ACCT_ID, SERV_ID, SUM(FEE) F
EE,BRAND,PHONE_ID,USER_TYPE FROM (select a.acct_id,e.serv_id,s
um(b.unpay_fee) FEE,a.brand,a.phone_id,a.user_type from www.ACC_B
ILL_010120121010 A , www.WWW_BILL_DTL_010120121010 B , www.OWE_MO
NITOR_QUEUE_ACTION E where a.bill_id=b.bill_id and A.ACCT_ID=E.
ACCT_ID and a.brand in (:"SYS_B_00",:"SYS_B_01",:"SYS_B_02",:"SY
S_B_03",:"SYS_B_04",:"SYS_B_05") and b.fee_item_id>:"SYS_B_06"
group by a.acct_id,e.serv_id,a.brand,a.phone_id,a.user_type ) g
roup by ACCT_ID, SERV_ID,BRAND,PHONE_ID,USER_TYPE UNION ALL SE
LECT ACCT_ID, SERV_ID, -:"SYS_B_07"*SUM(FEE) FEE,BRAND,PHONE_ID,
USER_TYPE FROM (select a.acct_id,e.serv_id,sum(b.unpay_fee) FE
E,a.brand,a.phone_id,a.user_type from www.WWW_BILL_010120121005 A
, www.WWW_BILL_DTL_010120121005 B , www.WWW_MONITOR_QUEUE_ACTION
E where a.bill_id=b.bill_id and A.ACCT_ID=E.ACCT_ID and a.bran
d in (:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12
",:"SYS_B_13") and b.fee_item_id>:"SYS_B_14" group by a.acct_id
,e.serv_id,a.brand,a.phone_id,a.user_type ) group by ACCT_ID, S
ERV_ID,BRAND,PHONE_ID,USER_TYPE ) GROUP BY ACCT_ID, SERV_ID,BRAN
D,PHONE_ID,USER_TYPE
执行计划如下
Plan hash value: 3236377944
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 19281 (100)| |
| 1 | HASH GROUP BY | | 2 | 184 | 19281 (2)| 00:03:52 |
| 2 | VIEW | | 2 | 184 | 19280 (2)| 00:03:52 |
| 3 | UNION-ALL | | | | | |
| 4 | SORT GROUP BY | | 1 | 92 | 19271 (2)| 00:03:52 |
| 5 | VIEW | | 1 | 92 | 19271 (2)| 00:03:52 |
| 6 | SORT GROUP BY | | 1 | 144 | 19271 (2)| 00:03:52 |
|* 7 | HASH JOIN | | 1 | 144 | 19270 (2)| 00:03:52 |
| 8 | MERGE JOIN CARTESIAN | | 1 | 65 | 8717 (2)| 00:01:45 |
| 9 | TABLE ACCESS FULL | WWW_MONITOR_QUEUE_ACTION | 1 | 26 | 2 (0)| 00:00:01 |
| 10 | BUFFER SORT | | 257K| 9810K| 8715 (2)| 00:01:45 |
|* 11 | TABLE ACCESS FULL | WWW_BILL_DTL_010120121010 | 257K| 9810K| 8715 (2)| 00:01:45 |
|* 12 | TABLE ACCESS FULL | WWW_BILL_010120121010 | 16755 | 1292K| 10552 (1)| 00:02:07 |
| 13 | SORT GROUP BY | | 1 | 53 | 9 (12)| 00:00:01 |
| 14 | VIEW | | 1 | 53 | 9 (12)| 00:00:01 |
| 15 | SORT GROUP BY | | 1 | 79 | 9 (12)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | WWW_BILL_DTL_010120121005 | 1 | 18 | 3 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 79 | 8 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 61 | 5 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | WWW_MONITOR_QUEUE_ACTION | 1 | 26 | 2 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID| WWW_BILL_010120121005 | 1 | 35 | 3 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | ITDX_ACCT_ID_10120121005 | 1 | | 2 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | TPK_BILL_DTL_ID_10120121005 | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
3,继续分析原因。仔细分析该sql语句,实际上可以拆分为两个并列子sql。后一部分的sql执行计划是好的,不优的是上半部分,因此解决方案就很简单了,把上半部分的统计信息和索引做成和下半部分同样的结构。经查确认,上部分的sql缺少相关索引,且由于表今天创建,且插入了大量数据,且没有统计信息,选择列没有设置索引,导致执行计划不优。
SQL> select * from dba_objects where object_name ='WWW_BILL_DTL_010120121010';
OWNER OBJECT_NAME SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S
---------- -------------- ------------------- ------------ ------------ ------------------- ------- - - -
ZC WWW_BILL_DTL_010120121010
1209877 1209877 TABLE 06-OCT-12 06-OCT-12 2012-10-06:11:19:25 VALID N N N
解决方法
1,表WWW_BILL_DTL选择列ACCT_ID,在表WWW_BILL上bill_id,FEE_ITEM_ID添加组合索引,
2,然后重新收集统计信息,sql即走合适的执行路径了
exec dbms_stats.gather_table_stats('ZC',WWW_BILL_DTL_010120121010,method_opt => 'FOR ALL COLUMNS SIZE 1',cascade =>TRUE,estimate_percent => 40,granularity=>'all',degree => 4,no_invalidate => false);
exec dbms_stats.gather_table_stats('ZC',WWW_BILL_010120121010,method_opt => 'FOR ALL COLUMNS SIZE 1',cascade =>TRUE,estimate_percent => 40,granularity=>'all',degree => 4,no_invalidate => false)
简单总结:
1,本次问题出在新建的临时表,没有同时新建相关索引,且在数据创建和大量数据插入后,没有立即收集统计信息,导致oracle无法选择合适的执行计划,进而占用大量临时表空间,sql无法正常执行完成。
2,merge join cartesian(笛卡儿算法):是每个集合的任务一个成员都要与其他集合的每个成员进行匹配。因此原有执行计划需要执行对两个大表的N×N次查询和排序,占用大量temp表空间
分享到:
相关推荐
只读表空间恢复问题 5-35 小结 5-37 练习概览:执行不完全恢复 5-38 6 闪回 课程目标 6-2 闪回技术:概述 6-3 闪回删除和回收站 6-4 回收站 6-5 从回收站还原表 6-7 回收站:自动回收空间 6-8 回收站:...
只读表空间恢复问题 5-35 小结 5-37 练习概览:执行不完全恢复 5-38 6 闪回 课程目标 6-2 闪回技术:概述 6-3 闪回删除和回收站 6-4 回收站 6-5 从回收站还原表 6-7 回收站:自动回收空间 6-8 回收站:...
说明: 确定查询是否获取表级的读取锁, 以防止在包含该查询的事务处理被提交之前更新任何对象读取。这种操作模式提供可重复的读取, 并确保在同一事务处理种对相同数据的两次查询看到的是相同的值。 值范围: TRUE | ...
导入/导出是ORACLE幸存的最古老的两个命令行工具,其实我从来不认为Exp/Imp是一种好的备份方式,正确的说法是Exp/Imp只能是一个好的转储工具,特别是在小型数据库的转储,表空间的迁移,表的抽取,检测逻辑和物理...
4.7 temp表空间溢出处理办法 57 4.8 还有一定表空间为何不能建新表 57 4.9 不带参数建立表发现表空间迅速变小 57 第5章 ORACLE数据字典与视图 58 5.1 常用数据字典与视图 58 5.1.1 dba_tablespaces(user_...
– – – – – SYSTEM 表空间 DATA 表空间 USER 表空间 TOOLS 表空间 TEMP 表空间 表空间 2-2 数据库 SYSTEM 表空间 Data1.Ora 1MB Data2.Ora 1MB 数据文件 USER 表空间 Data3.Ora 4MB 数据库的控制空间分配(例如...
– – – – – SYSTEM 表空间 DATA 表空间 USER 表空间 TOOLS 表空间 TEMP 表空间 表空间 2-2 数据库 SYSTEM 表空间 Data1.Ora 1MB Data2.Ora 1MB 数据文件 USER 表空间 Data3.Ora 4MB 数据库的控制空间分配(例如...
在数据库运行时删除了这个文件, ORACLE仍然用原来的文件指针进行写操作,有可能写一个不存在的文件导致硬盘空间占用。 我们要采用以下的方法: $tail -100 $ORACLE_BASE/admin/orasid/bdump/alert_orasid.log...
1.2.1 表空间 2 1.2.2 文件 2 1.3 实例 3 1.4 数据库内部结构 3 1.4.1 表、列和数据类型 4 1.4.2 约束条件 5 1.4.3 抽象数据类型 6 1.4.4 分区和子分区 7 1.4.5 用户 7 1.4.6 模式 8 1.4.7 索引 8 1.4.8 簇 9 1.4.9 ...
组成:表空间、段、区、块的组成层次 六、 oracle安装、卸载和启动 硬件要求 物理内存:1GB 可用物理内存:50M 交换空间大小:3.25GB 硬盘空间:10GB 安装 1. 安装程序成功下载,将会得到如下2个文件: ...
2、查看表空间物理文件的名称及大小 select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 3、查看回滚段名称及大小 ...