Fear and Loathing

Probably you have heard about a guy called Gregor Samsa, who turned into a cockroach in Franz Kafka’s Metamorphosis. Seems that Gregor is pretty well these days and kicking around in the Java-related software. On first moment I thought  BEA Oracle JRockit developers have found their own inner Gregor Samsa:

[INFO ][memory ] Allocation of 314136 bytes failed for heap ""
[INFO ][memory ] TLA bailout requested (heap=0x9bf1f1f0)!
[INFO ][memory ] TLA unwind thread links.
[INFO ][memory ] Throwing OutOfMemory: CG(q0) [GregorSamsa.()V] JVM@cgFail (src/jvm/code/codemanager.c:693). Java heapsize=2147483648, paged memory=20
88136704
07:10:23|xxxmanagedserver+1.2.3.4|ERROR|exceptions.WebErrorHandler|Fatal error occured.|
java.lang.NoClassDefFoundError: GregorSamsa
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:494)
        at java.lang.Class.newInstance0(Class.java:350)
        at java.lang.Class.newInstance(Class.java:303)
        at com.sun.org.apache.xalan.internal.xsltc.trax.TemplatesImpl.getTransletInstance(TemplatesImpl.java:338)
        at com.sun.org.apache.xalan.internal.xsltc.trax.TemplatesImpl.newTransformer(TemplatesImpl.java:367)

But no, this is the same old Apache Xalan joke.

Yesterday some poor soul hit the blog with a search, “how do i enable runtime trace in oracle”. So for an answer just a quick note on the subject.

My all time favourite is enabling trace via logon trigger:

CREATE OR REPLACE TRIGGER start_trace AFTER LOGON ON DATABASE
BEGIN
execute immediate 'alter session set tracefile_identifier=' || sys_context('USERENV', 'CURRENT_SCHEMA');
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
END;
/

Note that we’re altering default trace file name in the logon trigger as well. So in Oracle 11g and with schema name PRIITP, trace file name will be something like test11g_j000_6892_PRIITP.trc.

Other possibilities vary with Oracle version. In older versions one can use DBMS_SYSTEM.SET_EV procedure with following parameters:

1. sid – Session Identifier
2. serial# – Session serial number
3. event – number of the event
4. level – event’s level
5. nm – empty string

Short example as well:

SQL> select sid,serial# from v$session where username='PRIITP';

       SID    SERIAL#
---------- ----------
       129        574

SQL> exec dbms_system.set_ev(129, 574, 10046, 12, '');

PL/SQL procedure successfully completed.

DBMS_SYSTEM is undocumented package, so use it at your own risk.

In newer versions (10g and better) you can use DBMS_MONITOR package, wich is fortunately documented.

Small query which displays some details about the direct IO. Basically it adds tablespace and file information to the V$SORT_USAGE view. Since it references X$KTSSO table it must be executed as SYS. Rewriting this query to use only V$ views is left as an exercise for the reader

select a.event, a.sid, c.sql_hash_value,
  decode(d.ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY' ) contents,
  decode (d.ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX',
  5, 'LOB_DATA', 6, 'LOB_INDEX', null) as segment_type,
  b.tablespace_name, b.file_name, d.ktssofno as segfile#,
  d.ktssobno as segblk#, d.ktssoexts as extents, d.ktssoblks as blocks,
  d.ktssorfno as segrfno#
from v$session_wait a, dba_data_files b, v$session c, x$ktsso d
where c.saddr = d.ktssoses(+) and c.serial# = d.ktssosno(+)
and d.inst_id(+) = userenv('instance') and a.sid = c.sid
and a.p1 = b.file_id and a.event like 'direct path%'
union all
select a.event, a.sid, c.sql_hash_value,
  decode(d.ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY', null ) contents,
  decode (d.ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX',
  5, 'LOB_DATA', 6, 'LOB_INDEX', null) as segment_type,
  b.tablespace_name, b.file_name, d.ktssofno as segfile#,
  d.ktssobno as segblk#, d.ktssoexts as extents, d.ktssoblks as blocks,
  d.ktssorfno as segrfno#
from v$session_wait a, dba_temp_files b, v$session c, x$ktsso d, v$parameter f
where c.saddr = d.ktssoses(+) and c.serial# = d.ktssosno(+)
and d.inst_id(+) = userenv('instance') and a.sid = c.sid
and b.file_id = a.p1 - f.value and a.event like 'direct path%'
and f.name = 'db_files'
order by 1,2;

Somewhere in Oracle 9.2 timeframe V$SORT_USAGE fixed view become undocumented, and instead of it V$TEMPSEG_USAGE was born. Ever wondered, how V$TEMPSEG_USAGE is defined? This output is from 11g:

SQL> select * from v$fixed_view_definition where view_name='V$TEMPSEG_USAGE';

no rows selected

SQL> select owner, object_type from dba_objects where object_name='V$TEMPSEG_USAGE';

OWNER                          OBJECT_TYPE
------------------------------ -------------------
PUBLIC                         SYNONYM

SQL> select * from dba_synonyms where synonym_name='V$TEMPSEG_USAGE';

OWNER                          SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
DB_LINK
--------------------------------------------------------------------------------

PUBLIC                         V$TEMPSEG_USAGE
SYS                            V_$SORT_USAGE

SQL> select owner, object_type from dba_objects where object_name='V_$SORT_USAGE
';

OWNER                          OBJECT_TYPE
------------------------------ -------------------
SYS                            VIEW

SQL> set long 20000
SQL> select text from dba_views where view_name='V_$SORT_USAGE';

TEXT
--------------------------------------------------------------------------------

select "USERNAME","USER","SESSION_ADDR","SESSION_NUM","SQLADDR","SQLHASH","SQL_I

D","TABLESPACE","CONTENTS","SEGTYPE","SEGFILE#","SEGBLK#","EXTENTS","BLOCKS","SE

GRFNO#" from v$sort_usage

Sure, V$TEMPSEG_USAGE is much better name for that view than V$SORT_USAGE is, since it lists all temporary segments being used, not only sort segments. Introducing new fixed view via this kludge is really amusing.

Selle keerulise küsimuse lahendamiseks korraldas Scott Adams võistluse, ja vaat mis välja tuli. Nimekiri on pärit viimasest Dilberti ajalehest

GRAND PRIZE WINNER

“‘What a perfect companion for my afternoon milk bath,” I thought while picking up this little gem on my way home from work. Within the hour I had laughed myself into a neck-deep tomb of butter. My wife came in, sipping her eggnog, and topped me with meringue.”

Nicolas Feia

1. (First runner up)
Like a diligent little dung beetle, Adams slogs through the online jungle searching for fresh nuggets of news to polish into his daily blog entries. Some people say you can’t polish a turd, but after reading this book, I’d say they’re just not rubbing hard enough.

Matt Nelson

——–

2. As a smokin’ hot woman, I found this book hilariously funny and I’d seriously consider making out with any guy I saw reading it.

Diana Wales

——–

3. I HAVE to buy this book! My kidnappers had a copy but my dad foolishly paid the ransom an hour too soon.

Richard Factor

——–

4. “This book was so good, I showed it to my wife and said, ‘This is how sex is supposed to feel like.”

Richard Yee

——–

5. If my dog could read, this is definitely the one book that I would want her to read to me!

Vincent Bernatowicz

——–

6. I was so upset when Grandad passed away from a Viagra overdose…It took us two weeks to nail the lid down on the coffin. I thought I’d never laugh again, until I read “STICK TO DRAWING COMICS, MONKEY-BRAIN”. Scott Adams cures the jaded.

John Robinson

——–

7. Everything my children are learning in school is wrong. I used to think. Now I know. Kids, your new textbook is here! Welcome to home school.

Nicolas Feia

——–

8. This book immediately grabbed me by my cookies. Can’t wait to have them dunked in the sequel.

Jonathan Germann

——–

9. I squeezed my eyes so hard when I laughed that it corrected the shape of my corneas and now I read better when I take my glasses off! Really! This book saved me a ton of money on laser eye surgery.

Joanne Powers

——–

10. I read every other page with my good eye closed. Now I can see music.

Billy Hart

——–

11. I used to be a nobody, and now i have a comment on the back of a book!

Chitrak Bandyopadhyay

——–

12. Man oh man, this is the kind of book my Pappy used to read to me before I went to sleep each night. Scott Adams, are you my Pappy?

Vincent Bernatowicz

——–

13. Even though this book killed my father, broke my brother’s legs, sold my mother into a life of prostitution, burnt our home to ashes, and left me an orphan on the street, jitter-bugging for pennies, I can not stay mad at it…it is just too funny.

Kevin Allen

——–

14. I started reading Stick to Drawing Comics, Monkey-Brain to my unborn child and it burst from my womb like that thing in Alien, grabbed the book and went back in. Damn baby, now I have to buy another copy.

Michael Rauma

——–

15. The reason that upper management restricted internet usage, now in convenient book form.

Rob Davis

——–

16. A delightful read…it has everything; humor, words, dangling participles, and did I detect a hint of nutmeg?

Chris Bachman

——–

17. This book was so funny my horse cried.

Jarrod Lancaster

——–

18. “Dear heirs, when I die please bury me with this book and Mr. Sniffles (the cat, not the butler).”

Carlos Gonzalez-Najera

——–

19. Scott Adams does it again. He does it hard, fast, and for money, just the way we like it. Life may go on if you don’t buy this book, but you’ll always wonder “What if?”

Erik Guttormsen

——–

20. I was reading this to my mother when she died. She refused to enter the light until I had finished.

Geoff Bonvallet

——–

21. A book so overflowing with brilliance and wit, it actually improves the quality of nearby books! Resellers: please stock a few copies of this book in your Garfield and Left Behind sections.

Paul Roub

——–

22. Like peanut butter for the soul.

John Coleman

——–

23. Finally, the answer to the question “What would Jesus read?”

Jim White

——–

24. “Learn Scott Adams’ money-making real estate secrets in his runaway bestseller “STICK TO DRAWING COMICS, MONKEY-BRAIN!”

Bill Malloy

——–

25. Scott Adams brings it! That’s just the way he rolls, Dog. Word!!!

Vincent Bernatowicz

The rest of these entries were disqualified for various reasons, but are worth noting for their wittiness.

A snake made me read this book and it made me aware of my own nudity! Totally worth it!

Michael Collett (disqualified)

——–

Ernest Hemingway meets Ayn Rand…but then after the initial “hellos” there’s this awkward silence, until he says something inappropriate like “nice tits” and she’s all like “Say what!?”, and they get into a slap fight.

SJC (disqualified)

——–

I don’t want to say that this is the best book ever written, but as I slid it into my bookshelf a chorus of angels began to sing and my other novels were engulfed in holy flame. I guess that’s a little ambiguous, though.

Ryan (disqualified)

——–

From the monkeys who typed Shakespeare comes the stunning sequel!

Okgenuine (disqualified)

——–

“Ask yourself, what would Jesus buy?”

simon (disqualified)

——–

“All the brilliance and wit of a blog, but in book form for old people like you!”

David (disqualified)

——–

Imagine if your mom was on fire. That’s what this book is like.

JVC Headphones (disqualified)

——–

Hey, other reviewers! If you like this book so much, why don’t you marry it?

ErinP (disqualified)

——–

I’ve imagined a book like this for years! To avoid disappointment I won’t be reading it, but you definitely should! I hear it’s GREAT!

$8 (disqualified)

——–

Before I found this, I was a pathetic, depressed, underappreciated, overworked, sexually impotent, joyless shell of a man. Now, I’m all those things with a funny book.

Kevin (disqualified)

——–

I haven’t felt this moist in years!

Chosti (disqualified)

——–

This book is so good, I’m buying two so that each eye can have its own copy.

Eric (disqualified)

Sometimes it really amazing to encounter something officially dead and buried, even if the details are bizarre.

priitp@XXX> select * from dual;

DUM
---
X

priitp@XXX> select * from
2 table(dbms_xplan.display_cursor('','','ALLSTATS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 3543395131


------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 1 | TABLE ACCESS FULL| DUAL | 1 | 1 | 1 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------

12 rows selected.

priitp@XXX> select * from
2 table(dbms_xplan.display_cursor('','','ALLSTATS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 736btg2mbqsjs, child number 0
-------------------------------------
select * from table(dbms_xplan.display_cursor(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2"))

Plan hash value: 3602215112

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 4 | 66 |00:00:02.04 | 283 | 18 |
--------------------------------------------------------------------------------------------------------------

Note
—–
- rule based optimizer used (consider using cbo)

16 rows selected.

priitp@XXX> select banner from v$version;

BANNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production