I'm not an expert tuner, but know the basics and where to look. Yesterday someone came to me with a problem - 2 databases, same data, same query. One was taking 2 seconds (that's good), one was taking 15 seconds (that's bad).
I checked the obvious things first and compared the results between the 2 servers:
Hardware
Database Parameters
Amount of data
Explain Plan.
Did a "top"
Ran awrrpt
The hardware on the bad server (memory and CPU) is actually a lot better on than on the good server:
Bad = 48GB of RAM, and 24 x 2.93GHz CPUs
Good = 12GB of RAM, and 16 x 2.93GHz CPUs
(just goes to show that throwing hardware at a problem isn't always the solution).
The database parameters on the bad server were beefed up to take advantage of the hardware.
The "top" command didn't show any runaway processes, or anything untoward.
The explain plan was the same for both servers, and showed the query using an index.
I created a 5 GB file on both - we've had issues with disk I/O on some of these hosts before. The bad server created the file a lot faster, so I ruled out the earlier issues we experienced.
I ran
"analyze index owner.index_name validate structure;"
then a
"select * from index_stats;"
on both servers.
The result (sorry about the formatting):
Bad:
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
3 19272 DBQ_PROCESS_TIME 190128 18768 6004458 5524 18767 103 601529 8028 184802 5765005 190128 1 104501316 6605987 7 1 4 0 0 2 32
Good:
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
3 17208 DBQ_PROCESS_TIME 9426 16048 401044 6052 16047 87 544198 8028 9392 399684 9426 1 97820932 945242 1 1 4
The figures from the bad server are a lot higher.
I rebuilt the index on the bad server:
alter index owner.index_name rebuild online parallel 3;
Didn't take long, then re-ran the query - it then ran in 0.01 seconds.
I rebuilt the index the same way on the good server, same result.
I was later told that the application owner did a purge of the table the night before and removed about 10M rows.
If I'd known that before it would have saved me a bit of investigation.
