I have been very busy over last months (as you see from the lack of blog entries). Part of the reason is that I’ve been building new seminar material and now I’m pleased to announce some first seminar dates!

I have updated new seminar dates and cities in m new webpage:

• http://tech.e2sn.com/oracle-training-seminars

From April 2010 I offer total 3 different seminars

• Advanced Oracle Troubleshooting v2.0 (3 days)
• Advanced Oracle SQL Tuning (3 days)
• Partitioning and Parallel Execution for Performance (1 day)

I have rearranged the Advanced Oracle Troubleshooting class based on customer feedback, removed some content, added new content and I think this deserves a new version number, 2.0.

Also, I created an entirely new class Advanced Oracle SQL Tuning which should provide the same for SQL tuners that my Advanced Oracle Troubleshooting class has provided for database troubleshooters. This class will not start with CBO concepts and how SQL execution might work in theory, insead we will start from going very deep into understanding how Oracle really executes SQL execution plans and what is the data flow order and hierarchy in the execution plan tree.

From there we go on into learning how to read execution plans of any complexity and how to control SQL execution plans – how to make them do exactly what we want. And CBO topics will come in the end – by then the CBO fundamental concepts such as Cardinality, Density and IO/CPU Cost will make good sense and are not just some arbitrary names for some magic numbers coming from the optimizer ;-)

In addition, I separated Parallel Execution and Partitioning topics (which not everyone is using) into a separate 1-day seminar, Oracle Partitioning and Parallel Execution for Performance, which I usually deliver right after the 3-day SQL tuning seminar.

In coming days I also plan to upload some SQL tuning related content to tech.e2sn.com to show the quality of the upcoming seminar ;-)

So, feel free to check out the seminar dates and descriptions here:

• http://tech.e2sn.com/oracle-training-seminars

Tanel Poder Cool stuff, Oracle, Oracle 11g, Oracle 11gR2, Performance, SQL, Troubleshooting

You may already have noticed that Oracle 11gR2 for Linux is available for download on Oracle.com website, with documentation.

And this document ends speculation about whether Oracle 11.2 will support column-oriented storage – yes it will:

http://www.oracle.com/technology/products/database/oracle11g/pdf/oracle-database-11g-release2-overview.pdf

However, this is apparently available on Exadata storage only as a new error message below indicates:

ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage
Cause: An attempt was made to use hybrid columnar compression on unsupported storage.
Action: Create this table in a tablespace residing on Exadata storage or use a different compression type.

Update: Kevin Closson mentioned that 11gR2 doesn’t really have column oriented storage as some other products like Vertica’s and Sybase IQ use, but its rather just column oriented compression option where storage is still organized by row but individual fields in these rows use compression dictionaries whichcan span multiple block boundaries (we’ll thats my interpretation at least).

The 11gR2 release overview doc seems to be wrong in this case, as it says:

Hybrid columnar compression is a new method for organizing how data is stored. Instead of
storing the data in traditional rows, the data is grouped, ordered and stored one column at a time.

Read Kevin’s note here:

http://kevinclosson.wordpress.com/2009/09/01/oracle-switches-to-columnar-store-technology-with-oracle-database-11g-release-2/

Tanel Poder Oracle, Oracle 11g, Oracle 11gR2

After Randolf’s comment on my last post about identifying cursor SQL text from sql trace file I think one thing needs elaboration.

I mentioned earlier in this post that this cursor dumping technique works “as long as the cursor of interest is still open”.

So how do you know whether this cursor of interest is still open or has been closed and that slot reused by some other statement instead? You would not want to get misled to wrong SQL statement…

Luckily all the info you need is in SQL tracefile.

In 11g you would see “CLOSE #2″ line appearing in tracefile when a cursor is closed.

Before 11g you would see “STAT #2″ lines dumping out SQL plan execution stats – before 11g this is done when cursor is closed, so when you see STAT #2 lines, you know that at that location the cursor in slot #2 was closed.

There are few cases when the stat lines are not printed even if the cursor is closed – for example when the cursor’s library cache lookup (finding suitable shared cursor) happened before SQL trace was enabled. In this case the cursor would not use the extra instrumentation rowsources which generate the STAT lines.

In such case you would just grep the tracefile for PARSING IN CURSOR #2 after your WAIT #2’s. Whenever someone is parsing in a new cursor into slot #2, this means that existing cursor in slot #2 must have been closed somewhere between the PARSING IN CURSOR #2 line and last WAIT/FETCH/EXEC #2 line.

However, there’s one more special case; when the cursor was parsed in before SQL trace was enabled and another OPI call (like EXEC,FETCH) against this cursor is executed, then some Oracle versions actually retrieve the current SQL text and still dump it into trace just like before parsing. This means that if you just have lots of WAIT lines appearing in the trace for a SQL parsed in past, you won’t see the PARSING IN CURSOR lines dumped into tracefile, but if some OPI call is executed against this cursor, then the PARSING IN CURSOR line is dumped just before that OPI call.

I don’t in which exact version this appeared, but in my 10.2.0.1 test instance  this feature gives such output (I ran a select from dba_objects which started fetching lots of rows and then enabled SQL trace from another session):

PARSING IN CURSOR #1 len=29 dep=0 uid=0 oct=3 lid=0 tim=558589701689 hv=3336193532 ad='24f2e864'
select owner from dba_objects
END OF STMT
FETCH #1:c=0,e=5362,p=0,cr=102,cu=0,mis=0,r=500,dep=0,og=1,tim=558589701683
WAIT #1: nam='SQL*Net more data to client' ela= 23 driver id=1111838976 #bytes=2047 p3=0 obj#=39 tim=558589703066
WAIT #1: nam='SQL*Net message from client' ela= 57993 driver id=1111838976 #bytes=1 p3=0 obj#=39 tim=558589761409
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=39 tim=558589761753
WAIT #1: nam='SQL*Net more data to client' ela= 13 driver id=1111838976 #bytes=2001 p3=0 obj#=39 tim=558589762331
FETCH #1:c=0,e=1193,p=0,cr=93,cu=0,mis=0,r=500,dep=0,og=1,tim=558589762865
WAIT #1: nam='SQL*Net message from client' ela= 60762 driver id=1111838976 #bytes=1 p3=0 obj#=39 tim=558589823801
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=39 tim=558589824139
WAIT #1: nam='SQL*Net more data to client' ela= 19 driver id=1111838976 #bytes=2001 p3=0 obj#=39 tim=558589825284

Do you notice something missing?

The PARSE #1 and EXEC #1 lines are missing as the actual parsing and execute calls happened way before the SQL trace was enabled. But Oracle was helpful enough to dump out the SQL text when next OPI call (FETCH) was issued under SQL tracing mode (it “noticed” that the SQL text corresponding to cursor #1 had not been dumped to trace yet).

This is helpful, but of course can cause some confusion, as this PARSING IN CURSOR doesn’t really mean parsing as such (as parsing is done under PARSE call), the PARSING IN CURSOR should probably be named as “ABOUT TO PARSE CURSOR” or something like that.

Tanel Poder Internals, Oracle, Oracle 11g, Performance, Troubleshooting

I just noticed this in 11g Licensing doc ( http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/options.htm#sthref69 ):

• All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.The only exception are the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Diagnostic Pack license.

This exception is not present in 10.2 license guide, so before 11g you can query V$SEGMENT_STATISTICS and V$UNDOSTAT’s history “for free” :)

Of course, collecting this data manually with a 1-line PL/SQL loop script isn’t hard either :)

Tanel Poder Oracle, Oracle 11g, Performance, Troubleshooting

Oracle has done some major improvements in the diagnosability infrastructure in version 11g. Here’s one little detail.

Before Oracle 11g it is possible to access the alert log via SQL using an external table or a pipelined function which in turn uses utl_file.
After reading the text you need to parse it to extract the information you need from there.

Starting from 11g Oracle does all this work for you. There is a fixed table X$DBGALERTEXT, when you query it, Oracle reads the log.xml from alert directory (which contains all the data what alert.log does), parses it and returns the details back as rows:

SQL> select message_text from X$DBGALERTEXT where rownum <= 20;

MESSAGE_TEXT
-----------------------------------------------------------------------------------------------------------------
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in client-side pfile /u01/app/oracle/admin/LIN11G/pfile/init.ora on machine linux03
System parameters with non-default values:
  processes                = 150
  memory_target            = 404M
  control_files            = "/u01/oradata/LIN11G/control01.ctl"
  control_files            = "/u01/oradata/LIN11G/control02.ctl"
  control_files            = "/u01/oradata/LIN11G/control03.ctl"
  db_block_size            = 8192

20 rows selected.

This is the text representation, but you can get individual details from other columns as listed below:

Read more…

Tanel Poder Oracle, Oracle 11g, Troubleshooting

If you get a kick out of learning Oracle internals, especially undocumented commands then check out Miladin’s blog:

http://www.oraclue.com/

His blog uses the same wordpress template as mine, but the content and authors are different!

He has also published detailed notes about Oracle diagnostic events and about some serious improvements in 11g event syntax (as the whole diagnostics infrastructure got a major upgrade in 11g).

Just beware to not run anything in your non-toy databases without seriously thinking & testing why should the feature help and whether it’s safe to use it at all. Most undocumented stuff (in anyone’s blogs, including mine) should be used only in those rare cases where conventional, documented or at least widely used methods dont help.

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

Conferences & Slides

Here are the slides of my recent presentations at Hotsos Symposium and UTOUG events:

• Advanced Oracle Troubleshooting (and scripts I’ve used)
  
• Latch, Lock and Mutex Contention Troubleshooting
• Oracle SQL Plan Execution: How It Really Works

Advanced Oracle Troubleshooting Seminar

I have added Dallas, Salt Lake City, Denver and Hong-Kong to my Advanced Oracle Troubleshooting seminar list for first half of 2009, the full list is below. Check http://blog.tanelpoder.com/seminar/ for outline, scripts, example slides and details.

Seminar dates and locations 2009:

2-3. April – Miracle @ Utrecht, Netherlands – http://www.miraclebenelux.nl/tanel/

13-14. April – Oracle @ Singapore – http://www.oracle.com/education/apac/sg_tanel_poder.html

16-17. April – Oracle @ Sydney – http://www.oracle.com/education/apac/au_tanel_poder.html

20-21. April – Oracle @ Melbourne – http://www.oracle.com/education/apac/au_tanel_poder.html

23-24. April Oracle @ Hong-Kong – http://www.oracle.com/education/apac/hk_tanel_poder.html

27.-28. April – PiSec Ltd @ Edinburgh – http://www.pisec.org/index.php?option=com_content&view=article&id=6&Itemid=12

11-12. May – Oracle @ Spain – http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D70365_1060245&p_org_id=51&lang=E&source_call=

18-19. May – Miracle @ Denmark – http://www.miracleas.dk/index.php?option=com_content&view=article&id=100:advanced-oracle-troubleshooting&catid=19:info&Itemid=71

3.-5. June – Method-R @ Dallas, TX – http://www.method-r.com

10-12. June – Trutek @ Salt Lake City, UT – http://www.trutek.com/index.php?id=165

15-17. June – Trutek @ Denver, CO – http://www.trutek.com/index.php?id=165

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

I thought to post about another new interest of mine, TimestTen, as I’ve worked with it in past and I have become a fan of it, especially after Oracle bought the company.

Oracle has announced that TimesTen in-memory database will support PL/SQL in the upcoming release. That’s in 11gR2, where TimesTen is named the “in-memory database cache”.

I’m happy to see the deep level of integration Oracle is doing with it. It looks like both classic Oracle RDBMS and the TimesTen based code will have a (partially) shared PL/SQL code base.
There’s already a package called UTL_IDENT in Oracle 11.1.0.7 which stores couple variables used for conditional compilation depedent on the database product the packages are installed:

SQL> select dbms_metadata.get_ddl('PACKAGE', 'UTL_IDENT') from dual;

DBMS_METADATA.GET_DDL('PACKAGE','UTL_IDENT')
--------------------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYS"."UTL_IDENT" is

  /* A typical usage of these boolean constants is

         $if utl_ident.is_oracle_server $then
           code supported for Oracle Database
         $elsif utl_ident.is_timesten $then
           code supported for TimesTen Database
         $end
   */

  is_oracle_server     constant boolean := TRUE;
  is_oracle_client     constant boolean := FALSE;
  is_timesten          constant boolean := FALSE;

end utl_ident;
/

It looks that there will be at least some utility packages which share common code base with classic Oracle RDBMS and just use different code internally if needed, based on the UTL_IDENT variables.

This was a nice surprise as such approach promises to have full PL/SQL utilities in TimesTen cache and no discrepancy between the PL/SQL utility capabilities available in different platforms. So (I hope) there will be just one PL/SQL, not one PL/SQL version for Oracle and other for the cache.
Back before 9i days, it was quite annoying to hit the SQL interface discrepancies between PL/SQL and native SQL engines. So there’s hope that there are gonna be less porting issues between Oracle classic RDBMS and TimesTen cache (both for application developers and TimesTen coders as only low level interfaces need to be changed).

Well, at least in theory it’s like that… But I hope Oracle/TimesTen coders will do a good job with this as I’ve worked with some algorithmic trading apps doing extreme transaction processing and in this world you don’t even like wasted microseconds as this directly translates to lost trading opportunities and lost potential revenue. The legacy solutions for such apps are usually some in-memory data stores, often built in house and lots of C++ code around it for procedural work. The data is then loaded / dumped to some disk-based RDBMS asynchronously (as the multi-millisecond response times of a classic RDBMS take ages!!!)

I’m waiting to get my hands on the 11gR2 (as everyone who’s reading this blog I suspect :), but especially the integrated in-memory database cache engine as it promises to make the life easier for a lot of algorithmic trading systems developers (and also the ability adapt quicker to ever-changing markets and growing complexity of products for investment banks and trading houses).

Tanel Poder Internals, Oracle, Oracle 11g, TimesTen

Here are two more US dates of my Advanced Oracle Troubleshooting seminars, organized by Trutek.

These seminars are 3 days each, so there will be more time to thoroughly explain things and do even more demos!!! :)

10-12. June – Trutek @ Salt Lake City, UT – http://www.trutek.com/index.php?id=165
15-17. June – Trutek @ Denver, CO – http://www.trutek.com/index.php?id=165

Tanel Poder Cool stuff, Internals, Oracle, Oracle 11g, Performance, Training, Troubleshooting

The latest version of my TPT scripts are downloadable from the link below (TPT means Tanel Poder’s Troubleshooting (or tuning) scripts ;)

http://www.tanelpoder.com/files/TPT_public.zip

In the zip file there are over 300 Oracle sql scripts which I use for my everyday work. Also the demo scripts I show at Advanced Oracle Troubleshooting seminar are in there as well (in aot subdirectory).

To my seminar attendees in New York this week: I had a typo in the TPT script download link, the link above is the correct one!

Tanel Poder Administration, Cool stuff, Internals, Oracle, Oracle 11g, Performance, SQL, Tools, Troubleshooting