If you ever wondered how you can convert XML information in v$cell_config
on Exadata database servers into relational rows and columns so you can get a nice view of the cell configuration without going into the cell itself then here is a nice piece of SQL which does the job. I'm using celldisks
as an example and selecting only a number of column so the output fits nicely on the screen:
SQL> select cellname,
5 round(freeSpace/power(1024,3), 2) freeSpace,
6 round(disk_size/power(1024,3), 2) disk_size
7 from (
8 select cellname, XMLTYPE.createXML(confval) confval
9 from v$cell_config
Here is a quick SQL which I sometimes use to show people disks and their respective partners (grouped by a failure group) inside an ASM disk group every time I need to explain both of these concepts.
An example output from a quarter rack Exadata with a normal redundancy disk group:
SQL> column p format a80
SQL> variable group_number number
SQL> exec :group_number := 1;
PL/SQL procedure successfully completed.
SQL> select d||' => '||listagg(p, ',') within group (order by p) p
select ad1.failgroup||'('||to_char(ad1.disk_number, 'fm000')||')' d,
ad2.failgroup||'('||listagg(to_char(p.number_kfdpartner, 'fm000'), ',') within group (order by ad1.disk_number)||')' (more...)
One of the ways you can control how many mirror copies ASM keeps on disk is via diskgroup templates.
This presents an interesting question -- can you turn your normal redundancy disk group into a high redundancy by using a diskgroup template with high
protection attribute thus telling ASM to use triple mirroring for related files?
I'll start by creating a diskgroup:
SQL> create diskgroup data normal redundancy
2 disk '/dev/sdb1', '/dev/sdc1', '/dev/sdd1';
I'm not explicitly specifying any failgroups, each disk will end up in it's own FG and triple-mirroring will spread extents across all three disks. Let's (more...)
Serial direct path reads were first introduced in Oracle 11G which were noticed by many customers (in both positive and negative ways) who upgraded from the earlier releases. Back then I did a quick write up
on the subject to try and tackle some of the key variables which affect the behavior.
To recap, the following were observed for 11G:
- Serial direct path reads start at _small_table_threshold*5.
- Serial direct path reads stop when 50% of the table blocks are cached.
- Serial direct path reads stop when 25% of the table blocks are dirty.
Since then, many people noticed that 11GR2 (more...)
Reference partitioning combined with dbms_redefinition
can result in a perpetually disabled foreign key constraint (I'm using 126.96.36.199 as an example):
SQL> create table p (
2 n number primary key
3 ) partition by list (n)
5 partition p1 values (1)
SQL> create table r1 (n number not null);
SQL> create table r2 (
2 n number not null,
3 constraint fk_r$n foreign key (n) referencing p (n)
4 ) partition by reference (fk_r$n);
SQL> exec dbms_redefinition.start_redef_table(user, 'R1', 'R2', options_flag => dbms_redefinition.cons_use_rowid);
PL/SQL procedure successfully (more...)