ORA-00020: maximum number of processes NN exceeded

Today,One of the users told me that they cannot connect to one of our QA environment. Initially i though that database was down. But i was wrong, DB was up and running. When I tried logging in i got an error ORA-00020: maximum number of processes 300 exceeded.

At first i tried killing inactive session

select 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''' ;' from v$session where status='INACTIVE';

But this did not help and though number of sessions from v$session were reduced number of processes(v$process) were still the same.

After some googling i found a good query on Clean up sessions/processes to clean up sessions depending on processes.

SELECT 'ALTER SYSTEM KILL SESSION '''||s.SID||','||s.SERIAL#||''' ;' 
FROM gv$session s
 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

Be careful not to kill sys or oracle sessions. Your database may crash.

Some other workarounds are

1. Restart your database so that any orphan processes are cleaned up
2. Kill session from os level by looking at v$process or

   ps -ef | grep ora --(Think before doing this and kill only unwanted processes)

3. Increase processes parameter using below query and then reboot your system

alter system set processes=some_high_number scope=spfile;

 

 

 

ORA-14257: cannot move partition other than a Range, List, System, or Hash partition

Reason for writing this blog is, The error message for ORA-14257 is a but misleading(unlike Oracle !).

Recently I got a request from one of the developers to move one table to a different tablespace.

Table was partitioned,I got ORA-14257 while moving partitions to a new tablespace.

Apparently error occurs because table is subpartitioned and you should move subpartitions and not partitions. Since there is no extent under partitions command fails and we get this error msg.

How to overcome this error.

1. First check tables default attributes.
   

   SELECT owner,
    table_name,
    partitioned
    FROM dba_tables
    WHERE table_name IN ('SUBSCRIBER_UNION');

    

2. Your first step is to change tables default attribute tablespace. This is most necessary and usually forgotten. Unless you change tables default attributes any new partitions/subpartitions/indexes will be created on old tablespace and not new one. Most likely you will move current partitions/indexes but will not change default attributes and new ones will be created with default settings.
   

   ALTER TABLE ODS.SUBSCRIBER_UNION MODIFY DEFAULT attributes TABLESPACE ODINQ1_PET_DATA;

3. If table is not partitioned then its easy just one command and you are ready to go!
   

   ALTER TABLE ODS.SUBSCRIBER_UNION MOVE TABLESPACE ODINQ1_PET_DATA ;

4. Since its a partitioned table you now have to check default attributes of partitions.
   

   SELECT owner,
    table_name,
    def_tablespace_name,
    partitioning_type,
    subpartitioning_type
    FROM dba_part_tables
    WHERE table_name='SUBSCRIBER_UNION';

5. You now know that table is subpartitioned as well, your first step is to move all subpartitions to a new tablespace.
   

   SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME ||' TABLESPACE odinq1_pet_data ;'FROM dba_TAB_SUBPARTITIONSWHERE TABLE_NAME IN ('SUBSCRIBER_UNION');

6. Similarly move all indexes and its sub/partitons as well(Use DBA_IND_SUBPARTITIONS,DBA_IND_PARTITIONS view and REBUILD clause).
7.  Move all partitions(If you have subpartitions it will throw an error :))
   

   SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MOVE PARTITION ' ||PARTITION_NAME ||' TABLESPACE odinq1_pet_data ;'FROM dba_tab_partitionsWHERE table_name IN ('SUBSCRIBER_UNION');

8. Change default attributes of partitions
   

   SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MODIFY DEFAULT ATTRIBUTES FOR PARTITION ' ||PARTITION_NAME ||' TABLESPACE odinq1_pet_data ;'FROM dba_tab_partitionsWHERE table_name IN ('SUBSCRIBER_UNION');

 

Query to find top sqls by cpu,elapsed,execution

select details.*,sqltext.sql_text from 
(
select stat.sql_id, rank() over (order by (max(stat.cpu_time_total/stat.executions_total)) desc) cpu_rank,
rank() over (order by (max(stat.elapsed_time_total/stat.executions_total)) desc) elapsed_rank,
SUM(stat.executions_totaL) execution_TOTAL
from
 dba_hist_sqlstat stat inner join dba_hist_snapshot snap on snap.snap_id=stat.snap_id and stat.executions_total >0
--where
-- snap.begin_interval_time between sysdate-7
--and
-- sysdate
group by
 stat.sql_id
 ) details inner join dba_hist_sqltext sqltext on details.sql_id=sqltext.sql_id
 where cpu_rank <=100 
-- where elapsed_rank<=100;
-- where execution_rank<=100
 order by cpu_rank;
 order by elapsed_rank;
 order by execution_rank;

TD Tips

Show OBJECT_NAME:- Displays ddl for OBJECT_NAME

Show <Select… from table1,table2,table2 where …> will give ddls for all tables involved in the select statement

HELP STATISTICS:- To display stat details

 

TD PT Tips

Some reasons for performance issues on Teradata

1. Out of spool error
2. Skew data
3. Old stats
4. Bad PI selection
5. Product joins-Missing joins
6. Duplicating a large table on all amps
7. Redistributing a large table
8. locks

If there is a long running merge step

1. check for skew data
2. check for primary index keys they might be wrong
3. check if any indexes are update because column is getting changed(Update on indexed column)
4. check if partitioned column is updated

If you see a TRANSLATE function on a join step consuming resources

1. it means datatypes of joined columns are not same and conversion is taking place, causing any indexes to be ignored
2. since datatypes dont match stats are also ignored

You might see performance issues for SET tables because of duplicate row checks