12c

The news is out that 12c is now available for download (Code, Docs and Tutorials). There are plenty of nice little bits in it, and several important additions or enhancements to the optimizer, but there’s one feature that might prove to be very popular:

SQL> alter table p1 move partition solo ONLINE;

Table altered.

Now, I usually make a big fuss about considering whether there may be highly undesirable side effects on rebuilding a table – but sometimes it is necessary, and it might be really nice if you could rebuild it online. In 12c you can, if you’re rebuilding a single partition of a partitioned table. So if you’ve already paid for the partitioning option, maybe you need to look into changing some of your simple tables into partitioned tables with a single partition and local indexes.

Like the “online index rebuild”, the mechanism does depend on brief locking events being taken on the partition as the rebuilds start and finish, so that a journal table can be built and then applied properly. I would say that it’s going to generate a load of redo as it does the necessary “insert append” operations and direct path writes – but there’s been a lot of change to the log writer process (and its “workers”, and its statistics) and so the impact there may have been reduced.

If you’re wondering about redo – here’s a flavour of the new statistics (system level) showing you the redo information from my small partition move (I really like the breakdown of writes by size):

redo entries                                                             1,096
redo size                                                            2,093,852
redo size for direct writes                                          1,878,600
redo wastage                                                             9,644
redo writes                                                                 36
redo writes (group 0)                                                       33
redo writes (group 1)                                                        3
redo writes adaptive all                                                    36
redo writes adaptive worker                                                 36
redo blocks written                                                      4,242
redo blocks written (group 0)                                            2,299
redo blocks written (group 1)                                            1,943
redo write size count (   4KB)                                              21
redo write size count (   8KB)                                               5
redo write size count (  16KB)                                               4
redo write size count (  32KB)                                               2
redo write size count ( 128KB)                                               1
redo write size count ( 256KB)                                               1
redo write size count (1024KB)                                               2
redo write time                                                            108
redo blocks checksummed by FG (exclusive)                                3,570
redo subscn max counts                                                      35
redo synch time (usec)                                                     764
redo synch time overhead (usec)                                             39
redo synch time overhead count (  2ms)                                       1
redo synch writes                                                            1
redo write info find                                                         1
[/sourecode]

And the results of a query against v$session_wait_history for sessions with names like '%LG%':



PROGRAM                          EVENT
-------------------------------- ----------------------------------------
oracle@linux01 (LGWR)            rdbms ipc message
                                 log file parallel write
                                 rdbms ipc message
                                 log file parallel write
                                 rdbms ipc message
                                 log file parallel write
                                 rdbms ipc message
                                 log file parallel write
                                 rdbms ipc message
                                 log file parallel write

PROGRAM                          EVENT
-------------------------------- ----------------------------------------
oracle@linux01 (LG00)            log file parallel write
                                 log file parallel write
                                 log file parallel write
                                 LGWR worker group idle
                                 log file parallel write
                                 LGWR worker group idle
                                 log file parallel write
                                 LGWR worker group idle
                                 log file parallel write
                                 LGWR worker group idle

PROGRAM                          EVENT
-------------------------------- ----------------------------------------
oracle@linux01 (LG01)            LGWR worker group ordering
                                 log file parallel write
                                 LGWR worker group idle
                                 log file parallel write
                                 LGWR worker group idle
                                 log file parallel write
                                 LGWR worker group idle
                                 LGWR worker group ordering
                                 log file parallel write
                                 LGWR worker group idle

Update

One thing leads to another – you may have heard that 12c can use threading under Unix – i.e. a single operating system process can run several Oracle processes. You enable this by setting the parameter threaded_execution to true. To show the effect of this, the view v$process has a new column stid (system thread id) to go with the spid (system process id); so you can run a simple query like the following to see which threads are sharing which processes:

select
	spid, stid, program, background
from
	V$process
where
	spid != stid
and	spid in (
		select spid
		from v$process
		group by spid
		having count(*) > 1
	)
order by
	spid, stid, program
;

SPID       STID       PROGRAM                        B
---------- ---------- ------------------------------ -
813        817        oracle@linux01 (GEN0)          1
           820        oracle@linux01 (MMAN)          1
           834        oracle@linux01 (DBRM)          1
           844        oracle@linux01 (LGWR)          1
           847        oracle@linux01 (CKPT)          1
           850        oracle@linux01 (LG00)          1
           853        oracle@linux01 (LG01)          1
           856        oracle@linux01 (SMON)          1
           862        oracle@linux01 (LREG)          1

827        1022       oracle@linux01
           1025       oracle@linux01 (QM02)          1
           1031       oracle@linux01 (Q002)          1
           1034       oracle@linux01 (Q003)          1
           831        oracle@linux01 (DIAG)          1
           837        oracle@linux01 (DIA0)          1
           859        oracle@linux01 (RECO)          1
           865        oracle@linux01 (MMON)          1
           868        oracle@linux01 (MMNL)          1
           871        oracle@linux01 (D000)
           874        oracle@linux01 (S000)
           877        oracle@linux01 (N000)
           898        oracle@linux01 (P000)
           901        oracle@linux01 (P001)
           904        oracle@linux01 (TMON)          1
           907        oracle@linux01 (TT00)          1
           910        oracle@linux01 (SMCO)          1
           913        oracle@linux01 (FBDA)          1
           916        oracle@linux01 (AQPC)          1

It’s interesting that the sample of output I’ve supplied shows lgwr, lg00 and lg01 all sharing the same process – and sharing it with a number of other background processes; I can’t help feeling that this is a reason to be a little cautious about taking advantage of threading. (Perhaps that’s why it’s off by default.)

While we’re looking at v$process, 11g gave use the tracefile column, showing the filename that would be used if you enabled tracing for a process. Two changes – the file name now includes the thread id, e.g.: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2929_1058.trc (that’s spid first, stid second), and the view now includes a column called traceid, which displays the value you’ve set for the tracefile_identifier (alter session …).

Update 27th July 2014

Martin Bach has published a note with a few extra details on threaded execution which you may find useful.