The SELECT * that stopped month-end at our bank, and the change ticket that saved me

Friends, a story from month-end, because I am telling you, every bank sysadmin has one of these and mine finally has a happy ending.

Last day of the month, 11.40 pm. Our core banking batch on Oracle runs interest, fees, GL postings, and at the end the ATM and card settlement files for the switch. The batch window is tight. At 11.52 pm the batch stopped moving. Monitoring showed the TEMP tablespace at 100% and the batch job waiting.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

First thing, who is eating TEMP:

SELECT s.sid, s.serial#, s.username, s.program, u.sql_id,
       ROUND(u.blocks * 8192 / 1024 / 1024) AS temp_mb
FROM   v$session s
JOIN   v$tempseg_usage u ON u.session_addr = s.saddr
ORDER  BY u.blocks DESC;

Top of the list: a reporting user from a vendor, 31 GB of TEMP, one query. The SQL was a view the vendor had deployed two days before, joining the full transaction history to the customer table with SELECT * and no date filter. 900 million rows sorted for a "monthly summary" that the business would look at on the 3rd. In UAT it had 2 million rows and finished in 40 seconds, so everybody was happy.

At midnight I am not going to tune a vendor's view. I killed the session:

ALTER SYSTEM KILL SESSION '1832,40917' IMMEDIATE;

TEMP released, batch continued, settlement files reached the switch 34 minutes late but inside the cut-off. No customer noticed. My heart rate noticed.

Why the change ticket saved me

On the next morning the vendor's manager called my manager: "your sysadmin killed our report". My manager opened the change ticket. The vendor's view had been approved for deployment, yes, but the ticket said clearly: "report to be scheduled after month-end batch, not before". They had scheduled it at 11.30 pm on the last day. Conversation finished in two minutes. If it is not in the change ticket, it did not happen. If it IS in the change ticket, it protects you.

What we changed afterwards

• Reports no longer run on the primary. Finance finally approved the Active Data Guard licence after this (what to do, it takes an incident), and vendor reports now run on the read-only standby.
• Oracle Resource Manager plan: the reporting users get a consumer group with limits, so one bad query cannot take all of TEMP.
• Alert on TEMP usage at 70%, not only at 100%.
• The vendor rewrote the view with the date range as a parameter and only the 12 columns the report actually shows. The history table is partitioned by month, so with the date filter Oracle only touches one partition. The report now takes 3 minutes on the standby.
• No new reports go live in the last five working days of the month. No matter who signs.

People from outside think banks are slow for no reason. Banks are slow on purpose. This is the purpose.