ENABLE_DDL_LOGGING
With 11g you can now log ddl into your alert.log (which I thought was cool)
SQL> alter system set enable_ddl_logging=true;
SQL>
SQL> create table x (y number, z timestamp);
following is in trace/alert_MYDB1.log
..
..
Sat Apr 11 12:20:48 2010
create table x (y number, z timestamp)
..
..
Saturday, April 17, 2010
Thursday, April 15, 2010
9i/10g -> 11g timezone issue
Here's a quick way documented on Metalink ID 396670.1. They take no responsibility of the code and neither do I!
Please make sure you review the code and use it at your own risk.
There are 3 scripts:
1. prepare_zuv9.sql
2. restore_zuv9.sql
3. clean_zuv9.sql
You can use Script 1 to prepare look for all tables that might have potential issue with upgrading to 11g. (Note: if the tables are huge, you might want to use merge or come up with other solutions to make sure your outage window remains small)
Once you upgrade the second script is used to restore the data back to the tables and the 3rd script to clean up all these temp tables.
Here are the scripts.
prepare_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'CREATE TABLE '|| r1.table_owner || '.' || 'BACKUP_';
stmt := stmt || r1.object_id || '_' || r1.column_id || ' (ORIG_ROWID ';
stmt := stmt || 'ROWID PRIMARY KEY, SAVED_VALUE VARCHAR2(256))';
execute immediate stmt;
ln := 'Backup table ' || r1.table_owner || '.' || 'BACKUP_';
ln := ln || r1.object_id || '_' || r1.column_id || ' for ' || r1.table_owner;
ln := ln || '.' || r1.table_name || '(' || r1.column_name || ') created';
dbms_output.put (ln);
stmt := 'INSERT INTO ' || r1.table_owner || '.' || 'BACKUP_' || r1.object_id;
stmt := stmt || '_' || r1.column_id || ' SELECT ROWID, TO_CHAR(' ;
stmt := stmt || r1.column_name || ', ''YYYY-MM-DD HH24:MI:SSXFF TZR'') ';
stmt := stmt || 'FROM ' || r1.table_owner || '.' || r1.table_name||' WHERE ';
stmt := stmt || 'UPPER(TO_CHAR(' || r1.column_name || ',''TZR'')) ';
stmt := stmt || 'IN (SELECT UPPER(TIME_ZONE_NAME) FROM ';
stmt := stmt || 'SYS.SYS_TZUV2_AFFECTED_REGIONS)';
execute immediate stmt;
dbms_output.put_line (', ' || SQL%ROWCOUNT || ' row(s) inserted.');
end loop;
end;
/
restore_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'UPDATE ' || r1.table_owner || '.' || r1.table_name || ' T ';
stmt := stmt || 'SET T.' || r1.column_name||'=(SELECT TO_TIMESTAMP_TZ';
stmt := stmt || '(T1.SAVED_VALUE, ''YYYY-MM-DD HH24:MI:SSXFF TZR'') FROM ';
stmt := stmt || r1.table_owner || '.BACKUP_' || r1.object_id || '_';
stmt := stmt || r1.column_id || ' T1 WHERE T.ROWID=T1.ORIG_ROWID) ';
stmt := stmt || 'WHERE EXISTS (SELECT ORIG_ROWID FROM ' || r1.table_owner ;
stmt := stmt || '.BACKUP_' || r1.object_id || '_'|| r1.column_id;
stmt := stmt || ' T1 WHERE T.ROWID=T1.ORIG_ROWID)';
execute immediate stmt;
ln := SQL%ROWCOUNT || ' row(s) in column ' || r1.table_owner || '.';
ln := ln || r1.table_name || '(' || r1.column_name || ') updated.';
dbms_output.put_line (ln);
end loop;
end;
/
clean_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'DROP TABLE ' || r1.table_owner || '.BACKUP_';
stmt := stmt || r1.object_id || '_' || r1.column_id;
execute immediate stmt;
ln := 'Backup table ' || r1.table_owner || '.BACKUP_';
ln := ln || r1.object_id || '_' || r1.column_id || ' dropped.';
dbms_output.put_line (ln);
end loop;
execute immediate 'drop table sys.sys_tzuv2_temptab';
dbms_output.put_line ('Table SYS.SYS_TZUV2_TEMPTAB dropped.');
execute immediate 'drop table sys.sys_tzuv2_affected_regions';
dbms_output.put_line ('Table SYS.SYS_TZUV2_AFFECTED_REGIONS dropped.');
end;
/
Here's how the RESTORE output would look like:
SQL> @restore_zuv9.sql
415 row(s) in column
QA4.VENDORPREFERENCN_FV(SET_LAST_UPDATE_DATE) updated.
415 row(s) in column
QA4.VENDORPREFERENCES_FV(SET_CREATION_DATE) updated.
518 row(s) in column
QA4.VENDORPREFER_FV(PREFERENCE_START_DATE) updated.
518 row(s) in column
QA4.VENDORPREFERE_FV(PREFERENCE_END_DATE) updated.
518 row(s) in column QA4.VENDOCEATTRBEAN_FV(LAST_UPDATE_DATE)
updated.
18 row(s) in column QA4.TIMER_SERVICE_JOURNAL(CREATION_TIME)
updated.
834 row(s) in column QA4.FR_ALL_NLSTG(SERVICE_START_TIME)
updated.
834 row(s) in column QA4.FR_ALL_VERTICALS_NLSTG(SERVICE_END_TIME)
updated.
1492 row(s) in column QA4.FR_ALL_DATA(SERVICE_START_TIME)
updated.
1492 row(s) in column QA4.FR_ALL_DATA(SERVICE_END_TIME)
updated.
49 row(s) in column QA4.COST_DETAIL(START_DATE) updated.
49 row(s) in column QA4.COST_DETAIL(END_DATE) updated.
PL/SQL procedure successfully completed.
SQL>
Please make sure you review the code and use it at your own risk.
There are 3 scripts:
1. prepare_zuv9.sql
2. restore_zuv9.sql
3. clean_zuv9.sql
You can use Script 1 to prepare look for all tables that might have potential issue with upgrading to 11g. (Note: if the tables are huge, you might want to use merge or come up with other solutions to make sure your outage window remains small)
Once you upgrade the second script is used to restore the data back to the tables and the 3rd script to clean up all these temp tables.
Here are the scripts.
prepare_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'CREATE TABLE '|| r1.table_owner || '.' || 'BACKUP_';
stmt := stmt || r1.object_id || '_' || r1.column_id || ' (ORIG_ROWID ';
stmt := stmt || 'ROWID PRIMARY KEY, SAVED_VALUE VARCHAR2(256))';
execute immediate stmt;
ln := 'Backup table ' || r1.table_owner || '.' || 'BACKUP_';
ln := ln || r1.object_id || '_' || r1.column_id || ' for ' || r1.table_owner;
ln := ln || '.' || r1.table_name || '(' || r1.column_name || ') created';
dbms_output.put (ln);
stmt := 'INSERT INTO ' || r1.table_owner || '.' || 'BACKUP_' || r1.object_id;
stmt := stmt || '_' || r1.column_id || ' SELECT ROWID, TO_CHAR(' ;
stmt := stmt || r1.column_name || ', ''YYYY-MM-DD HH24:MI:SSXFF TZR'') ';
stmt := stmt || 'FROM ' || r1.table_owner || '.' || r1.table_name||' WHERE ';
stmt := stmt || 'UPPER(TO_CHAR(' || r1.column_name || ',''TZR'')) ';
stmt := stmt || 'IN (SELECT UPPER(TIME_ZONE_NAME) FROM ';
stmt := stmt || 'SYS.SYS_TZUV2_AFFECTED_REGIONS)';
execute immediate stmt;
dbms_output.put_line (', ' || SQL%ROWCOUNT || ' row(s) inserted.');
end loop;
end;
/
restore_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'UPDATE ' || r1.table_owner || '.' || r1.table_name || ' T ';
stmt := stmt || 'SET T.' || r1.column_name||'=(SELECT TO_TIMESTAMP_TZ';
stmt := stmt || '(T1.SAVED_VALUE, ''YYYY-MM-DD HH24:MI:SSXFF TZR'') FROM ';
stmt := stmt || r1.table_owner || '.BACKUP_' || r1.object_id || '_';
stmt := stmt || r1.column_id || ' T1 WHERE T.ROWID=T1.ORIG_ROWID) ';
stmt := stmt || 'WHERE EXISTS (SELECT ORIG_ROWID FROM ' || r1.table_owner ;
stmt := stmt || '.BACKUP_' || r1.object_id || '_'|| r1.column_id;
stmt := stmt || ' T1 WHERE T.ROWID=T1.ORIG_ROWID)';
execute immediate stmt;
ln := SQL%ROWCOUNT || ' row(s) in column ' || r1.table_owner || '.';
ln := ln || r1.table_name || '(' || r1.column_name || ') updated.';
dbms_output.put_line (ln);
end loop;
end;
/
clean_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'DROP TABLE ' || r1.table_owner || '.BACKUP_';
stmt := stmt || r1.object_id || '_' || r1.column_id;
execute immediate stmt;
ln := 'Backup table ' || r1.table_owner || '.BACKUP_';
ln := ln || r1.object_id || '_' || r1.column_id || ' dropped.';
dbms_output.put_line (ln);
end loop;
execute immediate 'drop table sys.sys_tzuv2_temptab';
dbms_output.put_line ('Table SYS.SYS_TZUV2_TEMPTAB dropped.');
execute immediate 'drop table sys.sys_tzuv2_affected_regions';
dbms_output.put_line ('Table SYS.SYS_TZUV2_AFFECTED_REGIONS dropped.');
end;
/
Here's how the RESTORE output would look like:
SQL> @restore_zuv9.sql
415 row(s) in column
QA4.VENDORPREFERENCN_FV(SET_LAST_UPDATE_DATE) updated.
415 row(s) in column
QA4.VENDORPREFERENCES_FV(SET_CREATION_DATE) updated.
518 row(s) in column
QA4.VENDORPREFER_FV(PREFERENCE_START_DATE) updated.
518 row(s) in column
QA4.VENDORPREFERE_FV(PREFERENCE_END_DATE) updated.
518 row(s) in column QA4.VENDOCEATTRBEAN_FV(LAST_UPDATE_DATE)
updated.
18 row(s) in column QA4.TIMER_SERVICE_JOURNAL(CREATION_TIME)
updated.
834 row(s) in column QA4.FR_ALL_NLSTG(SERVICE_START_TIME)
updated.
834 row(s) in column QA4.FR_ALL_VERTICALS_NLSTG(SERVICE_END_TIME)
updated.
1492 row(s) in column QA4.FR_ALL_DATA(SERVICE_START_TIME)
updated.
1492 row(s) in column QA4.FR_ALL_DATA(SERVICE_END_TIME)
updated.
49 row(s) in column QA4.COST_DETAIL(START_DATE) updated.
49 row(s) in column QA4.COST_DETAIL(END_DATE) updated.
PL/SQL procedure successfully completed.
SQL>
Upgrade 9i to 11g (Manually)
The following for upgrading a 9.2.0.8 DB to 11.2 version on Solaris
Required packges for installing 11g software: (see equivalent)
--------------------------------------------
unixODBC-devel-2.2.11
libaio-devel-0.3.105
elfutils-libeif-devel-0.97
gcc..
..
etc
Install 11g software in new ORACLE HOME...
/apps/oracle/product/db11gR2 happens to be mine..
Note ID: 429825.1 Database Upgrade steps from 9i to 11g:
--------------------------------------------------------
Step 1:
-------
Log in to the system as the owner of the new 11gR2 ORACLE_HOME and copy the following files from the 11gR1 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the $HOME/migration in my case:
mkdir $HOME/migration
cp $ORACLE_BASE/product/db11gR2/rdbms/admin/utlu11*i.sql $HOME/migration
cp $ORACLE_BASE/product/db11gR2/rdbms/admin/utltzuv2.sql $HOME/migration
Step 2:
-------
$ sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/utlrp.sql
Keep record of invalid objects to check after the upgrade to 11g to make sure you are re-compiling any objects that became INVALID during migration.
Step 3:
-------
Deprecated CONNECT Role
CONNECT role has only the CREATE SESSION privilege.
So you need to re-grant these privileges to users who have connect role. This SQL will help you save the result in somewhere:
SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
Step 4:
-------
Create script to save DBLINKS create statements:
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','PUBLIC ')||'DATABASE LINK ' ||
DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||
'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''
||L.HOST||''''||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#
Step 5:
-------
Convert the 9i database from TIMEZONE version 1 to version 4:
Download this interm patch..Extract..opatch apply => very simple
Then this query must result version 4:
SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then CASE COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 END
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
----------
4
If the above doesn't work (which it wouldn't), do the following steps. Depending on how much data you have, you might have to try different techniques to make sure you don't pass the downtime window.
http://oramadness.blogspot.com/2010/04/9i10g-11g-timezone-issue.html
Step 6:
-------
Run the script you extracted before from 11g binaries
spool utlu111i.log
@utlu112i.sql
spool off
This script will give you information about the tablespaces if they need to adjusted according to 11g and also give info about other initialization parameters that need to be modified and also Obsolete/Deprecated ones and also deprecated roles like connect.
Keep the log it will be helpful.
Step 7:
-------
Remove the stats for the dictionary ( you will be gathering them again when you are fully upgraded)
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
Step 8:
-------
Check for invalid and corrupt objects in the db.
Set verify off space 0 line 120 heading off
Set feedback off pages 1000
spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES'
/
spool off
exit;
SQL> @?/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
Make sure there is no invalid objects by query'ing the table INVALID_ROWS.
SQL> select * from invalid_rows;
no rows selected
SQL>
Step 9:
-------
a) Stop the listener for the database:
$ lsnrctl stop
b)Create a new listener in Oracle 11g for this db.
Step 10:
--------
Ensure no files need media recovery or in backup mode:
SELECT * FROM v$recover_file;
SELECT * FROM v$backup WHERE status!='NOT ACTIVE';
Step 11:
--------
Resolve any outstanding unresolved distributed transaction:
SQL> select * from dba_2pc_pending;
If this returns rows you should do the following:
SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
Step 12:
--------
Ensure the users sys and system have 'system' as their default tablespace.
SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');
Step 13:
--------
Ensure that the aud$ is in the system tablespace when auditing is enabled.
SELECT tablespace_name
FROM dba_tables
WHERE table_name='AUD$';
Step 14:
--------
Check whether database has any externally authenticated SSL users.
SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
If any SSL users are found, go thru the upgrade guide for further instructions.
Step 15:
-------
Put the database in noarchivelog mode to minimize the upgrade and finishing in the upgrade window.
Step 16:
-------
Note down the location of datafiles, redo logs, control files.
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT member FROM v$logfile;
After, noting down the the locations, Shutdown the database.
Step 17:
-------
Take cold backup (after shutting down the db and restarting it again)
or
if you have your database in archivelog mode then you can do this,
$ rman target / notcatalog
RMAN>run
{
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/admin/MYDB1/backup/%U';
backup current controlfile;
backup database format '/home/oracle/admin/MYDB1/backup/%U' TAG before_upgrade;
}
Step 18:
--------
Create the SYSAUX tablespace for 11g.
CREATE TABLESPACE SYSAUX
DATAFILE 'sysaux_01.dbf' size 2048M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
Step 19:
-------
Make a backup of the spfile.
Comment out these obsoleted parameters:
LOGMNR_MAX_PERSISTENT_SESSIONS
PLSQL_COMPILER_FLAGS
DDL_WAIT_FOR_LOCKS
Change the following deprecated parameters:
BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
CORE_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
COMMIT_WRITE
INSTANCE_GROUPS
LOG_ARCHIVE_LOCAL_FIRST
PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL)
PLSQL_V2_COMPATIBILITY
REMOTE_OS_AUTHENT
STANDBY_ARCHIVE_DEST
TRANSACTION_LAG attribute (of the CQ_NOTIFICATION$_REG_INFO object)
Set the COMPATIBLE parameter to 10.1.0 if you want to have the option to downgrade. To use all new features of 11g, you need to use
compatible=11.1.0
When done copy the pfile to the new 11g $ORACLE_HOME/dbs
Step 20:
-------
Create .profile11g under oracle user home directory to point to 11g new software and other relevant environment variables.
Step 20:
-------
Update the oratab entry:
/etc/oratab for linux
/var/opt/oracle/oratab for solaris
#ORCL:/u01/oracle/ora9i:Y
ORCL:/u01/oracle/ora11g:Y
Step 21:
========
Upgrading Database to 11gR1...
run .profile11g
Startup the DB in upgrade mode:
------------------------------
cd $HOME/migration
sqlplus '/ as sysdba'
startup UPGRADE
start the upgrade script:
------------------------
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off
At the end, the db will be shutdown by catupgrd.sql script.
Restart the Instance NORMALLY to reinitialize the system parameters for normal operation.
Run the Post-Upgrade Status Tool:
--------------------------------
@?/rdbms/admin/utlu111s.sql
Recompile any remaining stored PL/SQL:
-------------------------------------
@?/rdbms/admin/catuppst.sql
@?/rdbms/admin/utlrp.sql
There may be duplicate objects between SYS and SYSTEM so I followed the Note and dropped system duplicate objects:
You can use this query i wrote to find those duplicates:
select distinct 'drop ' || b.object_type || ' SYSTEM.'||b.object_name || ';'
from all_objects a,
all_objects b
where a.owner = 'SYS'
and b.owner = 'SYSTEM'
and a.object_name = b.object_name
order by 1
/
drop PACKAGE BODY SYSTEM.DBMS_REPCAT_AUTH;
drop PACKAGE SYSTEM.DBMS_REPCAT_AUTH;
drop SYNONYM SYSTEM.CATALOG;
drop SYNONYM SYSTEM.COL;
drop SYNONYM SYSTEM.PUBLICSYN;
drop SYNONYM SYSTEM.SYSCATALOG;
drop SYNONYM SYSTEM.SYSFILES;
drop SYNONYM SYSTEM.TAB;
drop SYNONYM SYSTEM.TABQUOTAS;
drop TABLE SYSTEM.AQ$_SCHEDULES;
drop TABLE SYSTEM.DEF$_AQCALL;
drop TABLE SYSTEM.DEF$_CALLDEST;
drop TABLE SYSTEM.DEF$_DEFAULTDEST;
drop TABLE SYSTEM.DEF$_ERROR;
drop TABLE SYSTEM.DEF$_LOB;
Post Upgrade Steps:
##################
Step 22:
--------
Check listener.ora for any modifications needed to listen on the upgraded DB.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PF11)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /apps/oracle/product/db11gR2)
(SID_NAME = PF11)
)
)
Start the listener:
lsnrctl start
Step 23:
--------
Oracle recommends that you lock all Oracle supplied accounts except for SYS and SYSTEM:
ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;
Step 24:
-------
Change the compatability version to use the new 11g features:
alter system set compatible='11.1.0.6' scope=spfile;
shutdown immediate;
startup;
Step 25:
-------
Now you can gather SYS schema stats.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS', options => 'GATHER', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
You have a cooked 11g database...
Required packges for installing 11g software: (see equivalent)
--------------------------------------------
unixODBC-devel-2.2.11
libaio-devel-0.3.105
elfutils-libeif-devel-0.97
gcc..
..
etc
Install 11g software in new ORACLE HOME...
/apps/oracle/product/db11gR2 happens to be mine..
Note ID: 429825.1 Database Upgrade steps from 9i to 11g:
--------------------------------------------------------
Step 1:
-------
Log in to the system as the owner of the new 11gR2 ORACLE_HOME and copy the following files from the 11gR1 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the $HOME/migration in my case:
mkdir $HOME/migration
cp $ORACLE_BASE/product/db11gR2/rdbms/admin/utlu11*i.sql $HOME/migration
cp $ORACLE_BASE/product/db11gR2/rdbms/admin/utltzuv2.sql $HOME/migration
Step 2:
-------
$ sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/utlrp.sql
Keep record of invalid objects to check after the upgrade to 11g to make sure you are re-compiling any objects that became INVALID during migration.
Step 3:
-------
Deprecated CONNECT Role
CONNECT role has only the CREATE SESSION privilege.
So you need to re-grant these privileges to users who have connect role. This SQL will help you save the result in somewhere:
SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
Step 4:
-------
Create script to save DBLINKS create statements:
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','PUBLIC ')||'DATABASE LINK ' ||
DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||
'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''
||L.HOST||''''||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#
Step 5:
-------
Convert the 9i database from TIMEZONE version 1 to version 4:
Download this interm patch..Extract..opatch apply => very simple
Then this query must result version 4:
SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then CASE COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 END
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
----------
4
If the above doesn't work (which it wouldn't), do the following steps. Depending on how much data you have, you might have to try different techniques to make sure you don't pass the downtime window.
http://oramadness.blogspot.com/2010/04/9i10g-11g-timezone-issue.html
Step 6:
-------
Run the script you extracted before from 11g binaries
spool utlu111i.log
@utlu112i.sql
spool off
This script will give you information about the tablespaces if they need to adjusted according to 11g and also give info about other initialization parameters that need to be modified and also Obsolete/Deprecated ones and also deprecated roles like connect.
Keep the log it will be helpful.
Step 7:
-------
Remove the stats for the dictionary ( you will be gathering them again when you are fully upgraded)
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
Step 8:
-------
Check for invalid and corrupt objects in the db.
Set verify off space 0 line 120 heading off
Set feedback off pages 1000
spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES'
/
spool off
exit;
SQL> @?/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
Make sure there is no invalid objects by query'ing the table INVALID_ROWS.
SQL> select * from invalid_rows;
no rows selected
SQL>
Step 9:
-------
a) Stop the listener for the database:
$ lsnrctl stop
b)Create a new listener in Oracle 11g for this db.
Step 10:
--------
Ensure no files need media recovery or in backup mode:
SELECT * FROM v$recover_file;
SELECT * FROM v$backup WHERE status!='NOT ACTIVE';
Step 11:
--------
Resolve any outstanding unresolved distributed transaction:
SQL> select * from dba_2pc_pending;
If this returns rows you should do the following:
SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
Step 12:
--------
Ensure the users sys and system have 'system' as their default tablespace.
SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');
Step 13:
--------
Ensure that the aud$ is in the system tablespace when auditing is enabled.
SELECT tablespace_name
FROM dba_tables
WHERE table_name='AUD$';
Step 14:
--------
Check whether database has any externally authenticated SSL users.
SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
If any SSL users are found, go thru the upgrade guide for further instructions.
Step 15:
-------
Put the database in noarchivelog mode to minimize the upgrade and finishing in the upgrade window.
Step 16:
-------
Note down the location of datafiles, redo logs, control files.
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT member FROM v$logfile;
After, noting down the the locations, Shutdown the database.
Step 17:
-------
Take cold backup (after shutting down the db and restarting it again)
or
if you have your database in archivelog mode then you can do this,
$ rman target / notcatalog
RMAN>run
{
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/admin/MYDB1/backup/%U';
backup current controlfile;
backup database format '/home/oracle/admin/MYDB1/backup/%U' TAG before_upgrade;
}
Step 18:
--------
Create the SYSAUX tablespace for 11g.
CREATE TABLESPACE SYSAUX
DATAFILE 'sysaux_01.dbf' size 2048M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
Step 19:
-------
Make a backup of the spfile.
Comment out these obsoleted parameters:
LOGMNR_MAX_PERSISTENT_SESSIONS
PLSQL_COMPILER_FLAGS
DDL_WAIT_FOR_LOCKS
Change the following deprecated parameters:
BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
CORE_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
COMMIT_WRITE
INSTANCE_GROUPS
LOG_ARCHIVE_LOCAL_FIRST
PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL)
PLSQL_V2_COMPATIBILITY
REMOTE_OS_AUTHENT
STANDBY_ARCHIVE_DEST
TRANSACTION_LAG attribute (of the CQ_NOTIFICATION$_REG_INFO object)
Set the COMPATIBLE parameter to 10.1.0 if you want to have the option to downgrade. To use all new features of 11g, you need to use
compatible=11.1.0
When done copy the pfile to the new 11g $ORACLE_HOME/dbs
Step 20:
-------
Create .profile11g under oracle user home directory to point to 11g new software and other relevant environment variables.
Step 20:
-------
Update the oratab entry:
/etc/oratab for linux
/var/opt/oracle/oratab for solaris
#ORCL:/u01/oracle/ora9i:Y
ORCL:/u01/oracle/ora11g:Y
Step 21:
========
Upgrading Database to 11gR1...
run .profile11g
Startup the DB in upgrade mode:
------------------------------
cd $HOME/migration
sqlplus '/ as sysdba'
startup UPGRADE
start the upgrade script:
------------------------
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off
At the end, the db will be shutdown by catupgrd.sql script.
Restart the Instance NORMALLY to reinitialize the system parameters for normal operation.
Run the Post-Upgrade Status Tool:
--------------------------------
@?/rdbms/admin/utlu111s.sql
Recompile any remaining stored PL/SQL:
-------------------------------------
@?/rdbms/admin/catuppst.sql
@?/rdbms/admin/utlrp.sql
There may be duplicate objects between SYS and SYSTEM so I followed the Note and dropped system duplicate objects:
You can use this query i wrote to find those duplicates:
select distinct 'drop ' || b.object_type || ' SYSTEM.'||b.object_name || ';'
from all_objects a,
all_objects b
where a.owner = 'SYS'
and b.owner = 'SYSTEM'
and a.object_name = b.object_name
order by 1
/
drop PACKAGE BODY SYSTEM.DBMS_REPCAT_AUTH;
drop PACKAGE SYSTEM.DBMS_REPCAT_AUTH;
drop SYNONYM SYSTEM.CATALOG;
drop SYNONYM SYSTEM.COL;
drop SYNONYM SYSTEM.PUBLICSYN;
drop SYNONYM SYSTEM.SYSCATALOG;
drop SYNONYM SYSTEM.SYSFILES;
drop SYNONYM SYSTEM.TAB;
drop SYNONYM SYSTEM.TABQUOTAS;
drop TABLE SYSTEM.AQ$_SCHEDULES;
drop TABLE SYSTEM.DEF$_AQCALL;
drop TABLE SYSTEM.DEF$_CALLDEST;
drop TABLE SYSTEM.DEF$_DEFAULTDEST;
drop TABLE SYSTEM.DEF$_ERROR;
drop TABLE SYSTEM.DEF$_LOB;
Post Upgrade Steps:
##################
Step 22:
--------
Check listener.ora for any modifications needed to listen on the upgraded DB.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PF11)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /apps/oracle/product/db11gR2)
(SID_NAME = PF11)
)
)
Start the listener:
lsnrctl start
Step 23:
--------
Oracle recommends that you lock all Oracle supplied accounts except for SYS and SYSTEM:
ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;
Step 24:
-------
Change the compatability version to use the new 11g features:
alter system set compatible='11.1.0.6' scope=spfile;
shutdown immediate;
startup;
Step 25:
-------
Now you can gather SYS schema stats.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS', options => 'GATHER', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
You have a cooked 11g database...
Thursday, January 14, 2010
11g Vitual Columns howto and performance
Let's do some basic testing on Virtual columns.
1 create table employees
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100),
6 loweremail as (lower(email)),
7 emp_full_name as (firstname || ' ' || lastname)
8* )
SQL> /
Table created.
Elapsed: 00:00:00.15
And try to insert some values....
insert into employees values (1,'ALLEN', 'BECK','AllenBeck@aol.com')
*
ERROR at line 1:
ORA-00947: not enough values
Elapsed: 00:00:00.00
Bamm...it failed. So it needs values for virtual columns too ??
insert into employees values (2,'Blah', 'Jlah', 'blah.jlah@msn.com', 'blah.jlah@msn.com','Blah Jlah')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
Elapsed: 00:00:00.01
Not really. Then ?? Why did it fail ? Let's try doing something different...
SQL> insert into employees (empno,firstname, lastname, email) values (1,'ALLEN', 'BECK','AllenBeck@aol.com');
1 row created.
Elapsed: 00:00:00.00
So we know using virtual columns puts some limitations on how we do inserts.
Let's retrieve this data back..
1* select * from employees
SQL> /
EMPNO FIRSTNAME LASTNAME EMAIL LOWEREMAIL EMP_FULL_NAME
---------- ---------- ------------------------- -------------------- -------------------- ------------------------------
1 ALLEN BECK AllenBeck@aol.com allenbeck@aol.com ALLEN BECK
Elapsed: 00:00:00.00
SQL>
How to make sure the column is Virtual ?
1 select table_name, column_name, data_type, hidden_column
2 from dba_tab_cols
3 where table_name = 'EMPLOYEES'
4* and virtual_column = 'YES'
SQL> /
TABLE_NAME COLUMN_NAME DATA_TYPE HID
--------------- ------------------------------ --------------- ---
EMPLOYEES EMP_FULL_NAME VARCHAR2 NO
EMPLOYEES LOWEREMAIL VARCHAR2 NO
SQL>
Now, let's look at some performance metrics between this table and a table with no virtual columns.
SQL> create table employees_nonv
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100)
6 );
Table created.
SQL> create table employees_v
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100),
6 loweremail as (lower(email)),
7 emp_full_name as (firstname || ' ' || lastname)
8 );
Table created.
SQL>
Let's insert 100000 records in a table with no virtual columns.
set timing on
declare
i number:=0;
begin
for i in 1..100000
loop
insert into employees_nonv values (i,'Fredreck', 'Herbert','Fredreck.Herbert@myemailaddress.com');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:29.74
SQL>
Now let's insert 100000 records in a table with virtual columns.
declare
i number:=0;
begin
for i in 1..100000
loop
insert into employees_v (empno, firstname, lastname, email) values (i,'Fredreck', 'Herbert','Fredreck.Herbert@myemailaddress.com');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:29.79
SQL>
Pretty much the same results. Now, let's try to select from them.
Let's do a test by fetching a column from the table without virtual columns.
declare
i number:=0;
j number;
begin
for i in 1..100000
loop
select empno into j
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Let's do the test by fetching a single column from the table with virtual columns.
Elapsed: 00:00:22.26
SQL> SQL>
declare
i number:=0;
j number;
begin
for i in 1..100000
loop
select empno into j
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:22.47
Another one...
declare
i number:=0;
email varchar2(100);
begin
for i in 1..100000
loop
select email into email
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.06
declare
i number:=0;
loweremail varchar2(100);
begin
for i in 1..100000
loop
select loweremail into loweremail
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.36
Let's do another test by fetching multiple columns from the tables without virtual columns.
declare
i number:=0;
firstname varchar2(100);
lastname varchar2(100);
full_name varchar2(100);
email varchar2(100);
begin
for i in 1..100000
loop
select firstname, lastname, firstname || ' ' || lastname full_name, email into firstname, lastname, full_name, email
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.73
Now, let's try to select multiple columns from the tables with virtual columns.
declare
i number:=0;
firstname varchar2(100);
lastname varchar2(100);
full_name varchar2(100);
email varchar2(100);
begin
for i in 1..100000
loop
select firstname, lastname, full_name, loweremail into firstname, lastname, full_name, email
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.79
So, it is clear there is no apparent degradation for using Virtual columns. If there is an index created on a virtual which is a Function based index on the underlying column, may have some performance issues. That is true for any Function based index created. But generally if it's a pseudo column which is used by a PK or other indexed column, there shouldn't be any issues.
1 create table employees
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100),
6 loweremail as (lower(email)),
7 emp_full_name as (firstname || ' ' || lastname)
8* )
SQL> /
Table created.
Elapsed: 00:00:00.15
And try to insert some values....
insert into employees values (1,'ALLEN', 'BECK','AllenBeck@aol.com')
*
ERROR at line 1:
ORA-00947: not enough values
Elapsed: 00:00:00.00
Bamm...it failed. So it needs values for virtual columns too ??
insert into employees values (2,'Blah', 'Jlah', 'blah.jlah@msn.com', 'blah.jlah@msn.com','Blah Jlah')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
Elapsed: 00:00:00.01
Not really. Then ?? Why did it fail ? Let's try doing something different...
SQL> insert into employees (empno,firstname, lastname, email) values (1,'ALLEN', 'BECK','AllenBeck@aol.com');
1 row created.
Elapsed: 00:00:00.00
So we know using virtual columns puts some limitations on how we do inserts.
Let's retrieve this data back..
1* select * from employees
SQL> /
EMPNO FIRSTNAME LASTNAME EMAIL LOWEREMAIL EMP_FULL_NAME
---------- ---------- ------------------------- -------------------- -------------------- ------------------------------
1 ALLEN BECK AllenBeck@aol.com allenbeck@aol.com ALLEN BECK
Elapsed: 00:00:00.00
SQL>
How to make sure the column is Virtual ?
1 select table_name, column_name, data_type, hidden_column
2 from dba_tab_cols
3 where table_name = 'EMPLOYEES'
4* and virtual_column = 'YES'
SQL> /
TABLE_NAME COLUMN_NAME DATA_TYPE HID
--------------- ------------------------------ --------------- ---
EMPLOYEES EMP_FULL_NAME VARCHAR2 NO
EMPLOYEES LOWEREMAIL VARCHAR2 NO
SQL>
Now, let's look at some performance metrics between this table and a table with no virtual columns.
SQL> create table employees_nonv
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100)
6 );
Table created.
SQL> create table employees_v
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100),
6 loweremail as (lower(email)),
7 emp_full_name as (firstname || ' ' || lastname)
8 );
Table created.
SQL>
Let's insert 100000 records in a table with no virtual columns.
set timing on
declare
i number:=0;
begin
for i in 1..100000
loop
insert into employees_nonv values (i,'Fredreck', 'Herbert','Fredreck.Herbert@myemailaddress.com');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:29.74
SQL>
Now let's insert 100000 records in a table with virtual columns.
declare
i number:=0;
begin
for i in 1..100000
loop
insert into employees_v (empno, firstname, lastname, email) values (i,'Fredreck', 'Herbert','Fredreck.Herbert@myemailaddress.com');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:29.79
SQL>
Pretty much the same results. Now, let's try to select from them.
Let's do a test by fetching a column from the table without virtual columns.
declare
i number:=0;
j number;
begin
for i in 1..100000
loop
select empno into j
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Let's do the test by fetching a single column from the table with virtual columns.
Elapsed: 00:00:22.26
SQL> SQL>
declare
i number:=0;
j number;
begin
for i in 1..100000
loop
select empno into j
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:22.47
Another one...
declare
i number:=0;
email varchar2(100);
begin
for i in 1..100000
loop
select email into email
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.06
declare
i number:=0;
loweremail varchar2(100);
begin
for i in 1..100000
loop
select loweremail into loweremail
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.36
Let's do another test by fetching multiple columns from the tables without virtual columns.
declare
i number:=0;
firstname varchar2(100);
lastname varchar2(100);
full_name varchar2(100);
email varchar2(100);
begin
for i in 1..100000
loop
select firstname, lastname, firstname || ' ' || lastname full_name, email into firstname, lastname, full_name, email
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.73
Now, let's try to select multiple columns from the tables with virtual columns.
declare
i number:=0;
firstname varchar2(100);
lastname varchar2(100);
full_name varchar2(100);
email varchar2(100);
begin
for i in 1..100000
loop
select firstname, lastname, full_name, loweremail into firstname, lastname, full_name, email
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.79
So, it is clear there is no apparent degradation for using Virtual columns. If there is an index created on a virtual which is a Function based index on the underlying column, may have some performance issues. That is true for any Function based index created. But generally if it's a pseudo column which is used by a PK or other indexed column, there shouldn't be any issues.
Tuesday, January 12, 2010
Is ROW MOVEMENT expensive ?
Create NON Partition table
SQL> create table non_part (x number, y number);
Table created.
SQL>
Create Primary Key on this table.
SQL> alter table non_part add primary key(x);
Table altered.
SQL>
Let's create Partitioned table
SQL> CREATE TABLE part
2 ( x NUMBER,
3 y number)
4 PARTITION BY RANGE (y)
5 ( PARTITION part_y_1 VALUES LESS THAN (1),
6 PARTITION part_y_2 VALUES LESS THAN (2),
7 PARTITION part_y_3 VALUES LESS THAN (MAXVALUE)
8 );
Table created.
Elapsed: 00:00:00.09
SQL>
alter table part add primary key (x)
SQL> /
Table altered.
Elapsed: 00:00:00.89
Let's populate the tables now....
declare
i number:=0;
begin
for i in 1..100000
loop
insert into non_part values (i,1);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL>
declare
i number:=0;
begin
for i in 1..100000
loop
insert into part values (i,1);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL>
SQL> select count(1) from non_part;
COUNT(1)
----------
100000
SQL>
SQL> select count(1) from part partition(part_y_1);
COUNT(1)
----------
0
1* select count(1) from part partition(part_y_2)
SQL> /
COUNT(1)
----------
100000
1* select count(1) from part partition(part_y_3)
SQL> /
COUNT(1)
----------
0
SQL> update non_part
2 set y = 3;
100000 rows updated.
Elapsed: 00:01:15.12
So, it took 1 minute and 15 seconds to update 100000 rows.
SQL> update part
2 set y=3;
100000 rows updated.
Elapsed: 00:04:50.19
SQL> select count(1) from part partition(part_y_3);
COUNT(1)
----------
100000
So, the conclusion is that it took almost 4 times as much time to update 100k rows in a partition table with row movement and we made sure all 100k rows were moved.
So each row that took .69 millisecond would take 2.7 millisecond. Generally, in an OLTP system this isn't good but your application should be purely subjective.
SQL> create table non_part (x number, y number);
Table created.
SQL>
Create Primary Key on this table.
SQL> alter table non_part add primary key(x);
Table altered.
SQL>
Let's create Partitioned table
SQL> CREATE TABLE part
2 ( x NUMBER,
3 y number)
4 PARTITION BY RANGE (y)
5 ( PARTITION part_y_1 VALUES LESS THAN (1),
6 PARTITION part_y_2 VALUES LESS THAN (2),
7 PARTITION part_y_3 VALUES LESS THAN (MAXVALUE)
8 );
Table created.
Elapsed: 00:00:00.09
SQL>
alter table part add primary key (x)
SQL> /
Table altered.
Elapsed: 00:00:00.89
Let's populate the tables now....
declare
i number:=0;
begin
for i in 1..100000
loop
insert into non_part values (i,1);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL>
declare
i number:=0;
begin
for i in 1..100000
loop
insert into part values (i,1);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL>
SQL> select count(1) from non_part;
COUNT(1)
----------
100000
SQL>
SQL> select count(1) from part partition(part_y_1);
COUNT(1)
----------
0
1* select count(1) from part partition(part_y_2)
SQL> /
COUNT(1)
----------
100000
1* select count(1) from part partition(part_y_3)
SQL> /
COUNT(1)
----------
0
SQL> update non_part
2 set y = 3;
100000 rows updated.
Elapsed: 00:01:15.12
So, it took 1 minute and 15 seconds to update 100000 rows.
SQL> update part
2 set y=3;
100000 rows updated.
Elapsed: 00:04:50.19
SQL> select count(1) from part partition(part_y_3);
COUNT(1)
----------
100000
So, the conclusion is that it took almost 4 times as much time to update 100k rows in a partition table with row movement and we made sure all 100k rows were moved.
So each row that took .69 millisecond would take 2.7 millisecond. Generally, in an OLTP system this isn't good but your application should be purely subjective.
Thursday, January 7, 2010
ROWID datatype
Now there is a ROWID datatype and you don't have to put that in an char or varchar2 datatype.
1 declare
2 rowids rowid;
3 begin
4 select rowid into rowids
5 from dual;
6 dbms_output.put_line('rowid is '|| rowids);
7* end;
8 /
rowid is AAAABzAABAAAAEmAAA
PL/SQL procedure successfully completed.
SQL>
1 declare
2 rowids rowid;
3 begin
4 select rowid into rowids
5 from dual;
6 dbms_output.put_line('rowid is '|| rowids);
7* end;
8 /
rowid is AAAABzAABAAAAEmAAA
PL/SQL procedure successfully completed.
SQL>
Tuesday, March 31, 2009
Pickler Fetch
SQL> create user rdba identified by rdba;
User created.
SQL> grant dba to rdba;
Grant succeeded.
SQL>
SQL> connect rdba/rdba
Connected.
SQL>
1 create table big_all_objects
2 as
3 select * from all_objects
4 union all
5 select * from all_objects
6 union all
7 select * from all_objects
8 union all
9 select * from all_objects
10 union all
11 select * from all_objects
12 union all
13* select * from all_objects
SQL> /
Table created.
Elapsed: 00:01:04.67
SQL> select count(1) from big_all_objects;
COUNT(1)
----------
141828
Elapsed: 00:00:00.50
SQL>
1* select /* Without Pickler fetch */ object_id from big_all_objects where object_id in (3,30,50,100,150,200)
OBJECT_ID
----------
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
30 rows selected.
Elapsed: 00:00:00.88
Let's prepare for pickler fetch:
SQL> create or replace type inListTable as table of number
2 /
Type created.
SQL> create or replace function str2tbl( p_str in varchar2 ) return inListTable
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data inListTable := inListTable();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15 end;
16 /
Function created.
SQL> SQL>
1 select /* with pickler fetch */ object_id
2 from big_all_objects
3 WHERE object_id IN (select *
4* from table ( cast ( str2tbl('3,30,50,100,150,200') as inListTable)))
SQL> /
OBJECT_ID
----------
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
30 rows selected.
Elapsed: 00:00:00.81
SQL>
Both of these take about the same time in running in the 2nd, 3rd iteration onwards.
Let's see what v$sqlarea has to say about these two.
First time after running query with pickler fetch..
First time after running query after pickler fetch.
Here are the results after running the above queries 10 times each.
From the above analysis we can conclude that after the procedure stays in shared pool and/or pinned, the execution time and resources used to run the SQL is the same. The big benefit we get is to have a non-fragmented shared pool. This is applicable when the number of arguments in an in list is random.
User created.
SQL> grant dba to rdba;
Grant succeeded.
SQL>
SQL> connect rdba/rdba
Connected.
SQL>
1 create table big_all_objects
2 as
3 select * from all_objects
4 union all
5 select * from all_objects
6 union all
7 select * from all_objects
8 union all
9 select * from all_objects
10 union all
11 select * from all_objects
12 union all
13* select * from all_objects
SQL> /
Table created.
Elapsed: 00:01:04.67
SQL> select count(1) from big_all_objects;
COUNT(1)
----------
141828
Elapsed: 00:00:00.50
SQL>
1* select /* Without Pickler fetch */ object_id from big_all_objects where object_id in (3,30,50,100,150,200)
OBJECT_ID
----------
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
30 rows selected.
Elapsed: 00:00:00.88
Let's prepare for pickler fetch:
SQL> create or replace type inListTable as table of number
2 /
Type created.
SQL> create or replace function str2tbl( p_str in varchar2 ) return inListTable
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data inListTable := inListTable();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15 end;
16 /
Function created.
SQL> SQL>
1 select /* with pickler fetch */ object_id
2 from big_all_objects
3 WHERE object_id IN (select *
4* from table ( cast ( str2tbl('3,30,50,100,150,200') as inListTable)))
SQL> /
OBJECT_ID
----------
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
30 rows selected.
Elapsed: 00:00:00.81
SQL>
Both of these take about the same time in running in the 2nd, 3rd iteration onwards.
Let's see what v$sqlarea has to say about these two.
First time after running query with pickler fetch..
EXECUTIONS HASH_VALUE SQL_TEXT SORTS CPU_TIME BUFFER_GETS DISK_READS PARSE_CALLS LOADS
---------- ---------- ------------------------------ ---------- ---------- ----------- ---------- ----------- ----------
1 3492981496 with pickler fetch 0 1370000 11953 9852 1 4
First time after running query after pickler fetch.
EXECUTIONS HASH_VALUE SQL_TEXT SORTS CPU_TIME BUFFER_GETS DISK_READS PARSE_CALLS LOADS
---------- ---------- ------------------------------ ---------- ---------- ----------- ---------- ----------- ----------
1 3532977385 without pickler fetch 0 530000 9174 9562 1 2
Here are the results after running the above queries 10 times each.
EXECUTIONS HASH_VALUE SQL_TEXT SORTS CPU_TIME BUFFER_GETS DISK_READS PARSE_CALLS LOADS
---------- ---------- ------------------------------ ---------- -------- ----------- ---------- ----------- ----------
10 1169982563 without pf 0 4970000 90745 90610 10 1
10 582411935 with pickler fetch 0 5270000 93657 90610 10 4
From the above analysis we can conclude that after the procedure stays in shared pool and/or pinned, the execution time and resources used to run the SQL is the same. The big benefit we get is to have a non-fragmented shared pool. This is applicable when the number of arguments in an in list is random.
Subscribe to:
Posts (Atom)