SQL

Gather SQL Statements from AWR and Cursor Cache without need for Diag and Tuning Packs

When we talk about database upgrades and migrations the most important (and unfortunately time-/resource consuming) activity is testing.

But often testing resources are limited or, even worse, don’t exist. I’ve worked with customers where we migrated a core EBS system off AIX to Linux – but only got a 6 year old Tru64 box for testing purposes (where the recompilation took 20x as long as later on the production environment). Or the classic one: Database is too big thus we test only with 10% of the data.

I know that often your hands are tied. Everybody wants an 1:1 …

Continue reading...

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 …

Continue reading...

Oracle Multitenant: New SQL Container Clause

Tiny little enhancement in Oracle Database 12.1.0.2:
The new CONTAINER clause to access data from different containers within one SQL statement. This may be very helpful, especially in case of schema consolidation. Similar things could have be done in Oracle 12.1.0.1 already by using database links – but resulting in way more complicated SQLs.

This is the new clause:

SELECT ename FROM CONTAINERS(scott.emp) WHERE CON_ID IN (45, 49);

See the documentation for more info about it.

–Mike…

Continue reading...