How to Collect index Statistics

INDEX_STATS figures for multiple indexes, because a normal 'analyze index ... validate structure' always replaces the content of index_stats when it is run. Script below also adds the number of columns in the index to assist when looking at the compression statistics

create table IDXSTAT
as select * from index_stats;

alter table idxstat add colcnt number;

begin
for i in ( select index_name from dba_indexes
      where owner = '...') loop
execute immediate 'analyze index iips.'||i.index_name||'

validate structure';
insert into idxstat
select x.*, cnt from index_stats x,
( select count(*) cnt from dba_ind_columns
where index_owner = '...'
and index_name = i.index_name );
commit;
end loop;
end;
/

select name, LF_BLKS, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE, COLCNT
from idxstat 

How To Backup All Tablespaces In Parallel With a Single Command

It is no longer necessary to issue a separate command to place each tablespace in hot backup mode.

You can now use the "ALTER DATABASE" statement to place all tablespaces in backup mode.

The database must be in mounted or open mode when you issue this command.

Also, the BEGIN BACKUP command now runs faster than before.

Earlier DBAs have used the prepare all online tablespaces for backup by issuing
all necessary ALTER TABLESPACE statements at once.

For example, put tablespaces users, tools, and indx in backup mode as follows:
SQL> ALTER TABLESPACE users BEGIN BACKUP;
SQL> ALTER TABLESPACE tools BEGIN BACKUP;
SQL> ALTER TABLESPACE indx BEGIN BACKUP;


Now you can Use ALTER DATABASE BEGIN BACKUP to put all tablespaces in backup mode simultaneously,


Hence If you are backing up all tablespaces, you might want to use this command:

SQL> ALTER DATABASE BEGIN BACKUP;


Example 1:
--------------------
Issue BEGIN BACKUP command when data files belonging to a tablespace are in READ-ONLY mode:

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace users begin backup;
alter tablespace users begin backup
*
ERROR at line 1:
ORA-01642: begin backup not needed for read only tablespace 'USERS'


SQL> alter database begin backup;

Database altered.

Example 2:
------------------------
Issue BEGIN BACKUP command when data files belonging to a tablespace are in READ-ONLY mode:

SQL> alter tablespace tools offline;

Tablespace altered.

SQL> alter tablespace tools begin backup;
alter tablespace tools begin backup
*
ERROR at line 1:
ORA-01128: cannot start online backup - file 5 is offline
ORA-01110: data file 5: 'C:\ORACLE\ORADATA\SAMPLE\tools .DBF'

SQL> alter database begin backup;

Database altered.

The two examples above demonstrate that when you issue the ALTER DATABASE BEGIN BACKUP command, any read-only and offline data files are simply skipped, and processing continues.



SQL> ALTER DATABASE END BACKUP;


ALTER DATABASE END BACKUP command when you have multiple tablespaces still in backup mode. You can issue the statement to take all data files currently in backup mode out of backup mode. However, you can use this statement only when the database is mounted, and not open in Oracle9i.

If the database is open, you can only use ALTER TABLESPACE ... END BACKUP or ALTER DATABASE DATAFILE ... END BACKUP for each affected tablespace or datafile.

In Oracle 10g, you can issue the ALTER DATABASE END BACKUP command when the database is open.

If you issue the command while one of the data files is offline or in read-only mode, a warning message will return:

SQL> alter database end backup;
alter database end backup
*
ERROR at line 1:
ORA-01260: warning: END BACKUP succeeded but some files found not to be in backup mode

Note: However, that by putting all tablespaces in online mode at once, you can generate
large redo logs if there is heavy update activitiy on the affected tablespaces,
because the redo must contain a copy of each changed data block in each changed
datafile.

Be sure to consider the size of the likely redo before using the procedure outlined here..

References

Oracle 10G Documentation / Metalink -Note:270531.1

Errors

ORA-1260 { Message text depends on version }
ORA-1642 begin backup not needed for read-only tablespace
ORA-1110 datafile :
ORA-1128 cannot start online backup - file is offline

---

Difference of CURSOR_SHARING / Dynamic SQL

You need to take extreme care when using cursor sharing and dynamic SQL. You might think that opening a dynamic ref cursor would be treated the same as that same SQL using 'execute immediate'...but check the example below

SQL> drop table t;

Table dropped.

SQL>create table T (x number primary key,y date,z varchar2(10));

Table created.

SQL> insert into t
2  select rownum, sysdate+rownum,rownum
3  from all_objects
4  where rownum <= 5000;  5000 rows created.  SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing = exact;

Session altered.

SQL>
SQL> DECLARE
2     rc SYS_REFCURSOR;
3  BEGIN
4    for i in 1 .. 5000 loop
5      open rc for 'SELECT /*+XXX*/ * from T where x = '||i;
6    end loop;
7  END;
8  /

PL/SQL procedure successfully completed.

SQL> select count(sql_text) from v$sql
2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
      5002  <== as expected, no sharing yet

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing = force;

Session altered.

SQL> select count(sql_text) from v$sql
2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
         1

SQL> DECLARE
2     rc SYS_REFCURSOR;
3  BEGIN
4    for i in 1 .. 5000 loop
5      open rc for 'SELECT /*+XXX*/ * from T where x = '||i;
6    end loop;
7  END;
8  /

PL/SQL procedure successfully completed.

SQL> select count(sql_text) from v$sql
2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
      5002  <== HEY! NO SHARING!

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing = force;

Session altered.

SQL> select count(sql_text) from v$sql
2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
         1

SQL> DECLARE
2  r number;
3  BEGIN
4    for i in 1 .. 5000 loop
5 execute immediate 'SELECT /*+XXX*/1 from T where x
                                        ='||i into r;
6    end loop;
7  END;
8  /

PL/SQL procedure successfully completed.

SQL> select count(sql_text) from v$sql
2  where sql_text like '%XXX%';

COUNT(SQL_TEXT)
---------------
         3    <== This time we got sharing!

SQL>

Who is Active Users on Database ?

Most people are aware of the STATUS column in V$SESSION. If its 'ACTIVE' then that connection is in the process of consuming database resources (running an SQL etc).

However, a lesser known column which is probably even more useful is the LAST_CALL_ET column. Unfortunately, the documentation is hardly explanatory giving

LAST_CALL_ET        NUMBER         The last call

but a more useful description is that its the the number of seconds since the last call.

Thus use this to determine how "idle" a connection has been. The following SQL may be of use

set verify off
select s.sid||','||s.serial# session,
      s.USERNAME,
      s.last_call_et seconds_since_active,
      s.status,
      s.sql_address,
      s.program
from v$session s
where s.sid = nvl(to_number('&sid'),s.sid);

If you add a column with a default, Oracle dutifully goes back and updates every single existing row. which Includes nice trip to row migration Heaven

Can we avoid this ? Yes, just don't do it "all at once"...

SQL> create table T
2  as select * from all_objects;

Table created.

SQL> alter table T add NEW_COL1 number default 10;

Table altered.

Elapsed: 00:00:02.08
SQL> alter table T add NEW_COL2 number;

Table altered.

Elapsed: 00:00:00.02
SQL> alter table T modify NEW_COL2 default 10;

Table altered.

Elapsed: 00:00:00.03
SQL> select count(new_col1), count(new_col2)
2  from T;

COUNT(NEW_COL1) COUNT(NEW_COL2)
--------------- ---------------
        50199               0

Elapsed: 00:00:00.05