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%':
1
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 …).
[…] Go to Source (Comments & like on source blog please) […]
Pingback by Jonathan Lewis : 12c on Partition tables rebuild | Database Scene — June 26, 2013 @ 1:51 am BST Jun 26,2013 |