THE Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Performance ======

==== Processes =====


    select total, active, inactive, system, killed
    from
       (select count(*) total from v$session)
     , (select count(*) system from v$session where username is null)
     , (select count(*) active from v$session where status = 'ACTIVE' and username is not null)
     , (select count(*) inactive from v$session where status = 'INACTIVE')
     , (select count(*) killed from v$session where status = 'KILLED');
	 

==== Memory ====

Without DB Restart:

  show parameter sga_target;
  alter system set sga_target = 10G;


With DB Restart:

  show parameter sga_max_size;
  alter system set sga_max_size = 12G scope=spfile;

Memory recommendation
  
  SQL> select * from v$memory_target_advice order by memory_size;
  MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
  180 .5 458 1.344 0
  270 .75 367 1.0761 0
  360 1 341 1 0
  450 1.25 335 .9817 0
  540 1.5 335 .9817 0
  630 1.75 335 .9817 0
  720 2 335 .9817 0

Backup


   set linesize 212
   set pagesize 1024
   
   col filename format a64
   
   select
       filename,
       round(bytes/1024/1024/1024, 2) "GByte",
       open_time,
       close_time,
       round(elapsed_time/100/60, 2) "Minuten",
       round(short_wait_time_total/100/60, 2) "Short Waits gesamt (Min)",
       round(long_wait_time_total/100/60, 2) "Long Waits gesamt (Min)",
       round(effective_bytes_per_second/1024/1024, 2) "MB/Sekunde"
   from
       v$backup_async_io
   where
       open_time > sysdate - 1
       and filename not like '%archivelog%'
   order by 3;
   
   exit;