select 'Index usage ratio' Name,
        round(sum(decode(name,'table scans (short tables)',value,'table scans (long tables)',value, 0 ))
            / sum(decode(name,'table fetch continued row',value,'table scans (short tables)',value,'table scans (long tables)',value, 0 ))
            *  100 , 4 ) Ratio,
        'Чем больше, тем меньше FETCH BY ROWID' Remarks
    from V$SYSSTAT

            select 'Data Dictionary cache' Name,
                    round((SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) *  100 , 4 ) Ratio,
                    'Должно быть около 99%' Remarks
                from V$ROWCACHE
            union all
            select 'Library cache' Name,
                    round((SUM(PINS - RELOADS)) / SUM(PINS) *  100 , 4 ) Ratio,
                    'Должно быть около 99%' Remarks
                from V$LIBRARYCACHE
            union all
            select 'Index usage ratio' Name,
                    round(sum(decode(name,'table fetch by rowid',value, 0 ))
                        / sum(decode(name,'table fetch by rowid',value,'table scans (short tables)',value,'table scans (long tables)',value, 0 ))
                        *  100 , 4 ) Ratio,
                    'Нужно достичь 99%' Remarks
                from V$SYSSTAT
            union all
            select 'Memory usage in Sorts ratio' Name,
                    round(sum(decode(name,'sorts (memory)',value, 0 ))
                        / sum(decode(name,'sorts (memory)',value,'sorts (disk)',value, 0 ))
                        *  100 , 4 ) Ratio,
                    'Отношение сортировки в Памяти ко всем сортировкам' Remarks
                from V$SYSSTAT
            union all
            select 'Cache hit ratio' Name,
                    round(( 1  - sum(decode(name,'physical reads',value, 0 ))
                        / sum(decode(name,'db block gets',value,'consistent gets',value, 0 ))
                    ) *  100 , 4 ) Ratio,
                    'Должно быть около 99%' Remarks
                from V$SYSSTAT
            union all
            select 'Buffer pool ' || Name || ' hit ratio' Name,
                    round(( 1  - physical_reads
                        / (db_block_gets+consistent_gets)
                    ) *  100 , 4 ) Ratio,
                    'Должно быть около 99%' Remarks
                from V$BUFFER_POOL_STATISTICS
            union all
            select 'Average buffers scanned' Name,
                    round( 1  + sum(decode(name,'free buffer inspected',value, 0 ))
                       / sum(decode(name,'free buffer requested',value, 0 )), 4 ),
                    'Должно быть 1..2, если больше двух -- серверу мало буферов' Remarks
                from V$SYSSTAT
            union all
            select 'Average dirty buffers' Name,
                    round(sum(decode(name,'dirty buffers inspected',value, 0 ))
                    / sum(decode(name,'free buffer inspected',value, 0 )), 4 ),
                    'Должно быть ??, если много "грязных" -- серверу мало буферов' Remarks
                from V$SYSSTAT
            union all
            select 'Shared pool free memory (MB)' Name,
                    round(bytes/ 1024 / 1024 , 4 ) Ration,
                    'Если много свободной памяти -- может она лишняя?' Remarks
                from V$SGASTAT
                where name = 'free memory'
                    and pool = 'shared pool';

Export the OL$ and OL$HINTS tables: 

EXP OUTLN/OUTLN FILE = exp_file TABLES = 'OL$' 'OL$HINTS' SILENT=y 



Remove the previous OL$ and OL$HINTS tables: 

CONNECT OUTLN/outln_password;
DROP TABLE OL$; 
CONNECT OUTLN/outln_password;
DROP TABLE OL$HINTS; 



Create a new tablespace for the tables: 

CREATE TABLESPACE outln_ts 
DATAFILE 'tspace.dat' SIZE 2MB 
DEFAULT STORAGE (INITIAL 10KB NEXT 20KB 
MINEXTENTS  1  MAXEXTENTS  999  PCTINCREASE  10 ) ONLINE; 


Enter the following statement: 

ALTER USER OUTLN DEFALUT TABLESPACE outln_ts;



Import the OL$ and OL$HINTS tables: 

IMPORT OUTLN/outln_password 
FILE=exp_file TABLES = 'OL$' 'OL$HINTS' IGNORE=y SILENT=y



The IMPORT statement re-creates the OL$ and OL$HINTS tables in the schema named OUTLN, but the schema now resides in a new tablespace called OUTLN_TS. 

alter table outln.ol$ move tablespace outlines_ts
[ 1 ]: (Error): ORA- 00997 : неверное использование типа данных LONG