Up until now I had only installed base versions of Oracle, but this weekend I decided to install the 11.1.0.7 patch. It was a rough ride, with some really weird error messages ("DBUA thinks this is a Rerun operation" and asking to remove a file which wasn't there). Developers shouldn't do these kind of things I suppose, especially developers who don't fully read the documentation... After doing the required reading and leaving the GUI-tools, everything went fine. And on the plus side, I got a little acquainted with the new ADRCI tool, which wasn't too bad at all. Here is the proof that it worked:
rwijk@ORA11GR1> select * from v$version where rownum = 1
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
1 row selected.
First things I checked were some of the 11.1.0.6 oddities I had seen and have blogged about in the past. I will list them here.
Reference partitioning
In the comment section Eugene - probably working for Oracle - told me that the problem with ORA-14400: inserted partition key does not map to any partition instead of the regular ORA-02291 foreign key violation, was fixed. So now is the time to see if this is really true. In the next SQL*Plus listing you'll see a parent table (notas), a child table ("nota_regels") and a grand child table ("nota_regel_details") being created with some content, showing how reference partitioning works. Please note the last error message.
rwijk@ORA11GR1> create table notasResolved! In 11.1.0.6 this was a ORA-14400 message. And it's much more convenient to just report issues on your blog instead of raising a service request and being told it is a feature, not a bug. Oracle should do that more often :-)
2 ( id number(10)
3 , klantnummer number(10)
4 , bedrag number(10,2)
5 , datum date
6 , constraint nta_pk primary key (id)
7 )
8 partition by range (datum)
9 ( partition partitie_voor_15_dec_2007 values less than (date '2007-12-15')
10 , partition rest values less than (maxvalue)
11 )
12 /
Table created.
rwijk@ORA11GR1> create table nota_regels
2 ( id number(10) primary key
3 , nta_id number(10) not null
4 , bedrag number(10,2)
5 , aantal number(10)
6 , constraint nrl_nta_fk foreign key (nta_id) references notas(id)
7 )
8 partition by reference (nrl_nta_fk)
9 /
Table created.
rwijk@ORA11GR1> pause
rwijk@ORA11GR1> insert into notas values (1,12345,678.90,date '2007-12-12')
2 /
1 row created.
rwijk@ORA11GR1> insert into notas values (2,12345,56.78,date '2007-12-12')
2 /
1 row created.
rwijk@ORA11GR1> insert into notas values (3,12345,9876.54,date '2007-12-13')
2 /
1 row created.
rwijk@ORA11GR1> insert into notas values (4,12345,123.54,date '2007-12-18')
2 /
1 row created.
rwijk@ORA11GR1> insert into nota_regels values (1,1,500,1)
2 /
1 row created.
rwijk@ORA11GR1> insert into nota_regels values (2,3,178.90,1)
2 /
1 row created.
rwijk@ORA11GR1> insert into nota_regels values (3,4,123.45,2)
2 /
1 row created.
rwijk@ORA11GR1> select partition_name
2 , high_value
3 from user_tab_partitions
4 where table_name = 'NOTA_REGELS'
5 order by partition_position
6 /
PARTITION_NAME HIGH_VALUE
------------------------------ ---------------------------------------------------------------------------
PARTITIE_VOOR_15_DEC_2007
REST
2 rows selected.
rwijk@ORA11GR1> select *
2 from nota_regels partition (partitie_voor_15_dec_2007)
3 /
ID NTA_ID BEDRAG AANTAL
---------- ---------- ---------- ----------
1 1 500 1
2 3 178.9 1
2 rows selected.
rwijk@ORA11GR1> select *
2 from nota_regels partition (rest)
3 /
ID NTA_ID BEDRAG AANTAL
---------- ---------- ---------- ----------
3 4 123.45 2
1 row selected.
rwijk@ORA11GR1> pause
rwijk@ORA11GR1> create table nota_regel_details
2 ( id number(10) primary key
3 , nrl_id number(10) not null
4 , bedrag number(10,2)
5 , constraint nrd_nrl_fk foreign key (nrl_id) references nota_regels(id)
6 )
7 partition by reference (nrd_nrl_fk)
8 /
Table created.
rwijk@ORA11GR1> insert into nota_regel_details values (1,1,100)
2 /
1 row created.
rwijk@ORA11GR1> insert into nota_regel_details values (2,1,50)
2 /
1 row created.
rwijk@ORA11GR1> insert into nota_regel_details values (3,1,250)
2 /
1 row created.
rwijk@ORA11GR1> insert into nota_regel_details values (4,3,100)
2 /
1 row created.
rwijk@ORA11GR1> insert into nota_regel_details values (5,3,200)
2 /
1 row created.
rwijk@ORA11GR1> select partition_name
2 , high_value
3 from user_tab_partitions
4 where table_name = 'NOTA_REGEL_DETAILS'
5 order by partition_position
6 /
PARTITION_NAME HIGH_VALUE
------------------------------ ---------------------------------------------------------------------------
PARTITIE_VOOR_15_DEC_2007
REST
2 rows selected.
rwijk@ORA11GR1> select *
2 from nota_regel_details partition (partitie_voor_15_dec_2007)
3 /
ID NRL_ID BEDRAG
---------- ---------- ----------
1 1 100
2 1 50
3 1 250
3 rows selected.
rwijk@ORA11GR1> select *
2 from nota_regel_details partition (rest)
3 /
ID NRL_ID BEDRAG
---------- ---------- ----------
4 3 100
5 3 200
2 rows selected.
rwijk@ORA11GR1> pause
rwijk@ORA11GR1> insert into nota_regel_details values (6,4,200)
2 /
insert into nota_regel_details values (6,4,200)
*
ERROR at line 1:
ORA-02291: integrity constraint (RWIJK.NRD_NRL_FK) violated - parent key not found
Fine Grained Dependency Tracking
I had a look around in the 11.1.0.7 data dictionary, but I still could not find a place where the column dependencies are recorded in the USER_% views.
Native compilation
Here the issue was that the data dictionary tells you a procedure is natively compiled, where in fact it is not, because of a wrong PLSQL_OPTIMIZE_LEVEL setting. In 11.1.0.7:
rwijk@ORA11GR1> alter session set plsql_warnings = 'enable:all'So unfortunately, this one is not resolved yet.
2 /
Session altered.
rwijk@ORA11GR1> create procedure p1
2 is
3 begin
4 for i in 1..100000000
5 loop
6 null;
7 end loop
8 ;
9 dbms_output.put_line('p1');
10 end;
11 /
Procedure created.
rwijk@ORA11GR1> alter session set plsql_optimize_level = 1
2 /
Session altered.
rwijk@ORA11GR1> alter session set plsql_code_type = native
2 /
Session altered.
rwijk@ORA11GR1> create procedure p2
2 is
3 begin
4 for i in 1..100000000
5 loop
6 null;
7 end loop
8 ;
9 dbms_output.put_line('p2');
10 end;
11 /
SP2-0804: Procedure created with compilation warnings
rwijk@ORA11GR1> show err
Errors for PROCEDURE P2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLW-06014: PLSQL_OPTIMIZE_LEVEL <= 1 turns off native code
generation
rwijk@ORA11GR1> alter session set plsql_optimize_level = 2
2 /
Session altered.
rwijk@ORA11GR1> create procedure p3
2 is
3 begin
4 for i in 1..100000000
5 loop
6 null;
7 end loop
8 ;
9 dbms_output.put_line('p3');
10 end;
11 /
Procedure created.
rwijk@ORA11GR1> select name
2 , type
3 , plsql_code_type
4 , plsql_optimize_level
5 , plsql_warnings
6 from user_plsql_object_settings
7 where name like 'P_'
8 order by name
9 /
NAME TYPE PLSQL_CODE_TYPE PLSQL_OPTIMIZE_LEVEL PLSQL_WARNINGS
------------------------------ ------------ ------------------------------ -------------------- ------------------------------
P1 PROCEDURE INTERPRETED 2 ENABLE:ALL
P2 PROCEDURE NATIVE 1 ENABLE:ALL
P3 PROCEDURE NATIVE 2 ENABLE:ALL
3 rows selected.
String aggregation using the model clause
Another thing I was a little bit worried about, was the ability to do string aggregation with the model clause. It works in 10.2.0.3, but it broke in 10.2.0.4. With some bad luck, Oracle could have implemented the same breaking code from 11.1.0.6 to 11.1.0.7, but luckily:
rwijk@ORA11GR1> select deptno
2 , rtrim(ename,',') enames
3 from ( select deptno
4 , ename
5 , rn
6 from emp
7 model
8 partition by (deptno)
9 dimension by (row_number() over
10 (partition by deptno order by ename) rn
11 )
12 measures (cast(ename as varchar2(40)) ename)
13 rules
14 ( ename[any] order by rn desc = ename[cv()]||','||ename[cv()+1]
15 )
16 )
17 where rn = 1
18 order by deptno
19 /
DEPTNO ENAMES
---------- ----------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
3 rows selected.
Something else has been resolved as well, but that's something for a new post ...
Hi Rob,
ReplyDeleteI did some research in this area a few months ago. The FGDT information is stored in sys.dependency$.d_attrs. In the value Oracle seems to bitwise encode the columns that are used.
For instance if you have table T with three columns C1, C2 and C3. And you create view V as select C1,C3 from T. Then you are using the first and third column. This is encoded as power(2,1) + power(2,3) which is 10, which (in RAW) maps to hexidecimal value 'A'. You will see a raw-value in d_attrs that ends with 'A'.
I have not yet discovered what the role of the d_reason column value is playing in this area. Would be nice if Oracle would document this...
Also: you should read the white paper of Bryn Llewellyn presented at Oracle openworld. He has a section in there that is very important to understand if you want to fully exploit the FGDT feature. It seems that you have to always fully qualify all embedded bind variables inside your sql-text. Read it: he explains it in a clear way.
Toon
Hi Toon,
ReplyDeleteThat's excellent! Now we can create our own DBA_DEPENDENCY_COLUMNS view if we wanted to. Food for another blogpost :-)
And I just read the first part of Bryn's white paper and yes it's very interesting. Thanks for that as well.
Groet,
Rob.