v$ views are documented with initialization parameters and static data dictionary views(user%-all%-dba%) in Oracle’s Reference Guide. When you query all_views you will see v$ views with names starting v_$%, there are public synonyms for each view with v$% as a result when granting access to these views we use v_$% objects. gv$% views are for RAC environments, simply they return unioned information from each node for related v$ view.

Behind v$ performance views you will find x$ fixed tables, which are externalizations of Oracle kernel’s memory structures. Since rows can not be inserted, deleted or updated via SQL on x$ objects “Fixed Table” concept and name is argued to come from this behavior. To query x$ data you must be sys, but x$ fixed tables can be made available over views created and granted by sys account.

Since rbo is dead with 10g we also collect statistics on dictionary objects, but for previous releases the optimizer may be unable to determine the best access path, join order or join method and you will need to force him with some appropriate hints. Or you may find that optimizer is not using a fixed index from autotrace or 10046 sql trace output so again hinting will be your friend, but from release to release you must be on alarm with these hard codings(hints).

July 17, 2007

I have written a post earlier about the expert categories in Oracle community I experienced. I didn’t know I was playing with fire :) I don’t know the historical reasons behind the below war I will mention but I can only imagine why someone like Mr.Lewis or Mr.Kyte would go after each post of someone like Mr.Burleson.

Mr.Burleson is a very well known Oracle expert for years and every single Oracle newbie who uses google during their researches instead of metalink or docs, meets with his articles immediately :) As a result someone who has this much power must be very careful about what he suggests to the community, if not we face another example of “Question Authority.”

On the other hand Mr. Lewis has always been like a scientist, when you read his books, articles you always feel like you are in a laboratory. Mr.Lewis kind of people never mislead the starters with a fear that it is too much complicated to write on, you never read something like “.. immediately rebuild your indexes when they have X amount of deleted leaves ..”

So there can not be any excuse like “since they are newbie they do not have the opportunity to learn the rights”, at least above word kills the wonder feeling and do not let others to try with a test case and see some outcomes for their specific conditions.

At forums alanm From: Shropshire, U.K. commented “It makes the forum something less than it should be.” and some anonym user user584951(maybe he is also someone important:) warned two community leader and the war seems to pause for sometime. Know I leave you with the war series I found :)

July 16, 2007

As Oracle continues to develop the Database Software, changes are introduced in the optimizer that are designed to give better performance. Occasionally, changes that provides improved performance for many, can have an adverse effect for a small number of others. New versions can also require different approaches in system management to maintain, or achieve, better performance.

The following notes give guidance on what to do when upgrading to ensure good performance is maintained and what to do if a performance regression is encountered:

As a summary I suggest you do good testing before you do a major Oracle release upgrade. Dump and store all your critical execution plans with Event 10132 trace, use those trace files as a library to check for the problems that may occur after migration.

Some default values of hidden optimizer parameters change unfortunately so try below to disable these new behaviours at session level during your tests;

July 13, 2007

So as I promised earlier I will start with sharing my experiences for the last two months. I was the performance team leader of my company’s biggest project in its life, we are in telco business and we have over 32 million customers – a huge operation and this project involved changing nearly %70 of all applications of the company. There were two phases, first a data migration limited with time constraints and second the applications performance after migration like internet, call center etc. I return with lots of new but mostly non-technical experiences summarised as follows –

1- if you are upto a critical mission project choose every single member of your team very carefuly, your team will take you to the destination so always work with the bests,

2- never stop learning, things you may see not needed or not very important at that point of time may have great value to you when time comes, test what you learn and turn it to a consistent knowledge because when the time comes you have to stand for it to your death, everyone will trust you and trust is something a leader should never loose,

3- if you understand something you know is different than you think do not be emotional, be rational and except the truth, always inform your managers with the truth even you did some mistake, again this is a trust issue(by the way during the project even I have taken high risks I never did a mistake, this note was for the group of people who were against my decisions:)

4- if you purchased physical memory use it! for oltp sga and for olap pga is very important – dont run with low buffer areas and suffer your customers, please dont. AWR and advisories are your friends, monitor and increase your cache areas upto limits, I have seen oltp systems which have 48 GB physical memory and their buffer cache was 800 MB,

6- operational dba groups must have a space for a risk, if not and you manage them with only availability focus they will loose their creativity and self confidence and of course performance point will have the greatest impact here,

8- if you want high performance this will have alternative costs, most probably you will be taking some risk, make those risks be calculated, dont let them turn into a gamble. For example during migration phase we used below three _parameters but we did very careful testing, as you know these parameters are to be used with the suggestion of Oracle support;_pga_max_size / default : 209715200 – we used : 3650000000
_smm_max_size / default : 102400 – we used : 425000
_smm_px_max_size / default : 31457280 – we used : 68000000

Until the coming post please check if you are using automatic pga management or not, if not please plan and test for a migration. Especially if you are responsible of an olap system check this presentation and try the above _parameters with test cases, you may fly like an angel as we did :)

So last words are for my idol, Mr.Danişment Gazi Ünal, I was very lucky to work under his consultancy during this project. He is one of the most knowledgable Oracle consultants I have ever met, he is like a teacher always and like Mr.Jonathan Lewis he knows internals so well that he can show you the picture so simple as a result it is imposible to not to understand his points.