SOUG last week

The documents from last SOUG special interest group with Tom Kyte are now available for downloadable on the soug.ch homepage (under history).

The day started with Sven Vetter, who talked about SLA management pack for Enterprise Manager Grid Control 10gR2. We saw also how to define a custom shell script and let OEM generate a graphic over time. He talked about "beacons", a kind of interface to define application interaction with the grid.

Than came an interesting presentation about Performance Tuning from Patrick Schwanke. He talked about views like DBA_HIST_SQLTEXT and DBA_HIST_SQLBIND. I also learnt the virtual index trick : There is a parameter called _use_nosegment_indexes with allow you to generate EXPLAIN PLAN for a segment without segment. There is a magic keyword called NOSEGMENT. This mechanism is used internally by Oracle Tuning Pack, and by other tools like the ones from Quest.


SQL> create table t as select * from all_objects;

Table created.

SQL> create index i on t(object_id) NOSEGMENT;

Index created.

SQL> alter session set "_use_nosegment_indexes"=true;

Session altered.

SQL> select * from t where object_id<10;

Execution Plan
----------------------------------------------------------
Plan hash value: 242607798

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 768 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 6 | 768 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I | 307 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"<10)

Note
-----
- dynamic sampling used for this statement

SQL> alter session set "_use_nosegment_indexes"=false;

Session altered.

SQL> select * from t where object_id<10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 768 | 151 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 6 | 768 | 151 (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"<10)

Note
-----
- dynamic sampling used for this statement

SQL> drop index i;

Index dropped.


Later, Tim Polland talked about Texas Memory and the RAMdisks. It is a piece of hardware which use memory instead of disk to store the datas. The hardware has a kind of RAID mechanism for consistency, and it is not supposed to lose data (!). It is quite different from a typical disk cache, because it does not "cache" the datas, it just saves them in memory. Well, you are not supposed to use this to stores a terabyte of datas, but you could store only your most time-critical datas there, for example move a table to a different tablespace, and store that table on the RAMDISK, also the redo logs, and eventually the undo/system tablespaces too. The RAMDisk hardware is not specific for Oracle. Some "entry-level" ramdisk can offer something like 400 Megabytes/seconds data with nanoseconds access time. Well, it is the speed of memory, so it is also the price of memory. So do not expect to have this at home to play your favorite games !

In the afternoon, we started with Oracle Benchmarks with Manfred Drozd. Very interesting presentation, which shows that there is no "quick" benchmark, but a benchmark should consider I/O, PL/SQL performance, amount of disks, volume manager/ASM, cpu types/count, OS, architecture, Oracle Version, etc. The performance for select, for insert, for update, all those may differ from one system to another.

Real world example just followed with the presentation of a study case in the swiss post. The comparison was mainly between Sun Solaris on Sparc and Linux on x86_64. Well, there was no Better/Worst answer, you know, like is real world...

Last but not least, Tom Kyte, the "Tom" behind asktom, did talk about Instrumentation and the advantage -the need- of using debugging info, with DBMS_APPLICATION_INFO, with your own debug procedure, with Log4Plsql/Log4J packages. The tools we show were DBMS_MONITOR (10g) and trcsess tool (located in $ORACLE_HOME/bin), DBMS_TRACE and PL/SQL conditional compilation. DBMS_TRACE is usefull for detecting catched exception :


SQL> @?/rdbms/admin/dbmspbt
SQL> @?/rdbms/admin/prvtpbt.plb
SQL> @?/rdbms/admin/tracetab
SQL> exec dbms_trace.set_plsql_trace(dbms_trace.trace_all_exceptions)

PL/SQL procedure successfully completed.

SQL> exec begin dbms_output.put_line(1/0); exception when others then null; end;

PL/SQL procedure successfully completed.


SQL> select EXCP,EVENT_UNIT_KIND,ERRORSTACK from plsql_trace_events where ERRORSTACK is not null;
EXCP EVENT_UNIT_KIND
---------- --------------------
ERRORSTACK
----------------------------------------
1476 ANONYMOUS BLOCK
ORA-01476: divisor is equal to zero