Hyper-partitioned index avoidance thingamajig

July 25, 2018July 26, 2018 Connor McDonald 18c, dba, merge, online, partitioning

As you can tell, I have no idea on a name for what I am about to describe. So let me start from the beginning, and set the scene for an idea I have to utilize a cool new 18c feature.

Often in a transactional-style system the busiest table (let us call it SALES for the sake of this discussion) is also

the biggest table, after all, it has all of our sales in it,
the most demanded for table, in that, almost every query in our application wants to access it in some way shape or form.

This is in effect the database version of the Pareto Principle. Everyone wants a slice of that SALES “pie”, and the piece of that pie that is in most demand is typically the most recent data. Your application may have pages that will be showing:

sales in the past hour,
sales in the past day,
products sold in the past hour,
largest purchase amount for sales in the past few hours,
suspicious activity for today,
A customer will want their sales for the last few days

The list goes on and on, the common theme being that the data being requested is bound by a range of time in the recent past. Our SALES table already will have a primary key, probably being some sort of unique transaction ID for each purchase, but to the satisfy the style of requests in the list above, we will probably need:

an index on the time(stamp) of the sale,
an index on the product ID that was sold,
an index on the customer ID who made the purchase,
potentially even an index on the sale amount

You can see the troubled waters into which we are sailing here. It is our biggest and busiest table, and here we are, adding index after index after index to improve query performance, whilst at the same time:

adding overhead/contention to DML operations on the SALES table,
increasing the size of the database,
increasing the duration of the backups,
increasing time for maintenance and copies to Development and Test

None of this is looking great but we might be thinking “What choice do we have?”

Here is perhaps an alternate strategy: Let’s not create any indexes. The challenges (1) through (4) above evaporate to non-existence. But of course, now we have a remaining challenge in getting those queries to run efficiently.

I’ll tackle that in a different way – I will partition the SALES table very “aggressively”, hence my blog post title “hyper-partitioning”. Considering the typical query requirements I listed above, I will partition my SALES down to as small as a partition for every hour.


SQL> create table sales
  2    ( ts   timestamp,
  3      id   int,
  4      amt  number,
  5      product int,
  6      customer int,
  7      item_cnt int,
  8      terminal int,
  9      operator int,
 10      credit_card int,
         ...
         ...
 17    )
 18  partition by range ( ts )
 19  interval ( numtodsinterval(1,'HOUR') )
 20  (
 21    partition p1 values less than ( timestamp '2018-07-01 00:00:00' )
 22  );

Table created.

SQL>
SQL> insert /*+ APPEND */ into sales (ts,id,amt,product,customer)
  2  select date '2018-07-01' + rownum / 240, rownum, dbms_random.value(1,100),
  3        dbms_random.value(1,100),dbms_random.value(1,100)
  4  from dual
  5  connect by level 
SQL> set serverout on
SQL> declare
  2    h varchar2(1000);
  3  begin
  4  for i in (
  5    select partition_name, high_value
  6    from user_tab_partitions
  7    where table_name = 'SALES'
  8    and   interval = 'YES'
  9    and   partition_name like 'SYS_P%'
 10    order by partition_position
 11  ) loop
 12    h := i.high_value;
 13    execute immediate 'select to_char('||h||'-0.01,''yyyymmdd_hh24'') from dual' into h;
 14    execute immediate 'alter table sales rename partition '||i.partition_name||' to p'||h;
 15  end loop;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL> col high_value format a60
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES'
  4  order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------
P1                             TIMESTAMP' 2018-07-01 00:00:00'
P20180701_00                   TIMESTAMP' 2018-07-01 01:00:00'
P20180701_01                   TIMESTAMP' 2018-07-01 02:00:00'
P20180701_02                   TIMESTAMP' 2018-07-01 03:00:00'
P20180701_03                   TIMESTAMP' 2018-07-01 04:00:00'
P20180701_04                   TIMESTAMP' 2018-07-01 05:00:00'
P20180701_05                   TIMESTAMP' 2018-07-01 06:00:00'
P20180701_06                   TIMESTAMP' 2018-07-01 07:00:00'
P20180701_07                   TIMESTAMP' 2018-07-01 08:00:00'
P20180701_08                   TIMESTAMP' 2018-07-01 09:00:00'
P20180701_09                   TIMESTAMP' 2018-07-01 10:00:00'
P20180701_10                   TIMESTAMP' 2018-07-01 11:00:00'
P20180701_11                   TIMESTAMP' 2018-07-01 12:00:00'
P20180701_12                   TIMESTAMP' 2018-07-01 13:00:00'
P20180701_13                   TIMESTAMP' 2018-07-01 14:00:00'
P20180701_14                   TIMESTAMP' 2018-07-01 15:00:00'
P20180701_15                   TIMESTAMP' 2018-07-01 16:00:00'
P20180701_16                   TIMESTAMP' 2018-07-01 17:00:00'
P20180701_17                   TIMESTAMP' 2018-07-01 18:00:00'
P20180701_18                   TIMESTAMP' 2018-07-01 19:00:00'
P20180701_19                   TIMESTAMP' 2018-07-01 20:00:00'
P20180701_20                   TIMESTAMP' 2018-07-01 21:00:00'
P20180701_21                   TIMESTAMP' 2018-07-01 22:00:00'
P20180701_22                   TIMESTAMP' 2018-07-01 23:00:00'
P20180701_23                   TIMESTAMP' 2018-07-02 00:00:00'
P20180702_00                   TIMESTAMP' 2018-07-02 01:00:00'
P20180702_01                   TIMESTAMP' 2018-07-02 02:00:00'
P20180702_02                   TIMESTAMP' 2018-07-02 03:00:00'
P20180702_03                   TIMESTAMP' 2018-07-02 04:00:00'
P20180702_04                   TIMESTAMP' 2018-07-02 05:00:00'
P20180702_05                   TIMESTAMP' 2018-07-02 06:00:00'
P20180702_06                   TIMESTAMP' 2018-07-02 07:00:00'
P20180702_07                   TIMESTAMP' 2018-07-02 08:00:00'
P20180702_08                   TIMESTAMP' 2018-07-02 09:00:00'
P20180702_09                   TIMESTAMP' 2018-07-02 10:00:00'
P20180702_10                   TIMESTAMP' 2018-07-02 11:00:00'
P20180702_11                   TIMESTAMP' 2018-07-02 12:00:00'
P20180702_12                   TIMESTAMP' 2018-07-02 13:00:00'
P20180702_13                   TIMESTAMP' 2018-07-02 14:00:00'
P20180702_14                   TIMESTAMP' 2018-07-02 15:00:00'
P20180702_15                   TIMESTAMP' 2018-07-02 16:00:00'

41 rows selected.

I’ve run a small anonymous block to rename the (system-named) interval partitions into some sensible names to reflect the date range the partition covers. Let’s now look at the typical queries we will now be performing on the SALES table:


SQL> set autotrace traceonly explain
SQL> select * from sales
  2  where ts > timestamp '2018-07-02 15:00:00';

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     9 |   387 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|       |     9 |   387 |     7   (0)| 00:00:01 |    41 |1048575|
|*  2 |   TABLE ACCESS FULL      | SALES |     9 |   387 |     7   (0)| 00:00:01 |    41 |1048575|
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')

SQL> select * from sales
  2  where ts > timestamp '2018-07-02 15:00:00'
  3  and product = 12;

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |    43 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|       |     1 |    43 |     7   (0)| 00:00:01 |    41 |1048575|
|*  2 |   TABLE ACCESS FULL      | SALES |     1 |    43 |     7   (0)| 00:00:01 |    41 |1048575|
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("PRODUCT"=12 AND "TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')

SQL>
SQL> select * from sales
  2  where ts > timestamp '2018-07-02 15:00:00'
  3  and customer = 25;

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |    43 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|       |     1 |    43 |     7   (0)| 00:00:01 |    41 |1048575|
|*  2 |   TABLE ACCESS FULL      | SALES |     1 |    43 |     7   (0)| 00:00:01 |    41 |1048575|
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CUSTOMER"=25 AND "TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')

SQL>
SQL> select max(amt) from sales
  2  where ts > timestamp '2018-07-02 15:00:00';

---------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       |     1 |    33 |     7   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE           |       |     1 |    33 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|       |     9 |   297 |     7   (0)| 00:00:01 |    41 |1048575|
|*  3 |    TABLE ACCESS FULL      | SALES |     9 |   297 |     7   (0)| 00:00:01 |    41 |1048575|
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')

SQL> set autotrace off

All of them scan a tiny portion of the data, namely, just the hours of sales data relevant to the query, and the query response times will be relatively consistent for all cases no matter which customer, product or other predicate will be passed because the data to be scanned is a fixed number of hours.

But there’s a problem here. If I am partitioning to the hour, or even to the minute…then it won’t be long before I have a lot of partitions. In the latter case (minutes) I will be up to over 500,000 partitions in the first year of SALES alone! That is a lot of database metadata to store. There is the partitions themselves, plus optimizer statistics on them, plus historical optimizer statistics, plus potentially histograms on every column. A lot of optimizer data might lead to expensive parse times because there is just so much information to wade through when optimizing queries.

But we only need the extreme granularity of partitions for the SALES table for today. Once today ticks over and becomes “yesterday”, then we might only need a partition for the entire day. And once “yesterday” ticks over to “last week”, then maybe only weekly partitions are needed and so forth.

One of the cool things in 18c is ability to do this style of maintenance with negligible disruption to service. Because I have named my partitions in a logical fashion, here is a simple routine to merge “yesterdays” hourly partitions into a single one for the day.


SQL> set serverout on
SQL> declare
  2    d date := date '2018-07-01';
  3    ddl varchar2(4000);
  4  begin
  5    select listagg(partition_name||chr(10),',') within group ( order by partition_position )
  6    into   ddl
  7    from   user_tab_partitions
  8    where  table_name = 'SALES'
  9    and    partition_name like 'P'||to_char(d,'yyyymmdd')||'%';
 10
 11    ddl := 'alter table sales merge partitions '||ddl||' into partition p'||to_char(d,'yyyymmdd')||' online';
 12
 13    dbms_output.put_line(ddl);
 14    execute immediate ddl;
 15  end;
 16  /
alter table sales merge partitions
 P20180701_00
,P20180701_01
,P20180701_02
,P20180701_03
,P20180701_04
,P20180701_05
,P20180701_06
,P20180701_07
,P20180701_08
,P20180701_09
,P20180701_10
,P20180701_11
,P20180701_12
,P20180701_13
,P20180701_14
,P20180701_15
,P20180701_16
,P20180701_17
,P20180701_18
,P20180701_19
,P20180701_20
,P20180701_21
,P20180701_22
,P20180701_23
 into partition p20180701 online

PL/SQL procedure successfully completed.

Now I have a single partition for yesterday’s data, and hourly partitions for today’s data.


SQL> col high_value format a60
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES'
  4  order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ -------------------------------
P1                             TIMESTAMP' 2018-07-01 00:00:00'
P20180701                      TIMESTAMP' 2018-07-02 00:00:00'
P20180702_00                   TIMESTAMP' 2018-07-02 01:00:00'
P20180702_01                   TIMESTAMP' 2018-07-02 02:00:00'
P20180702_02                   TIMESTAMP' 2018-07-02 03:00:00'
P20180702_03                   TIMESTAMP' 2018-07-02 04:00:00'
P20180702_04                   TIMESTAMP' 2018-07-02 05:00:00'
P20180702_05                   TIMESTAMP' 2018-07-02 06:00:00'
P20180702_06                   TIMESTAMP' 2018-07-02 07:00:00'
P20180702_07                   TIMESTAMP' 2018-07-02 08:00:00'
P20180702_08                   TIMESTAMP' 2018-07-02 09:00:00'
P20180702_09                   TIMESTAMP' 2018-07-02 10:00:00'
P20180702_10                   TIMESTAMP' 2018-07-02 11:00:00'
P20180702_11                   TIMESTAMP' 2018-07-02 12:00:00'
P20180702_12                   TIMESTAMP' 2018-07-02 13:00:00'
P20180702_13                   TIMESTAMP' 2018-07-02 14:00:00'
P20180702_14                   TIMESTAMP' 2018-07-02 15:00:00'
P20180702_15                   TIMESTAMP' 2018-07-02 16:00:00'

18 rows selected.

I stress – this is not my recommendation to race out and partition every transactional table you have, and drop all of the indexes . But it with so many online partitioning operations in 18c, it raises some exciting new opportunities there were not available in previous releases. So start thinking about how you can exploit this to get advantages with the partitioning option that might sit “outside the box” of the standard usage.

18.3 As easy as 1…2…3

July 24, 2018July 26, 2018 Connor McDonald 18c, dba, installation, linux

Well, finally it’s here! 18c for on-premise installation so the world can all get stuck into the cool new features of the latest release on their own laptops At least that is what I’ll be doing!

Naturally as soon as I heard the news, I downloaded the software and got ready to set aside the day for installation and creation of an 18c database. But I didn’t need that long – I didn’t need that long at all. Just a few clicks and a few commands and there it was – my 18c database up and running.

Check out how easy it is with my three videos.

Software Installation

Listener Creation

Database Creation

It really is as easy as 1…2…3

Enjoy 18c !

UTL_FILE_DIR and 18c

June 27, 2018July 18, 2018 Connor McDonald 18c, utl_file

I wrote a blog post called The Death of UTL_FILE which attracted a comment from a reader:

“There is NO chance to stay at UTL_FILE as it is DESUPPORTED starting with database Version 18c”

This is not the case, but since I wanted to clarify what has changed in 18c, it warrants this small but separate blog post. When UTL_FILE first into existence in Oracle 7, the concept of directory object did not apply to UTL_FILE. Clearly we could not just let UTL_FILE to write to any destination, otherwise a malicious person could write a little PL/SQL block like this:


declare
  f utl_file.file_type;
begin
  for i in ( select 
                regexp_substr(name,'(.*)\\(.*)', 1, 1, 'i', 1) path,
                regexp_substr(name,'(.*)\\(.*)', 1, 1, 'i', 2) name
             from v$datafile 
             order by file# desc ) 
  loop
    f := utl_file.fopen(i.path,i.name,'W');
  end loop;
  end;
  utl_file.fclose_all;
end;
/

and voila! No more database. So an initialization parameter was created to nominate which directories UTL_FILE was allowed to access. This parameter was called UTL_FILE_DIR, and the above code hopefully is justification enough to show that you should never ever set UTL_FILE_DIR to “*”, meaning it could write to wherever the OS permissions on the Oracle software account would allow it. Bye Bye datafiles…bye bye database

In more recent versions, UTL_FILE was improved so that the directory parameter could be supplied as a directory object. This is a much tighter implementation because read and write privileges on directory objects can be controlled from within the database.

In 18c, it is not UTL_FILE that has been de-supported, it is the ability to use the older style convention of hard-coded path names for the directory that is no longer allowed. You have to use directory objects. If you set the traditional UTL_FILE_DIR path in the spfile it will be ignored, and you’ll get a warning on startup.


SQL> alter system set utl_file_dir = 'c:\temp' scope=spfile;

System altered.


SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.


--
-- alert log
--
Obsolete system parameters with specified values:
  utl_file_dir
End of obsolete system parameter listing

But even if you set UTL_FILE_DIR in the spfile, you will not be allowed to use OS directory paths in the UTL_FILE dir calls.


SQL> declare
  2    f utl_file.file_type;
  3  begin
  4    f := utl_file.fopen('c:\temp','demo.dat','W');
  5    utl_file.fclose_all;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-29280: invalid directory object
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 4

If you prefer a video version of this explanation, I had some fun riding my bike when I talked about this when 18c first came out earlier this year.

18.2 patch… painless for me

April 19, 2018June 7, 2018 Connor McDonald 18c, patching, upgrade

18.2 was released a few days ago, so I thought I’d throw it against my 18c instance and see how things played out. This was just a single instance database, running with a single pluggable.

Step 1 for me was to download the latest OPatch. To be honest, I don’t even check whether I already have the latest version in any installed ORACLE_HOME anymore. If I’m going to apply a patch, I’m always going to download the latest OPatch unless the README explicitly tells me some reason why I should not. I figure getting the latest version reduces the chances I’ll hit problems. For me, I grabbed version “12.2.0.1.13”, but your experience may differ depending on when you encountered upon this post.

So I then downloaded patch 27676517 (note, this does not include patches for the JVM which are done separately).

From there is was just a case of following the README instructions and I had no dramas at all. Software patching went through smoothly:


[oracle@host18 27676517]$ export PATH=$PATH:$ORACLE_HOME/OPatch
[oracle@host18 27676517]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/18.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/18.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.13
OUI version       : 12.2.0.4.0
Log file location : /u01/app/oracle/product/18.0.0/dbhome_1/cfgtoollogs/opatch/opatch2018-04-19_08-32-51AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   27676517  

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/18.0.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '27676517' to OH '/u01/app/oracle/product/18.0.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.assistants.usm, 18.0.0.0.0 ] , [ oracle.has.crs, 18.0.0.0.0 ] , [ oracle.network.cman, 18.0.0.0.0 ] , [ oracle.assistants.asm, 18.0.0.0.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.network.rsf, 18.0.0.0.0...

Patching component oracle.rdbms.crs, 18.0.0.0.0...

Patching component oracle.assistants.acf, 18.0.0.0.0...

Patching component oracle.sqlplus.ic, 18.0.0.0.0...

Patching component oracle.rdbms.deconfig, 18.0.0.0.0...

Patching component oracle.sqlplus, 18.0.0.0.0...

Patching component oracle.rdbms.util, 18.0.0.0.0...

Patching component oracle.rdbms, 18.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 18.0.0.0.0...

Patching component oracle.assistants.deconfig, 18.0.0.0.0...

Patching component oracle.assistants.server, 18.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 18.0.0.0.0...

Patching component oracle.rdbms.rsf, 18.0.0.0.0...

Patching component oracle.rdbms.rman, 18.0.0.0.0...

Patching component oracle.javavm.client, 18.0.0.0.0...

Patching component oracle.ldap.owm, 18.0.0.0.0...

Patching component oracle.ldap.security.osdt, 18.0.0.0.0...
Patch 27676517 successfully applied.
Log file location: /u01/app/oracle/product/18.0.0/dbhome_1/cfgtoollogs/opatch/opatch2018-04-19_08-32-51AM_1.log

OPatch succeeded.

And the datapatch stage to update the database(s) also went through without any problems.


[oracle@host18 OPatch]$ ./datapatch -verbose
SQL Patching tool version 18.0.0.0.0 Production on Thu Apr 19 08:37:40 2018
Copyright (c) 2012, 2018, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_10552_2018_04_19_08_37_40/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    18.2.0.0.0 Release_Update 1804041635: Installed
  PDB CDB$ROOT:
    No release update patches installed
  PDB PDB$SEED:
    No release update patches installed
  PDB PDB1:
    No release update patches installed

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED PDB1
    No interim patches need to be rolled back
    Patch 27676517 (Database Release Update : 18.2.0.0.180417 (27676517)):
      Apply from 18.1.0.0.0 Feature Release to 18.2.0.0.0 Release_Update 1804041635
    No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 3

Validating logfiles...done
Patch 27676517 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27676517/22097537/27676517_apply_DB18_CDBROOT_2018Apr19_08_38_45.log (no errors)
Patch 27676517 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27676517/22097537/27676517_apply_DB18_PDBSEED_2018Apr19_08_39_58.log (no errors)
Patch 27676517 apply (pdb PDB1): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27676517/22097537/27676517_apply_DB18_PDB1_2018Apr19_08_40_01.log (no errors)
SQL Patching tool complete on Thu Apr 19 08:41:13 2018
[oracle@host18 OPatch]$ 




[oracle@host18 ~]$ sqlplus scott/tiger@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 19 08:44:27 2018
Version 18.2.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0

SQL>

So all in all, a painless and straightforward experience for me.

One minor annoyance for me was the “Bugs Fixed” document. The README references this as:

but that document is not accessible currently. However, if you follow a different link in the README

then you will get to the document which contains the list of fixes etc.

Footnote: If you are not familiar with the new nomenclature of “Patch”, “Release Update”, etc etc, here is a good place to start

https://mikedietrichde.com/2017/11/07/ru-rur-recommendations-facts/

and follow the various links within that post for more information.

Happy upgrading!

Addenda: When it comes to the ‘datapatch’ script, the README is perhaps a little ambiguous. More on that here

https://mikedietrichde.com/2018/04/19/do-you-have-to-execute-datapatch-when-you-create-a-new-database/

18c merge partition online

February 27, 2018June 27, 2018 Connor McDonald 18c, merge, partitioning

One of the cool things in 18c is the ability to merge partitions without causing a service interruption. Here’s a video demonstration of that in action:

This is just an accompanying blog post to let you grab the scripts for the demo so that you can try this yourself on livesql.oracle.com, or any of the Oracle Cloud services that will be running 18c in the near future.

But also, make sure that you watch to the end of the video, because I also discuss some of the opportunities that this new feature provides. I would contend that 99% of people using partitioning in their real world applications rarely change the size or structure of their partitions. With online merge, you now have much more freedom in those choices. For analytics, you might now be able to run workloads against your live Production transaction processing systems by utilizing a fine-grained partitioning regime, and later merging those partitions to become larger granules over time.

So try to think “outside of the box” on this one. There could be a lot of cool benefits for you that you might not have initially considered. The true feature is not “just” merge online – it is that you have new partitioning designs and implementations at your fingertips.


 create table sales
   ( ts        date,
     id        int,
     amt       number,
     completed varchar2(1)
   )
 partition by range ( ts )
 interval ( numtodsinterval(1,'DAY') )
 (
   partition p1 values less than ( date '2017-01-01' ),
   partition p2 values less than ( date '2017-02-01' ),
   partition p3 values less than ( date '2017-03-01' ),
   partition p4 values less than ( date '2017-04-01' ),
   partition p5 values less than ( date '2017-05-01' )
 );

 insert /*+ APPEND */ into sales
 select date '2017-01-01' - 10 + rownum / 10000 ,rownum, dbms_random.value(1,100), null
 from dual
 connect by level < 10000*120;


 exec dbms_stats.gather_table_stats('','SALES') 

 select partition_name, num_rows
 from user_tab_partitions
 where table_name = 'SALES'
 order by partition_position;

 create index sales_ix on sales ( id );

 alter table sales merge partitions p3, p4 
     into partition p3a update indexes ONLINE;

18c–If you can’t wait

February 23, 2018February 27, 2018 Connor McDonald 18c, livesql, upgrade

You’ve seen the tweet !!

but perhaps the accompanying blog post has tempered your enthusiasm Sad smile

You might be thinking:

“I’m not on Exadata – how can I play with 18c?”

Well, there is still a way to get ahead of the game and skill up on 18c. We’ve upgraded livesql.oracle.com to 18c, and we’ve pre-loaded some demo scripts for you as well.

So get cracking! Oracle Database 18c builds upon the foundation set by 12.2.

Enjoy!

Oracle Database 18c

February 17, 2018June 27, 2018 Connor McDonald 18c, database, upgrade

Yup…it’s arrived!

New name obviously, because we’ve jumped to our new naming model to align with the calendar year as opposed to version number. You might be thinking “So what?” but it’s a significant change in the way we getting software to customer. Mike Dietrich blogged about this at length here so I won’t repeat what has been said, but in TL;DR form:

More frequent releases, with smaller amounts of change per release

In this way, the approach lets us focus more of solidifying existing features, and being able to quickly respond to bugs that arise.

So 18c is more an incremental release on 12.2 (in fact, internally it was referred to as “12.2.0.2” for most of it’s build cycle) focussed on stability and hardening of existing features.

Don’t worry, we still managed to pack some cool new things in there – which you can read about in the New Features guide.

I’ll be doing some blog posts and videos on the 18c content soon, but here’s something to whet your appetite

Learning is not a spectator sport

Connor McDonald's Oracle blog

18c