I was asked to give my opinion on someone else's analysis of a performance run. Here's the story.
A benchmark environment was set up at one-fourth of the production environment, to run a stress test. The stress test ran from 2:57PM to 4:35PM on May 16. Just for the heck of it, here are some numbers about that run:
The performance team report of that run starts with a bunch of figures about the machine resources, the transaction rate, the response times per web service etc. Then appears the "Recommendations for the database" part of the report. Here is how it starts:
On the screenshot above, you will have recognized the "Top Activity" part of OEM. The grey vertical bar starts at 3:12PM so focuses on the 1st half of our stress test. Yes yes, the small mostly green mountain on the right-hand side. For the curious among you, the big mountain from about 4AM to 6AM was the loading of the test data.
During the whole duration of the stress test (with 50 users), the AAS (Average Active Sessions) never goes above 0.6 and the datatabase activity is mostly CPU (the green color). These 2 facts tell me that whatever the users were doing did not require much resources and those users must not have suffered any waiting (at least not from the database). I asked and was told that no, there was no complaint about peformance during the stress test.
The first sentence of the analysis, which you can see at the top of the screenshot, says in French "3 SQL statements use lots of resources (presence of Full Table Scans)". The said 3 statements must be the 3 we can see at the bottom left-hand corner of the screenshot. The analysis goes on to make recommendations on each of those 3.
Given that the whole stress test did not use much resources, how could 3 SQL statements use "lots of resources"? Unless what is meant is "most of the resources used by the stress test". According to my first comment, the stress test gave satisfactory results in terms of performance. So why even bother looking at what used most of the little CPU that was used?
The analysis says those 3 statements used "a lot of resources" but the screenshot shows they used CPU only (they're all green). OK, so they did use some CPU -- but that's what we like our databases to do right (that's what we pay Oracle licenses per CPU for)?
The analysis goes on to say that the statements used lots of resources because there were full table scans. OK, we've already established that those statements did not use lots of resources. But the impact of a full table scan will only be according to the size of the tables. If the tables that were fully scanned only weigh a few MBs, surely the impact of the FTS can be sneezed at. Maybe that analysis meant that there were unnecessary table scans -- because FTS can be the best option, especially in the case of small tables.
Then there is a recommendation for the first of those 3 SQL statements:
The recommendation ("préconisation" in French) is to create a performance index on the 3 columns of table COTISATION that are mentionned in the FROM and WHERE clauses.
Apart from the fact that no evidence of the full table scan is shown, no justification for that 3-column index is given. Again, the full table scan could be justified. Maybe an index on CD_ORIGINE,ID_VEHICULE (with the 1st column compressed possibly) would suffice. Maybe those 2 tables could be a clustered table since they share that ID_GARANTIE.
There ensues a recommandation for the 2nd SELECT:
This time, the analysis says only "investigate the creation of a performance index to avoid the full table scan on table PARAM_FRANCHISE_VALEUR".
Again, no justification. With no comparison between an FTS and an index path, one cannot claim that an index is required.
Lastly, a recommandation about the 3rd SQL statement:
This time we're given a piece of the explain plan. The analys suggests the creation of 2 indexes, one on the PARAM_LIBELLE table and another one on the PARAM_REP_AUTO table, in the hope of doing away with 2 FTSs.
Apart from the fact that again there is no justification for the bad FTS, it seems that there is only one FTS. Given that the INDEX FAST FULL SCAN is on an index named X1_EDA_PARAM_REP_AUTO_IX5, I have a feeling that index belongs to table PARAM_REP_AUTO, so that table is accessed by rowid, not fully scanned.
And lastly, the FTS that did take place has a cost of 15, which is negligeable.
I do not know the person who wrote that analysis. I'm sharing this with you all in the hope of sharing useful information and of learning something new from your feedback!
My conclusions from this exercise are:
- don't spend time trying to improve what is working well already (unless there is a need for scalability)
- I love green! If the SQL code produces green in OEM Top Activity, then I'm happy
- Oracle performance is complex and many things that are often bad can often be good, so it's best to test and substantiate one's claims with empiric evidence
Please add any comment to that analysis you feel would be useful, and please do comment on my comments above!