Friday, March 14, 2008

Resize undo tablespace 10gR2 RAC

On RAC configuration each instance has assigned its own UNDO tablespace. So in order to resize the UNDO you must create a new one for each instance and assign it to the instance then you cane drop the old ones.

For each instance :

-- Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size 100m;

-- Set new tablespace as undo_tablespace
SQL> alter system set undo_tablespace=undo_rbs1;

-- Drop the old tablespace.
SQL> drop tablespace undo_rbs0 including contents.


NOTE: Dropping the old tablespace may give ORA-30013 : undo tablespace '%s' is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.

Recreate temp tablespace 10g

SQL> create temporary tablespace temp1 tempfile '/DATA/oradata/rdtwh/temp1.dbf' size 100M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

SQL> CREATE SMALLFILE TEMPORARY TABLESPACE TEMP
2 TEMPFILE '/DATA/oradata/rdtwh/temp01.dbf' SIZE 1000M
3 AUTOEXTEND ON NEXT 150M MAXSIZE 32767M
4 ,'/DATA/oradata/rdtwh/temp02.dbf' SIZE 1000M
5 AUTOEXTEND ON NEXT 150M MAXSIZE 32767M
6 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> drop tablespace temp1 including contents and datafiles;

Tablespace dropped.

SQL>

Tables with chain rows

Tables with chain rows are usually potentially performance bottlenecks , as oracle when needs to fetch rows from these tables my scan more than one block to get one row.
These tables are tables with rows that are highly updated changing row length. The above query finds and rebuild tables with chained rows and corresponding indexes when the chained rows count are above 10% of the table's total rows. It also gathers statistics after the rebuild.

select
  'alter table '||owner||'.'||table_name||' move pctfree 20;'
from
  dba_tables
where
  chain_cnt > 0
and owner = 'MYUSER'
and table_name='TEST'
and round((chain_cnt/num_rows)*100) >=10
union all
select
  'alter index '||owner||'.'||index_name||' rebuild'||
  case when index_type='NORMAL'
    then ' online;'
    else ';'
  end
from
  dba_indexes
where (table_owner,table_name) in
  (
    select
      owner,
      table_name
    from
      dba_tables
    where
      chain_cnt > 0
    and owner = 'MYUSER'
    and table_name='TEST'
    and round((chain_cnt/num_rows)*100) >=10
  )
union all
select
  'exec dbms_stats.gather_table_stats(ownname=>'''||owner||''',tabname=>'''||
  table_name||''',cascade=>true,estimate_percent=>5);'
from
  dba_tables
where
  chain_cnt > 0
and owner = 'MYUSER'
and table_name='TEST'
and round((chain_cnt/num_rows)*100) >=10 ;

Thursday, March 6, 2008

Remote SQL Statements Tuning

If you have a remote sql statement and the execution elapsed time is not the expected, the plan on the remote site is not the desirable. Especially if you using hints to tune your query on the remote site , this may not work on the actual execution because of Oracle behavior.
E.g

If the entire SQL statement is sent to the remote database, the optimizer uses table aliases A1, A2, and so on, for all tables and columns in the query, in order to avoid possible naming conflicts. For example:

On remote site you tune the query as follows ans runs ok:

SELECT /*+ index(a pk1) index(b pk2) */
DNAME, ENAME
FROM DEPT a, EMP b
WHERE DEPT.DEPTNO = EMP.DEPTNO;

And on your site you do

insert into table mytable
SELECT /*+ index(a pk1) index(b pk2) */
DNAME, ENAME
FROM DEPT@REMOTE a, EMP@REMOTE b
WHERE DEPT.DEPTNO = EMP.DEPTNO;

The query is sent to the remote database as:

   SELECT /*+ index(a pk1) index(b pk2) */
A2.DNAME, A1.ENAME
FROM DEPT A2, EMP A1
WHERE A1.DEPTNO = A2.DEPTNO;

So the hint is not working.

Best way is to create a view on remote site with the tuned query
and then use the view in the local insert.

REMOTE SITE :
create or replace myview as
SELECT /*+ index(a pk1) index(b pk2) */
DNAME, ENAME
FROM DEPT a, EMP b
WHERE DEPT.DEPTNO = EMP.DEPTNO;

LOCAL SITE :

insert into table mytable select * from myview@remote;





Wednesday, March 5, 2008

ORA-4030 on 10gR1 RAC instance at Windows 2003 Server Part2

The ORA-4030 error indicates that an Oracle process could not allocate the amount of memory
required for an operation.
On Windows 32-bit servers the total address space for a single process is 2 GB, regardless of the amount of available physical memor
y.
The 32-bit architecture limits the addressing range to 4 GB, and as 2 of these GBs are reserved of the kernel, a user process cannot be more than 2 GB.

When a client connects to the database a shadow process is created.
This is where the PGA memory belongs to. On Windows systems, this is not created as a new proces
s, but as a thread under the ORACLE.EXE executable. This means that the Oracle d
atabase and all its connections are treated as one single process (ORACLE.EXE) w
ith a large number of threads. In other words, the total address space for the d
atabase and all connections to it are restricted to 2 GB. In real-life the limit
is at appr. 1.7 GB.

As you know SGA + max alloc. PGA + oracle mem structures = total size of the ORACLE.EXE process.

We need to get more memory for max alloc. PGA.

1. If your server has got up to 3GB RAM then you should decrease the value of SGA components (shared_pool, db_cache);
2. If your server has got +/- 4Gb RAM then it's important to use the /3Gb switch in the boot.ini.