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

HP2133 Mini-Note PC The Little wonder for DBAs.

If you don't know it by know is because you haven't read my articles, but I am an enthusiastic in technology and an early adopter of it. I just love technology and I can't live without it.




For sometime I have been looking for an inexpensive laptop, small size but powerful enough that I could have my Oracle database and programs on it, and carry it every where I go without the need for a heavy laptop bag. Also the most important feature I was looking for was no Windows on it, yes I am more than disappointed with Windows Vista although I own several computers that have it installed.

I am glad to announce that I believe I got to the end of the journey in my finding, I recently got an HP2133 Mini-Note PC equipped with SUSE Linux Enterprise Desktop 10 SP1 and I just love it.

This Laptop has a price tag of $349 (at least by the time I bought it) from CompUSA.com comes with 1GB RAM and a powerful 120GB hard drive, and for $20 you can get a 2GB memory SDIMM from Newegg.com (BTW there is a handy video in Youtube that explains how to add the memory), HP sales it for $399, but I am sure you can get it for around $311 from other Internet sites like Provantange.

http://h10010.www1.hp.com/wwpc/us/en/sm/WF05a/321957-321957-64295-321838-306995-3687084.html?jumpid=oc_R1002_USENC-001_HP%202133%20
Base%20Model%20Mini-Note%20PC&lang=en&cc=us


It comes with the following

Specifications

Lifestyle: Business Netbooks Condition: New Operating Systems: SUSE Enterprise Desktop 10 SP1. Startup Operating System: SUSE Enterprise Desktop 10 SP1. Platform: Notebook PC Expansion Ports: 1 - Express Card Slot/54 Display Type: WXGA Viewable Screen Size: 8.9" Maximum Resolution: 1280 x 768 Processor Brand: VIA Processor Class: C7-M Processor Speed: 1.60GHz Processor Cache: 128KB L2 Memory Type: DDR2 Memory Size: 2GB Memory Speed: DDR2 667 Memory Slots (Total): 1 Memory Slots (Available): 0 Maximum Memory Supported: 2GB Graphics Description: Integrated Graphics GPU/VPU: VIA® Chrome9™ Video Memory: Up to 256MB Shared Video Interface: VGA Capacity: 120GB Optical Drive Type: N/A Supplemental Drive Type: Media Reader Capacity: 1 Media Types: SecureDigital Audio Description: Integrated Audio Audio Chipset: ADI High Definition Codec PS/2 Mouse Connectors: N/A PS/2 Keyboard Connectors: N/A Serial Communication Ports: N/A Parallel Ports: N/A USB Ports: 2 FireWire Ports: N/A Fast Infrared Ports (FIR): N/A LAN Ports: 1 Modem Ports: N/A Audio Out Jacks: 1 Line In Jacks: N/A Microphone Jacks: 1 VGA Ports: 1 S-Video Connectors: N/A DVI Video: N/A Port Replicator/Connector: N/A Communications Description: Integrated LAN Integrated Wireless LAN Integrated Bluetooth Interface Type: RJ-45 Ethernet Connector Bluetooth™ Broadcom 802.11a/b/g WLAN Data Transfer Rate: 10/100/1000Mbps Network 54 Mbps Bluetooth 2.0 Protocols: 802.11b 802.11a 802.11g Width: 10.04", 255 mm Height: 27 mm, 1.05" Depth: 6.5", 165 mm Weight: 2.6 lbs 1.19 kg Mouse Type: Touch Pad Scrolling Capability: Scroll Zone Keyboard Type: Spill Resistant Battery Type: 6-Cell Lithium-ion Battery Life: Up to 4.5 hours

I was able to install Oracle 10gR2 on it with the help of my friend Nick Donatone from Oracle, the biggest challenge was to get installed the pre-required packages for Oracle like gcc and gcc++, etc. This laptop comes with no CD ROM nor DVD ROM, but I believe you can buy one optional or you can just get an external USB CD-ROM drive (No that you need one)

The packages you need to install Oracle comes with the software which you can download from Novell (NOTE: Just install the packages you need and no more than that) , Novell support tells me that this is an special edition of SUSE for HP, therefore not all open source software will work on it.

For the price tag that this computer has, all the features on it, and the flexibility it represents I will say this is a must buy.

So far Pros and Cons:

- Support from HP for matters with the OS could be a lot better.
- Support from Novel has been great so far (thanks Jack Hodge).
- The speakers are great
- The keyword is good enough and you can always get an USB keyword, mouse and connect it to
and external display and you won't have any problems with it.
- The Webcam does not work in SUSE Linux but, I heard there is a way to make it work with a
open source driver.
- The battery life is about 2 hours, I am thinking in getting the extended battery (gives you 4 hours)
- It is very light.
- It warms up a bit at the bottom while you use it for extended periods.
- It comes pack with plenty of neat Open source software such as Open office.
- The wireless is very reliable.
- I wish the processor were faster, but this is just good enough.
- I love the touch pad technology HP implemented here, it has an scroll zone that simulates that
third mouse button.

In conclusion I give to it a 3.5 rating out of 5.

This is what it needs to be a 5 out of 5.

- HP Support improvements.
- Webcam working out of the box with SUSE.
- Faster CPU.