The Oracle Optimizer & Philosophycal discussions

In Oracle 10.2.x I have found recently very often that some of our systems are slowing down in performance, when looking at the specific SQLs that are causing the problem to our surprise we see poor plan execution, when looking at the tables involved we found that the table has not been analyzed for a while and that puzzle me.

Starting 10g Oracle has a default job process called GATHER_STATS_JOB that using DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC suppose to intelligent decide whether or not collect stats for a table depending of the changes on that table.

I find that job is not working as it suppose to work, we have been replacing that job for explicit analyze using the following command.

EXEC DBMS_STATS.gather_schema_stats (ownname => '', cascade =>true, estimate_percent => dbms_stats.auto_sample_size);

When you specify the cascade parameters equal true explicitly it will include indexes

Oracle Corporation recommend setting the ESTIMATE_PERCENT parameter of the gathering procedures to DBMS_STATS.AUTO_SAMPLE_SIZE. This will maximize performance gains while achieving good statistical accuracy.

I know I may be opening Pandora's Box with the following topic, but I want to set my stand on this clear.

While doing the changes above for the analyze on multiple systems (about 15 production databases), one database presented performance degradation in 2 tables. This particular application was written by a famous software house of billing systems called "A M * * C S" you fill in the stars. When looking to what is causing the problem we found that the particular query has a column in the predicate that was not indexed, and we found that they added a tremendous amount of hidden parameters that modify the optimizer behavior without authorization. When questioned about why they introduced those parameters they said that although they don't know what they do, it has worked for some other customers (It is up to your to decide if that is a valid argument, but it is not for me) . The vendor told the client that the reason for the performance degradation is that the tables were analyzed. They said verbally that we should never analyze this two tables because they do not change much. When asked about number of daily changes we found that in 8 days 11% of the table will change and in a month 41.25% of the table will change (too much for a no changing table in my opinion).

This is my stand, "tables should always be analyzed". If you don't want a particular table to be analyzed for x or y reason you must lock the stats on those tables and document it. The Oracle optimizer picks the right execution plan 90% to 95% of the times (this is only true if you have a decent developer and you understand how the Oracle architecture works). For the other 5% to 10% of the code you need to use hint, SQL Profiles and other artifacts that Oracle provides so you can get a good execution plan.

"Changing the optimizer behavior so 5% to 10% of your code runs properly is just not a good idea nor makes any sense."

I will advice to change the optimizer behavior just when you exhausted any other option, you are dealing with an small set of tables that you know works well with the change, and you know you will not add more code or tables to that database.

As usual I respect the opinion of those people that think that "if it is no broken don't fix it", the fact is that if you are a DBA you better you better get your hands dirty and understand well how the application uses your database, what are your heaviest tables, what are your problematic queries, etc, etc. The vendor will leave after they finish the application code, data will change and you will have a ticking boom in your hands.

Rovio

Rovio is an amazing robot created by WowWee,
The beauty about Rovio is that it allows you keep in contact
with your family while you are away. Rovio has a video camera
on the top that has 3 levels and can be managed remotely through the internet
using a browser; but it does not stop there it allows you to talk
to your family using full duplex communication.


The UI that Rovio uses is very intuitive, the best of all is loaded into the device itself, you do not have to deal with complex setup of web servers or anything like that. The UI allows you to move Rovio in any possible direction, switch the camera to 3 different levels, setup the sound, video quality and brightness.


The quality of the picture is amazing, the sound is not bad but it only works with Internet Explorer, there is not plug in for any other browser. It allows you to grant guest accounts to your family, in that way they will not be able to mess with the setup, but will be able to use it. Rovio is powered by rechargeable batteries that gets charged through a base that comes with the device. The auto docking features of the device is just amazing, it uses a beacon of light reflected on the roof of the room to guide itself into the docking station with the push of a button.


Rovio is propelled by 3 sets of wheels each wheel has several mini wheels, that gives Rovio and amazing moving flexibility. I am not clear about the price, I have seen it going from $130 at BJS to over $400 on the internet while I think at $130 is a fair price I would not pay for it $400, but I can't understand why such a difference in the price.

Pros & Cons

Pros:

• Amazing moving flexibility.
• Good quality picture.
• Very intuitive user interface.
• Very easy to setup.

Cons:

• Once in a while it loses the network signal and needs manual reboot.
  
• The battery could last longer.
• It should come with additional beacons so you can preset way points to different parts of the house.
• You should be able to move the video camera up and down on demand rather than having preset position.

Rating: 4 out of 5