Though not officially supported, it is possible to migrate data from Oracle8i to 11gR1. Using the standard export/import the migration is normally quite painless, espcially when done one schema at a time.
For web based apps, the following are some issues to contend with:
1. Security
A new privilege has come in 11g that might bite you if you use utl_tcp (utl_smtp, utl_mail, utl_http and other tcp/ip based tools). The new dbms_network_acl_admin package allows fine grained control over access to the network. If you just want to migrate and get things working, the following will give a schema full network access from within the database:
BEGIN
-- required for Oracle11 to access network
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'myapp.xml',
description => 'Network permissions for *',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'connect');
END;
/
commit;
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'myapp.xml',
host => '*');
END;
/
commit;
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('piction.xml','SCOTT', TRUE, 'resolve');
commit;
SELECT DECODE( DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE( 'myapp.xml', 'SCOTT', 'resolve'), 1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE FROM DUAL;
2. SQL Aliases when using objects
Any select statement that references an object needs an alias to reference it, and an alias name to make it unique.
So if your code was:
select myimage.source.localdata from myimage;
should be changed to
select u.myimage.source.localdata blb from myimage u;
3. Ordering
In 8i to 10G when Oracle retrieved data from an index it kept it sorted. In fact, it was a useful tip to remove ordering and retrieve data just from the index.
From 11G this cannot be guaranteed. The optimizer now has new algorithms for doing queries and it might not mean the data comes back sorted.
The solution is to put an order by on the statements.
Workaround? Put an order by on SQL Statements. Yep, its painful.
At this point of time I am not aware of any init.ora parameter or lingusitic setting that forces a sort.
The SQL Standard does say that to ensure ordering using an Order by. This doesn't help if you have 3rd party apps or if you can track down the source for your app.
I don't think Oracle realise that this one cosmetic issue will cause most sites to not upgrade, as its this cosmetic issue that causes users the most heartache.
This issue has impacted upgrades I have done and made me rethink coding strategies when trying to build an app that works across multiple Oracle releases.
The Oracle Multimedia Blog
Monday, April 28, 2008
Subscribe to:
Post Comments (Atom)
Posts
5 comments:
Marcel,
I'd like you to expand your comments on "order by" and indexed access paths.
Are your starting comments ("table will be accessed in the order of index ... can eliminate the order by") made with reference only to a single table accessed by index ? If so you should still include the "order by" because (a) if it's not needed the "sort order by" operation will not happen anyway and (b) sometimes the "sort order by" operation is still needed.
It was never safe to assume that you could drop the "order by" clause, even in 8i (or 7).
As far as the 11g enhancements go, do you have a test case to recreate an example of the change in behaviour you are talking about. There's been a "prefetch" feature since 9i which I believed ought to have made it possible to produce the disorder you suggest - but I've never been able to make it happen.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
Jonathon,
To answer your points. When I first did beta testing in 11g a year ago and noticed the issue happening, I reviewed the whole code base and put the order by's in on anything that looked like it needed it.
What I didn't realise was that some parts of our code base have a user configured interface where they can added their own queries. When these stopped behaving as they expected, we went in and changed them (which was a quick fix).
Your point is well stated in that it is never safe to assume you can drop the order by. I still remember from my Uni days in the mid 80's being taught the line, that to ensure ordering you have to use the order by.
Dealing in the real world though, I have seen a lot of developers forget to put in order by's or are not aware they are needed. This is because when they write their statements in their simple dev environment, the queries they run come back sorted (because in most cases they have 10 rows, and put the data in sorted order, and it comes out that way). They don't realise it can change. In fact, I bet if you ask newbie developers they will have the view that the order in which you insert the data is the order in which it comes out. So if you put it in sorted it comes out sorted.
So what is needed are DBAs to review code and educate developers to put the order by's in now, before they upgrade.
As for a test case, I'll drag out some old Oracle8i virtualisation and get the statements out and come up with a bona-fide test case.
Due to customer confidentiality I had to rework the statements into a simpler test case, but they highlight the issue. Note, there is no index in this case, its exactly as shown. I will accept a slap on the wrist if this example is not in the spirit of the original post.
---- Oracle 8i
SQL>create table test (col1 varchar2(100));
SQL>insert into test values ('BBB');
SQL>insert into test values ('AAA');
SQL>insert into test values ('KKK');
SQL>insert into test values ('CCC');
SQL>commit;
SQL>select distinct col1 from test;
COL1
---------------------------------------------------
AAA
BBB
CCC
KKK
4 rows selected.
SQL>show parameter opt
NAME TYPE VALUE
------------------------------------ ------- ----------
object_cache_optimal_size integer 102400
optimizer_features_enable string 8.1.7
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 80000
optimizer_mode string FIRST_ROWS
optimizer_percent_parallel integer 0
----- Oracle 11
SQL> select distinct col1 from test;
COL1
-------------------------------------------------------------------------
BBB
AAA
CCC
KKK
4 rows selected.
SQL> show parameter opt
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
filesystemio_options string none
object_cache_optimal_size integer 102400
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string FIRST_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
plsql_optimize_level integer 2
Marcel,
Sorry about the delay in responding.
The bit I was interested in was your comment:
In 8i to 10G when Oracle retrieved data from an index it kept it sorted. In fact, it was a useful tip to remove ordering and retrieve data just from the index.
From 11G this cannot be guaranteed. The optimizer now has new algorithms for doing queries and it might not mean the data comes back sorted.
The comment about retrieving the data through an index and not getting the results in order in 11g is the interesting one.
Your example is the side effect of a change to using a "hash group by" for the distinct rather "sort group by" - and could appear in 10g.
My concern is that 11g offers both NLJ prefetching and NLJ batching - and one (or both) of those might make it possible to change the raw order of the rowsource from the join - because of a possible change in the way index rowid access might happen - resulting in more work in subsequent operations.
For me, the annoyance of this ordering has to do with core DBA work. I run the statement "select username from dba_users" a lot of the time (across different customer databases). Now in 10g I have to put the order by in. Accessing a lot of the DBA views now requires an order by which wasn't needed before, and its extra typing. A trivial issue, but one that gets annoying over time. But one just has to live with it.
Post a Comment