Parameters

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...

Optimizer Issue in Oracle 12.0.1.2: “Reduce Group By”

Wrong Query Results BugDBAs biggest fears I’d guess are Optimizer Wrong Query Results bugs as usually the optimizer does not write a message into the alert.log saying “Sorry, I was in a bad mood today …”.

The Oracle Database Optimizer is a complex piece – and in Oracle 12c it delivers great performance results. Plus (my personal experience when you know what to do) it is more predictable which I like a lot when changing databases from one to another release. But due to its complexity sometimes we see issues – and sometimes it is necessary to switch off tiny little pieces …

Continue reading...

Log Writer Slave Issues in 12.1.0.2 – mainly on IBM AIX and HP-UX but also on Linux

Currently we see a lot of issues with the Multiple Logwriter feature in Oracle Database 12.1.0.2, especially on the IBM AIX platform.

What are Multiple LGWRs?

You will see multiple LGnn (Log Writer Worker) processes on multiprocessor systems, The LGWR (Log Writer) creates worker processes (slaves) to improve the performance of writing to the redo log. LGnn (Log Writer Worker) processes are not used when there is a SYNC standby destination. Possible processes include LG00LG99.

Here is a list of new background processes in Oracle Database 12.1:
MOS Note 1625912.1 – New Background

Continue reading...

Is it always the Optimizer? Should you disable Group By Elimination in 12c?

I wouldn’t say it’s always the optimizer – but sometimes one or two tiny little things are broken making it necessary to turn off new functionality for a while.

Please don’t misinterpret this posting!
As far as I see (and I really do work with customers!) I’d state the Oracle Database 12.1.0.2 Optimizer is more stable, faster and predictable compared to 11.2.0.x. Our Optimizer Architects and Developers have done a great job. But with all the complexity involved sometimes it takes a few fixes or incarnations until a great new feature really matures. The Group-By-Elimination feature in Oracle Database …

Continue reading...

Maintenance Windows is too small? Autotask Jobs fail

Since Oracle Database 10g we have automatic maintenance jobs in the database running. Since Oracle Database 11g (and the same applies to 12c) we have:

  • Weekday Jobs being able to get executed from 10pm until 2am on MON-FRI
  • Weekend Jobs being able to get executed from 6am until 2am on SAT-SUN

A support colleague contacted me a while ago asking why at his customer the jobs don’t start anymore as they have set the duration for weekdays to 15 minutes. You may adjust the window generally by yourself with:

  • exec DBMS_SCHEDULER.SET_ATTRIBUTE(‘MONDAY_WINDOW’,’DURATION’,numtodsinterval(2,’hour’));

But in this particular case only changing the default

Continue reading...

New (some undocumented) Parameters in Oracle 12.1.0.2

Every release offers some surprises – even to myself 😉

Right now Roy and I are in the final steps to refresh our big slide deck to the new layout, but more important, to have Oracle 12.1.0.2 information included as well (were necessary). So I did my usual “compare parameters” query between releases – getting unusual surprises this time.

This is the list of new parameters introduced with the patch set Oracle Database 12.1.0.2. Where applicable I have added the link to the doc.

But as you may recognize not all of them are explained in the doc 😉

Continue reading...

New 11.2.0.4 Parameter: ENABLE_GOLDENGATE_REPLICATION

Just learned something new I couldn’t find actually in the doc at the first glance:

There’s a new init.ora parameter introduced in Oracle Database 11.2.0.4 named:
ENABLE_GOLDENGATE_REPLICATION

By default it is set to FALSE and the parameter got introduced because not only the external use of Oracle GoldenGate requires a valid license but also the use of the internal APIs. For example, XStream provides high performance APIs that enable client
applications to receive and send real-time data changes from an Oracle
database. Other APIs were added for encryption support, trigger
suppression, etc. None of these APIs are licensed with

Continue reading...

Why you shouldn’t set OPTIMIZER_FEATURES_ENABLE

Roy today answered an interesting question on one of our internal mailing lists. And this reminds me to pick up that piece of information as we see this quite often on customer sites, especially after upgrades or migrations. People set OPTIMIZER_FEATURES_ENABLE (OFE) to revert the optimizer’s behaviour to another pre-current release. That’s what a lot of people think this parameter does.

But in fact this is not true. Even though our documentation states it:

OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number. For example, if you upgrade your database

Continue reading...

Parameter _rollback_segment_count can cause trouble

Just some weeks ago we’ve learned that setting the hidden underscore parameter:

_rollback_segment_count

may cause trouble during upgrade. This parameter is used in very rare cases to have under all circumstances and situations this specified number of UNDO’s online. Now during upgrade this may result in massive latch contention  – and there’s a patch available as well. Recommendation is to unset it during upgrade.

I don’t think that many people will hit this as I personally haven’t seen databases with this underscore in their init.ora or spfiles. So take this post more or less as a reminder …

Continue reading...

NLS_LENGTH_SEMANTICS – Why not in the init.ora?

.Few days ago at the Upgrade and Migration Workshop in Hamburg when I did talk about Unicode Migrations a customer had an interesting question.

“Why does the documentation states NOT to set NLS_LENGTH_SEMANTICS parameter to CHAR in the init.ora/spfile to enable char semantics for newly build objects by default?”

Honestly I was not aware of that hint. So I did some research and tried to get an answer from the developers.

The documentation says clearly not to set that parameter permanently:

Continue reading...

Upgrade – and an interesting surprise

Patchset 11.2.0.2 is out there for a long, looong time. But still Roy and me – and unfortunately our customers – sometimes experience some nice surprises after upgrade.

Roy did work on the weekend with a financial institution customer in the US to support them during their go-live on Oracle 11.2.0.2 with several systems. It was well tested and long planned. And overall the whole process went well except for one database (see Roy’s entry below on the change with JOB_QUEUE_PROCESSES in 11.2.0.2).

Yesterday I’ve received a text message from Roy to have a closer look into a service request …

Continue reading...

DBMS_SCHEDULER jobs stuck after upgrade to 11.2? Check this parameter!

I think I learn something new practically every time that I talk to or work with customers. Here’s a nugget of information that may be worth its weight in gold if you are upgrading in a RAC environment. You might find after the upgrade that your DBMS_SCHEDULER jobs seem to be scheduled but unable to run, as if the scheduler is stuck for some reason.

The reason for this is an interesting parameter job_queue_processes whose behavior changed in 11.2. The parameter itself has been around for a long time, but here is the important note from the 11.2 Upgrade

Continue reading...

Too many heap subpools might break the upgrade

Recently one of our new upcoming Oracle Database 11.2 reference customers did upgrade their production database – a huge EBS system from Oracle 9.2.0.8 to Oracle Database 11.2.0.2. They’ve tested very well, we’ve optimized the upgrade process, the recompilation timings etc.

But once the live upgrade was done it did fail in the JAVA component piece with this error:

begin if
initjvmaux.startstep(‘CREATE_JAVA_SYSTEM’) then
*
ORA-29553:
classw in use: SYS.javax/mail/folder
ORA-06512: at “SYS.INITJVMAUX”, line 23
ORA-06512: at line 5

Support diagnosis was pretty quick – and refered to:
Bug 10165223 – ORA-29553: class in
use: sys.javax/mail/folder during database

Continue reading...

New hidden parameters in Oracle 11.2

We really welcome every external review of our slides. And also recommendations from customers visiting our workshops.

So it happened to me more than a week ago that Marco Patzwahl, the owner of MuniqSoft GmbH, had a very lengthy train ride in Germany (as the engine drivers go on strike this week it could have become even worse) and nothing better to do than reviewing our slide set. And he had plenty of recommendations.

Besides that he pointed us to something at least I was not aware of and added it to the slides:

In patch set 11.2.0.2 …

Continue reading...