Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Setting APPINFO in SQL*Plus

Ever used the MODULE column of V$SESSION view? If you haven't, you are missing out on a very important piece of instruemntation code built right into the Oracle database session management. This allows you to assign a completely arbitary name, as you would see will properly describe for your application. Later when you want to identify that session, you can check the MODULE column in V$SESSION. Even though the userid is the same for all these sessions; the module column will help you identify the session.

Let's see how it works by a little example. Here is how you check the module information.

select module
from v$session
where username = 'SYS'


In both cases the MODULE column shows the same value. You can set he module to a more descriptive name by issuing this command:

SQL> exec dbms_application_info.set_module('MyModule','MyAction')

PL/SQL procedure successfully completed.

Now if you check the module:


As you can see the session where executed the packaged procedure has the descriptive module name. If you want to enable tracing, check the sessions stats, debug what's going on, etc., the ability to identify the session uniquely and accurately could be lifesaver.

But we had to execute the package dbms_application_info. The principles of least privileges says that we don't want to grant more privileges than absolutely necessary. While no one will argue against this being convenient, it will probably be hard to justify as "absolutely" necessary. So, what could you do to identify the session via the module? The generic module name "sqlplus.exe" is pretty much useless.

There is a much simpler solution. The SQL*Plus parameter appinfo comes to rescue. By default the setting is off. You can confirm that by issuing the following:

SQL> show appinfo
appinfo is OFF and set to "SQL*Plus"

To set to a value you want, use the follwoing:

SQL> set appinfo MyApp

Now if you check the sessions from another sessions:

SQL> select module
2 from v$session
3 where username = 'SYS';


See how the module is set to MyApp, which allows you to locate the session from many from the same user. And you could do that without calling the package.

What are the practical implications? The best usecase, I think is using this in the automatic login scripts such as glogin.sql. Put the following line in gloin.sql in $ORACLE_HOME/sqlplus/admin directory.

set appinfo "AcmeModule"

Now any session using that Oracle Home will have the Module column set to AcmeModule.

However you can also create local files called login.sql in individual directories and set appinfo apprpriately. For instance, suppose you have three major directories where you generally run the SQL scripts from. Create login.sql files on each directory. Directory "C:\App1" has a login.sql file with the following content:

set appinfo "App1"

Create login.sql files in the other directories with the appropriate contents. Now when someone connects to the database from SQL*Plus from those directories, the module will be set appropriately.
You will be able to know what directory the user has been in calling the scripts.

A caveat on the above, though. In Oracle 12.2, login.sql file is not executed, if a glogin.sql file is present. It is silently ignored. To make sure the local login.sql file is read, you have to explcitly set the variable ORACLE_PATH or SQLPATH to that directory.

Hope you find use of this little known setting in SQL*Plus.

12.2 ACFS compression, part I

One of the new 12.2 features is the ability to transparently compress ACFS filesystems.

Compression is enabled using acfsutil utility:

[root@raca1 ~]# acfsutil -h compress on
Usage: acfsutil [-h] compress on [-a ]
- Set default compression algorithm
Currently only 'lzo' available
- Enable compression on volume

Clearly there is support for more compression algorithms to be added in the future but right now only lzo is supported.

Let's go ahead and enable compression on the ACFS filesystem I have:

[root@raca1 ~]# acfsutil compress on /u02/oradata

Compression status can be checked using acfsutil info fs:

[root@raca1 ~]# acfsutil info fs /u02/oradata
ACFS Version:
on-disk version: 46.0
ACFS compatibility:
flags: MountPoint,Available
mount time: Fri Jul 7 12:53:39 2017
mount sequence number: 0
allocation unit: 4096
metadata block size: 4096
volumes: 1
total size: 8589934592 ( 8.00 GB )
total free: 6935588864 ( 6.46 GB )
file entry table allocation: 393216
primary volume: /dev/asm/data-95
state: Available
major, minor: 250, 48641
logical sector size: 4096
size: 8589934592 ( 8.00 GB )
free: 6935588864 ( 6.46 GB )
metadata read I/O count: 23833
metadata write I/O count: 39103
total metadata bytes read: 257896448 ( 245.95 MB )
total metadata bytes written: 421969920 ( 402.42 MB )
ADVM diskgroup: DATA
ADVM resize increment: 67108864
ADVM redundancy: unprotected
ADVM stripe columns: 8
ADVM stripe width: 1048576
number of snapshots: 0
snapshot space usage: 0 ( 0.00 )
replication status: DISABLED
compression status: ENABLED

As a quick test I've created a new empty database inside ACFS filesystem. We can now check compressed file sizes:

[root@raca1 ora12cr2]# acfsutil compress info /u02/oradata/ora12cr2/sysaux01.dbf
Compression Unit size: 8192
Disk storage used: ( 88.70 MB )
Disk storage saved: ( 461.31 MB )
Storage used is 16% of what the uncompressed file would use.
File is not scheduled for asynchronous compression.
[root@raca1 ora12cr2]# ls -l /u02/oradata/ora12cr2/sysaux01.dbf
-rw-r----- 1 oracle dba 576724992 Jul 7 13:45 /u02/oradata/ora12cr2/sysaux01.dbf

It is worth noting that not all file types are compressed. For example redo logs are left uncompressed:

[root@raca1 ora12cr2]# acfsutil compress info /u02/oradata/ora12cr2/redo01.log
The file /u02/oradata/ora12cr2/redo01.log is not compressed.

So far so good -- the feature indeed allows you to save some space by compressing the files. I will continue exploring this capability in the next part.

Oracle Security Audit and Open Ports on a Database Server

As part of a detailed security audit of an Oracle database performed by our company we look at most areas that are related to two things; the security of the Oracle platform itself, i.e. the Oracle database and its software....[Read More]

Posted by Pete On 07/07/17 At 04:31 PM


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.


Friday Philosophy – Robots Rising & Tech Taking Over?

Today I saw some cracking photographs of a lighthouse. How many of us have at some point wondered if it might be nice to be a lighthouse keeper? The solitude, the scenery, the stoic fortitude in the face of the storm – quite literally. (Thank you Brendan Tierney for the photo I’ve stolen from him).

It’s an odd job lighthouse keeper, it holds a special place in Western culture and literature. A job to be held by those a little apart from society and yet with a dedication to the betterment of mankind. I suspect a lot of people in I.T. (and the wider community) find a resonance in that, as so many of us are a little bit apart and yet intelligent & care.

Well, you can’t be a lighthouse keeper anymore. At least, in the UK you can’t. Check out This web site about UK lighthouses and lighthouse keeping. That job, that vocation, was handed over to automated I.T. systems a few years ago, effectively handed to robots & technology. You might think you know where I am going with this, and initially you will be right, but bear with me.

I’ve been thinking a lot over the last 2 or 3 years about the increasing use of technology and robotics to do tasks that we humans have been doing. An obvious one is autonomous driving vehicles, where The I.T. smarts and sensors are leaping along incredibly fast. I am in a long-running “argument” with a friend about when fully autonomous vehicles will be a reality on public roads. He says under 5 years, I think it is more (I started saying more than 5 years to him in 2016, so, giving some leeway, I say not before December 2021 will we see fully autonomous vehicles driving from a town centre to another town centre, sharing lanes with human drivers – specific enough Neil?). But self-driving vehicles will be safer than humans “soon”, and cheaper than employing humans, so companies will swap to it. That will end a lot of employment.

I know others have pointed this out and it is not as if history isn’t almost a continuous tale of technology assisting or replacing human effort. Tolpuddle martyrs, dark satanic mills and all that. Industrialisation of farming has put a lot of farm labours out of work but we could not feed the current mass of humanity without it. People move on to new tasks. 920w, 150w, 300w, 768w" sizes="(max-width: 460px) 100vw, 460px" />

But the difference now is not that we are handing jobs to a slightly better automated system where we still need some human control, we are removing the human element. And we are doing this in a lot of areas in a very short space of time. Factories are becoming far more automated, we order our goods online and huge conveyor robotic systems are being built to do the packing, with fewer people involved and lower long-term costs.

But it’s not just physical tasks that are being automated. Genetic algorithms, neural nets, deep data and machine learning is starting to replace tasks that have needed human interaction. Chatbots are getting smarter, to the point where they are used by companies as first-line support {often laughably poorly at present, but it is getting better – and Oracle do have an interest as was covered in Oracle Scene recently {sorry, that link might not work for long}. Expert systems have been developed that can judge simple court cases such as parking fines and beat humans at spotting pre-cancerous cells in tissue samples. 228w" sizes="(max-width: 460px) 100vw, 460px" />

Oracle and the Bots

We now see expert computer systems breaking a lot of barriers and doing things that until now have been deemed uniquely human cerebral tasks. Computers won at playing chess 10+ years ago, they triumphed in “Go” last year and now they can win at versions of Poker where you are not sure of the data and have to read the play of your opponent – in effect second guess a human. Currently all these systems are very expensive, highly focused and specific to a task, built on huge data sets and using fine-tuned sets of algorithms, to do one task. We have nothing as generally capable as a 5 year old child or even a dog.

Only, we keep building systems that are better and better at specific tasks.

So why do I say this bothers me but not in the way you would expect? It’s because I keep seeing “thought leaders” present the same denial of these impacts on us in I.T. of the systems we as an industry are developing, platitudes that we are a special case and will be OK. Several times over the last couple of years I see some utter pillock in a suit from upper management telling a crowd of I.T. experts that we will be just fine as we are smart and we can stop doing the easy tasks and concentrate on the harder ones, use our brains more.

This is balls for two reasons. Firstly:

What about everyone who is below smart?

Most of us in I.T. are not only above average intelligence (probably IQs of 125 and upwards), we are surrounded by similar smart people. Our life partners are generally above normal intelligence, we work in teams who are above-average smart, we probably mostly socialise with generally intelligent people (as a raft of psychological studies show, we gravitate to those at a similar IQ to ourselves, irrespective of where we are on the scale). Even the end users we abuse tend to be above average intelligence. I suspect that most of us somehow don’t “get” that well over 60% of people are not only less intelligent than we are but they have few options if our society passes the jobs they can do to computers and robots. And they are not that likely to be philosophical about having no point to their lives and being poorer. They’re probably going to be very angry, very poor and pretty pissed off with smart-arses who say that “we are OK” – and there are a lot, lot more of them than us.

And that leads to the second reason it is balls.

The smart work will also be doable by Tech

As I’ve said already, we can already create technological systems that can beat us at specific cerebral tasks and there is going to be a small and smaller pool of work for highly-intelligent workers. Let’s face it, a lot of what we do now in I.T. is drudge and boring, there is not really that much smart work needed doing, even in this industry stacked by us smart people. And doing work that really needs you to be smart is tiring (well, I find it tiring!). And our work in I.T. tends to be logic-based and what are computers good at? We will just have a breathing space before our work is also more cheaply done by computers.

I’m annoyed as I think those of use who are involved in this revolution are being told a deluded lie that we will be OK if it pans out like I have just said. Those extra 25+ IQ points are not going to keep us special for very long.

So if computers can drive the taxis & lorries, manage the steel works and build the cars, derive the best drug treatment and give the perfect injection (yep, theoretically a robot already wins on that) what do we as humans do?

Only a few people can be “utterly human” – artists, poets and philosophers. And we do not need 7 billion of them anyway.

We could try and legislate against it, tax robots hard. But those who make a lot of money already run the “free market economy” and will continue to do so. If Robots and computer programs do tasks more cheaply, companies that uses robots will rise to the top of any monetary-based society, i.e. a capitalist society. What will change what has been in place for 100+ years? I can’t see the currently rich and powerful objecting to working methods that increase their wealth. Even if it means more and more poorer people.

Some argue for a basic living wage to keep us all alive – fed, warm and basic healthcare whilst machines do the work. That would give us that often cited nirvana of being free to do “what we want”. But if you have no job, what do you do? Again, for those of us with high IQ we can maybe come up with things to do. Maybe. I seem to be relatively happy being semi-retired, but I’ve done a lot of stuff and had my time of striving to achieve. And still do. But how about those who are IQ 100 and below? I suspect entertaining yourself is not as easy. I think anger, resentment and the feeling of being disenfranchised is just going to continue increasing. I think it’s why the UK is leaving Europe and why the US has an egotistical man-child as president. More and more normal people are unhappy with their lot and see no good future – so they vote for a change. ANY change. Even if it is crazy change.

I know, not a very happy Friday Philosophy. Will someone please tell me it will all be OK? And I mean OK for everyone, not just us “smart” people.

12.2 New Feature: the FLEX ASM disk group part 1

I knew about the 12.2 FLEX ASM disk group type from other presenters but until now – when researching the feature for the upcoming DOAG HA Day – I haven’t been able to appreciate how cool this is. And I really think it is pretty cool and worth sharing! There is a lot to be said about the feature and these tests, which is why I am splitting it into multiple parts.

Please be aware that this post is about my lab experiments, I have no production experience with FLEX ASM disk groups. As with all new features it might take a while to mature, so test, test, test…


In previous versions of Oracle Grid Infrastructure/Automatic Storage Management (ASM), especially in consolidation environments, certain operations I would have liked to perform were not easily possible. Most properties of the disk group – such as redundancy levels etc. – are valid for all files that reside within it. For example, if you wanted to have normal redundancy for some databases, and high redundancy for others, you typically ended up with two “data” disk groups. The same goes for +RECO. I can think of scenarios where “filesystem-like” attributes within a disk group would have been nice. In the build-up to ASM 12.2, Oracle has steadily increased ASM limits to allow for more customisation, and in 12.1 you really could go a little over the top. You could have 63 ASM disk groups in 11.2 and up to 511 in 12.1. Although this should allow for plenty customisation, it adds a little maintenance overhead.

A more granular option to manage storage came with 12.1 and the introduction of Container Databases (CDBs). As part of the Pluggable Database’s creation, the administrator could specify a pdb_storage_clause as in this very basic example:

SQL> create pluggable database pdb1 
  2   admin user someone identified by somepassword
  3    ...
  4   storage (maxsize 200G);

Pluggable database created.

However, if the database was created within a disk group with high redundancy, all files residing in that disk group inherited that property. I couldn’t define a PDB with normal redundancy in a high redundancy disk group, at least I wasn’t aware of a way to do so in 12.1.

Flex Disk Group

A Flex Disk Group promises fine-granular management of data within the disk group. You can also enforce quotas in a disk group (probably most useful on database-level), and you can define properties such as redundancy settings per file type (also per database or Pluggable Database). So in other words you can now have a disk group containing 2 databases for example, out of which database 1 uses normal redundancy, and database 2 uses high redundancy. If database 2 is a Container Database (CDB), you can even manage settings as low down as the PDB level. A few new concepts need introducing before that can happen. Let’s begin with the most essential part: the new Flex ASM disk group. There is a variation of the theme for extended distance clusters, which is not in scope of this article series.

In my lab system, I have created a new ASM disk group of flex redundancy. The system I am using is a two-node RAC running on Oracle Linux 7.3 with UEK4. I called the disk group FLEX, here is the command used for its creation:


Note the use of ASM Filter Driver which I am testing as part of my lab set up. It’s also enabled by default when you install ASM 12.2. Looking at the code example I do realise now that the disk group name is maybe not ideal … The important bit in the example is the use of “FLEX REDUNDANCY”, the 5 implicit different failure groups and the compatibility settings that need to be 12.2.

The documentation (Automatic Storage Management Administrator’s Guide, chapter “Managing Oracle ASM Flex Disk Groups”) states that a Flex disk group generally tolerates the loss of 2 failure groups (FGs). The same bullet point then elaborates that at least 5 failure groups are needed to absorb the loss of 2 FGs. The minimum number of FGs within a Flex disk group are 3.

If you now get all excited about this new feature, there is one Big Caveat: you need a 12.2 RDBMS instance to use the feature.

This command results in a flurry of activity in the ASM instance, I have captured the output from the command initiation to completion because it’s quite interesting to see what happens in 12.2 ASM when you create a new disk group. Feel free to scroll down past the listing if you aren’t interested in the finer details.

 ATTRIBUTE 'compatible.asm'='','compatible.rdbms'='','compatible.advm'='','au_size'='4M'
NOTE: Assigning number (5,0) to disk (AFD:FLEX1)
NOTE: Assigning number (5,1) to disk (AFD:FLEX2)
NOTE: Assigning number (5,2) to disk (AFD:FLEX3)
NOTE: Assigning number (5,3) to disk (AFD:FLEX4)
NOTE: Assigning number (5,4) to disk (AFD:FLEX5)
2017-07-03 10:38:53.811000 +01:00
NOTE: initializing header (replicated) on grp 5 disk FLEX1
NOTE: initializing header (replicated) on grp 5 disk FLEX2
NOTE: initializing header (replicated) on grp 5 disk FLEX3
NOTE: initializing header (replicated) on grp 5 disk FLEX4
NOTE: initializing header (replicated) on grp 5 disk FLEX5
NOTE: initializing header on grp 5 disk FLEX1
NOTE: initializing header on grp 5 disk FLEX2
NOTE: initializing header on grp 5 disk FLEX3
NOTE: initializing header on grp 5 disk FLEX4
NOTE: initializing header on grp 5 disk FLEX5
NOTE: Disk 0 in group 5 is assigned fgnum=1
NOTE: Disk 1 in group 5 is assigned fgnum=2
NOTE: Disk 2 in group 5 is assigned fgnum=3
NOTE: Disk 3 in group 5 is assigned fgnum=4
NOTE: Disk 4 in group 5 is assigned fgnum=5
GMON updating for reconfiguration, group 5 at 657 for pid 45, osid 25857
NOTE: group 5 PST updated.
NOTE: initiating PST update: grp = 5
GMON updating group 5 at 658 for pid 45, osid 25857
NOTE: set version 0 for asmCompat for group 5
NOTE: group FLEX: initial PST location: disks 0000 0001 0002 0003 0004
NOTE: PST update grp = 5 completed successfully
NOTE: cache registered group FLEX 5/0x0A58F009
NOTE: cache began mount (first) of group FLEX 5/0x0A58F009
NOTE: cache is mounting group FLEX created on 2017/07/03 10:38:52
NOTE: cache opening disk 0 of grp 5: FLEX1 label:FLEX1
NOTE: cache opening disk 1 of grp 5: FLEX2 label:FLEX2
NOTE: cache opening disk 2 of grp 5: FLEX3 label:FLEX3
NOTE: cache opening disk 3 of grp 5: FLEX4 label:FLEX4
NOTE: cache opening disk 4 of grp 5: FLEX5 label:FLEX5
* allocate domain 5, valid ? 0
kjbdomatt send to inst 2
NOTE: attached to recovery domain 5
NOTE: cache creating group 5/0x0A58F009 (FLEX)
NOTE: cache mounting group 5/0x0A58F009 (FLEX) succeeded
NOTE: allocating F1X0 (replicated) on grp 5 disk FLEX1
NOTE: allocating F1X0 (replicated) on grp 5 disk FLEX2
NOTE: allocating F1X0 (replicated) on grp 5 disk FLEX3
NOTE: allocating F1X0 on grp 5 disk FLEX1
NOTE: allocating F1X0 on grp 5 disk FLEX2
NOTE: allocating F1X0 on grp 5 disk FLEX3
2017-07-03 10:38:56.621000 +01:00
NOTE: Created Used Space Directory for 1 threads
NOTE: Created Virtual Allocation Locator (1 extents) and Table (5 extents) directories for group 5/0x0A58F009 (FLEX)
2017-07-03 10:39:00.153000 +01:00
NOTE: VAM migration has completed for group 5/0x0A58F009 (FLEX)
NOTE: diskgroup must now be re-mounted prior to first use
NOTE: cache dismounting (clean) group 5/0x0A58F009 (FLEX)
NOTE: messaging CKPT to quiesce pins Unix process pid: 25857, image: oracle@rac122pri1 (TNS V1-V3)
2017-07-03 10:39:01.805000 +01:00
NOTE: LGWR not being messaged to dismount
kjbdomdet send to inst 2
detach from dom 5, sending detach message to inst 2
freeing rdom 5
NOTE: detached from domain 5
NOTE: cache dismounted group 5/0x0A58F009 (FLEX)
GMON dismounting group 5 at 659 for pid 45, osid 25857
GMON dismounting group 5 at 660 for pid 45, osid 25857
NOTE: Disk FLEX1 in mode 0x7f marked for de-assignment
NOTE: Disk FLEX2 in mode 0x7f marked for de-assignment
NOTE: Disk FLEX3 in mode 0x7f marked for de-assignment
NOTE: Disk FLEX4 in mode 0x7f marked for de-assignment
NOTE: Disk FLEX5 in mode 0x7f marked for de-assignment
SUCCESS: diskgroup FLEX was created
NOTE: cache deleting context for group FLEX 5/0x0a58f009
NOTE: cache registered group FLEX 5/0x4718F00C
NOTE: cache began mount (first) of group FLEX 5/0x4718F00C
NOTE: Assigning number (5,0) to disk (AFD:FLEX1)
NOTE: Assigning number (5,1) to disk (AFD:FLEX2)
NOTE: Assigning number (5,2) to disk (AFD:FLEX3)
NOTE: Assigning number (5,3) to disk (AFD:FLEX4)
NOTE: Assigning number (5,4) to disk (AFD:FLEX5)
2017-07-03 10:39:08.161000 +01:00
NOTE: GMON heartbeating for grp 5 (FLEX)
GMON querying group 5 at 663 for pid 45, osid 25857
NOTE: cache is mounting group FLEX created on 2017/07/03 10:38:52
NOTE: cache opening disk 0 of grp 5: FLEX1 label:FLEX1
NOTE: 07/03/17 10:39:07 FLEX.F1X0 found on disk 0 au 10 fcn 0.0 datfmt 1
NOTE: cache opening disk 1 of grp 5: FLEX2 label:FLEX2
NOTE: 07/03/17 10:39:07 FLEX.F1X0 found on disk 1 au 10 fcn 0.0 datfmt 1
NOTE: cache opening disk 2 of grp 5: FLEX3 label:FLEX3
NOTE: 07/03/17 10:39:07 FLEX.F1X0 found on disk 2 au 10 fcn 0.0 datfmt 1
NOTE: cache opening disk 3 of grp 5: FLEX4 label:FLEX4
NOTE: cache opening disk 4 of grp 5: FLEX5 label:FLEX5
NOTE: cache mounting (first) flex redundancy group 5/0x4718F00C (FLEX)
* allocate domain 5, valid ? 0
kjbdomatt send to inst 2
NOTE: attached to recovery domain 5
start recovery: pdb 5, passed in flags x4 (domain enable 0)
validate pdb 5, flags x4, valid 0, pdb flags x204
* validated domain 5, flags = 0x200
NOTE: cache recovered group 5 to fcn 0.0
NOTE: redo buffer size is 512 blocks (2105344 bytes)
NOTE: LGWR attempting to mount thread 1 for diskgroup 5 (FLEX)
NOTE: LGWR found thread 1 closed at ABA 0.11262 lock domain=0 inc#=0 instnum=0
NOTE: LGWR mounted thread 1 for diskgroup 5 (FLEX)
NOTE: setting 11.2 start ABA for group FLEX thread 1 to 2.0
NOTE: LGWR opened thread 1 (FLEX) at fcn 0.0 ABA 2.0 lock domain=5 inc#=12 instnum=1 gx.incarn=1192816652 mntstmp=2017/07/03 10:39:08.437000
NOTE: cache mounting group 5/0x4718F00C (FLEX) succeeded
NOTE: cache ending mount (success) of group FLEX number=5 incarn=0x4718f00c
NOTE: Instance updated compatible.asm to for grp 5 (FLEX).
NOTE: Instance updated compatible.asm to for grp 5 (FLEX).
NOTE: Instance updated compatible.rdbms to for grp 5 (FLEX).
NOTE: Instance updated compatible.rdbms to for grp 5 (FLEX).
SUCCESS: diskgroup FLEX was mounted
NOTE: diskgroup resource ora.FLEX.dg is online
 ATTRIBUTE 'compatible.asm'='','compatible.rdbms'='','compatible.advm'='','au_size'='4M'
2017-07-03 10:39:09.429000 +01:00
NOTE: enlarging ACD to 2 threads for group 5/0x4718f00c (FLEX)
2017-07-03 10:39:11.438000 +01:00
SUCCESS: ACD enlarged for group 5/0x4718f00c (FLEX)
NOTE: Physical metadata for diskgroup 5 (FLEX) was replicated.

Quite a bit of activity for just 1 command… I checked the attributes of the disk group, they don’t seem too alien to me:

SQL> select name, value from v$asm_attribute 
  2   where group_number = 5 
  3   and name not like 'template%';

NAME                           VALUE
------------------------------ ------------------------------
idp.type                       dynamic
idp.boundary                   auto
disk_repair_time               3.6h
phys_meta_replicated           true
failgroup_repair_time          24.0h
thin_provisioned               FALSE
preferred_read.enabled         FALSE
sector_size                    512
logical_sector_size            512
content.type                   data
content.check                  FALSE
au_size                        4194304
appliance._partnering_type     GENERIC
cell.smart_scan_capable        FALSE
cell.sparse_dg                 allnonsparse
access_control.enabled         FALSE
access_control.umask           066
scrub_async_limit              1
scrub_metadata.enabled         FALSE

22 rows selected.

I didn’t specify anything about failure groups in the create disk group command, hence I am getting 5 of them:

SQL> select name, os_mb, failgroup, path from v$asm_disk where group_number = 5;

NAME            OS_MB FAILGROUP                      PATH
---------- ---------- ------------------------------ --------------------
FLEX1           10239 FLEX1                          AFD:FLEX1
FLEX2           10239 FLEX2                          AFD:FLEX2
FLEX3           10239 FLEX3                          AFD:FLEX3
FLEX4           10239 FLEX4                          AFD:FLEX4
FLEX5           10239 FLEX5                          AFD:FLEX5

The result is the new disk group, a new part of my existing lab setup. As you can see in the following output I went with a separate disk group for the Grid Infrastructure Management Repository (GIMR), named +MGMT. In addition I have a disk group named +OCR which (surprise!) I use for OCR and voting files plus the usual suspects, +DATA and +RECO. Except +FLEX, all these are disk group types that have been available forever.

[oracle@rac122pri1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     20476    17068                0           17068              0             N  DATA/
MOUNTED  FLEX    N         512             512   4096  4194304     51180    50676                0               0              0             N  FLEX/
MOUNTED  EXTERN  N         512             512   4096  4194304     40956     6560                0            6560              0             N  MGMT/
MOUNTED  NORMAL  N         512             512   4096  4194304     15348    14480             5116            4682              0             Y  OCR/
MOUNTED  EXTERN  N         512             512   4096  4194304     15356    15224                0           15224              0             N  RECO/
[oracle@rac122pri1 ~]$ 

The values of 0 in required_mirror_free_mb and useable_file_mb for +FLEX aren’t bugs, they are documented to be empty for this type of disk group. You need to consult other entities – to be covered in the next posts – to check the space usage for your databases.

Wrap Up Part 1

Flex ASM disk groups are hugely interesting and worth keeping an eye out for when you are testing Oracle 12.2. I admit that 12.2 is still quite new, and the cautious person I am won’t use it for production until the first major patch set comes out and I tested it to death. I am also curious how the use of the ASM Filter Driver will change the way I am working with ASM. It might be similar to ASMLib but I have yet to work that out.

In the next part(s) I will cover additional concepts you need to understand in the context of Flex ASM disk groups.

Offline Analysis of ASH Data with ASHDUMP

From time to time, it happens to me to carry out offline analyses of ASH data. For that, I mean to analyze the ASH data without having access to the database instance that generated it. For that purpose, Oracle Database provides the possibility to dump the content of the ASH buffer as well as information on how to load it through SQL*Loader to a file. The typical steps to carry out to move the data from the source to the destination database (the best thing is to use a destination database with exactly the same version as the source database) are the following:

On the source database…

  • Check the number of ASH samples and the oldest entry (to make sure that the period you are looking for is in there)
SQL> SELECT sample_count, oldest_sample_time FROM v$ash_info;

------------ -------------------------------
       34997 06-JUL-17 AM
  • Dump the ASH buffer for the last n minutes (60 minutes in the example)
SQL> ALTER SYSTEM SET events 'immediate trace name ashdump level 60';
  • Get the name of the trace file containing the ASH data
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';


On the destination database…

  • Create a table named ASHDUMP
  • Extract the SQL*Loader control file from the trace file
$ cd /u00/oracle/diag/rdbms/dba121/DBA121/trace/
$ sed -n '1,/^Step 2:/d;/^Step 3:/,$d;p' DBA121_ora_22501.trc > ashldr.ctl
  • Run SQL*Loader to insert ASH data into the ASHDUMP table
$ sqlldr control=ashldr.ctl data=DBA121_ora_22501.trc errors=1000000

Once the data is available in the ASHDUMP table, you can start the analysis. But, in general, you cannot execute against the ASHDUMP table exactly the same queries as the ones you would use with the V$ACTIVE_SESSION_HISTORY view. In fact, the structure and information it provides are quite different.

To make the analysis easier I create a view (ASHDUMP_V) on top of the ASHDUMP table that is much more close to the V$ACTIVE_SESSION_HISTORY view. It allows me to run the same queries that I would use against the V$ACTIVE_SESSION_HISTORY view.

How does the view look like? It is not so simple. Hence, I provide it as a script that contains not only the CREATE VIEW statement but also grants the necessary privileges to the owner of the view. Note that the script has to be executed as SYS.

Here you can download the script for the last four versions (in general, every new version adds new columns):

Feel free to test them. In case you find a mismatch between my view and the V$ACTIVE_SESSION_HISTORY view, please, let me know. There might be cases that I did not handle. In any case, note that since some data is missing in the dump. Therefore, not all information can be reconstructed.

In Memoriam

My mother died a few weeks ago after a couple of months in terminal care. One of my tasks while she was in care was to go through all her paperwork and while doing so I discovered a couple of stories from her past that she had typed out (remember type-writers?) about 30 years ago. I typed them up on my laptop and printed several copies to hand out for people to read at the tea-party we held for her old friends – of all ages, ranging from 15 to 99 – after the funeral; this seemed to give them a lot of pleasure and they found them so interesting that I decided to share them with a larger audience. So here’s the story, written by my mother in 1983, of her evacuation experience at the start of the 2nd world war when she was a little over 14 years old.

The Summer of 1939.

Reminiscences of Dorothy Kathleen Lewis (1925 – 2017)

There had been a lot of talk about a war coming. Adolf Hitler and his armies had marched into Austria and were threatening Poland. We had all been issued with gas masks – just in case we would need them – and emergency plans had been made to evacuate all children from the big cities.

During the school holidays I was taken by my parents, with my sister, to my mother’s home village of Llangeitho in Cardiganshire. My mother had a cousin who was a retired school teacher and it was arranged with Auntie Jane that if war broke out Peggy and I would be sent to stay with her. I don’t think we were very pleased with the arrangement because to us she was very old-fashioned, not a bit like our mother. We ended our holiday and went back to London to wait for the school term to begin.

On the 1st September we heard that all children from our school whose parents wanted them to be evacuated should assemble at the school gates with a small suitcase and their gas masks. As we had already been told we were going to Llangeitho if the war broke out we stood and watched all our friends walking in crocodile fashion down the street and mothers and fathers crying watching them go. It was a very sad day, but I wished I was going with them. I didn’t like the idea of staying with Auntie Jane. None of these children knew where they were going, just somewhere in the countryside for safety, and they didn’t know who would be looking after them.

Well, on the morning of 3rd September Neville Chamberlain, our prime minister, spoke on the wireless (we now call it a radio) to say that we were at war with Germany. Immediately the sirens went and everyone ran to the shelters. My parents, Peggy, and I went to Baker Street Station, which has very deep platforms. There were hundreds of people with the same thing on their minds. We all took our gas masks with us. After a short time the all-clear went. My father sent a telegram to Auntie Jane to say Peggy and I would be leaving London on the train at 9:25pm that night. Trains did not travel as fast as they do today and we were due to arrive at Pont Llanio Station at 7:30am on Monday morning. Peggy’s friend and her mother (an Italian lady who did not speak very good English) was coming too, also one of the young people from the village who was working in London.

Paddington Station had very dim lights and when we got on the train there were no lights at all. After a little while we children began to feel a bit less afraid and started to tell ghost stories and play memory games. It was fun going to the toilet on the train because there were people sitting in the corridor and so was their luggage. We could not see them and I don’t think we really tried – it was all a game. We were supposed to be sleeping, but we were too excited for that. When it came time to eat our sandwiches we had to taste them before we knew what we were eating. Can you imagine being in a train without any lights, and there were no lights in the streets or houses or on the station platforms that we passed. Names of stations had already been removed in case the country was invaded by the enemy. The belief was that the enemy would not know were he was if there were no road signs etc. No-one thought about them using maps and compasses as they would now. [ed: 1983]

We eventually arrived in a town called Carmarthen where we had to change trains and take a slow train to Pont Llanio where a car would meet us. Our train from Paddington was very late arriving and the slow train had gone. Someone telephoned Pont Llanio station to say we would be late and to send the car back. The train from Carmarthen was a very slow one and my father used to say “you could get out of the train and pick flowers on the bank and get back into the train again”. It really was very slow and chugged its way along the line. We arrived at last in Pont Llanio and then in Llangeitho after a journey of 16 hours. [ed: 4:30 to 5:00 hours driving time, now; 6 hours by public transport] I am sure we must have looked very dirty and untidy. The trains in those days were steam and there would be plenty of coal smuts flying around.

I did not think Auntie Jane would be very pleased to see us and I was soon to find out that I had thought rightly. The first thing she did was to take up the stair carpet in case we wore it out. I don’t know how she thought we would do that because once we came down in the morning we were not allowed to go back upstairs again until we went to bed. [ed: if you’ve read “Carrie’s War” you may recognise the behaviour]  She also did not know that children eat quite a lot too. For breakfast Auntie Jane would boil an egg and cut it in half, so Peggy and I had half each. And the same for our dinner, we would have two small potatoes – and this was before rationing and shortage of food. We had a lot of friends in the village and if it was not for them asking us out to tea and/or supper we would have been very hungry. Peggy went to school in the village, but I was too old [ed: at 14 yrs 4 months] and had nothing to do all day, but soon found a baby I could take out in the pram and that meant I would be asked if I would like a piece of cake and a drink. After a few weeks and a number of letters home things goT a little better because my mother was sending parcels of food to Auntie Jane. I don’t know what arrangements were made money wise; because we were not Government evacuees Auntie Jane would not have been paid by the authorities to keep us.

One of the things we used to do together with two of our friends was to help the local butcher clean out his slaughter-house after he had killed a beast. This meant he then asked us to supper in his old farm-house with a huge Inglenook fireplace. Another of my mother’s friends used to have us in early for a meal and say “don’t tell Auntie Jane or she will not give you anything else to eat”. I often think back on those days and wonder why she was so mean. She had never married and had children, but being a teacher I would have expected her to be more tolerant.

In December of 1939 Peggy wrote a letter home which was full of complaint and left it somewhere where Auntie Jane found it and this letter was sent to my parents with a letter from Auntie Jane asking that we be sent back to London. A lot of the people in the village were very surprised to think that she should think to send us back to London when there were air-raids (these had not started at that time). People were saying we would be going home to be killed, but as for me I would rather take that chance than be left in Llangeitho.

Going back to London wasn’t much fun – the school was closed so once again we were at a loose end. We stayed in London over Christmas and again the government started evacuating school children and in February we joined a group who were leaving London – this time as London School Evacuees. We were sent to Buckingham to a family with a little girl of 2 years. This seemed to be alright and we went to school in the afternoons whilst the local children went to school in the mornings. It got rather uncomfortable there after a while because the man of the house, aged 24, lost his job (I don’t know why) and there were a lot of arguments in the house. His wife did not make herself look smart and he started to pay too much attention to me. Again a letter home that it was time we left there and one morning my father arrived and said: “pack your bags, you’re coming home”. What joy!

I don’t have much memory about this part of being an evacuee except to say I was relieved to be out of that house and back in the safety of my family. Whilst we were in Buckingham there had been bombing in London and Peggy and I were taken to see some of the damage that had been done. I think this was to frighten us so that we would be willing to go away again. I certainly did not like the sirens going and having to stop what we were doing and go to the shelter[1]. Once again we were on the move and this time I have a very detailed memory of the events.

We were assembled at the school playground all with our cases and gas masks – worried children and even more worried parents and teachers. No one knew where we were going except that we all piled into a double-decker bus. Lots of tears this time because we knew that people were being killed and injured. Would we see our parents again? What was going to happen to us if they were killed? Where would we go, who would look after us? Questions, questions!

We were taken to Marylebone station and put on an underground train. Where was it going? What were mum and dad doing now; were they still blowing their noses? We were not so bothered because we still knew where we were. Next stop Paddington Station and hundreds of children milling about. I remember I was in the Junior Red Cross at that time and a Red Cross nurse saw my badge and came to speak to me. Such a little thing but it meant such a lot and I have never forgotten her kind words, saying I was going somewhere safe and would be alright. Maybe I was crying at the time, I don’t know.

As the train pulled out of Paddington Station we were all trying to get to a window to wave, although we didn’t know anybody and we didn’t know where we were going. Well of all places – we arrived in Banbury. Now my Auntie Kit, my father’s sister, only lived about 2 miles out of Banbury in a village called Bodicote. I knew Banbury well because we had often visited Bodicote. If only she knew I was here. I know we could not stay with her because she had a very small house and she had 4 children already.

Again on another bus and somehow Peggy and I and two other girls from my class got separated from the rest of our school and we were taken to a village called Great Rollright. Peggy and I went to stay with a lady called Mrs. Robinson who kept the village shop, and my two class friends went to a farm.

Mrs. Robinson was a kind lady – she had asked for two boys but somewhere along the line she had two girls instead. It was very strange arriving in Great Rollright. We were all taken into the Church Hall and there were village people there who, to us, had a funny way of speaking. And one after the other they were saying how many children they would take. Mrs. Robinson wasn’t there, so maybe that is why she didn’t get two boys. I thought it was very embarrassing to be standing there with these quaint country people whilst they were deciding whether they wanted one, two, or three children.

Our time with Mrs. Robinson was very happy. Peggy went to the village school and I went to the county school in Chipping Norton, again on a part-time basis. Mrs. Robinson had a pet cockerel which was allowed to roam round the house – I hated that bird and I think it knew it. Its name was Cocky. Every time I wanted to go down the garden to the toilet this bird would follow me and stay outside until I came out again and peck my legs as I raced back up the garden.

There was certainly plenty to eat in this house and we really had an enjoyable time there. We were always sent to bed with a small glass of cider. I never knew then that cider was an alcoholic drink and I thought it was lovely. We didn’t seem any the worse for it anyway.

We got involved with the village church and would have been happy to have stayed there. But doom. One day I came home from school to find my head mistress from London sitting at the table. Unbeknown to us she had been frantically looking for these four girls she had lost at Banbury Station. I don’t know how she had found us, whether she had contacted our parents or hunted through the schools in the area. With a surname like Walklett I don’t think we would have been difficult to find. I don’t think she had been in touch with our parents – what an awful thing to say to them: “I’m very sorry but I may have lost your children”. No, she must have hunted through the school registers.

The upshot of this visit was that she had found somewhere more suitable for us and would we pack our things because she had found somewhere else for us to stay. More tears because we liked Mrs. Robinson, and the village people were all lovely to us and we were invited to their homes. Off we went with Miss Attride in the car to another village called Duns Tew. The strange thing was that none of our school were there, so why were we moved yet again?

This time we stayed with Mr. and Mrs. Beck, his name was Harry and hers was Daisy, but they were Mr. and Mrs. Beck to us. Mr. Beck was a farm hand and he worked with horses. He used to plough the fields of the farm across the road. He must have walked miles in the days he was ploughing. Although I had had many holidays in Wales and Shropshire at haymaking time I knew nothing about ploughing.

Mr. and Mrs. Beck had a young man living with them. He was like their son; although his family lived in the village he had lived with the Becks since he was a baby and they called him their son. His name was Walter. The village was a beautiful place and we lived in No. 73. There were no street names, every house had a name and a number so we were at No. 73 Duns Tew, the last house in the village, a lovely old thatched cottage. There was always a big wood fire in the grate and plenty on the table. Mr. and Mrs. Beck were the nicest people in village.

Peggy now had to go to Steeple Aston School (since moving to Banbury in 1975 I have met the widow of her headmaster there), and I went to a Continuation College which had been evacuated from the East End of London. This was very odd to me – we were taught shorthand, typing, arithmetic, English grammar, French. This was obviously training us for the commercial world. I was much younger than the other girls there but my education was more advanced than theirs so I soon became top of the class. My English was always being complimented. What they didn’t know was that I had a Welsh mother and the Welsh language used very letter in the word. My French was well in advance and my Maths took a flying leap.

I made friends in the class. The class was held in The Hall, North Aston – a country seat. The Hall was so large that there were 9 girls living there and they had servants. The school room was in the Grand Hall and it was so beautiful it seemed a pity to me that there were desks etc. on the polished floor.

In Duns Tew we had one of the masters of the school staying in The Nurseries (which is still there) and every Friday evening the family he stayed with invited those of us in the village to spend the evening in their house and they had a piano so all the war songs were being sung: “Roll out the Barrel”, “We’re going to hang out the washing on the Siegfried line” and many more.

Because the school at North Aston was a long walk I bought a bike, something I had always wanted, and I joined the cycling group. This meant on one day a week we would go for an outing to places like Blenheim Palace [ed: 10 miles away] etc. I became a good cyclist and had plenty of energy when others flagged behind. I certainly made use of my bike.

One particularly happy time was when it snowed in the winter. Yes, we did get snow in London, but not like this. It was white[2] and where the wind blew it was as high as the hedgerows; I couldn’t believe what I saw. Walter the Beck’s son had a sledge and he showed us where it was good to use it. It was a fantastic time.


[Banbury, 1983] 1200w, 150w, 300w, 768w, 1024w" sizes="(max-width: 600px) 100vw, 600px" />


[1] One of the stories about my mother that I first heard at her funeral was about the time she persuaded her parents to let her stay at home overnight. At the time the family used to head for the air-raid shelter (i.e. the local underground station) at the end of the day and stay there all night long. My mother hated this and persuaded her parents to let her stay at home in her own bed provided she promised to join them at the air-raid shelter as soon as the air-raid sirens sounded. She was only allowed to do this once – because she managed to sleep through two bombing runs and without being woken by the sirens or the explosions.

[2]If you’re wondering why white snow is worth mentioning you probably don’t know about the density of London smog at that time.

POUG voucher – contest

Contest time!
I had a cool function in my database:

SQL> ;
  1  select object_name, object_type
  2  from user_objects
  3* where object_name like '%POUG%'
SQL> /

------------------------------ -----------------------

When executed, it returned a -15% voucher for POUG conference. And POUG is a REALLY COOL confernece </p />

    	  	<div class=

POUG voucher – contest

Contest time!
I had a cool function in my database:

SQL> ;
  1  select object_name, object_type
  2  from user_objects
  3* where object_name like '%POUG%'
SQL> /

------------------------------ -----------------------

When executed, it returned a -15% voucher for POUG conference. And POUG is a REALLY COOL confernece </p />

    	  	<div class=