The SQL Execution Plan error causes the temporary tablespace to be insufficient.

Fault description: the problem of insufficient temporary tablespace has already been reported three times, and the customer is also bored. The first two were colleagues who added 5G data files. Currently, they have reached 40 GB, the occupied temporary tablespaces are mainly distinct, group by, and Union all tables with a data volume of about million, so that the 40 Gb temporary tablespaces will not pop up.

Cause Analysis: Since sorting cannot use so many temporary tablespace, it should be caused by other reasons.

From the AWR report that contains the fault time period, we can see that dbtime is very high at this stage, and SQL execute elapsed time actually accounts for 99.43%, which can be determined to be caused by SQL statements.

Locate the problematic SQL using top SQL

Check whether the following SQL statements are caused:

Select 'A ',
D. explanation, -- Financial Institution ID code
C. account_no, -- transaction account
To_date (A. batchentrydate, 'yyyy-mm-dd'), -- date of occurrence
C. currencycode, -- currency
Sum (decode (A. creditdebit, 'C', A. transactionamount, 0), -- credit amount of the current day
Sum (decode (A. creditdebit, 'D', A. transactionamount, 0), -- amount of the debit on the current day
Case
When C. currencycode = 'jpy' then
Round (C. ccyledgerbalance, 0)
Else
C. ccyledgerbalance
End balance, -- account balance
-- B. instcode, -- System Virtual Organization Code
1 datastatus, -- the data status of the foreground
C. account_no | C. currencycode | '2017-01-04 ',
To_date ('2017-01-04 ', 'yyyy-mm-dd ')
From df_cust C
Left join (select distinct accountbranch,
Description,
Masterno,
Currencycode,
Account_number,
Seqno,
Acct_class_code,
Productcode,
Valuedt_yyyy,
Valuedt_mm,
Valuedt_dd,
Batchentrydate,
Valuedt_yyyymmdd,
Narrationpost,
Transactionamount,
Creditdebit,
Accountbranching,
Segmentcode,
Referencenumber,
Narrationtran,
Batchnumber,
Gldeptid,
Armcode,
Extrefno,
Makerid,
Checkerid,
Channelid,
Transaction_amt_in_usd,
Accshortname,
Armname,
Segname,
Txncode,
Reversalflag,
Ebbsreference,
Transtypecode,
Customerrate,
Advtreasuryflag,
Va_flag
From Df_acmov_today
Where creditdebit in ('C', 'D') A on A. account_number =
C. account_no
Left join da_mid_acc_gl_dic D on D. Source = A. accountbranch
Where exists (select 1
From acc. t_base_account B
Where B. Account = C. account_no
And B. currence_code = C. currencycode)
And a. account_number is not null
And C. account_no like '0%'
Group by D. explanation, -- Financial Institution ID code
C. account_no, -- transaction account
A. batchentrydate, -- date of occurrence
C. currencycode, -- currency
C. ccyledgerbalance-system organization code

Observe and analyze the execution plan, and it seems that there is no problem, because df_acmov_today (around million data) is cleared every day, no index, full table scan, nestloops is normal.

However, when SQL statements are executed, scripts are used to monitor the usage of temporary tablespace, and the usage of temporary tablespace quickly reaches 40 GB. The temporary tablespace is insufficient...

Dbms_stats.gather_table_stats is used to analyze the following table and then run the statement. The problem is clear.

Compared with the previous SQL Execution Plan, it is found that the number of execution plans is basically incorrect, but it is actually 1. The estimation gap is too large.

Why is the last execution plan of the analysis table (crontab job) incorrect?

Finally, we used crontab to analyze the table at AM, but at AM. Other tasks are performed on the table, which is caused by truncate and insert.

Finally, the scheduled task time is adjusted to completely solve the problem.