Improving Performance of Table

Scenario 1:

Each database user should have assigned default permanent and temporary tablespace and all tablespace should be locally managed. Also system tablespace should not be assigned for any db users.

Query:

SELECT    DU.USERNAME, DT.TABLESPACE_NAME, DT.EXTENT_MANAGEMENT

FROM     DBA_TABLESPACES DT, DBA_USERS DU

WHERE   DT.TABLESPACE_NAME       = DU.DEFAULT_TABLESPACE

AND        DU.DEFAULT_TABLESPACE = 'SYSTEM'

OR           DT.EXTENT_MANAGEMENT NOT LIKE 'LOCAL';

Scenario 2:

Set undo management to __auto__ and set DEFAULT_PERMANENT_TABLESPACE and DEFAULT_TEMP_TABLESPACE. 

Query:

SELECT   *

  FROM   database_properties

 WHERE   property_name IN

               ('DEFAULT_PERMANENT_TABLESPACE', 'DEFAULT_TEMP_TABLESPACE');

SELECT   name, VALUE

  FROM   v$parameter

 WHERE   name IN ('undo_management', 'undo_tablespace');

Scenario 3:

For loading large amount of data into a table use nologging and direct path loading feature.

Query:

Check object log status

SELECT   table_name, logging

  FROM   dba_tables

 WHERE   table_name LIKE 'EMP%';

Change logging/nologging status

alter table scott. COUNTRIES nologging;

Bulk insert query

insert into scott.countries select rownum, 'Name'||rownum from dual connect by rownum<=1000000;

Bulk insert query with “__APPEND__” hint

insert /*+APPEND */ into scott.countries select rownum, 'Name'||rownum from dual connect by rownum<=1000000;