compression - order by matters

You get much better compress if you order the data as you compress
use this sql to work out the best order by clause


SELECT
', ' || dtc.column_name
, dtc.num_distinct
, dtc.avg_col_len
FROM dba_tab_cols dtc
WHERE dtc.owner = 'OWNER'
AND dtc.table_name = 'TABLE_TO_COMRESS'
ORDER BY
dtc.num_distinct
,dtc.avg_col_len DESC;

More on - Is my index used

try this SQL :-

SELECT P.object_name
--, P.operation
--, P.options
, COUNT ( 1 ) times_used
, MIN ( P.TIMESTAMP ) min_timestamp
, MAX ( P.TIMESTAMP ) max_timestamp
, AVG ( ind_size.size_mb ) size_mb
FROM dba_hist_sql_plan P
, dba_hist_sqlstat s
, (SELECT A.index_name
, A.owner
, ROUND ( SUM ( b.BLOCKS * 8 ) / 1024, 0 ) size_mb
FROM dba_indexes A
, dba_segments b
WHERE A.index_name = b.segment_name
AND A.UNIQUENESS <> 'UNIQUE'
GROUP BY A.index_name
, A.owner) ind_size
WHERE P.object_owner <> 'SYS'
AND P.operation LIKE '%INDEX%'
AND P.object_owner IN ('DWDIMS','DWFACTS')
AND P.sql_id = s.sql_id
AND P.object_owner = ind_size.owner
AND P.object_name = ind_size.index_name
GROUP BY P.object_name
--, P.operation
--, P.options
ORDER BY 4
, 1
, 2
, 3;

unique index on a timestamp column - only works to the second

A unique index on a timestamp column truncates the milliseconds from the timestamp and can result in an unexpected unique constraint violation.



CREATE TABLE reh_temp
AS
SELECT SYSTIMESTAMP tstamp FROM dual;

CREATE UNIQUE INDEX reh_tmp_pk ON reh_temp(tstamp)
;
INSERT INTO reh_temp
SELECT SYSTIMESTAMP
FROM dual
UNION
SELECT CAST(SYSTIMESTAMP AS date) FROM dual;

SELECT * FROM reh_temp

Linux - directory size

du -sm * | sort -n

to log or not to log

In summary :-
If nologging is specified on a table or tablespace all operations against a table will still be logged except

Alter table...move partition
alter table...split partition
alter index...split partition
alter index...rebuild
alter index...rebuild partition
create table...as select (CTAS)
create index
direct load with SQL*Loader
direct load INSERT (using APPEND)

with nologging specified


http://www.dbasupport.com/oracle/ora10g/managing_tables.shtml

parallel_max_servers

parallel_max_servers set the TOTAL maximum number of parallel processes that each server.


http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10736/usingpe.htm#i1008006

parallel, degrees and instances

create table
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )

The Degree 'default value' is worked out thus :-
number of CPUs (cores) * number_of_thread_per_cpu (from v$parameter)

Instances is the number of nodes in the RAC that you want it to use

We hit a problem with a query going 'massively paralle' because the server had 16 cpus
, the number_of_thread_per_cpu was2 and the number of nodes was 2
We therefore ended up with something close to 64 parallel processes.

unusable columns and exchange partitions

column mismatch during exchange may be caused by unusable columns.

http://itnewscast.com/ora14097-column-type-or-size-mismatch-alter-table-exchange-partition

PL/sql function and no_data_found

PL/SQL functions with a 'select col1 into v1 from table' line in them behave differently when they hit a no_data_found error (i.e. if the table is empty).
IF
you call the function like this .....
BEGIN
my_function;
END;
THEN
YOU get a no_data_found exception raised.


BUT
IF YOU CALL THE FUNCTION LIKE THIS....
SELECT my_function FROM dual;
THEN
It will not raise a no_data_found

see
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:317260900346187160