Tablespace usage info from DBA_TABLESPACE_USAGE_METRICS view inconsistent with other methods

In the following test, for the same tablespace:PAC_TLGP02M, we obtained different used percent info between method 1 and 2.


Database version:

system@TESTDB> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

Method 1:

system@TESTDB> @tbs_usage_metrics_10g.sql
system@TESTDB> select * from dba_tablespace_usage_metrics
  2  order by used_percent desc;

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
PAC_TLGP02M                       2726400         2816000   96.8181818
PAC_TLGP52M                       4894720         5120256   95.5952202
PAC_TBS11L                       88857600        95207040   93.3309133
PAC_CHARGE_HIST_TAXES51          40320000        43499776   92.6901325
PAC_MEMO01M                      33925120        37633152   90.1469003

( .... truncated ....)

Method 2

system@TESTDB>
system@TESTDB> @l2/freespace
system@TESTDB> clear columns
system@TESTDB> clear computes
system@TESTDB> clear breaks
system@TESTDB>
system@TESTDB> set verify off
system@TESTDB> set linesize 180
system@TESTDB> set pagesize 40
system@TESTDB>
system@TESTDB> compute sum of fsize on dummy
system@TESTDB> compute sum of totalfree on dummy
system@TESTDB> break on dummy
system@TESTDB>
system@TESTDB> column dummy noprint
system@TESTDB> column ts        format a18         heading "Tablespace"
system@TESTDB> column fn        format a65         heading "File Name"
system@TESTDB> column fid       format 9999        heading "File|ID  "
system@TESTDB> column fsize     format 99,999,999  heading "File     |Size (MB)"
system@TESTDB> column maxfree   format 999,999.9   heading "Largest  |Free     |Frag (MB)"
system@TESTDB> column totalfree format 99,999,999  heading "Total    |Free (MB)"
system@TESTDB> column pctused   format 999.99      heading "% Used"
system@TESTDB>
system@TESTDB> select null                                  dummy,
  2         a.tablespace_name                     ts,
  3         a.file_name                           fn,
  4         a.file_id                             fid,
  5         nvl(a.bytes/1024/1024,0)              fsize,
  6         nvl(max(b.bytes/1024/1024),0)         maxfree,
  7         nvl(sum(b.bytes/1024/1024),0)         totalfree,
  8         (1 - nvl(sum(b.bytes),0)/a.bytes)*100 pctused
  9  from dba_data_files a, dba_free_space b
 10  where a.tablespace_name = upper('&tablespace')
 11    and a.file_id = b.file_id (+)
 12  group by a.tablespace_name, a.file_name, a.file_id, a.bytes
 13  order by substr(a.file_name, instr(a.file_name,'/',-1) + 1, 40)
 14  /
Enter value for tablespace: PAC_TLGP02M

                                                                                                        Largest
                                                                                      File   File       Free        Total
Tablespace         File Name                                                          ID     Size (MB)  Frag (MB)   Free (MB)  % Used
------------------ ----------------------------------------------------------------- ----- ----------- ---------- ----------- -------
PAC_TLGP02M        /ora_44a_02/ORACLE/TESTDB/dbf/tlgp02mTESTDB_01.dbf                179      22,000      690.0       8,680   60.55
                                                                                           -----------        -----------
                                                                                                22,000          8,680



The following query list all tablespace usage by method 2

system@TESTDB> set echo on
system@TESTDB> @tf2
system@TESTDB> col tsname  format         a24    justify c heading 'Tablespace'
system@TESTDB> col nfrags  format     9,999,990  justify c heading 'Free|Frags'
system@TESTDB> col mxfrag  format   999,990.999  justify c heading 'Largest|Free Frag'
system@TESTDB> col totsiz  format 9,999,990.999  justify c heading 'Total|Mbytes'
system@TESTDB> col avasiz  format 9,999,990.999  justify c heading 'Available|Mbytes'
system@TESTDB> col pctusd  format       990.99   justify c heading 'Percent|Used'
system@TESTDB>
system@TESTDB> set pagesize 200
system@TESTDB> set linesize 120
system@TESTDB>
system@TESTDB> select
  2  total.tablespace_name                       tsname,
  3  count(free.bytes)                           nfrags,
  4  round(nvl(max(free.bytes)/1048576,0),2)              mxfrag,
  5  total.bytes/1048576                         totsiz,
  6  round(nvl(sum(free.bytes)/1048576,0),2)              avasiz,
  7  round((1-nvl(sum(free.bytes),0)/total.bytes)*100,2)  pctusd
  8  from
  9  (select tablespace_name, sum(bytes) bytes
 10  from dba_data_files
 11  group by tablespace_name)  total,
 12  dba_free_space     free
 13  where
 14  --total.tablespace_name in ('TBS01S','TBS01M','TBS01L','TBS51S','TBS51M','TBS51L')
 15  --and
 16  total.tablespace_name = free.tablespace_name(+)
 17  group by
 18  total.tablespace_name,
 19  total.bytes
 20  order by 6 desc, 1
 21  /

                            Free      Largest        Total        Available    Percent
       Tablespace          Frags     Free Frag       Mbytes         Mbytes      Used
------------------------ ---------- ------------ -------------- -------------- -------
PAC_TBS11L                       32   21,200.000    743,805.000     49,400.000   93.36
PAC_CHARGE_HIST_TAXES51           5   11,200.000    339,842.000     24,500.000   92.79
PAC_MEMO01M                       4   24,160.000    294,009.000     28,920.000   90.16
PAC_TBS61L                        5   11,400.000    360,014.000     36,000.000   90.00
PAC_CHARGE_TAXES51                2    2,210.000     40,768.000      4,150.000   89.82
PAC_TBS61M                       31   10,570.000    175,771.000     18,030.000   89.74

( ... truncated ... )
PAC_TLGP52M                   1,348    1,760.000     40,002.000     15,230.000   61.93
PAC_TLGP02M                     800      690.000     22,000.000      8,680.000   60.55

( ... truncated .... )

Unable to find library 'libsqlplus.sl'

One user account on a Unix server, which is not belong to DBA group, is unable to use sqlplus, the following message received when typing sqlplus on the command line:

/usr/lib/pa20_64/dld.sl: Unable to find library 'libsqlplus.sl'.

This issue was fixed by changing permession of libsqlplus.sql under $ORACLE_HOME/lib from 750 to 755.

Since before yesterday, this user account did not have problem. And last night we did the patch on this server, possibly the permission was changed during the patching.

Search internet there is a thread of disscussion quite similar to our situation.

Why waiting for 'db file sequential read' whereas plan shows FTS?

Application team reported that a query usually run less than 1 hour now took almost 3 hour and still not finish. This query is only a select statement against one table. I checked the execution plan of this query it is a very simple FTS. But when check the wait event, it is waiting for 'db file sequential read', which usually means index scan.

The application team killed the session and re-run the query some time later, and this time it was finished much faster.

Later performance team provided a nice explaination, first of all they provided the following query result:

select x.* , (select file_name from dba_data_files where file_id=p1) name from (
  select p1, count(*) cnt
    from v$active_session_history where sql_id='8xnhck4a6d400' and event='db file sequential read'
   group by p1 order by 2 desc
  )x


                  P1                     CNT                                             NAME                                      
----------------------- ---------------------- --------------------------------------------------------
                    114                    188 /ora_36a_04/ORACLE/DBNAME/dbf/undoDBNAME_6.dbf
                      2                    188 /ora_36a_01/ORACLE/DBNAME/dbf/undoDBNAME_1.dbf                                
                    162                    176 /ora_36a_05/ORACLE/DBNAME/dbf/undoDBNAME_13.dbf                               
                     33                    173 /ora_36a_02/ORACLE/DBNAME/dbf/undoDBNAME_2.dbf                                
                    113                    172 /ora_36a_05/ORACLE/DBNAME/dbf/undoDBNAME_5.dbf                                
                     95                    170 /ora_36a_03/ORACLE/DBNAME/dbf/undoDBNAME_9.dbf                                
                    112                    169 /ora_36a_04/ORACLE/DBNAME/dbf/undoDBNAME_5.dbf                                
                    105                    168 /ora_36a_04/ORACLE/DBNAME/dbf/undoDBNAME_3.dbf                                
                     96                    167 /ora_36a_05/ORACLE/DBNAME/dbf/undoDBNAME_10.dbf                               
                     97                    166 /ora_36a_05/ORACLE/DBNAME/dbf/undoDBNAME_11.dbf                               
                     94                    165 /ora_36a_05/ORACLE/DBNAME/dbf/undoDBNAME_8.dbf                                
                     93                    161 /ora_36a_04/ORACLE/DBNAME/dbf/undoDBNAME_7.dbf                                
                    111                    161 /ora_36a_04/ORACLE/DBNAME/dbf/undoDBNAME_4.dbf                                

This query undoubtly demonstrate that the wait for 'db file sequntial read' is due to read from undo tablespace blocks.

They further expained:

The difference between the first and second run is that on the first (slow) run when query started , it appears there was a lot of DML activity was going on against CH_OBJECTS table - possibly involving longer than normal running non-committed transactions. This made Oracle perform a lot of extra I/O against the UNDO segments in order to maintain a read consistent image of the data true to the moment the query was kicked off.

When the query was kicked off the second time, the abnormal DML activity had already completed and there were no reads from UNDO segments, just the normal serial full scan occurred.

It is a great learning experience for me. Particularly what insterested to me is the way they query the v$active_session_history view. I did the following
exercise in another database:

system@DB2NAME> select distinct(event)  from v$active_session_history where sql_id='axuuxsm4p8hct';

EVENT
----------------------------------------------------------------

reliable message
os thread startup
direct path read
PX Deq: Signal ACK


system@DB2NAME> select p1, p1text, count(*) from v$active_session_history 
where sql_id='axuuxsm4p8hct' and  event='direct path read' group by p1, p1text;

        P1 P1TEXT                             COUNT(*)
---------- -------------------------------- ----------
       540 file number                               1
       407 file number                               2
       193 file number                               1

system@DB2NAME> select file_name from dba_data_files where file_id=540;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/ora_44a_04/ORACLE/DB2NAME/dbf/tlgp11mDB2NAME_06.dbf

system@DB2NAME> select file_name from dba_data_files where file_id=407;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/ora_44a_07/ORACLE/DB2NAME/dbf/tlgp11mDB2NAME_05.dbf



  1  select x.*, (select file_name from dba_data_files where file_id=p1) from
  2  (
  3  select p1, count(*) from v$active_session_history where sql_id='axuuxsm4p8hct' and  event='direct path read' group by p1
  4* ) x
system@DB2NAME> /

        P1   COUNT(*)
---------- ----------
(SELECTFILE_NAMEFROMDBA_DATA_FILESWHEREFILE_ID=P1)
------------------------------------------------------------------------------------------------------------------------
       407          2
/ora_44a_07/ORACLE/DB2NAME/dbf/tlgp11mDB2NAME_05.dbf

       193          1
/ora_44a_02/ORACLE/DB2NAME/dbf/tlgp11mDB2NAME_01.dbf

       540          1
/ora_44a_04/ORACLE/DB2NAME/dbf/tlgp11mDB2NAME_06.dbf

Locking and unlocking Oracle optimizer statistics

Last Saturdy night, I supported a Change Request which involved copying the statistics from a populated partition in the partitioned table to partitions currently showing zero rows. Such change would be made for about 24 partitioned tables . Script to implement this change was provided by the requestor. However, when I run the script, just for the first partitioned table in the script, I recived the following error:

declare
*
ERROR at line 1:
ORA-00081: address range [0x60000000000D5E80, 0x60000000000D5E84) is not
readable
ORA-00600: internal error code, arguments: [4832], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 6518
ORA-06512: at "SYS.DBMS_STATS", line 7254
ORA-06512: at line 30

Note: dababase version: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit

I have no idea at all about this. Later, the requester suggested to unlock the statistics before running the script. After unlocking the schema statistics, the script went through. I don't know lock and unlock schema or table statistics before, so I decided to explore this feature a little bit.

First of all, in the Oracle documentation:
Statistics for a table or schema can be locked. Once statistics are locked, no modifications can be made to those statistics until the statistics have been unlocked. These locking procedures are useful in a static environment in which you want to guarantee that the statistics never change.

The DBMS_STATS package provides two procedures for locking and two procedures for unlocking statistics:

LOCK_SCHEMA_STATS

LOCK_TABLE_STATS

UNLOCK_SCHEMA_STATS

UNLOCK_TABLE_STATS
From another Oracle do:

The LOCK_* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.

I build up a test case trying to simulate the situaltion. However, I got more descriptive error message instead of the one presented above: (my test db is 32-bit 10g)

denis@TEST10G> @lock_stats2
denis@TEST10G> set echo on
denis@TEST10G> rem lock_stats2.sql
denis@TEST10G> rem  -- simulate copy stats from one partition to other partitions that have zero rows
denis@TEST10G> rem
denis@TEST10G> 
denis@TEST10G> drop table t purge;

Table dropped.

denis@TEST10G> drop table my_stat_tab purge;

Table dropped.

denis@TEST10G> 
denis@TEST10G> create table t nologging
  2  partition by range(id)
  3  (
  4    partition  p1 values less than (100),
  5    partition  p2 values less than (200),
  6    partition  p3 values less than (300),
  7    partition  p4 values less than (400),
  8    partition  p5 values less than (500),
  9    partition  p_maxval values less than (MAXVALUE)
 10  )
 11  as
 12  select
 13    rownum id, a.*
 14  from dba_objects a
 15  where rownum < 1000;

Table created.

denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> Prompt >>> only analyze one partition
>>> only analyze  one partition
denis@TEST10G> exec dbms_stats.gather_table_stats(user, 'T', 'P1');

PL/SQL procedure successfully completed.

denis@TEST10G> 
denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                     99
P2
P3
P4
P5
P_MAXVAL

6 rows selected.

denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> Prompt >>> in the case of table stats is locked
>>> in the case of table stats is locked
denis@TEST10G> exec dbms_stats.lock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G> 
denis@TEST10G> exec dbms_stats.create_stat_table(user,'my_stat_tab')

PL/SQL procedure successfully completed.

denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> begin
  2    for part_rec in ( select partition_name from user_tab_partitions where table_name= 'T'
  3          and nvl(num_rows,0) <= 75) loop
  4    begin
  5       dbms_stats.export_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
  6       execute immediate 'update my_stat_tab set c2='||''''||part_rec.partition_name||''''|| ' where c1 = ''T''';
  7       dbms_stats.import_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
  8    end;
  9    end loop;
 10  end;
 11  /
begin
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 1607
ORA-06512: at "SYS.DBMS_STATS", line 2117
ORA-06512: at "SYS.DBMS_STATS", line 7249
ORA-06512: at line 7


denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                     99
P2
P3
P4
P5
P_MAXVAL

6 rows selected.

denis@TEST10G> 
denis@TEST10G> Prompt >>> in the case of table stats is unlocked
>>> in the case of table stats is unlocked
denis@TEST10G> exec dbms_stats.unlock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G> 
denis@TEST10G> begin
  2    for part_rec in ( select partition_name from user_tab_partitions where table_name= 'T'
  3          and nvl(num_rows,0) <= 75) loop
  4    begin
  5       dbms_stats.export_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
  6       execute immediate 'update my_stat_tab set c2='||''''||part_rec.partition_name||''''|| ' where c1 = ''T''';
  7       dbms_stats.import_table_stats(user,'T', null, 'my_stat_tab',null, false,null);
  8    end;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

denis@TEST10G> select partition_name, num_rows from user_tab_partitions where table_name='T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                     99
P2                                     99
P3                                     99
P4                                     99
P5                                     99
P_MAXVAL                               99

6 rows selected.

denis@TEST10G> spool off

The following test shows that we can query the stattype_locked column of user_tab_statistics to know if the table stats is locked or not:

denis@TEST10G> exec dbms_stats.lock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G> select table_name,partition_name, stattype_locked from user_tab_statistics;

TABLE_NAME                     PARTITION_NAME                 STATT
------------------------------ ------------------------------ -----
T                                                             ALL
MY_STAT_TAB
T                              P_MAXVAL                       ALL
T                              P5                             ALL
T                              P4                             ALL
T                              P3                             ALL
T                              P2                             ALL
T                              P1                             ALL

8 rows selected.

denis@TEST10G> exec dbms_stats.unlock_table_stats(user, 'T');

PL/SQL procedure successfully completed.

denis@TEST10G> select table_name,partition_name, stattype_locked from user_tab_statistics;

TABLE_NAME                     PARTITION_NAME                 STATT
------------------------------ ------------------------------ -----
T
MY_STAT_TAB
T                              P_MAXVAL
T                              P5
T                              P4
T                              P3
T                              P2
T                              P1

8 rows selected.

Troubleshooting ORA-16191 : Primary log shipping client not logged on standby

I recieved an error ORA-16191 from email alert tonight when I was on-call. I searched the internet immediately, found that:

---
ORA-16191: Primary log shipping client not logged on standby
Cause: An attempt to ship redo to standby without logging on to standby or with invalid user credentials.
Action: Check that primary and standby are using password files and that both primary and standby have the same SYS password. Restart primary and/or standby after ensuring that password file is accessible and REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to SHARED or EXCLUSIVE
---

I checked the alert log file of the primary database, found that the alert starting from 1:18

Tue Sep 23 01:18:15 2008
Error 1017 received logging on to the standby
------------------------------------------------------------
RA-16191: Primary log shipping client not logged on standby
PING[ARC3]: Heartbeat failed to connect to standby 'PS4008A.world'. Error is 16191.
Tue Sep 23 01:23:16 2008
Error 1031 received logging on to the standby
Tue Sep 23 01:23:16 2008
Errors in file /logs/ORACLE/MYDBNAME/bdump/pphi08a_arc3_2846.trc:
ORA-01031: insufficient privileges
PING[ARC3]: Heartbeat failed to connect to standby 'PS4008A.world'. Error is 1031.

Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------

I also checked the alert log file of the standby database, find that the someone probably was doing shutdown/start the standby db

-----
Tue Sep 23 02:00:33 2008
Physical Standby Database mounted.
Completed: alter database mount standby database
Tue Sep 23 02:00:33 2008
Physical Standby Database mounted.
Completed: alter database mount standby database
Tue Sep 23 02:01:26 2008
alter database recover managed standby database parallel 16 disconnect
Tue Sep 23 02:01:26 2008
alter database recover managed standby database parallel 16 disconnect
---

The timestamp of the pmon process also indicated it was just started tonight.

$ ps -ef  grep pmon
   oracle 23059     1  0 01:59:26 ?         0:00 ora_pmon_PS4008A

Another team member explained to me that we change the SYS password every 2 or 3 months, however,
when we change for the primary database, we don't change for the standby database. So after standby db down and up, Oracle trying to connect the standby using the new password, but the password file in the standby db site still contains old password. This is the reason that I saw the error.

Based on what he said and the problem observed, it looks to me that somehow there is a "connection" from primary db to the standby db using the password in the password files and the two password files have to be in sync to enable such a connection. When we changed the sys password in the primary only, the password files are out of sync. So when standby is bounced, this "connection" need to be re-established but it can not because the out of sync situation.

I thus used the "orapwd" command to create a new password file on the standby site. Problem resolved. No such alerts recieved again.