Advert - Online SQL Tuning Workshop

Frustrated with poorly performing SQL? Unsure where to begin the tuning process? Then join Daniel Fink, Oracle performance specialist, for an Online SQL Tuning Workshop. This hands-on course will provide you with the skills necessary to create scalable, high performance SQL-based Oracle applications (proactive SQL tuning). You will also learn how to identify existing high-load, poor performing SQL statements and tune them (reactive SQL tuning). Particular attention is paid to making the best use of Oracle architecture, bench-marking various SQL formulations and identifying the best schema object (e.g. heap table, IOT, etc.) for a given situation. A full description can be found at http://www.skillbuilders.com/instructor-led-training/Course_outlines/oracle-10g-sql-tuning.cfm

This class runs during the afternoons (1:00 PM through 5:00 PM EDT) of Monday May 4th - Friday May 8th. All you need is an internet and audio (phone or VOIP) connection. No travel, reduced time away from work. The class is limited to 6 students. To register, please call Gary at +1-888-803-5607 or email gary@skillbuilders.com. Mention OptimalDBA for a 15% discount for the class.

Damnit Oracle!

/* This is on Oracle 10gR2 on AIX. I have not checked it on other releases or platforms*/

In checking the logon triggers, I came across a little situation.

select trigger_name, trigger_body
from dba_triggers
where owner = 'DEMO'
and triggering_event = 'LOGON'

no rows selected

SELECT distinct triggering_event
from dba_triggers
where owner = 'DEMO'

TRIGGERING_EVENT
----------------------------------------
INSERT
LOGON
INSERT OR UPDATE OR DELETE
UPDATE OR DELETE
UPDATE
INSERT OR UPDATE

6 rows selected.

So...LOGON is certainly a valid event, so I decide to retype the query and rerun it...

select trigger_name, trigger_body
from dba_triggers
where owner = 'DEMO'
and triggering_event = 'LOGON'

no rows selected

After trying the query several times and expecting different results (yes...I know...the definition of insanity), I checked the actual data being returned using the DUMP function.


select distinct triggering_event, dump(triggering_event)
from dba_triggers

TRIGGERING_EVENT
----------------------------------------------------------------------------------------------------
DUMP(TRIGGERING_EVENT)
----------------------------------------------------------------------------------------------------
UPDATE
Typ=1 Len=6: 85,80,68,65,84,69

UPDATE OR DELETE
Typ=1 Len=16: 85,80,68,65,84,69,32,79,82,32,68,69,76,69,84,69

INSERT OR UPDATE OR DELETE
Typ=1 Len=26: 73,78,83,69,82,84,32,79,82,32,85,80,68,65,84,69,32,79,82,32,68,69,76,69,84,69

DELETE
Typ=1 Len=6: 68,69,76,69,84,69

DROP
Typ=1 Len=5: 68,82,79,80,32

LOGON
Typ=1 Len=6: 76,79,71,79,78,32

INSERT
Typ=1 Len=6: 73,78,83,69,82,84

INSERT OR UPDATE
Typ=1 Len=16: 73,78,83,69,82,84,32,79,82,32,85,80,68,65,84,69

It seems that the view includes a single space (ascii 32) to the end of some of the events. In looking at the view text, I see this when they concatenate triggering events together. Way to code a view!

So if you are using the TRIGGERING_EVENT as part of a predicate, add a trailing space or wrap it in the TRIM() function or you may not get the results you want!

select trigger_name
from dba_triggers
where owner = 'DEMO'
and triggering_event = 'LOGON '

TRIGGER_NAME
------------------------------
KATHY_START_TRACE
TIM_START_TRACE
START_TRACE
DFINK_START_TRACE
CLOSE_START_TRACE

5 rows selected.

select trigger_name
from dba_triggers
where owner = 'DEMO'
and TRIM(triggering_event) = 'LOGON'

TRIGGER_NAME
------------------------------
KATHY_START_TRACE
TIM_START_TRACE
START_TRACE
DFINK_START_TRACE
CLOSE_START_TRACE

5 rows selected.