Oracle 11i new features

1. PL/SQL native compilation. In oracle 10g, there is an option to compile pl/sql with c compiler and so it can execute directly without interpretion, but it needs a c compiler on your system. In oracle 11i, it actually can compile pl/sql in a native way without the needs of a local c compiler. One initialization parameter, plsql_code_type, can be set to be either native or interpreted to change the way pl/sql is compiled.
2. Adaptive cursor sharing. You can set cursor_sharing parameter to be 'similar' to make oracle parsing to share parsing plan between similar queries. But the problem is when data are skewed. The execution plan used for one value may not be good for another value at all. What oracle 11i does now is that it has a few excution plans for a query, when it finds one plan is too expensive, then it will start to look at others.
3 Server result cache, which is actually part of the shared pool in the SGA. the initialization parameter result_cache_max_size is the one to setup the size. New package DBMS_RESULT_CACHE provides various procedurs to aminister the result cache feature. v$result_cache_* views are the place to look at the success of a cached SQL query or a PL/SQL function by determining the cache-hit scucess of the query of function.

Query to get long executed operation/sql

select 'OSUSER : 's.osuser a ,'OSPROC : 's.process a ,'ORAUSER : 'sl.username a ,'TARGET : 'sl.target a ,'SOFAR : 'sl.sofar a ,'TOTALWRK: 'sl.totalwork a ,'START : 'to_char(sl.start_time,'YY-MON-DD HH24:MI:SS') a ,'LASTUPD : 'to_char(sl.last_update_time,'YY-MON-DD HH24:MI:SS') a ,'ELAPSED : 'round(sl.elapsed_seconds/60,2) a ,'TMREMAIN: 'round(sl.time_remaining/60,2) a ,'CONTEXT : 'sl.context a ,'MESSAGE : 'sl.message afrom v$session_longops sl ,v$session swhere sl.target not like 'SYS%'and sl.sid = s.sidand sl.serial# = s.serial#order by start_time asc

Dropping a function index make packages using thre related table invalid

But if just drop a normal index will not invalidate packages related.

Query to find who is locking my object

set lines 120 hea on trunc on pages 50
col sid for 9999
col serial# for 9999999
col osuser for a10
col owner for a10
col module for a10
col username for a10
col type for a12
select sid
, serial#
, osuser
, username
, b.owner
, b.type
, module
, action act
from v$session v
,dba_ddl_locks b
where b.session_id = v.sid
and b.name =upper('&object_name')

Funny thing when use a package global variable through select

update ( select /*+ ALL_ROWS */ t.discography_intf_id, t.intf_status_id, t.err_mesg from da_discography_intf t, artist t1 where t.primary_artist_id = t1.artist_id and t.primary_artist_name <> t1.display_name ) set intf_status_id = (select da_discography_utl.get_intf_status_id( da_discography_constants.gc_intf_status_load_err) from dual), err_mesg = (select da_discography_constants.gc_intf_status_wrong_art_msg from dual);

This query causes ora-06502 error. My guess is when you use select from dual to get a constant value, it loses the sense of the length of the value, so will give a numeric error.
So don't select a function with a global constant.

Lag function can make query to have a default order by

select album_id, client_id, lag(client_id) over (partition by album_id order by submit_ts asc) lag_client_idfrom stat.submit_factwhere album_id = 440556

In this query, I did not put a 'order by' at the end of the query. But because I use lag function, it will make the whole query order by submit_ts. It makes sense.

Interesting Query Plan Change

I just observed that a query can have different execution plans when it runs as an anonymous plsql and runs in a store procedure.

select * from da_discography_intf t where exists (select null from album where album_id = t.album_id) and not exists (select null from album where album_name <> t.album_name)

When in anonymous plsql, it uses anti-hash, but it uses nest loop when in store procedure.
After I give it a 'ALL_ROWS' hint, it started to use 'anti-hash join'.