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.
[…] 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 |
[…] При этом на *nix системах установка параметра threaded_execution = true может привести к выполнению LGWR вместе с рабочими процессами одним процессом ОС, что, видимо, совсем неинтересно в плане выигрыша скорости записи логов — см. Update в Jonathan Lewis.12c […]
Pingback by Дочерние процессы LGWR 12c | Oracle mechanics — July 12, 2014 @ 10:32 am BST Jul 12,2014 |