UNDO in 9i and 10g

it is advisable to have AUTEXTEND OFF or have a suitable MAXSIZE in UNDO datafiles.

ref-

http://www.orafaq.com/node/61

best practice

This following list of recommendations will help you manage your undo space to best advantage.

• You need not set a value for the UNDO_RETENTION parameter unless your system has flashback or LOB retention requirements.

• Allow 10 to 20% extra space in your undo tablespace to provide for some fluctuation in your workload.

• Set the warning and critical alert thresholds for the undo tablespace alert properly. Please refer to "Managing Space in Tablespaces " for information on how to set alert thresholds for the undo tablespace.

• To tune SQL queries or to check on runaway queries, use the value of the SQLID column provided in the long query or in the V$UNDOSTAT or WRH$_UNDOSTAT views to retrieve SQL text and other details on the SQL fromV$SQL view.

• Always use indexes for Flashback Version Query.

  
  

  
  

  ref oracle docs

  Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger

  In the following example, a system wide AFTER SUSPEND trigger is created and registered as user SYS at the database level. Whenever a resumable statement is suspended in any session, this trigger can have either of two effects:

  • If an undo segment has reached its space limit, then a message is sent to the DBA and the statement is aborted.

  • If any other recoverable error has occurred, the timeout interval is reset to 8 hours.

  Here are the statements for this example:

  CREATE OR REPLACE TRIGGER resumable_default AFTER SUSPEND ON DATABASE DECLARE    /* declare transaction in this trigger is autonomous */    /* this is not required because transactions within a trigger       are always autonomous */    PRAGMA AUTONOMOUS_TRANSACTION;    cur_sid           NUMBER;    cur_inst          NUMBER;    errno             NUMBER;    err_type          VARCHAR2;    object_owner      VARCHAR2;    object_type       VARCHAR2;    table_space_name  VARCHAR2;    object_name       VARCHAR2;    sub_object_name   VARCHAR2;    error_txt         VARCHAR2;    msg_body          VARCHAR2;    ret_value         BOOLEAN;    mail_conn         UTL_SMTP.CONNECTION; BEGIN    -- Get session ID    SELECT DISTINCT(SID) INTO cur_SID FROM V$MYSTAT;     -- Get instance number    cur_inst := userenv('instance');     -- Get space error information    ret_value :=     DBMS_RESUMABLE.SPACE_ERROR_INFO(err_type,object_type,object_owner,         table_space_name,object_name, sub_object_name);    /*    -- If the error is related to undo segments, log error, send email    -- to DBA, and abort the statement. Otherwise, set timeout to 8 hours.    --     -- sys.rbs_error is a table which is to be    -- created by a DBA manually and defined as    -- (sql_text VARCHAR2(1000), error_msg VARCHAR2(4000),    -- suspend_time DATE)    */     IF OBJECT_TYPE = 'UNDO SEGMENT' THEN        /* LOG ERROR */        INSERT INTO sys.rbs_error (            SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME            FROM DBMS_RESUMABLE            WHERE SESSION_ID = cur_sid AND INSTANCE_ID = cur_inst         );        SELECT ERROR_MSG INTO error_txt FROM DBMS_RESUMABLE             WHERE SESSION_ID = cur_sid and INSTANCE_ID = cur_inst;          -- Send email to receipient via UTL_SMTP package         msg_body:='Subject: Space Error Occurred                     Space limit reached for undo segment ' || object_name ||                     on ' || TO_CHAR(SYSDATE, 'Month dd, YYYY, HH:MIam') ||                    '. Error message was ' || error_txt;          mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25);         UTL_SMTP.HELO(mail_conn, 'localhost');         UTL_SMTP.MAIL(mail_conn, 'sender@localhost');         UTL_SMTP.RCPT(mail_conn, 'recipient@localhost');         UTL_SMTP.DATA(mail_conn, msg_body);         UTL_SMTP.QUIT(mail_conn);          -- Abort the statement         DBMS_RESUMABLE.ABORT(cur_sid);     ELSE         -- Set timeout to 8 hours         DBMS_RESUMABLE.SET_TIMEOUT(28800);     END IF;      /* commit autonomous transaction */     COMMIT;    END;

pga_aggregate_target

v$pga_target_advice - estd_pga_cache_hit_percentage

v$pgastat - maximum PGA allocated

dba_hist_pgastat

from doc

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater

init parameter

pga_aggregate_target

hidden parameters

_pga_max_size

_smm_px_max_size

Tuning - check the hit , firstpass and multipass

--eliminate overalloc and maximize cache hit

SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024/1024) target_in_GB,

ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

ESTD_OVERALLOC_COUNT

FROM v$pga_target_advice

SELECT    program,    pga_used_mem "PGA Used",    pga_alloc_mem "PGA Alloc",    pga_max_mem "PGA Max"   FROM v$process;

SELECT    low_optimal_size/1024 "Low (K)",    (high_optimal_size + 1)/1024 "High (K)",    optimal_executions "Optimal",    onepass_executions "1-Pass",    multipasses_executions ">1 Pass"   FROM v$sql_workarea_histogram  WHERE total_executions <> 0;

check multipass
SELECT    optimal_count "Optimal",    round(optimal_count * 100 / total,2) "Optimal %",    onepass_count "OnePass",    round(onepass_count * 100 / total,2) "Onepass %",    multipass_count "MultiPass",    round(multipass_count * 100 / total,2) "Multipass %"   FROM (       SELECT          DECODE (SUM(total_executions), 0, 1, SUM(total_executions)) total,          SUM(optimal_executions) optimal_count,          SUM(onepass_executions) onepass_count,          SUM(multipasses_executions) multipass_count         FROM v$sql_workarea_histogram -- Limits consideration of queries with LOW_OPTIMAL_SIZE limit <64k> 64*1024)

SELECT *   FROM (SELECT             workarea_address,             operation_type,             policy,             estimated_optimal_size           FROM v$sql_workarea       ORDER BY estimated_optimal_size)  WHERE ROWNUM <= 20;

ref-

http://www.dba-oracle.com/tips_pga_aggregate_target.htm

it is important to remember that automatic PGA memory management via PGA_AGGREGATE_TARGET only applies to dedicated server connections. Memory allocation forshared server connections is still managed by SORT_AREA_SIZE, HASH_AREA_SIZE, and so forth

http://www.databasejournal.com/features/oracle/article.php/10893_2109921_1/Activating-Monitoring-and-Tuning-Automatic-PGA-Memory-Management-via-PGAAGGREGATETARGET-Under-Oracle-9i.htm

tasklist -v in windows

works same as ps -ef ................