So, which number takes more bytes inside an Oracle row? A: 123 B:  1000000000000000000000000000000000000 And the correct answer is ... (drumroll) ... A! The "big" number 1000000000000000000000000000000000000 actually takes less space than the "small" 123! Let's verify this: SQL> select vsize(123) A, vsize(1000000000000000000000000000000000000) B from dual; A B ---------- ---------- 3 2 WTF? Why does such a small number 123 take more space than  1000000000000000000000000000000000000 ? Well, the answer lies in how Oracle stores numbers. ...

Tanel Poder
Internals, Oracle

Here's the list of events where I'll speak this year: Michigan OakTable Symposium 2010 Ann Arbor, MI 16-17 September 2010 Considering the concentration of OakTable members there, this will be an awesome event! I will be delivering my "Back to Basics: Choosing The Entry Point to Performance Troubleshooting Wisely" and "Understanding LGWR, log file sync waits and commit performance" sessions there. Promo video: http://www.oaktable.net/media/michigan-oaktable-symposium-2010-promo Agenda & Registration: http://michigan.oaktable.net/ Oracle Open Closed World San Francisco, CA 19-22. September Note that I won't be speaking at the official Oracle Open World conference, but I will be speaking at a secret underground event there, about some really fun stuff, like deep ...

Tanel Poder
Cool stuff, Oracle

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