Feed aggregator


Jonathan Lewis - Fri, 2017-07-07 07:14

The title is a well-known shorthand for parameter optimizer_features_enable and it has been the topic of a recent blog post by Mike Dietrich in which he decries the practice of switching the parameter back to an older version on an upgrade (even though, as he points out, Oracle support has been known to recommend it and the manuals describe – though not with 100% accuracy – why you might do so).

I am one of the people who will suggest that on the upgrade a client should consider setting the optimizer_features_enable to the version just left behind as a strategy for getting to a newer version of the base code while minimising the threat of plan instability, so I’m going to play devil’s advocate in this case even though, as we shall see, I am nearly 100% in favour of Mike’s complaint.

The first point, of course, is full disclosure to the client.  Eventually they will have to set the parameter to the current database version, all they’re doing to trying to spread out the workload of addressing a perceived threat. Moreover, they are only minimising the threat, not eliminating it. Setting the parameter has the effect of changing the state of a long list of parameters and “fix controls” – but there’s no guarantee that it will reverse out all the code changes between the two versions. One hopes it won’t reverse out a bug-fix (though Mike quotes a MoS note where exactly that problem appears); more significantly it might not reverse out a clever code optimisation that (in a few unlucky cases) happens to make the SQL run more slowly even when a new transformation is not involved. What you’re hoping for when you set this parameter is that the number of places in your application where you get an unlucky change in performance is much smaller than it would be if you didn’t set the parameter.

The second point is that you really want to have the minimum impact possible while doing expending as little human effort as possible. To this end it’s better to think in terms of setting the parameter for specific users (via a logon trigger), or specific sessions (e.g. batch runs), or specific statements (through a hint or SQL Patch). It may take a couple of test runs to spot the critical classes of statements that point you at the right granularity of implementation, but the more of your SQL that runs at the newer optimizer level the better.

If you’re going to aim for minimum impact, though, and if you’ve got the time to do some broad-brush testing it’s worth going back to my comment that this parameter is a big switch for a number of parameters and fix controls. Perhaps you will be able to spot which new feature, or which fix control is the one thing that needs to be changed – in the short-term – for your system.  Again, statement level is preferable to session level, which is preferable to user level, which is preferable to system level.

The thought of adding a controlling parameter as hint to a statement will probably have some people thinking about creating SQL baselines rather than adding hints to code – and if it’s 3rd party code then an SQL Baseline may be the necessary strategy. Bear in mind that a common advisory for upgrades is “create SQL Baselines for all your SQL first” – it wouldn’t have been me that said it, though!  So here’s something to consider in the light of the whole yes/no argument about optimizer_features_enable, what does a baseline look like ? Here, taken from an 11g database is the critical content of a baseline for “select user from dual”:


It’s a set of hints – including the optimizer_features_enable() hint.Using SQL Baselines to stabilise your code on the upgrade leaves you exposed (in principle) to exactly the problem in the MoS notes that Mike cited as his example of the parameter undoing a bug-fix and producing wrong results. That, by the way, is why I’m not worried by Mike’s example: if the parameter re-introduces a bug then you would have been running with the bug – or probably a workaround to the bug – anyway (Unless, say, you upgraded from to and decided to set the parameter to – but that’s not a strategy compatible with the idea of using the parameter for stability with minimum short-term change.)

The second MoS note that Mike cites is really the one that states – emphasis is mine – a realistic view of the parameter (though I’d view the restriction to Oracle Global Support is a legal cop-out rather than a pure technology requirement):

Modifying the OPTIMIZER_FEATURES_ENABLE parameter generally is strongly discouraged and should only be used as a short term measure at the suggestion of Oracle Global Support.

The follow-up comment is, to my mind, a bit hand-wavy:

By reducing the OPTIMIZER_FEATURES_ENABLE level, new optimizer features are disabled. This has serious potential for negatively affecting performance generally by eliminating the possibility of choosing better plans that are only available with features enabled within the higher revision levels.

Arguing the case against setting the parameter because of the potential for affecting performance negatively – when you’re doing it so that nothing changes – is about as valid as the argument for setting it because of the potential for affecting performance negatively in a tiny percentage of plans that use new features when it’s a very bad idea.

Bottom line: whether or not you set the parameter you’re likely to hit a few edge cases where performance suffers; the less time you have for proper testing in advance the more likely you are to feel the need to set the parameter – but if you start heading in that direction think about using the time you do have available to minimise the scope, or even getting down to the detail of which ACTUAL feature is the problem feature that needs to be disabled for your system.


If you want to check which parameters and fix controls change as you set the optimizer_features_enable you could mess around with the dynamic performance views. Alternatively you could take advantage of the optimizer trace – it’s one of the easy things that the 10053 offers.  Enable the trace, optimize a simple statement, then check the trace file for the bit about optimizer parameters – the section you need from 12c trace will be as follows:


... Some 1,700 lines

Column Usage Monitoring is ON: tracking level = 1

In my case I connected to SQL*Plus, enabled the trace and executed “select 1 from dual”; then I reconnected, set the trace again, set the optimizer_features_enable back to (I was on at the time) and executed “select 2 from dual”. Then I deleted everything but the relevant section from the two trace files. One of the joys of Unix is that you can then run commands like the following:

sdiff  -s  or32_ora_2098.trc  or32_ora_2110.trc  |  expand  >ofe_diff.txt

That’s “side by side comparison, showing only the differences, expand tab marks out to spaces”. Here’s the result (with one blank line inserted between the parameters and the fix controls):

                                                              > optimizer_features_enable           =
                                                              > _fix_control_key                    = -1750344682
optimizer_features_enable           =                <
_optimizer_undo_cost_change         =                | _optimizer_undo_cost_change         =
_fix_control_key                    = 0                       <
_optimizer_cube_join_enabled        = true                    | _optimizer_cube_join_enabled        = false
_optimizer_hybrid_fpwj_enabled      = true                    | _optimizer_hybrid_fpwj_enabled      = false
_px_replication_enabled             = true                    | _px_replication_enabled             = false
_optimizer_partial_join_eval        = true                    | _optimizer_partial_join_eval        = false
_px_concurrent                      = true                    | _px_concurrent                      = false
_px_object_sampling_enabled         = true                    | _px_object_sampling_enabled         = false
_optimizer_unnest_scalar_sq         = true                    | _optimizer_unnest_scalar_sq         = false
_px_filter_parallelized             = true                    | _px_filter_parallelized             = false
_px_filter_skew_handling            = true                    | _px_filter_skew_handling            = false
_optimizer_multi_table_outerjoin    = true                    | _optimizer_multi_table_outerjoin    = false
_px_groupby_pushdown                = force                   | _px_groupby_pushdown                = choose
_optimizer_ansi_join_lateral_enhance = true                   | _optimizer_ansi_join_lateral_enhance = false
_px_parallelize_expression          = true                    | _px_parallelize_expression          = false
_optimizer_ansi_rearchitecture      = true                    | _optimizer_ansi_rearchitecture      = false
_optimizer_gather_stats_on_load     = true                    | _optimizer_gather_stats_on_load     = false
_px_adaptive_dist_method            = choose                  | _px_adaptive_dist_method            = off
_optimizer_batch_table_access_by_rowid = true                 | _optimizer_batch_table_access_by_rowid = false
_px_wif_dfo_declumping              = choose                  | _px_wif_dfo_declumping              = off
_px_wif_extend_distribution_keys    = true                    | _px_wif_extend_distribution_keys    = false
_px_join_skew_handling              = true                    | _px_join_skew_handling              = false
_px_partial_rollup_pushdown         = adaptive                | _px_partial_rollup_pushdown         = off
_px_single_server_enabled           = true                    | _px_single_server_enabled           = false
_optimizer_dsdir_usage_control      = 126                     | _optimizer_dsdir_usage_control      = 0
_px_cpu_autodop_enabled             = true                    | _px_cpu_autodop_enabled             = false
_optimizer_use_gtt_session_stats    = true                    | _optimizer_use_gtt_session_stats    = false
_optimizer_adaptive_plans           = true                    | _optimizer_adaptive_plans           = false
_optimizer_proc_rate_level          = basic                   | _optimizer_proc_rate_level          = off
_adaptive_window_consolidator_enabled = true                  | _adaptive_window_consolidator_enabled = false
_optimizer_strans_adaptive_pruning  = true                    | _optimizer_strans_adaptive_pruning  = false
_optimizer_null_accepting_semijoin  = true                    | _optimizer_null_accepting_semijoin  = false
_optimizer_cluster_by_rowid         = true                    | _optimizer_cluster_by_rowid         = false
_optimizer_cluster_by_rowid_control = 129                     | _optimizer_cluster_by_rowid_control = 3
_distinct_agg_optimization_gsets    = choose                  | _distinct_agg_optimization_gsets    = off
_gby_vector_aggregation_enabled     = true                    | _gby_vector_aggregation_enabled     = false
_optimizer_vector_transformation    = true                    | _optimizer_vector_transformation    = false
_optimizer_aggr_groupby_elim        = true                    | _optimizer_aggr_groupby_elim        = false
_optimizer_reduce_groupby_key       = true                    | _optimizer_reduce_groupby_key       = false
_optimizer_cluster_by_rowid_batched = true                    | _optimizer_cluster_by_rowid_batched = false
_optimizer_inmemory_table_expansion = true                    | _optimizer_inmemory_table_expansion = false
_optimizer_inmemory_gen_pushable_preds = true                 | _optimizer_inmemory_gen_pushable_preds = false
_optimizer_inmemory_autodop         = true                    | _optimizer_inmemory_autodop         = false
_optimizer_inmemory_access_path     = true                    | _optimizer_inmemory_access_path     = false
_optimizer_inmemory_bloom_filter    = true                    | _optimizer_inmemory_bloom_filter    = false
_optimizer_nlj_hj_adaptive_join     = true                    | _optimizer_nlj_hj_adaptive_join     = false
_px_external_table_default_stats    = true                    | _px_external_table_default_stats    = false
_optimizer_inmemory_minmax_pruning  = true                    | _optimizer_inmemory_minmax_pruning  = false
_optimizer_inmemory_cluster_aware_dop = true                  | _optimizer_inmemory_cluster_aware_dop = false

    fix  9898249 = enabled                                    |     fix  9898249 = disabled
    fix 10004943 = enabled                                    |     fix 10004943 = disabled
    fix  9554026 = enabled                                    |     fix  9554026 = disabled
    fix  9593547 = enabled                                    |     fix  9593547 = disabled
    fix  9833381 = enabled                                    |     fix  9833381 = disabled
    fix 10106423 = enabled                                    |     fix 10106423 = disabled
    fix 10175079 = enabled                                    |     fix 10175079 = disabled
    fix 10236566 = enabled                                    |     fix 10236566 = disabled
    fix  9721228 = enabled                                    |     fix  9721228 = disabled
    fix  9929609 = enabled                                    |     fix  9929609 = disabled
    fix 10182672 = enabled                                    |     fix 10182672 = disabled
    fix  9832338 = enabled                                    |     fix  9832338 = disabled
    fix 11668189 = enabled                                    |     fix 11668189 = disabled
    fix 11940126 = enabled                                    |     fix 11940126 = disabled
    fix 12390139 = enabled                                    |     fix 12390139 = disabled
    fix 11744016 = enabled                                    |     fix 11744016 = disabled
    fix 10216738 = enabled                                    |     fix 10216738 = disabled
    fix 12563419 = enabled                                    |     fix 12563419 = disabled
    fix 12535474 = enabled                                    |     fix 12535474 = disabled
    fix 12561635 = enabled                                    |     fix 12561635 = disabled
    fix 12569245 = enabled                                    |     fix 12569245 = disabled
    fix 12569300 = enabled                                    |     fix 12569300 = disabled
    fix 12569316 = enabled                                    |     fix 12569316 = disabled
    fix 12569321 = enabled                                    |     fix 12569321 = disabled
    fix  9002958 = enabled                                    |     fix  9002958 = disabled
    fix 12810427 = enabled                                    |     fix 12810427 = disabled
    fix 12914055 = enabled                                    |     fix 12914055 = disabled
    fix 12978495 = enabled                                    |     fix 12978495 = disabled
    fix 13110511 = enabled                                    |     fix 13110511 = disabled
    fix 13345888 = enabled                                    |     fix 13345888 = disabled
    fix 13396096 = enabled                                    |     fix 13396096 = disabled
    fix 12999577 = enabled                                    |     fix 12999577 = disabled
    fix 12954320 = enabled                                    |     fix 12954320 = disabled
    fix 13036910 = enabled                                    |     fix 13036910 = disabled
    fix 12648629 = enabled                                    |     fix 12648629 = disabled
    fix 13704977 = enabled                                    |     fix 13704977 = disabled
    fix 11843466 = enabled                                    |     fix 11843466 = disabled
    fix 13909909 = enabled                                    |     fix 13909909 = disabled
    fix 12856200 = enabled                                    |     fix 12856200 = disabled
    fix  9852856 = enabled                                    |     fix  9852856 = disabled
    fix 14033181 = enabled                                    |     fix 14033181 = disabled
    fix 13836796 = enabled                                    |     fix 13836796 = disabled
    fix 13699643 = enabled                                    |     fix 13699643 = disabled
    fix 13735304 = enabled                                    |     fix 13735304 = disabled
    fix 14464068 = enabled                                    |     fix 14464068 = disabled
    fix 13448445 = enabled                                    |     fix 13448445 = disabled
    fix  9114915 = enabled                                    |     fix  9114915 = disabled
    fix 13109345 = enabled                                    |     fix 13109345 = disabled
    fix 14605040 = enabled                                    |     fix 14605040 = disabled
    fix 14633570 = enabled                                    |     fix 14633570 = disabled
    fix 13573073 = enabled                                    |     fix 13573073 = disabled
    fix 16237969 = enabled                                    |     fix 16237969 = disabled
    fix 13994546 = enabled                                    |     fix 13994546 = disabled
    fix 14750443 = enabled                                    |     fix 14750443 = disabled
    fix 14552075 = enabled                                    |     fix 14552075 = disabled
    fix 16324844 = enabled                                    |     fix 16324844 = disabled
    fix 13583529 = enabled                                    |     fix 13583529 = disabled
    fix 14565911 = enabled                                    |     fix 14565911 = disabled
    fix 16368002 = enabled                                    |     fix 16368002 = disabled
    fix 16077770 = enabled                                    |     fix 16077770 = disabled
    fix 11814337 = enabled                                    |     fix 11814337 = disabled
    fix 14764840 = enabled                                    |     fix 14764840 = disabled
    fix 16555865 = enabled                                    |     fix 16555865 = disabled
    fix 16625151 = enabled                                    |     fix 16625151 = disabled
    fix 16609749 = enabled                                    |     fix 16609749 = disabled
    fix 16751246 = enabled                                    |     fix 16751246 = disabled
    fix 16749025 = enabled                                    |     fix 16749025 = disabled
    fix 16750067 = enabled                                    |     fix 16750067 = disabled
    fix 15899648 = enabled                                    |     fix 15899648 = disabled
    fix 16690013 = enabled                                    |     fix 16690013 = disabled
    fix 16544878 = enabled                                    |     fix 16544878 = disabled
    fix 16725982 = enabled                                    |     fix 16725982 = disabled
    fix 14648222 = enabled                                    |     fix 14648222 = disabled
    fix 16507317 = enabled                                    |     fix 16507317 = disabled
    fix 16837274 = enabled                                    |     fix 16837274 = disabled
    fix 14085520 = enabled                                    |     fix 14085520 = disabled
    fix 16713081 = enabled                                    |     fix 16713081 = disabled
    fix 14703295 = enabled                                    |     fix 14703295 = disabled
    fix 16908409 = enabled                                    |     fix 16908409 = disabled
    fix 16212250 = enabled                                    |     fix 16212250 = disabled
    fix 17087729 = enabled                                    |     fix 17087729 = disabled
    fix 17088819 = enabled                                    |     fix 17088819 = disabled
    fix 13848786 = enabled                                    |     fix 13848786 = disabled
    fix 13522189 = enabled                                    |     fix 13522189 = disabled
    fix 16796185 = enabled                                    |     fix 16796185 = disabled
    fix 15950252 = enabled                                    |     fix 15950252 = disabled
    fix 16976121 = enabled                                    |     fix 16976121 = disabled
    fix 16582322 = enabled                                    |     fix 16582322 = disabled
    fix 16712213 = enabled                                    |     fix 16712213 = disabled
    fix 17382690 = enabled                                    |     fix 17382690 = disabled
    fix 14846352 = enabled                                    |     fix 14846352 = disabled
    fix 16516751 = enabled                                    |     fix 16516751 = disabled
    fix  8611462 = enabled                                    |     fix  8611462 = disabled
    fix 14062749 = enabled                                    |     fix 14062749 = disabled
    fix 16346018 = enabled                                    |     fix 16346018 = disabled
    fix 12977599 = enabled                                    |     fix 12977599 = disabled
    fix 14191778 = enabled                                    |     fix 14191778 = disabled
    fix 15939321 = enabled                                    |     fix 15939321 = disabled
    fix 17543180 = enabled                                    |     fix 17543180 = disabled
    fix 17301564 = enabled                                    |     fix 17301564 = disabled
    fix 12373708 = enabled                                    |     fix 12373708 = disabled
    fix 17397506 = enabled                                    |     fix 17397506 = disabled
    fix 14558315 = enabled                                    |     fix 14558315 = disabled
    fix 16615686 = enabled                                    |     fix 16615686 = disabled
    fix 16622801 = enabled                                    |     fix 16622801 = disabled
    fix 16954950 = enabled                                    |     fix 16954950 = disabled
    fix 17728161 = enabled                                    |     fix 17728161 = disabled
    fix 17760375 = enabled                                    |     fix 17760375 = disabled
    fix 17640863 = enabled                                    |     fix 17640863 = disabled
    fix 17716301 = enabled                                    |     fix 17716301 = disabled
    fix 17597748 = enabled                                    |     fix 17597748 = disabled
    fix 17303359 = enabled                                    |     fix 17303359 = disabled
    fix 16673868 = enabled                                    |     fix 16673868 = disabled
    fix 17800514 = enabled                                    |     fix 17800514 = disabled
    fix 14826303 = enabled                                    |     fix 14826303 = disabled
    fix 17663076 = enabled                                    |     fix 17663076 = disabled
    fix 17760755 = enabled                                    |     fix 17760755 = disabled
    fix 17997159 = enabled                                    |     fix 17997159 = disabled
    fix 14733442 = enabled                                    |     fix 14733442 = disabled
    fix 17781659 = enabled                                    |     fix 17781659 = disabled
    fix 17526569 = enabled                                    |     fix 17526569 = disabled
    fix 17760686 = enabled                                    |     fix 17760686 = disabled
    fix 17696414 = enabled                                    |     fix 17696414 = disabled
    fix 18116777 = enabled                                    |     fix 18116777 = disabled
    fix 16052625 = enabled                                    |     fix 16052625 = disabled
    fix 18091750 = enabled                                    |     fix 18091750 = disabled
    fix 17572606 = enabled                                    |     fix 17572606 = disabled
    fix 18196576 = enabled                                    |     fix 18196576 = disabled
    fix 17736165 = enabled                                    |     fix 17736165 = disabled
    fix 16434021 = enabled                                    |     fix 16434021 = disabled
    fix 18035463 = enabled                                    |     fix 18035463 = disabled
    fix 18011820 = enabled                                    |     fix 18011820 = disabled
    fix 16405740 = enabled                                    |     fix 16405740 = disabled
    fix 18365267 = enabled                                    |     fix 18365267 = disabled
    fix 17863980 = enabled                                    |     fix 17863980 = disabled
    fix 18398980 = enabled                                    |     fix 18398980 = disabled
    fix 18304693 = enabled                                    |     fix 18304693 = disabled
    fix 18508675 = enabled                                    |     fix 18508675 = disabled
    fix 18456944 = enabled                                    |     fix 18456944 = disabled
    fix 17908541 = enabled                                    |     fix 17908541 = disabled
    fix 18467455 = enabled                                    |     fix 18467455 = disabled
    fix 16033838 = enabled                                    |     fix 16033838 = disabled
    fix 16809786 = enabled                                    |     fix 16809786 = disabled
    fix 18425876 = enabled                                    |     fix 18425876 = disabled
    fix 18461984 = enabled                                    |     fix 18461984 = disabled
    fix 17023040 = enabled                                    |     fix 17023040 = disabled
    fix 14776289 = enabled                                    |     fix 14776289 = disabled

That’s 50 parameter differences, and 147 fix controls. Quite a lot of fixes between the two versions.

If you’re coming to the upgrade a couple of years late then you might want to consider using the new version number and list of parameters you generate as the criteria as a search for bugs in MoS. You might even find that simply running your eye down the list of parameters gives you a clue about a type of execution plan that you’ve never seen in the older version.


The importance of being CURRENT_USER

Flavio Casetta - Fri, 2017-07-07 06:00
Categories: DBA Blogs

Procedures, roles and grants

Tom Kyte - Fri, 2017-07-07 05:06
Hi Tom, We are using 2 schemas, user1, user2. User1 has user1_table, and created role role1. The following was issued: grant select, insert, update, delete on user1_table to role1; grant role1 to user2 User2 created private synonym user1_table...
Categories: DBA Blogs

Huge Blob Data over DB link

Tom Kyte - Fri, 2017-07-07 05:06
Hi, We have an implementation where datasize has grown over 24TB's and most of it(13 TB's including the indexes) is from couple of BLOB tables which have partitions(around 40 for biggest table) and subpartitions(44 in each partition). Now the task...
Categories: DBA Blogs

Virtual column issue : ORA-31495: error in synchronous change table on Schema.Tablename

Tom Kyte - Fri, 2017-07-07 05:06
Hi Team, I am facing tricky issue that never engage before in my database. WE have some tables which caring few virtual columns. These virtual columns are generated using user define function which is just doing strings to upper. It was working fi...
Categories: DBA Blogs

Bind mismatch(21):

Tom Kyte - Fri, 2017-07-07 05:06
Hi, I have a Database with cursor mismatch for a sql_id , but I cant find information about this Bind mismatch "mismatch(21)" neither for new_oacexl. This is a RAC three node, the result of tannel script nonshared2.sql for this sql_id is SQL...
Categories: DBA Blogs

build a hierarchy caculation with ONE sql

Tom Kyte - Fri, 2017-07-07 05:06
Hi Team, We want to build a hierachy process bar with one SQL but failed. We decide to turn to you guys for help. Plz allow me to put it in this way with a simple test. >>> Here's my test data: <code>create table DEMP ( ename VARCHAR2(1...
Categories: DBA Blogs

View for finding Bind variable values

Tom Kyte - Fri, 2017-07-07 05:06
Hi, We are using the database enterprise edition , and due to the application performance need we had to set CURSOR_SHARING=FORCE, as application was not able to use bind variables. When i try to generate the PLAN for sql_id using OR w...
Categories: DBA Blogs

Oracle Cloud Machine ; Your Own Cloud Under Your Own Control

Pakistan's First Oracle Blog - Fri, 2017-07-07 04:12
Yes, every company wants to be on cloud but not everyone wants that cloud to be out there in wild no matter how secure it is. Some want their cloud to be trapped within their own premise, under their own control.

Enters Oracle Cloud Machine.

Some of the key reasons why this would make sense are sovereignty, residency, compliance, and other business requirements. Moreover, the cloud benefits would be there like turn key solutions, same IaaS and PaaS environments for development, test and production.

Cost might be a factor here for some organizations so a hybrid solution might be a go for majority of corporations.Having a private cloud machine and also having a public cloud systems would be the solution for many. One advantage here would be that the integration  of this private cloud with public one would be streamed lined.
Categories: DBA Blogs

Design Guidelines

Anthony Shorten - Thu, 2017-07-06 23:24

The Oracle Utilities Application Framework is both flexible and powerful in terms of the extensibility of the products that use the product. As the famous saying goes though, "With Great Power comes Great Responsibility". Flexibility does not mean that you have carte blanche in terms of design when it comes to using the facilities of the product. Each object in the product has been specifically designed for a specific purpose and trying to use the extension facilities with those object must also respect those purposes.

Let me give some advice that may help guide your design work when building extensions:

  • Look at the base - The most important piece of advice I give partners and customers is look at the base product facilities first. I am amazed how many times I see an enhancement that has been implemented by a partner only to find that the base product already did that. This is particularly important when upgrading to a newer version. We spend a lot of time adding new features and updating existing ones (and sometimes replacing older features with newer features) so what you have as enhancements in previous now are part of the base product. It is a good idea to revert back to the base to reduce your maintenance costs.
  • Respect the objects - We have three types of objects in the product: Configuration, Master and Transaction.
    • The configuration objects are designed to hold meta data and configuration that influence the behavior of the product. They are cached in a L2 Cache that is designed for performance and are generally static data that is used as reference and guidance for the other objects. They tend to be low volume and are the domain of your Administrators or Power Users (rather than end users). A simple rule here is that they tend to exist on the Admin menu of the product.
    • The master objects are medium volume, with low growth, and define the key identifier or root data used by the product. For example, Accounts, Meters, Assets, Crews, etc.
    • The transaction objects are high volume and high growth and are added by processes in the product or interfaces and directly reference master objects. For example, bills, payments, meter reads, work activities, tasks etc.. These objects tend to also support Information Lifecycle Management.
    • Now you need to respect each of them. For example, do not load transaction data into a configuration object is a good example. Each its own place and each resource profile and behaviors.
  • Avoid overuse of the CLOB field - The CLOB field was introduced across most objects in the product and is a great way of extending the product. Just understand that while they are powerful they are not unlimited. They are limited in size for performance reasons and they are not a replacement for other facilities like characteristics and even building custom tables. They are XML remember and have limited maintenance and search capabilities over other methods.
  • Avoid long term issues - This one is hard to explain so let me try. When you design something, think about the other issues that may arise due to your design. For example, lots of implementers forget about volume increases over time and run into issues such as storage long term. Remember data in certain objects has different lifecycles and needs to be managed accordingly. Factor that into your design. Too many times I see extensions that forget this rule and then customer calls support for advice only to hear they need to redesign it to cater for the issue.

I have been in the industry over 30 years and made a lot of those mistakes myself early in my career so it is not impossible. Just learn and make sure you do not repeat your mistakes over time. One more piece of advice, talk about your designs with a few people (of various ages as well) to see if it makes sense. Do not take this as a criticism as a lot of great designers bounce ideas off others to see if they make sense. Doing that as part of any design process helps make the design more robust. Otherwise it just looks rushed and from the other side looks like lazy design. As designers I have seen great designs and bad designs, but it is possible to transform a requirement into a great design with some forethought.

performance tuning - library cache pin and library cache lock

Tom Kyte - Thu, 2017-07-06 10:46
Hi , i want to know about wait events of library cache pin and library cache lock in my environment i have faced this issue but i am not able to get session whos is waiting for library cache pin and session which is acessing that object . ...
Categories: DBA Blogs

BULK COLLECT on DB types Vs PL/SQL types

Tom Kyte - Thu, 2017-07-06 10:46
Hi, I have writtem below PL/SQL block to populate ROWID of table into intermidiate table tb_load_stats. Below block works fine when TYPE typ_rowid/t_typ_rowid is directly used in PL/SQL block. But when these types are created as a oracle obj...
Categories: DBA Blogs

Oracle SQL statement is taking long time

Tom Kyte - Thu, 2017-07-06 10:46
<code>Hi , My SQL statement is taking long time approximately 2.30 hr to complete. I have query like below Select from tables UNION Select from tables Here is the Gather Stat Plan. SQL> SELECT * 2 FROM TABLE(DBMS_XPLAN.DISPLAY...
Categories: DBA Blogs

Performance Issue due to RMAN Jobs

Tom Kyte - Thu, 2017-07-06 10:46
Hello, We are facing performance issues on our database. Our application is designed in such a way that it is rigorously used only for <b>5-days</b> in a month and while it is used there are around 300 sessions running in parallel at any point...
Categories: DBA Blogs

Find oracle database owner

Tom Kyte - Thu, 2017-07-06 10:46
Hi Ora experts, I have some quick queries regarding oracle database. Is there any way to find an oracle database owner information (in Windows platform, where I don't have 'oratab' file)? Also, we also need to find the 'last access time' of an or...
Categories: DBA Blogs


Tom Kyte - Thu, 2017-07-06 10:46
Dear Tom, Does V$SQL_PLAN_STATISTICS shows only the statics about the current (connected) session? SQL> select count(*) from v$sql_plan ; --I can get the plan details here COUNT(*) ---------- 18540 SQL> select count(*) from V$SQ...
Categories: DBA Blogs

unindexed foreign keys

Tom Kyte - Thu, 2017-07-06 10:46
Do you have a script that lists all the foreign keys with no associated indexes?
Categories: DBA Blogs

Unify - bringing together the best of both worlds

Rittman Mead Consulting - Thu, 2017-07-06 09:00

Since I started teaching OBIEE in 2011, I had the pleasure of meeting many fascinating people who work with Business Intelligence.

In talking to my students, I would generally notice three different situations:

  1. Folks were heavy users of OBIEE, and just ready to take their skills to the next level.

  2. They were happily transitioning to OBIEE from a legacy reporting tool, that didn’t have the power that they needed.

  3. There were not-so-good times, like when people were being forced to transition to OBIEE. They felt that they were moving away from their comfort zone and diving into a world of complicated mappings that would first require them to become rocket scientists. They were resistant to change.

It was this more challenging crowd, that mostly sparked my interest for other analytics tools. I received questions like: “Why are we switching to another system? What are the benefits?”


I wanted to have a good answer to these questions. Over the years, different projects have allowed me the opportunity to work with diverse reporting tools. My students’ questions were always in mind: Why? And what are the benefits? So, I always took the time to compare/contrast the differences between OBIEE and these other tools.

I noticed that many of them did a fantastic job at answering the questions needed, and so did OBIEE. It didn’t take me long to have the answer that I needed: the main difference in OBIEE is the RPD!


The RPD is where so much Business Intelligence happens. There, developers spend mind boggling times connecting the data, deriving complex metrics and hierarchies, joining hundreds of tables, and making everything a beautiful drag and drop dream for report writers.

Yes, many other tools will allow us to do magic with metadata, but most of them require this magic to be redefined every time we need a new report, or the report has a different criteria. Yes, the RPD requires a lot of work upfront, but that work is good for years to come. We never lose any of our previous work, we just enhance our model. Overtime, the RPD becomes a giant pool of knowledge for a company and is impressively saved as a file.


For tapping into the RPD metadata, traditionally we have used BI Publisher and OBIEE. They are both very powerful and generally complement each other well. Other tools have become very popular in the past few years. Tableau is an example that quickly won the appreciation of the BI community and has kept consistent leadership in Gartner’s BI Magic quadrant since 2013. With a very slick interface and super fast reporting capability, Tableau introduced less complex methods to create amazing dashboards - and fast! So, what is there not to like? There is really so much TO like!

Going back to the comparing and contrasting, the main thing that Tableau doesn’t offer is… the RPD. It lacks a repository with the ability to save the join definitions, calculations and the overall intelligence that can be used for all future reports.

At Rittman Mead, we’ve been using these tools and appreciate their substantial capabilities, but we really missed the RPD as a data source. We wanted to come up with a solution that would allow our clients to take advantage of the many hours they had likely already put into metadata modeling by creating a seamless transition from OBIEE’s metadata layer to Tableau.


This past week, I was asked to test our new product, called Unify. Wow. Once again, I am so proud of my fellow coworkers. Unify has a simple interface and uses a Tableau web connector to create a direct line to your OBIEE repository for use in Tableau reports, stories and dashboards.


In Unify, we select the subject areas from our RPD presentation layer and choose our tables and columns as needed. Below is a screenshot of Unify using the OBIEE 12c Sample App environment. If you are not familiar with OBIEE 12c, Oracle provides the Sample App - a standalone virtual image with everything that you need to test the product. You can download the SampleApp here: http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html


We are immediately able to leverage all joins, calculated columns, hierarchies, RPD variables, session variables and that’s not all… our RPD security too! Yes, even row level security is respected when we press the “Unify” button and data is brought back into Tableau. So now, there is no reason to lose years of metadata work because one team prefers to visualize with Tableau instead of OBIEE.

Unify allows us to import only those data needed for the report, as we can utilize ‘in-tool’ filtering, keeping our query sets small, and our performance high.

In sum, Unify unites it all - have your cake and eat it too. No matter which tool you love the most, add them together and you will certainly love them both more.


Categories: BI & Warehousing

A few useful Oracle 12cR2 MOS Docs

Syed Jaffar - Thu, 2017-07-06 07:33
A few useful MOS Docs are listed below , in case if 12cR2 upgrade around the corner.

  • How to Upgrade to/Downgrade from Grid Infrastructure 12.2 and Known Issues (Doc ID 2240959.1)
  • Complete Checklist for Upgrading to Oracle Database 12c Release 2 (12.2) using DBUA (Doc ID 2189854.1)
  • 12.2 Grid Infrastructure Installation: What's New (Doc ID 2024946.1)
  • Patches to apply before upgrading Oracle GI and DB to (Doc ID 2180188.1)
  • Differences Between Enterprise, Standard Edition 2 on Oracle 12.2 (Doc ID 2243031.1)
  • 12.2 gridSetup.sh Does Not List Disks Unless the Discovery String is Provided (Doc ID 2244960.1)

Create a 12c physical standby database on ODA X5-2

Amis Blog - Thu, 2017-07-06 07:06

ODA X5-2 simplifies and speeds up the creation of a 12c database quite considerably with oakcli. You can take advantage of this command by also using it in the creation of physical standby databases as I discovered when I had to setup Dataguard on as many as 5 production and 5 acceptance databases within a very short time.

I used the “oakcli create database …” command to create both primary and standby databases really fast and went on from there to setup a Dataguard Bbroker configuration in max availability mode. Where you would normally duplicate a primary database on to a skeleton standby database that’s itself without any data or redo files and starts up with a pfile, working with 2 fully configured databases is a bit different. You do not have to change a db_unique_name after the RMAN duplicate, which proved to be quite an advantage, and the duplicate itself doesn’t have to address any spfile adaptations because it’s already there. But you may get stuck with some obsolete data and redo files of the original standby database that can fill up the filesystem. However, as long as you remove these files in time, just before the RMAN duplicate, this isn’t much of an issue.

What I did to create 12c primary database ABCPRD1 on one ODA and physical standby database ABCPRD2 on a second ODA follows from here. Nodes on oda1 are oda10 and oda11, nodes on oda2 are oda20 and oda21. The nodes I will use are oda10 and oda20.

-1- Create parameterfile on oda10 and oda20
oakcli create db_config_params -conf abcconf
-- parameters:
-- Database Block Size  : 8192
-- Database Language    : AMERICAN
-- Database Characterset: WE8MSWIN1252
-- Database Territory   : AMERICA
-- Component Language   : English
-- NLS Characterset     : AL16UTF16
file is saved as: /opt/oracle/oak/install/dbconf/abcconf.dbconf

-2- Create database ABCPRD1 on oda10 and ABCPRD2 on oda20
oda10 > oakcli create database -db ABCPRD1 -oh OraDb12102_home1 -params abcconf
oda20 > oakcli create database -db ABCPRD2 -oh OraDb12102_home1 -params abcconf
-- Root  password: ***
-- Oracle  password: ***
-- SYSASM  password - During deployment the SYSASM password is set to 'welcome1 - : ***
-- Database type: OLTP
-- Database Deployment: EE - Enterprise Edition
-- Please select one of the following for Node Number >> 1
-- Keep the data files on FLASH storage: N
-- Database Class: odb-02  (2 cores,16 GB memory)

-3- Setup db_name ABCPRD for both databases... this is a prerequisite for Dataguard
oda10 > sqlplus / as sysdba
oda10 > shutdown immediate;
oda10 > startup mount
oda10 > Change database name of database ABCPRD1 to ABCPRD? (Y/[N]) => Y
oda10 > exit

oda20 > sqlplus / as sysdba
oda20 > shutdown immediate;
oda20 > startup mount
oda20 > Change database name of database ABCPRD2 to ABCPRD? (Y/[N]) => Y
oda20 > exit

-4- Set db_name of both databases in their respective spfile as well as ODA cluster,
    and reset the db_unique_name after startup back from ABCPRD to ABCPRD1|ABCPRD2
oda10 > sqlplus / as sysdba    
oda10 > startup mount
oda10 > alter system set db_name=ABCPRD scope=spfile;
oda10 > alter system set service_names=ABCPRD1 scope=spfile;
oda10 > ! srvctl modify database -d ABCPRD1 -n ABCPRD
oda10 > shutdown immediate
oda10 > startup
oda10 > alter system set db_unique_name=ABCPRD1 scope=spfile;
oda10 > shutdown immediate;
oda10 > exit

oda20 > sqlplus / as sysdba    
oda20 > startup mount
oda20 > alter system set db_name=ABCPRD scope=spfile;
oda20 > alter system set service_names=ABCPRD2 scope=spfile;
oda20 > ! srvctl modify database -d ABCPRD2 -n ABCPRD
oda20 > shutdown immediate
oda20 > startup
oda20 > alter system set db_unique_name=ABCPRD2 scope=spfile;
oda20 > shutdown immediate;
oda20 > exit

-5- Startup both databases from the cluster.
oda10 > srvctl start database -d ABCPRD1
oda20 > srvctl start database -d ABCPRD2

Currently, 2 identical configured databases are active with the same db_name, which is a first condition for the following configuration of Dataguard Broker. By just matching the db_name between databases and keeping the db_unique_name as it was, ASM database and diagnostic directory names remain as they are.

Also, the spfile entry in the cluster continues to point to the correct directory and file, as well as the init.ora in $ORACLE_HOME/dbs. Because the standby started with an existing and correctly configured spfile you no longer need to retrieve it from the primary. It simplifies and reduces the RMAN duplicate code to just a one line command, apart from login and channel allocation.

-6- Add Net Service Names for ABCPRD1 and ABCPRD2 to your tnsnames.ora on oda10 and oda20
      (ADDRESS = (PROTOCOL = TCP)(HOST = oda10)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = oda20)(PORT = 1521))

-7- Add as a static service to listener.ora on oda10 and oda20
oda10 >   (SID_LIST =
oda10 >     (SID_DESC =
oda10 >       (GLOBAL_DBNAME = ABCPRD1_DGB)
oda10 >       (ORACLE_HOME = /u01/app/oracle/product/
oda10 >       (SID_NAME = ABCPRD1)
oda10 >     ) 
oda10 >   )        

oda20 >   (SID_LIST =
oda20 >     (SID_DESC =
oda20 >       (GLOBAL_DBNAME = ABCPRD2_DGB)
oda20 >       (ORACLE_HOME = /u01/app/oracle/product/
oda20 >       (SID_NAME = ABCPRD2)
oda20 >     ) 
oda20 >   )

-8- Restart listener from cluster on oda10 and oda20
oda10 > srvctl stop listener
oda10 > srvctl start listener

oda20 > srvctl stop listener
oda20 > srvctl start listener

-9- Create 4 standby logfiles on oda10 only (1 more than nr. of redologgroups and each with just 1 member)
    The RMAN duplicate takes care of the standby logfiles on oda20, so don't create them there now
oda10 > alter database add standby logfile thread 1 group 4 size 4096M;
oda10 > alter database add standby logfile thread 1 group 5 size 4096M;
oda10 > alter database add standby logfile thread 1 group 6 size 4096M;
oda10 > alter database add standby logfile thread 1 group 7 size 4096M;
oda10 > exit

-10- Start RMAN duplicate from oda20
oda20 > srvctl stop database -d ABCPRD2
oda20 > srvctl start database -d ABCPRD2 -o nomount
oda20 > *****************************************************************************
oda20 > ********* !!! REMOVE EXISTING DATA EN REDO FILES OF ABCPRD2 NOW !!! *********
oda20 > *****************************************************************************
oda20 > rman target sys/***@ABCPRD1 auxiliary sys/***@ABCPRD2
oda20 > .... RMAN> 
oda20 > run {
oda20 > allocate channel d1 type disk;
oda20 > allocate channel d2 type disk;
oda20 > allocate channel d3 type disk;
oda20 > allocate auxiliary channel stby1 type disk;
oda20 > allocate auxiliary channel stby2 type disk;
oda20 > duplicate target database for standby nofilenamecheck from active database;
oda20 > }
oda20 > exit

And there you are… primary database ABCPRD1 in open read-write mode and standby database ABCPRD2 in mount mode. The only thing left to do now is the dataguard broker setup, and activate flashback and force_logging on both databases.

-11- Setup broker files in shared storage (ASM) and start brokers on oda10 and oda20
oda10 > sqlplus / as sysdba
oda10 > alter system set dg_broker_config_file1='/u02/app/oracle/oradata/datastore/.ACFS/snaps/ABCPRD1/ABCPRD1/dr1ABCPRD1.dat' scope=both; 
oda10 > alter system set dg_broker_config_file2='/u02/app/oracle/oradata/datastore/.ACFS/snaps/ABCPRD1/ABCPRD1/dr2ABCPRD1.dat' scope=both;
oda10 > alter system set dg_broker_start=true scope=both;
oda10 > exit

oda20 > sqlplus / as sysdba
oda20 > alter system set dg_broker_config_file1='/u02/app/oracle/oradata/datastore/.ACFS/snaps/ABCPRD2/ABCPRD1/dr1ABCPRD2.dat' scope=both; 
oda20 > alter system set dg_broker_config_file2='/u02/app/oracle/oradata/datastore/.ACFS/snaps/ABCPRD2/ABCPRD1/dr2ABCPRD2.dat' scope=both;
oda20 > alter system set dg_broker_start=true scope=both;
oda20 > exit

-12- Create broker configuration from oda10
oda10 > dgmgrl sys/***
oda10 > create configuration abcprd as primary database is abcprd1 connect identifier is abcprd1_dgb;
oda10 > edit database abcprd1 set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oda10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ABCPRD1_DGB)(INSTANCE_NAME=ABCPRD1)(SERVER=DEDICATED)))';
oda10 > add database abcprd2 as connect identifier is abcprd2_dgb maintained as physical;
oda10 > edit database abcprd2 set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oda20)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ABCPRD2_DGB)(INSTANCE_NAME=ABCPRD2)(SERVER=DEDICATED)))';
oda10 > enable configuration;
oda10 > edit database abcprd2 set state=APPLY-OFF;
oda10 > exit

-13- Enable flashback and force logging on both primary and standby database
oda10 > sqlplus / as sysdba
oda10 > alter database force logging;
oda10 > alter database flashback on;
oda10 > exit

oda20 > sqlplus / as sysdba
oda20 > alter database force logging;
oda20 > alter database flashback on;
oda20 > exit
oda20 > srvctl stop database -d abcprd2
oda20 > srvctl start database -d abcprd2 -o mount

oda10 > srvctl stop database -d abcprd1
oda10 > srvctl start database -d abcprd1

-14- Configure max availability mode from oda10
oda10 > dgmgrl sys/*** 
oda10 > edit database abcprd2 set state=APPLY-ON;
oda10 > edit database abcprd1 set property redoroutes='(LOCAL : abcprd2 SYNC)';
oda10 > edit database abcprd2 set property redoroutes='(LOCAL : abcprd1 SYNC)';
oda10 > edit configuration set protection mode as maxavailability;
oda10 > show database abcprd1 InconsistentProperties;
oda10 > show database abcprd2 InconsistentProperties;
oda10 > show configuration
oda10 > validate database abcprd2;
oda10 > exit

You should now have a valid 12c Max Availability Dataguard configuration, but you better test it thoroughly with
some switchovers and a failover before taking it into production. Have fun!

The post Create a 12c physical standby database on ODA X5-2 appeared first on AMIS Oracle and Java Blog.


Subscribe to Oracle FAQ aggregator