Oracle 11.2 SEone on Oracle linux 5, with ASM
Feeling like a beginner again ...
Beginning monday morning, everything on this db is taking orders of magnitude longer. Queries/packages are the same as they have been for months. Fresh stats collected on key tables. Even a very simple ad-hoc query takes c. 15 seconds - even on a second execution when everything should be the buffer.
TKPROF: Release 11.2.0.1.0 - Development on Wed May 2 14:02:23 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: dwprd_ora_20811_STEVENS.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
sELECT * FROM dw.program_log a
WHERE a.program_name LIKE '%CBA%' AND a.date_time > to_date('01-Apr-2012','dd-Mon-yyyy')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 3.55 53.21 374587 374589 0 29
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 3.56 53.22 374587 374589 0 29
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
29 TABLE ACCESS BY INDEX ROWID PROGRAM_LOG (cr=374589 pr=374587 pw=0 time=0 us cost=419831 size=118145736 card=1093942)
29 INDEX RANGE SCAN SYS_C0014081 (cr=374573 pr=374571 pw=0 time=0 us cost=380860 size=0 card=1093942)(object id 77238)
In the above, index SYS_C0014081 is a 3-column composite key with DATE_TIME at the high-order end (first column in th index def). It is the PK of the table.
The above isn't the only one - and in fact no one cares about that query itself, but it is very simple and hopefully symptomatic of the underlying issue. Anything else I could show is far more complex. The datafile is on ASM on a SAN.
As for the basic question, "what has changed" the answer is - nothing from the database side. Last week the SA removed some SAN disks that were no longer being used. That happened on Thursday, but it was Monday before some heavy jobs were scheduled that started causing issues.
It's been a loong time since I've had to analyze anything like this and am definately out of practice anaylizing tkprof reports .. especially when judgement born of experience is involved. to make matters more difficult, statspack is broken and dbcontrol not even configured. I need to get those working as soon as I can get this put to bed.
Ideas? thoughts? Any additional info I can provide.
Ideas? Thoughts?