This was surprisingly more difficult than I expected. We know that we can export stats from the dictionary to a table, and from the table to a file, and that file can be copied and imported to another database for the stats to be imported. Easy right?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 19 08:55:39 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> exec DBMS_STATS.CREATE_STAT_TABLE('SCHEMA','MYSTATS','USERS');

PL/SQL procedure successfully completed.

SQL> @export_stats
BEGIN
DBMS_STATS.EXPORT_TABLE_STATS ( 'SCHEMA', 'MYTABLE', NULL, stattab => 'MYSTATS');
END;

*
ERROR at line 1:
ORA-20002: Version of statistics table "SCHEMA"."MYSTATS" is too old.  Please
try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at "SYS.DBMS_STATS", line 18000
ORA-06512: at line 1

Hang on, I just created it! How can it be too old? Still, I will upgrade it if it insists.

We are running a data conversion. The powers that be decided to use APIs to convert the data as they contain error checking. The problem is that they are generally designed for interactive use updating one row at a time, so they are very slow to update large batches of data.

This was tuned and is getting much faster, however we noticed that there are a lot of waits on cursor: pin: S wait on X. From experience I know that this is caused by excessive hard parsing. However, I couldn’t find any statements with a lot of hard parses. The most was about 40, so nothing like the thousands which were a cause of this problem early in the release cycle of 12c, where cursors weren’t being shared properly (Bug 20476175 fixed by the patch of the same number).

Problem

Since Oracle 12c, you can recover a crash consistent snapshot. Oracle support note 604683.1 says how to do this.

We had an issue where the recovery wanted to effectively run to the end of time, and wouldn’t ever finish. No matter how many logs were applied, it said:

1
2
3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/CS_SR/system/system01.dbf'

Solution

The solution is the snapshot time of the recovery clause. To demonstrate I have a database being recovered from a snapshot, and here are some archived redo logs from the source database.

Dominic Giles drew our attention to the safe harbour statement. Anything can change between now and when the new version is released.

There has been some confusion about the announcements regarding autonomous database. This is a service on top of oracle 18c. Oracle 18c is a product.

Release Schedule

Oracle will change its release process to release annually. Hopefully this will lead to more stable releases, as there won’t be a rush to get the new features into the current version of the database. Changes will be incremental, but will happen every year. There will be quarterly release updates. These will go back to using a number rather than a date format, These will include all fixes, not just security fixes, but performance changes will be switched off by default.

The first session after lunch was Oracle Database 12c Release 2 - What is new in the Oracle Optimiser. It was taken by Nigel Baylis who is the product manager for the oracle optimiser.

I was impressed by Nigel’s honesty when he talked about what worked, what didn’t and how things are being changed to correct the mistakes of the past. This is the main reason to come to user groups of course. It made me feel like Oracle are willing to accept where things haven’t worked out as they hoped, and take action to correct it.

For the last session before lunch I listened to Richard Foote talking about new index features in 12.2. Richard is well known as the Oracle Indexing expert, and so I was eager to hear what he had to say.

Object name length

Indexes can now have 128 character names, where before they could only be 30 characters. I expect this will make life easier where naming standards dictate long names for whatever reason.

The presenter was Pete Finnigan.

Pete talked through a solution (PFCATK) he had developed for auditing the oracle database. This appears to be currently in development and not available for general use. He seemed inclined to release the core tool and sell a pretty front end or consultancy, but it seemed he hadn’t yet decided. The lack of anything to play with (Unless you ask for a copy of the tool and agree to install it and give feedback) made this session less interesting than it might otherwise be.

This is second session I attended in Tech 17. It was presented by Martin Bach and David Kurtz. The answer is eDB360. The session was in two parts with a presentation by Martin, then a demo with David.

Martin discussed the problems of doing a database health check, particularly for a third party company who may not actually have access to the database. It needs a standard approach, which is consistent across databases, and repeatable. Ideally performance data in AWR should be persisted for just over a year so that the performance of annual business cycles can be compared e.g., year end for financial systems.