I finally finished my first Exadata performance troubleshooting article. This explains one bug I did hit when stress testing an Exadata v2 box, which caused smart scan to go very slow - and how I troubleshooted it: Troubleshooting Exadata v2 Smart Scan Performance Thanks to my secret startup company I've been way too busy to write anything serious lately, but apparently staying up until 6am helped this time! :-) Anyway, maybe next weekend I can repeat this and write Part 2 in the Exadata troubleshooting series ;-) Enjoy! Comments are welcome to this blog entry as I haven't figured out a good way to ...

Tanel Poder
Exadata, Oracle, Oracle 11gR2, Performance, Troubleshooting, Tuning

You probably already know that it's possible to drop tables in Oracle read only tablespaces... (You did know that already, right? ;-) Here's a little example: SQL> create tablespace ronly datafile '/u03/oradata/LIN112/ronly.01.dbf' size 10m; Tablespace created. SQL> create table test tablespace ronly as select * from all_users; Table created. SQL> alter tablespace ronly READ ONLY; Tablespace altered. SQL> drop table test; Table dropped. I just dropped a table from a read only tablespace! Well, perhaps it's because that instead of dropping the table was put into recyclebin instead (which is a data dictionary update)? Let's check which segments remain in the RONLY tablespace: SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name ...

Tanel Poder
Administration, Internals, Oracle, Oracle 11gR2

I haven't written any blog entries for a while, so here's a very sweet treat for low-level Oracle troubleshooters and internals geeks out there :) Over a year ago I wrote that Oracle 11g has a completely new low-level kernel diagnostics & tracing infrastructure built in to it. I wanted to write a longer article about it with comprehensive examples and use cases, but by now I realize I won't ever have time for this, so I'll just point you to the right direction :) Basically, since 11g, you can use SQL_Trace, kernel undocumented traces, various dumps and other actions at much ...

Tanel Poder
Cool stuff, Internals, Oracle, Oracle 11g, Tools, Troubleshooting

Randolf Geist has written a good article about systematic troubleshooting of a PL/SQL memory allocation & CPU utilization problem - and he has used some of my tools too! http://oracle-randolf.blogspot.com/2010/05/advanced-oracle-troubleshooting-session.html

Tanel Poder
Internals, Oracle, Performance, Troubleshooting

At Hotsos Symposium Training Day I used rlwrap with sqlplus - which gives nice command line editing and history capabilities for tools like sqlplus. Additionally I pre-generated commonly used Oracle keywords, data dictionary view and package names into rlwrap wordfile, so I got nice tab-completion too. Sqlplus sucks much less with rlwrap ;-) It's relatively easy to install rlwrap on Unix (there are rlwrap RPMs out there, Solaris freeware packages and I installed it on Mac via macports.org). Just google around... You can have rlwrap on Windows too - As rlwrap has been coded for Unix flavors, then on Windows you need ...

Tanel Poder
Administration, Cool stuff, Oracle, Productivity, Tools

Here's a little trick question. Check out the execution plan below. What the hell, shouldn't the INDEX/TABLE access be the other way around?! Also, how come it's TABLE ACCESS FULL (and not by INDEX ROWID) in there? This question is with a little gotcha, but can you come up with a query which produced such plan? ;-) ---------------------------------------------- | Id | Operation | Name | E-Rows | ---------------------------------------------- | 0 | SELECT STATEMENT | | | |* ...

Tanel Poder
Cool stuff, Internals, Oracle, SQL

Did you know that it's possible to use EXPLAIN PLAN FOR CREATE INDEX ON table(col1,col2,col3) syntax for explaining what exactly would be done when an index is created? That's useful for example for seeing the Oracle's estimated index size without having to actually create the index. You can also use EXPLAIN PLAN FOR ALTER INDEX i REBUILD to see whether this operation would use a FULL TABLE SCAN or a FAST FULL INDEX SCAN (offline index rebuilds of valid indexes can use this method). Anyway, you can experiment with this yourself, but here's a little quiz (with a little gotcha :) What kind of ...

Tanel Poder
Administration, Cool stuff, Internals, Oracle, Troubleshooting

I recently consulted one big telecom and helped to solve their sporadic performance problem which had troubled them for some months. It was an interesting case as it happened in the Oracle / OS touchpoint and it was a product of multiple "root causes", not just one, an early Oracle mutex design bug and a Unix scheduling issue - that's why it had been hard to resolve earlier despite multiple SRs opened etc. Martin Meyer, their lead DBA, posted some info about the problem and technical details, so before going on, you should read his blog entry and read my comments ...

Tanel Poder
Internals, Oracle, Oracle 11g, Performance, Troubleshooting, Unix/Linux

Some time ago I wrote that since Oracle 10.2, some of the buffer cache can physically reside within shared pool granules. I just noticed this in an 11.2 instance: SQL> select * from v$sgastat where name like 'KGH%'; POOL         NAME                            BYTES ------------ -------------------------- ---------- streams pool KGH: NO ACCESS                4186144 So, it looks that also streams pool can surrender parts of its memory granules to buffer cache, if it's unable to flush everything out from the granule for complete granule handover. Let's see ...

Tanel Poder
Cool stuff, Internals, Oracle, Oracle 11gR2, Troubleshooting

Gwen Shapira has written an article about a good example of a non-trivial performance problem. I'm not talking about anything advanced here (such as bugs or problems arising at OS/Oracle touchpoint) but that sometimes the root cause of a problem (or at least the reason why you notice this problem now) is not something deeply technical or related to some specific SQL optimizer feature or a configuration issue. Instead of focusing on the first symptom you see immediately, it pays off to take a step back and see how the problem task/application/SQL is actually used by the users or client applications. In ...

Tanel Poder
Administration, Oracle, Performance, Troubleshooting, Tuning
Oracle, Performance, SQL, Troubleshooting, Tuning