Posted by Riyaj Shamsudeen on August 9, 2009
My earlier blog about shared pool duration got an offline response from one of my reader:
” So, you say that durations aka mini-heaps have been introduced from 10g onwards. I have been using Steve Adams’ script shared_pool_free_lists.sql. Is that not accurate anymore?”
Shared pool free lists
I have a great respect for Steve Adams . In many ways, he has been a great virtual mentor and his insights are so remarkable.
Coming back to the question, I have used Steve’s script before and it is applicable prior to Oracle version 9i. In 9i, sub-heaps were introduced. Further, shared pool durations were introduced in Oracle version 10g. So, his script may not be applicable from version 9i onwards. We will probe this further in this blog.
This is the problem with writing anything about internals stuff, they tend to change from version to version and In many cases, our work can become obsolete in future releases(including this blog!).
Posted in 11g, Oracle database internals, Performance tuning, shared_pool | Tagged: bucket, durations, heapdump_freelist.ksh, ksmchdur, ksmchidx, oracle, oracle performance, performance logfile dump, sga heap, shared pool, shared_pool_free_lists_9i.sql, x$ksmsp | 2 Comments »
Posted by Riyaj Shamsudeen on May 6, 2009
I just presented about Oracle 11g new features specific to performance in COLLABORATE 2009, Orlando Florida. You can download presentation and paper from here:
11g performance specific new features – presentation
11g performance specific new features – paper
I met couple of new friends and many familiar faces.
Catherin Devlin introduced sqlpython tool to me. This tool is an user friendly replacement for sqlplus with many UNIX like goodies. This tool will be an useful addition to command line tools. You can see installation instructions for sqlpython here.
I also attended an interesting application performance panel discussion with fellow Oakie Mark W Farnheim, Mike Brown and Sandra Vucinic. We discussed few customer issues.
An interesting problem discussion is worth mentioning. A client clones production database to development database using full rapidclone methodology. But, access plans for a SQL statement is different between production and development, even though everything was exactly the same. I am hoping, that client will send 10053 trace files from both databases and I will blog about it if I get anything from that client. I think, bind peeking is causing the plan difference, but need to analyze 10053 trace file to confirm that. Another possibility is that, may be , he is not cloning Oracle Software and some software patches are not existing in cloned environment. Need trace files to proceed further here.
Of course, I met few other friends Jeremy Schneider, Dan Norris to mention few.
Posted in 11g, CBO, EBS11i, Oracle database internals, Performance tuning, Presentations | Tagged: 11g, adaptive cursor sharing, bind peeking, CBO, deduplicate, extended_stats, invisible indexes, lob compression, OD lock_type, oracle performance, securefile lobs | Leave a Comment »
Posted by Riyaj Shamsudeen on April 18, 2009
I presented about Cost based optimizer explaining why some times CBO chose inefficient access plan, even though, there is an efficient plan in the search space. This entry is to post presentation slides and they can be downloaded from Why_optimizer_hates_my_sql
Update: Updated presentation after Greg’s comments.
Update2: Further bug fixes in presentation and script.
Update3: Updates after Randolf’s comments.
Posted in 11g, CBO, Performance tuning, Presentations | Tagged: CBO, dbms_xplan, extended statistics, first_rows, oracle performance, performance | 8 Comments »
Posted by Riyaj Shamsudeen on January 8, 2009
My client DBA wrote a small script: To drop a column to a busy table. This application is mostly an OLTP application and table is modified heavily. It is hard to get a lock on the table, even though DDL to drop the column will be quick completing within sub-seconds. Following script was tried in the middle of night so as to minimize production impact.
set serveroutput on size 100000
-- lock table in exclusive mode
execute immediate 'lock table t1 in exclusive mode';
dbms_output.put_line ('**** Table locked. Dropping column ****' ) ;
-- If we lock it, drop the column
execute immediate 'alter table t1 drop column n5 ';
when others then
dbms_output.put_line(' No luck ');
dbms_output.put_line(' SQL code'||sqlcode||','|| 'error '||sqlerrm );
Logic of the above script is a) wait for 5 minutes to lock the table in exclusive mode, before timing out. b) If we acquire exclusive lock on that table, then add a column.
Do you see any issues with this script?
So, did we add that column ?
Posted in 11g, Performance tuning | Tagged: 11g, ddl_lock_timeout, exclusive mode, HW enqueue HWM x$ksqst 10704, oracle, oracle performance, performance | 14 Comments »
Posted by Riyaj Shamsudeen on December 19, 2008
I blogged about extended stats in my earlier blog, extended stats, and also documented that as an investigation in Investigations: extended stats and multi-column correlation. I was testing extended stats further and ran in to some interesting situations.
Extended stats can be used to store correlation between columns. Correlation between two columns needs to detect at least, two properties of the column values:
- Correlated column values
- Uncorrelated column values
Let’s explore this further.
Posted in 11g, CBO, Performance tuning | 4 Comments »