Note: This post is about diagnosing real life scenario caused by direct path decision change on 18.104.22.168 which already covered by Tanel Poder on his awesome Optimizer statistics-driven direct path read decision for full table scans (_direct_read_decision_statistics_driven) post
If you already read and know how to (more...)
Todays story is about, how dynamic sampling can effect your plan stability during upgrade and how you can recover from it as long as you know what your are doing.
A little bit of background, I’m currently in the middle of migrate and upgrade cycle for 12TB warehouse from 10.2.0.3 Solaris to 22.214.171.124 (yet) Linux.
Plan stability on this system is maintained by locking the main driving table statistics and hinting the queries in case CBO does not pick the right driving table.
The system uses temporary tables a lot for staging operations and (more...)
Just a quick post for letting people know that Carlos Sierra started blogging in his Oracle SQL Tuning Notepad. He is the developer of many tools like “coe_xplain.sql, followed by profiler.sql, Trace Analyzer (TRCANLZR), SQLTXPLAIN (SQLT) and SQL Health-Check (SQLHC). ” which are available in MOS.
Never seen or talk him personally but I’m big fan and user of his tools for a long time and I’m sure he will write plenty of tuning stories. I would like to say welcome to Oracle Blogosphere and wish him good luck with his blogging journey
Last week my colleague (Paul Reed – my new content provider with issues on his databases ), had a problem where he needed to identify which baseline was using index
on a plan where collection table (like SELECT column FROM TABLE(:B1) ) was used.
When he tried DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE he got “ERROR: An error has happened, error code: 22905″ which was related with the bug below.
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE returns "ERROR: An error has happened, error code: 22905" [ID 1418186.1]
When he asked me, if there is a workaround, I was under the impression that dba_sql_plan_baselines has got the outline (more...)
Today I saw a post from my friend Dominic Brooks (orastory) whom I work together at the same place for different environments (Wish he is working on the environments I look after together we could have made a great performance team ). His post is about how Adaptive Cursor Sharing and SQL Plan Baselines work together . Post looks long but easy to read and enlightening about the issue with one small addition/correction/observation needed. I think he covered well enough but finished his tests a bit soon. I will not repeat what he has done but I need to cover the (more...)
Just a quick note to point a MOS note which I found out yesterday . I already tweeted about it and it has been retweeted many time by my follower, but for wider audience I decided to write a blog post as well.
As you may already know, to migrate databases between different endian formats, using RMAN Transportable Tablespaces is an option but for this option to work, you need to have a downtime since tablespaces needs to be read only, and this downtime can be very long duration depending on the data size.
To sort this problem for migrations (more...)
Warning : This long blog is for educational and quality purposes and especially targeting Junior DBAs and DBAs who does not know how to use the Session Snapper
(greatest Oracle Tuning Tool yet) by Tanel Poder . If you feel comfortable with snapper and you think your relationship with your
clients just rocks then there is no need to read the post but you may still give it a go
Story started with a long email thread forward by a developer who is asking question below for a system I am responsible but not feeling perfectly comfortable about how things really (more...)
update ( 10/10/2012 ): I have changed the script to make work better with hourly requests at it was causing issues. Thanks to Dominic Brooks for the trick
update ( 11/10/2012 ): Added ADDM and Global report generation
Since I got turbo boost (over 1000 hits a day-wooow) from Tanel Poder the Great, better I write something to deserve the fame. This one is a real quick one which made my life easier.
On my second week at current company, I had to cover Exadata for 2 weeks which were completely nightmare due to some problems. I had to (more...)
In this post I will give information on quick ways to find the sql_id’s of sessions for which you can’t see any entry when you look at v$session.
I usually see this behavior when sql is burning CPU in a nested loop and after a long time of running sql_id column suddenly not updated anymore. When I tweeted about it , ,Tanel Poder answered (If I a recall it right damn twitter does not have a proper search and history) something like ” this happens when oracle does too many recursive calls and loses track of the sql_id” (Tanel, if (more...)