So, the emails start flying, something is wrong, the database has a problem. That is a very typical situation, and instead of defending the database right away, take some time to do a quick check of a couple of things.
Check number one might just be too obvious, but check the alert for errors. Validate that there is nothing goofy going on. And while you are checking out the bdump directory, a quick glance at udump for any trace files that could also be out there might show some information.
Check number two, any invalid objects or unusable indexes? Make sure that all procedures, views, triggers have a status of valid, but before recompiling, make sure you grab that last_modified date, because it might be needed later. Also, unusable indexes that might need to be rebuilt should be noted for what tables they are on and see if they are part of the issue.
Check number three, validate that statistics are up to date on indexes and tables.
And then check to make sure that there are no objects that were recently changed. Check that modified date on all of the objects. Even a modification to a data type can cause a join that was previously working to fail.
Maybe you use the checks in a different order, but with just this four, any obvious errors on the server have been found, anything that has changed has been validated and noted as changes made to the database and statistics have been checked, which can either show that this regular type of maintenance is not running or things are looking good and up to date on the datebase.
So, something wrong with the database, possibly, but now after these quick checks you can pull out more details about what they are seeing and what can be wrong. There is also supporting information if things have been changed or modified and help drilldown to more of the issue at hand.
good idea! to find something wrong.
ReplyDeleteAbout trace file... it's so hard to read.
I wish you show scripts to get stats on indexes/tables.
Gathering statistics on the tables or indexes can be as simple as
ReplyDeleteexec dbms_stats.gather_table_stats('SCHEMA','TABNAME');
And not specifying estimate_percent, it will default to compute, which for large tables will take a long time.
Normally I use:
exec dbms_stats.gather_table_stats('schema','TABNAME',estimate_percent => 25, method_opt => FOR ALL INDEXED, cascade => TRUE)
This will estimate 25% and is a value that you can test at different levels, gather stats on the table and indexes for the columns that are in your indexes, so hopefully the ones that are being used to join with. Hope that helps.
Also for the trace file, if you spool it to a file, then open it in excel or something like that you might be able to develop a format that is easier to read. Even some of the available tools might provide an easier to read format like SQL developer, TOAD, etc.