As a performance tuning DBA I regularely get questions about query performance gone bad. Often you get to hear that everyone was minding their own business and certainly did not change the application code or anything, when suddenly, from one day to the other certain screens took minutes to load in stead of milliseconds.
On performance tuning trainings I tell my students that my attitude in these performance investigations is somewhat like that of Dr. House (from the House MD series). Not that I call everyone a moron like House does, but certainly I don’t take anything for granted. Dr. House says “Everybody lies”. I don’t think people blatantly lie about what preceded, but the truth is often out there. “Nothing was changed and the performance went bad”? I often doubt that. Something has changed, whether it’s the application code or database objects or data multiplied overnight. Not to mention changed statistics. In Oracle 10g R2 I had numerous occasions where dbms_stats’ automatic ways suddenly decided that it was time to have a sample size of only 0.4% or so. So check what changed everywhere – if you can.
But how do you check for changes in the application. While DBA’s often can’t check the application itself, you can check changes in the data model, packages, etc.. As a crude first investigation I use this query:
select owner,object_type, object_name, last_ddl_time
where owner not in ('SYS', 'SYSTEM', 'EXFSYS', 'ORACLE_OCM')
order by last_ddl_time desc;
OWNER OBJECT_TYPE OBJECT_NAME LAST_DDL_TIME ----------- ------------ -------------- ------------------- APP_OWNER PACKAGE PCK_APP 27-01-2013:13:33:44 APP_OWNER TABLE CHANGES 11-01-2013:13:33:44 APP_OWNER INDEX I1_CHANGES 11-01-2013:13:33:39 APP_OWNER INDEX TEST1 18-12-2012:14:40:21 APP_OWNER VIEW V_INCIDENTS_2 04-12-2012:21:59:05 APP_OWNER VIEW V_INCIDENTS_1 04-12-2012:21:59:05
I’ve investigated a case of bad performance once. It was for a customer of my former employer, Transfer Solutions. The customer assured me that “nothing had changed”, but with above query I quickly found changes in lots of packages, views, etc.. from the night before. Did the customer “confess” the changes? No. He was completely unaware that another departement in his organization had done an update of their part of the application. It’s how these things go. He did vow to talk with the other departments to share information about changes to the application from then on.
There is however another category of changes that you’ll won’t detect with above query. And that is when the volume of data has changed. Or the average row length has changed. I didn’t think there was such a thing as statistics history until I ran a recent version of SQLT (or SQLTxplain). That is a highly recommended tool found on the Oracle support site (ID 215187.1). You feed SQLT a query or SQL_ID and it generates loads and loads of data concerning the tables the query does access. And indexes, statistics, execution plans, etc., etc..
Amongst the data is a list of statistics history of tables and indexes. I’ve racked my brain where this data could have come from. I don’t want to install and run SQLT on every database, but I do want that statistics history. At a certain point it became an obsession. Collegues told me it could not be done. And yet.. and yet. SQLT could do it.
I tried to reverse engineer SQLT, but I didn’t quite find it. I might have given up the search, was it not for a bug I encountered with AWR in an Oracle 22.214.171.124 database. I started a SR, and after a while the bug seemed to concern rows in sys.WRI$_OPTSTAT_TAB_HISTORY. Normally that table is automatically cleaned after (default) 31 days, but for some reason not in this particular database. Long story slightly shorter: I looked in that table and lo and behold: table and index statistics history. With the following query you can retrieve it:
SELECT ob.owner, ob.object_name, ob.object_type, rowcnt, avgrln ,samplesize, analyzetime
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
and object_type in ('TABLE')
order by savtime asc;
OWNER OBJECT_NAME OBJECT_TYPE ROWCNT AVGRLN SAMPLESIZE ANALYZETIME ---------- ------------ ----------- ------- ------- ---------- ----------- APP_OWNER PROBLEMS TABLE 368470 2428 36847 19-01-2013 APP_OWNER PROBLEMS TABLE 369510 2426 36951 20-01-2013 APP_OWNER PROBLEMS TABLE 369000 2426 36900 21-01-2013 APP_OWNER PROBLEMS TABLE 368710 2427 36871 22-01-2013 APP_OWNER PROBLEMS TABLE 371500 2426 37150 23-01-2013 APP_OWNER PROBLEMS TABLE 373730 2427 37373 24-01-2013 APP_OWNER PROBLEMS TABLE 376700 2428 37670 25-01-2013 APP_OWNER PROBLEMS TABLE 373160 2426 37316 26-01-2013 APP_OWNER PROBLEMS TABLE 414450 2424 8312 27-01-2013
I’d say that’s pretty useful.
[Update November 12th 2013]: I’ve created a script that can give you table/index/column statistics history, based on a SQL_ID (actually based on the execution plan of that SQL_ID). The script is called sqlperf.sql and you can read more about it here.