UKOUG Day 3: PL/SQL hints by Tom Kyte & Oracle Active Data Guard

Today’s first session I assisted was presented by Tom Kyte. He talked about SQL topics we surely do not know. The first topic concerned the possibility of using the plsql_warning clause with the PL/SQL compiler. This feature exists since version 10.1 but is not widely used. The different values you can define for the plsql_warning with a classical alter session command are:

Severe: code might cause unexpected errors or wrong results
Performance: code might cause performance issues
Informational: code is not wrong but this is just bad code

Thus by using this command before compiling the PL/SQL code you will receive messages helping you to improve your code.
The second hint was about implicit conversion. For example the implicit conversion from string to number or to date is probably the first cause of bugs or performance degradation. It could be even worse with the implicit conversion that rely on NLS default settings.
If we had used the PL/SQL warnings, those implicit conversion would have been told out.
One interesting hint was concerning the different levels of the optimizing compiler. You can set the PLSQL_OPTIMIZER level to the following values:

1: no rearranging code
2: rearranging code is possible
3: code is rearranging

He showed us some very interesting examples which showed us that the level 3 might be very aggressive and modify the results.
As an overview, the PL/SQL warning framework is important and very useful; the last level of the PLSQL_OPTIMISER must be used with caution.

My second slot was about Oracle Active Data Guard, the Next Generation Data Protection with Oracle 12c presented by Mr. Larry Carpenter, Master Oracle Product Manager at Oracle USA.
He presented us the following new features available:

The new Data guard fast sync feature is reducing the synchronous transport impact. In maximum availability protection mode, the standby database acknowledges the receipt of redo before writing to standby redo logs. The main benefit is that it reduces the synchronous performance impact on the primary database, and consequently the primary database performances are more predictable.

He also announced that DML on global temporary tables are now supported in active data guard 12c. It is enabled by the new parameter TEMP_UNDO_ENABLED.

The data guard broker has also evaluated in terms of monitoring, robustness, log monitoring and warnings, validating role transition readiness. For example the Transport lag and Apply lag is included now in the show database output. The validate command can be used to assess readiness to a switchover or a failover, it validates each database current status , verifies there is no archive lag gap, detects parameters property inconsistency and also performs a log switch on the primary database to verify the log is applied on all standbys.

The Rolling Upgrade has also been centralized and simplified and uses a dedicated interface by using dbms_rolling package which will greatly simplify the rolling upgrade in comparison to the previous 11g release by using only five phases:

dbms_rolling.init_plan: generates an upgrade plan
dbms_rolling.set_parameters: modifies the parameters of the rolling upgrade
dbms_rolling.start_plan: configures primary and standby database for the upgrade
dbms_rolling.switchover: swaps roles between the primary and the standby, this is the only downtime
dbms_rolling.finish_plan: completes upgrade and resynchronizes

Finally I assisted to an EM12c session: Using BI publisher with EM12c for enhanced productivity animated by Mr. Dananjay Papde author of Oracle EM12c administration cookbook.

BI publisher is the oracle’s primary reporting tool for authoring managing and delivering documents, highly formatted and published in various formats sucha as PDF, Word, Excel, Power Point …

To use Bi publisher with EM 12c you have to install and integrate it with EM 12c in two phases:

1: software only install of BI version 11.1.1.6.0
2: integrate BI publisher with EM12c

Note that in the next version of OEM 12c (12.1.0.4.0) you won’t be obliged to install and configure BI publisher, it will be integrated directly in OMS.
Before running the configuration it is recommended to take a backup of the repository and the EM domain, then you only have to run the configureBIP script located in the $OMS_HOME/BIP/bin directory.
Creating BI Publisher reports can be done in an easy way, you select the EM repository as data source and you can use the query builder to build your queries from mgmt$ views.

Another interesting point is that you can efficiently manage your BI publisher from the EM 12c console. By accessing the BI Farm summary you have access to a lot of metrics which might help you to correctly administer your BI publisher.

I also participate at an interactive and open questions and answers session about Oracle Enterprise Manager 12c. The session was really interactive, at the beginning Oracle managers asked classical questions about what kind of targets we used to administer with EM12c, if we used other products then EM12c, then the problematic of the licenses has been discussed, in particular the very complicated usage of the different pack licensing methods for the different targets administered by EM12c. It was a pleasure to share this session with different customers and Oracle managers.