Thursday Jul 30, 2015

SQL Monitoring - Limitation at 300 lines per statement

One of the best parts of my job at Oracle:
I still learn something new every day.

Yesterday I've learned from my colleague from Oracle Switzerland, Thomas Teske, that SQL Real Time Monitoring has an embedded default limitation on the number of lines in the statement. If the limit (default: 300 lines) is exceeded the statement won't be monitored. We both work with a leading Swiss company and we wanted to monitor a complex plan. 

Now you may think: Who the heck has statements longer than 300 lines?
Well ... sometimes that is beyond your influence as in this particular case this is of course done by the application.


SQL> alter system set "_sqlmon_max_planlines"=800 scope=both;

or set in your spfile:


This limitation is described in:

MOS Note:1613163.1
How to Monitor SQL Statements with Large Plans Using Real-Time SQL Monitoring?

If you'd like to read a bit more about SQL Real Time Monitoring please follow one of these links - and be aware that it's part of the Tuning Pack license and VERY helpful in many everyday situations. You'll have to have STATISTICS_LEVEL either TYPICAL (the default) or ALL and CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' (the default as well).



Monday Oct 31, 2011

Are you still on Daylight Savings Time?

Central Europe and many other countries switched back from Daylight Savings Time to regular time on this Sunday's night. But others don't. Thanks to a "wise" decision in 2006 the US will switch back to regular time in one week. And another "wise" man has decided that there's no need for Russia to switch back to regular time (see the last paragraph on "DST" in this linked Wikipedia article) - so Russia will stay forever on DST being one hour ahead ;-) Samoa will simply jump from the end of the time zone puzzle now heading it end of this year - so there will no December 28th this year in Samoa (I wonder what will happen if they switch back one day?).

And if you wonder why your new Apple iPhone 4S drains the battery pretty quick the algorithm polling the time zone server in the internet every second might be the reason Why ... ;-)

Now you might ask yourself:
Why am I writing about all this stuff?

Simple reason: As you might know the database has time zone definitions in $ORACLE_HOME/oracore/zoneinfo since Oracle Database 9.2. Good news for everybody who'll upgrade from Oracle Database Patch Set to - both contain TZ V14 so there's no time zone adjustment post patch upgrade necessary. But the newest available Time Zone files is TZ V17.

One day a customer did complain to me about the misleading MOS notes calling it a "Time Zone Spaghetti". Even though the note situation has been cleaned up  (at least a bit) and MOS Note:977512.1 contains the one-and-only script to change the settings you may watch a little 6 minute video about the background on DST published by C.G.P. Grey:

By the way, this is an overview on Time Zone Version per Oracle Database release:

Oracle Database Release

Default Time Zone Version




DST V2 –

DST V3 –

DST V4 –


DST V11 –


Monday May 23, 2011

Time Zone Upgrade might be slow

One of the best things with my role in Oracle's Upgrade Development is:
I work with customers - and I learn a lot from customers.

This is an issue Richie and his team from Accenture in Ireland brought to my attention. I try to support them currently in an EBS database upgrade. And he did highlight a potential issue with the post upgrade time zone change to me:

Time ZonesBug 10209691 - slow performance on ALL_TSTZ_TAB_COLS
Workaround: alter session set "_with_subquery"=materialize;

All together the upgrade of an Oracle EBS 11i database takes approx 30 minutes. And we still do recommend upgrading the Time Zone Data post upgrade as first of all we use the data type TIMESTAMP WITH TIMEZONE internally in the dictionary and - more important - the scheduler relies on correct settings. Otherwise it could happen that jobs don't get executed.

Of course Roy and me added this information to the slides already ;-)


Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -


« November 2015
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers