Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

July 30, 2007

What about the dynamic performance views(V$) performance

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 6:26 am

I was reading each post of Mr.Lewis from his profile on Oracle forums and I saw this thread on “”find Hot Block” query taking long time”. Writing queries against v$ views maybe a challenge especially when you need to work on an active production system with large amount of sessions, locks, latches, sqls etc. data.

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.

x$ fixed tables are not documented by Oracle and since Oracle is not an open source application maybe only place you may find quality research information about them are Mr.Steve Adams’s ixora or Mr.Lewis’s company site. Mr.Adam’s researches were done on 8i but in my opinion they are still very valuable today. Here is a list of x$ fixed table categories – Listing 103 – Categories of x$ fixed tables

You can list all v$ and x$ tables from sys.v_$fixed_table, query v$ views definitions from sys.v_$fixed_view_definition and list indexes available on x$ fixed tables from sys.v_$indexed_fixed_table. Below scripts are adapted from http://www.ixora.com.au/scripts/x$tables.htmCode listing 103 – List of v$ and x$ objects

Now lets play with v$session and see what sql*plus autotrace explain feature tell us about x$ access paths – Code listing 103b – access path research on v$session

Below is a little summary for the above tests -


-- first query with a literal over fixed index
select machine from v$session where sid = 1281 ;
FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)

-- what about a range scan over fixed index - - careful #1; remember this is not a b*-tree index behaves more like a hash cluster -
select machine from v$session where sid < 1281 ;
FIXED TABLE FULL       | X$KSUSE

-- ok binding time
select machine from v$session where sid = :x ;
FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)

-- function with bind disabled fixed index usage - careful #2 -
select machine from v$session where sid = round(:x) ;
FIXED TABLE FULL       | X$KSUSE 

-- opps in also disabled fixed index usage - careful #3 -
select machine from v$session where sid in (1281, 1280, 1282) ;
FIXED TABLE FULL       | X$KSUSE 

-- ok, here is what we expected with a little trick :)
select machine from v$session where sid = 1281 union all
select machine from v$session where sid = 1280 union all
select machine from v$session where sid = 1282 ;
FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)

-- careful #4; when it comes to joins, join method and join order are important that you must be aware of
select s.machine, p.spid from v$session s, v$process p where sid = 1281 and s.paddr=p.addr;
NESTED LOOPS
FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)
FIXED TABLE FIXED INDEX | X$KSLED (ind:2)

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).

Last words are for the coming Log Buffer #56: a Carnival of the Vanities for DBAs – this week its my turn to be the Log writer – LGWR :) for the Oracle Blogsphere, after Jeremy’s Log buffer #55 I really have to work harder and I hope not to fail Mr.Dave Edwards of Pythian Group.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Refences Used :
Oracle8i Internal Services for Waits, Latches, Locks, and Memory by Steve Adams
Oracle Database Internals FAQ
Oracle SQL High-Performance Tuning by Guy Harrison
Note:2856990.8 – Bug 2856990 – Fixed table indexes are not used in some cases
Oracle X$ Tables
Note:329617.1 – Queries On DBA_JOBS_RUNNING Take Too Long In Oracle10g
Note:272479.1 – Gathering Statistics For All fixed Objects In The Data Dictionary.
ALL_TYPE_ATTRS poor implementation for JDBC calls

July 18, 2007

Endless loop – read, test and learn

Filed under: Oracle Other — H.Tonguç Yılmaz @ 7:22 am

First of all psoug.org started publishing new 11gR1 demos :)

I love to read metalink notes, you never get bored with these :) After sometime forums, asktom and docs resources compared to metalink quality starts to be secondary.

I know lots of people can not access metalink, so I will try to let them know what they are loosing, below three are from my today’s readings;

Note:274436.1How To Integrate Statspack with EM 10G

Note:175817.1Building local indexes on partitioned tables using DBMS_PCLXUTIL package

Note:215187.1SQLTXPLAIN.SQL – Enhanced Explain Plan and related diagnostic info for one SQL statement (sqlt.zip)

Note:68836.1How To Efficiently Drop A Table With Many Extents

July 17, 2007

Oracle Star Wars

Filed under: Oracle Other,Other — H.Tonguç Yılmaz @ 7:37 am

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 :)

Burlesonhttp://forums.oracle.com/forums/profile.jspa?userID=105473
http://www.dba-oracle.com/t_biased_test_cases.htm

Lewishttp://forums.oracle.com/forums/profile.jspa?userID=554708
http://jonathanlewis.wordpress.com/2007/07/14/analysing-statspack-6/

Part I – http://forums.oracle.com/forums/thread.jspa?threadID=527201&start=0&tstart=0

Part II – http://forums.oracle.com/forums/thread.jspa?threadID=527201&start=15&tstart=0

July 16, 2007

Deffensive upgrade methods, but still no pain no gain

Filed under: Oracle 10g New Features,Oracle How To,Oracle Performance — H.Tonguç Yılmaz @ 9:35 am

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:

Metalink Note 160089.1 TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance
Metalink Note 258167.1 Upgrading from 8.1.X to 9.X – Potential Query Tuning Related Issues
Metalink Note 258945.1 Upgrading from 8.1.X to 9.X – Subquery Issues – Diagnosing and Resolving
Metalink Note 258946.1 Upgrading from 8.1.X to 9.X – View Issues – Diagnosing and Resolving
Metalink Note 259126.1 Upgrading from 8.1.X to 9.X – Btree Bitmap Plan Issues – Diagnosing and Resolving
Metalink Note 295819.1 Upgrading from 9i to 10g – Potential Query Tuning Related Issues
Metalink Note 223806.1 Query with unchanged execution plan is slower after database upgrade

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;

– during 9i -> 10g
alter session set “_optimizer_cost_based_transformation” =off;
alter session set “_gby_hash_aggregation_enabled” = FALSE;

– during 8i -> 9i
alter session set “_UNNEST_SUBQUERY” = false;
alter session set “_ALWAYS_SEMI_JOIN” = off;
alter session set “_ALWAYS_ANTI_JOIN” = off;
alter session set “_COMPLEX_VIEW_MERGING” = false;
alter session set “_B_TREE_BITMAP_PLANS” = false;

– with below hint after 10g you can also manuplate a parameter at statement level
select /*+ opt_param(‘hash_join_enabled’,’false’) */ empno
from emp e, dept d where e.ename=d.dname;

When you have time these may also have your interest;

http://tonguc.wordpress.com/2007/03/01/another-great-undocumented-oracle-hint/
Metalink Note:398838.1 FAQ: Query Tuning Frequently Asked Questions
Metalink Note 179668.1 TROUBLESHOOTING: Tuning Slow Running Queries
Metalink Note 163563.1 TROUBLESHOOTING: Advanced Query Tuning

ps: with 10g I love playing with this view;

SELECT *
FROM (SELECT rownum rn, a.sql_text, a.CPU_TIME, a.executions
FROM v$sqlstats a
ORDER BY a.CPU_TIME DESC)
WHERE rn < 11

and this package;

explain plan set statement_id ‘tong’ for
SELECT …
;

SELECT plan_table_output
FROM TABLE(dbms_xplan.display(NULL, ‘tong’, ‘ALL’));

Turkcell R&D – Software Development Internship 2007 started

Filed under: Oracle Other,Other — H.Tonguç Yılmaz @ 5:56 am

I have been working on my company’s summer internship program for the last five years; http://tonguc.oracleturk.org/index.php?s=turkcell+staj

Every year it starts with choosing the right people and matching them with the right team, we work on small assignments with candidates; http://tonguc.wordpress.com/2006/12/25/hello-world/

With the chosen ones we start for a three months internship and support them with an education program; http://apex.oracle.com/pls/otn/f?p=20898:1:332182477501552

This year Bilal is writing his experiences, this is unique since anyone will have a chance to follow the program with his chronicles;
http://www.bhatipoglu.com/entry.asp?id=6

It is always a great experience for me to study with young talented software engineers, I am sure someday these guys will have important contributions to this community :)

July 13, 2007

I am back :)

Filed under: Oracle Other,Oracle Performance — H.Tonguç Yılmaz @ 7:35 am

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,

5- never go into a critical project with Release 1 products – buffer cache advisory crashes system or job proses take 4-5gb memory and never returns them back to os – you will be telling these sentences to your top management and they will immediately ask why R1? immediately plan and test to migrate to Release 2 as I mentioned earlier,

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,

7- always get prepared for the worst and have a b-c-d.. plans :)

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

Earlier with below two posts I mentioned the fundamental importance of settings when you are upto a data migration project, I will continue with the importance of pga management during data migration and give detail information on my experiences with the above parameters.
http://tonguc.wordpress.com/2007/03/17/some-migration-experiences-to-share/
http://tonguc.wordpress.com/2007/01/20/direct-path-inserts-nologging-option-and-index-cost/

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 :)

By the way during this time 11g is now on the market :) Check out Oracle 11G database new features also Mr.Arup Nanda is again preparing one of his great top features series for 11g :)
Real Application Testing seems to be the most popular new feature for some time. Here you can find a summary for all 11g new features.

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.

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 80 other followers