Analyze Options
- Estimate over all rows
DBMS_UTILITY.ANALYZE_SCHEMA('userid', 'COMPUTE');
- Estimate 20% of all rows for a specific Schema
DBMS_UTILITY.ANALYZE_SCHEMA('userid', 'ESTIMATE',NULL,20);
- Estimate 20% of a table
DBMS_UTILITY.ANALYZE_SCHEMA('TABLE' , 'schema', 't_name', 'ESTIMATE',null,20);
or
ANALYZE TABLE table ESTIMATE STATISTICS sample 20 percent;
- Estimate 20% of an index
DBMS_UTILITY.ANALYZE_SCHEMA('INDEX' , 'schema', 'i_name', 'COMPUTE';
- Estimate 1000 rows of all the tables for a schema
DBMS_UTILITY.ANALYZE_SCHEMA ('userid', 'ESTIMATE', 100000);
or
ANALYZE TABLE table ESTIMATE STATISTICS sample 5000 rows;
- Delete all stats
DBMS_UTILITY.ANALYZE_SCHEMA ('userid', 'DELETE');
or
ANALYZE TABLE table DELETE STATISTICS;
Working with UNDO Parameters
When you are working with UNDO Tablespace, there are two important things to consider:
The size of the UNDO tablespace The UNDO_RETENTION parameter
To get information of your current settings you can use the following query:
set serveroutput on DECLARE
tsn VARCHAR2(40);
tss NUMBER(10);
aex BOOLEAN;
unr NUMBER(5);
rgt BOOLEAN;
retval BOOLEAN;
v_undo_size NUMBER(10);
BEGIN
select sum(a.bytes)/1024/1024 into v_undo_size
from v$datafile a, v$tablespace b, dba_tablespaces c where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name and a.ts# = b.ts#;
retval := dbms_undo_adv.undo_info(tsn, tss, aex, unr, rgt);
dbms_output.put_line('UNDO Tablespace is : ' || tsn);
dbms_output.put_line('UNDO Tablespace size is : ' || TO_CHAR(v_undo_size) || ' MB');
IF aex THEN
dbms_output.put_line('Undo Autoextend is set to : TRUE');
ELSE
dbms_output.put_line('Undo Autoextend is set to : FALSE');
END IF;
dbms_output.put_line('Undo Retention is : ' || TO_CHAR(unr));
IF rgt THEN
dbms_output.put_line('Undo Guarantee is set to : TRUE');
ELSE
dbms_output.put_line('Undo Guarantee is set to : FALSE');
END IF;
END;
/
UNDO Tablespace is : UNDOTBS1 UNDO Tablespace size is : 925 MB Undo Autoextend is set to : TRUE Undo Retention is : 900 Undo Guarantee is set to : FALSE
You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity.
This tip help you get the information you need whatever the method you choose.
set serverout on size 1000000 set feedback off
set heading off set lines 132 declare
cursor get_undo_stat is
select d.undo_size/(1024*1024) "C1", substr(e.value,1,25) "C2",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "C3", round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "C4"
from (select sum(a.bytes) undo_size
from v$datafile a, v$tablespace b, dba_tablespaces c where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name and a.ts# = b.ts#) d,
v$parameter e, v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec from v$undostat) g where e.name = 'undo_retention'
and f.name = 'db_block_size';
begin
dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) || 'To optimize UNDO you have two choices :');
dbms_output.put_line('====================================================' || chr(10));
for rec1 in get_undo_stat loop
dbms_output.put_line('A) Adjust UNDO tablespace size according to UNDO_RETENTION :' || chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',60,'.')|| ' : ' || TO_CHAR(rec1.c1,'999999') || ' MB');
dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (' || ltrim(TO_CHAR(rec1.c2,'999999')) || ' SECONDS) ',60,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MB');
dbms_output.put_line(chr(10));
dbms_output.put_line(chr(10));
dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :' || chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',60,'.') || ' : ' || TO_CHAR(rec1.c2,'999999') || ' SECONDS');
dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (' || ltrim(TO_CHAR(rec1.c1,'999999'))
|| ' MEGS) ',60,'.') || ' : ' || TO_CHAR(rec1.c4,'999999') || ' SECONDS');
end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/
To optimize UNDO you have two choices :
====================================================
A) Adjust UNDO tablespace size according to UNDO_RETENTION :
ACTUAL UNDO SIZE ... : 925 MB OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (15 MINUTES) .. : 82 MB B) Adjust UNDO_RETENTION according to UNDO tablespace size :
ACTUAL UNDO RETENTION ... : 900 SECONDS OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (925 MEGS) .... : 10125 SECONDS
Undo Segments
With the following query, we can check the segments of the UNDO:
SELECT active.active, unexpired.unexpired, expired.expired FROM (SELECT Sum(bytes / 1024 / 1024) AS unexpired
WHERE status = 'UNEXPIRED') unexpired, (SELECT Sum(bytes / 1024 / 104) AS expired FROM dba_undo_extents tr
WHERE status = 'EXPIRED') expired, (SELECT CASE
WHEN Count(status) = 0 THEN 0 ELSE Sum(bytes / 1024 / 1024) END AS active
FROM dba_undo_extents
WHERE status = 'ACTIVE') active;
ACTIVE UNEXPIRED EXPIRED 0 10 100.923077
Where:
ACTIVE = it means that those UNDO segments contains active transactions, so a commit was not executed yet.
UNEXPIRED = it means that those UNDO segments contains commited transactions, and those transactions are still required for FLASHBACK.
EXPIRED = it means that those UNDO segments are not required after the time defined under "undo_retention" parameter.
So when you execute an insert, you start using undo segments and those are in ACTIVE state until you fire the COMMIT.
Once the COMMIT is fired, they are on UNEXPIRED status (still using UNDO Tablespace) until they reach the "undo_retention" time.
Once that time is completed, they are moved to the EXPIRED status.
Monitorting Transactions in UNDO
It's possible to monitor the transactions that are taking UNDO segments with the following query:
SELECT v$transaction.status AS status_transaccion, start_time, logon_time, blocking_session_status, schemaname, machine, program, v$session.module, v$sqlarea.sql_text, serial#, sid, username, v$session.status AS status_sesion, v$session.sql_id, prev_sql_id
FROM v$transaction INNER JOIN v$sessionON v$transaction.ses_addr = v$session.saddr LEFT JOIN v$sqlarea ON v$session.sql_id = v$sqlarea.sql_id;
If there are ACTIVE Transactions (not commited transactions) it will show:
STATUS_TRANSACCION ACTIVE
START_TIME 08/16/11 09:08:40 LOGON_TIME 8/16/2011 9:08:15 BLOCKING_SESSION_STATUS NO HOLDER
SCHEMANAME FRAUDGUARD
MACHINE VOP-DPAFUMI
PROGRAM sqlplus.exe
MODULE SQL*Plus
SQL_TEXT insert into state values (1111, 'AAA');
SERIAL# 1489
SID 9
USERNAME FRAUDGUARD
STATUS_SESION INACTIVE
SQL_ID 9babjv8yq8ru3
PREV_SQL_ID 9babjv8yq8ru3
Where each value means:
LOGON_TIME = Date and time when the instruction was executed
BLOCKING_SESSION_STATUS = It says if this session is blocking another session SCHEMANAME = Schema that executed the instruction.
MACHINE = Machine name that executed the instruction.
PROGRAM = Program Name that executed the instruction.
MODULE = Module that executed the instruction.
SQL_TEXT = Executed instruction.
SERIAL# = Serial number of the session that executed the instraction.
SID = ID Session that executed the instraction..
USERNAME = User that executed the instraction..
STATUS_SESION = Status of the that executed the instraction, ACTIVE if that is CURRENTLY performing any actions, INACTIVE if is not performing and actions.
SQL_ID = Internal ID that executed the instraction.
PREV_SQL_ID = ID of the instructions previous to the current executed instraction.