During the 11th holiday, it may not be easy. If the database reports an error and a critical business cannot be properly executed on a certain day, it must be processed immediately.
Cause Analysis
1, log on to the database, view the host log, the error content is ORA-01652, temp tablespace is insufficient
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01
2. Ask the business department to re-execute the relevant script and find the specific statement that occupies the temp tablespace is as follows. Currently, the temp tablespace is 96 GB, which will be used up by the SQL statement in about one hour. The SQL statement exits unexpectedly.
The SQL statement is as follows:
- Insert into www. WWW_BILL_DTL_TEMP_0101 (ACCT_ID, SERV_ID, identifier, BRAND,
- PHONE_ID, USER_TYPE) SELECT ACCT_ID, SERV_ID, sum (distinct) values, BRAND
- , PHONE_ID, USER_TYPE FROM (SELECT ACCT_ID, SERV_ID, SUM (distinct) F
- EE, BRAND, PHONE_ID, USER_TYPE FROM (select a. acct_id, e. serv_id, s
- Um (B. unpay_type) values, 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 (distinct) values, 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
- , 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
The execution plan is as follows:
- Plan hash value: 3236377944
- Bytes --------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
- Bytes --------------------------------------------------------------------------------------------------------------------
- | 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 |
- Bytes --------------------------------------------------------------------------------------------------------------------
