Fixed Table STATS Issue after Gathering Fixed Object STATS

Recently we were working on another 130+TB database with 5200+ datafiles. After the database was upgraded from 11.2.0.3 to 11.2.0.4 on a full rack X4 Exadata, I did some verifications and noticed my query (using dba_free_space and dba_data_files) to find out tablespace usage took forever and never completed. I have run the same query on other 100+ TB databases and it could take some time, but never run into the situation that it has never completed.

I pulled out the execution plan for the query and found out the majority of was on fixed table X$KTFBUE. The plan was doing full table scan. At the time I killed the query, the actual row processed was 10 million rows while the estimate total number of rows was 100,000 rows. By the way, 100,000 rows is the default value for X$KTFBUE when no stats is available. Obviously it is related to STATS issue. Then I run the query below to find out whether X$KTFBUE has STATS or not.

select * from dba_tab_statistics where  table_name='X$KTFBUE';

It indeed did not have stats. Maybe we forgot to collect the fixed object stats. Run the queries below and find out we had stats in the majority of fixed tables.

SELECT * FROM DBA_TAB_STATISTICS 
WHERE OBJECT_TYPE='FIXED TABLE'; 

SELECT * FROM DBA_TAB_STATISTICS 
WHERE OBJECT_TYPE='FIXED TABLE' 
and last_analyzed is null;

Maybe we had issue during gathering fixed objects. So I rerun the stats collection shown below for fixed objects.

SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
Elapsed: 00:05:19.42

It completed successfully in 5 minutes. Checked out X$KTFBUE, still no STATS. This became interesting to me. With a fixed table having at least 10+ million rows and seem to be one of important fixed table to get tablespace sizing information, why is it excluded from fixed object stats collection?

With this question in mind, I did some research. There is a document discussing why STATS is missing for certain fixed tables: Why do some Fixed Tables not have Optimizer Statistics (Doc ID 1355608.1). Here is the answer in the document.

Why do some fixed tables not have statistics even after running: DBMS_STATS.GATHER_FIXED_OBJECTS_STATS?

Some fixed tables are left without stats because the development team has flagged the code to avoid statistics gathering for some fixed tables. This is because they know they will perform better without stats or because it is not supported to gather the statistics upon them.
This was confirmed in : Bug 12844116 fixed tables statistics

The document applies to version 10.2.0.4 to 11.2.0.2. Obviously it is covered version 11.2.0.4 in my case.

Another document also discussed the similar behavior: Query Against DBA_FREE_SPACE is Slow After Applying 11.2.0.4 (Doc ID 1904677.1). The document mentions two major symptoms that simialr to my issue:
1. Slow query from dba_free_space after applying 11.2.0.4
2. The query is doing a Full Tabble Scan (FTS) against X$KTFBUE.

Some fixed tables(X$) have no CBO statistics against them and use defaults; it may be too expensive to gather stats or the tables are too volatile such that any statistics are immediately outdated.

The sys.x$ktfbue table is one such object. These statistical inaccuracies can cause suboptimal plans in some cases.

The document mentioned gathering stats on x$ktfbue table. So I went ahead collecting stats as follows:

EXEC DBMS_STATS.gather_table_stats('SYS', 'X$KTFBUE');

WaitFixedTableStats
As it took only 5 minutes to collect fixed object stats, I thought a couple of minutes or maxmium 10 minutes should complete the stats gathering for X$KTFBUE table. I was so wrong, one hour, two hours passed and stats job was still going. Maybe the gather_table_stats was stuck somewhere. Check out OEM SQL Monitor, I can see the number of rows processed still going up slowly. I was just like the above squirrel patiently waiting for the finish. Finally, after 7.5 hours it completed successfully. The table has about 99 million rows, about 1000 times more than default value of 100,000 rows. Now I understand why this fixed table is excluded from GATHER_FIXED_OBJECTS_STATS. It is just too expensive to collect stats. Majority of time it should not cause an issue. This is the first Exadata database I notices having fixed object issue after running GATHER_FIXED_OBJECTS_STATS.

During the time I waited for the stats gathering completion, I found another Oracle Document: Query against DBA_EXTENTS slow after upgrade to 11.2.0.3 (Doc ID 1453425.1). This applies to database version 11.2.0.3 and later. Here are some part of the note:

SYMPTOMS
Queries on DBA_EXTENTS are much slower due to the presence of a merge join cartesian.
This can also influence the performance of script used to collect upgrade/migrate diagnostics as per instructions in:
Document 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql).
Running script on version 11.2.0.1 prior to upgrade, there was no performance issue.
The following SQL query from dbupgdiag.sql may run slowly (~2 hours) after applying patchset 11.2.0.3.
Prompt ===========================================
Prompt Tablespace and the owner of the aud$ table
Prompt ===========================================
select owner,tablespace_name from dba_extents where segment_name=’AUD$’ group by
owner,tablespace_name;

CHANGES
An execution plan regression occurs if there are a large number of records in the X$KTFBUE table. Gathering of dictionary or fixed object stats does not improve performance as this table is intentionally omitted from those packages.

CAUSE
The Cost Based Optimizer uses a cardinality estimate of 100,000 rows for this table, and the 11.2.0.3 execution plan is doing a full table scan. If you have a large number of extents, this query can take more than 1 hour to complete. There are a number of unpublished bugs open on slow perfomance for queries on DBA_EXTENTS.
For example, following bugs:
Bug:13542477 ON 11.2.0.3 QUERY DBA_EXTENTS IS SO SLOW
Bug:14221159 JOIN CARDINALITY INCORRECT CALCULATED AFTER FIX 11814428

SOLUTION
Following are possible workarounds to improve the performance
Gathering stats on the X$KTFBUE table using the following command:
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SYS’, ‘X$KTFBUE’);
OR
alter session set “_optimizer_cartesian_enabled” = false;
OR
alter session set “_smm_auto_cost_enabled” = false;
OR
Disable fix for unpublished Bug Bug 11814428: POOR CARDINALITY ESTIMATE FROM JOIN WITH A UNIONALL VIEW:
alter session set “_FIX_CONTROL” = “11814428:0″;

This document reminded me the similar issue we run into last year when we built the same database for POC purpose. At the time when we tried to upgrade the POC database from 11.2.0.3 to 11.2.0.4. The dbupgdiag.sql script run forever and never finished. Opened SR with Oracle and Oracle Support recommended to comment out the following line in dbupgdiag.sql script as a workaround solution.
select owner,tablespace_name from dba_extents where segment_name=’AUD$’ group by owner,tablespace_name;

Finally after the stats collection completed, run the same query again, the result comes back within 15 seconds. Checked out the execution plan, I can see it does not use full table scan of the X$KTFBUE table, but use an index from this table. Issue solved and life is good.

fixedTableStatsIssue

Additional note:
1. So what exactly X$KTFBUE table is used for? X$KTFBUE is a dynamically generated map of used extents in locally managed tablespace.

The followings are the definitions for the columns in x$ktfbue.

ADDR : N/A
INDX : N/A
INST_ID : N/A
KTFBUESEGTSN : ts# containing this segment
KTFBUESEGFNO : Relative number of the file containing the segment header
KTFBUESEGBNO : segment header block number
KTFBUEEXTNO : Extent number
KTFBUEFNO : Relative number of the file containing the extent
KTFBUEBNO : Starting block number of the extent
KTFBUEBLKS : Size of the extent in ORACLE blocks
KTFBUECTM : commit_jtime,Commit Time of the undo in the extent expressed as Julian date
KTFBUESTT :commit_wtime,Commit Time of the undo in the extent expressed as wall clock time
KTFBUESTA : Transaction Status of the undo in the extent;1, ‘ACTIVE’, 2, ‘EXPIRED’, 3, ‘UNEXPIRED’, 0 for non‐undo

2. If looked at the execution plan, even we fixed the stats issue for X$KTFBUE table, we might still run into issue related to recyclebin. The plan shows nested loop involving recyclebin. Here is the DBA_FREE_SPACE looks like.

SQL> set long 90000;
SQL> set pages 999;
SQL> select dbms_metadata.get_ddl('VIEW','DBA_FREE_SPACE') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','DBA_FREE_SPACE')
--------------------------------------------------------------------------------
  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_FREE_SPACE" ("TABLESPACE_NAME", "FILE_
ID", "BLOCK_ID", "BYTES", "BLOCKS", "RELATIVE_FNO") AS
  select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
union all
select
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
  and rb.ts# = fi.ts#
  and u.ktfbuefno = fi.relfile#
  and u.ktfbuesegtsn = rb.ts#
  and u.ktfbuesegfno = rb.file#
  and u.ktfbuesegbno = rb.block#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select ts.name, fi.file#, u.block#,
       u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
  and u.ts# = fi.ts#
  and u.segfile# = fi.relfile#
  and u.ts# = rb.ts#
  and u.segfile# = rb.file#
  and u.segblock# = rb.block#
  and ts.bitmapped = 0

Right now our database was just built and recyclebin was empty. If there are a lot of rows in the recyclebin, I will not be surprised the performance could be bad again. If it happens, one possible solution is to purge the recyclebin.

3. Another interesting part is that the total number of rows in X$KTFBUE table was about 99 million rows while the total number of extents in the database was also around 99 million rows. So reducing the total number of extent in the database seem a good way to reduce this stats issue. Pay attention to the INITIAL and NEXT in the storage clause. Setting too small will have a large number of extents and create headache in querying fixed tables.

New archivelog Files are Deleted before Applied During the Restore of Standby Database

Following one of blogs last year about restoring/recovering archivelog files to a standby database manually, I run into another interesting issue. At that time, I didn’t have time to write a blog about the issue. Right now I finally can write down what I found last year.

After a few days’ restore of archivelog files, our recover process began running into issue. Our process was to restore all archivelog files first for a certain date, then perform the recovery for the same date. So the recovery should run through until the last recover point identified by my previous blog . Interesting, the recovery stop in the middle of process and complaint about archivelog files not found. At first, I was thinking maybe I run the restore for the wrong sequence range. Then performed the restore of archive logfile again. The recovery process passed that sequence, but quickly stop at another archivelog. At this moment, I know something was not right. Of course, it is not related to wine shown below.

file_missing

Could be archivelog file deleted from RECO due to the FRA space constraint? But it did not make sense for the deletion of archive log even before the archivelogs were applied. I checked the alert logfile and found the something interesting. The alert log shows log seqence 615578 thread 5 was deleted at 09:34.

Sun Sep 07 09:34:50 2014
Deleted Oracle managed file +RECO/userdb/archivelog/2014_09_07/thread_5_seq_615578.22013.857628717
Sun Sep 07 09:34:50 2014
Deleted Oracle managed file +RECO/userdb/archivelog/2014_09_07/thread_5_seq_615577.22019.857628719
Deleted Oracle managed file +RECO/userdb/archivelog/2014_09_07/thread_5_seq_615568.22021.857628661

The recover log shows we just recover to 614809 thread 5 at 20:19. So for the log sequence 615578, 615577, and 615568 for thread 5 were deleted long before they can be applied.

ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT …
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log +RECO/userdb/archivelog/2014_09_07/thread_6_seq_491327.21626.857651973
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT …
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log +RECO/userdb/archivelog/2014_09_07/thread_5_seq_614809.9496.857655203
Sun Sep 07 20:19:51 2014
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT …
ALTER DATABASE RECOVER CONTINUE DEFAULT

After did some research, we found an Oracle note (Doc ID: Bug 17370174 : NEW ARCHIVELOG FILES ARE DELETED FIRST FROM THE FRA). Here is part of the description from the note:

DETAILED PROBLEM DESCRIPTION
============================
The algorithm used by the FRA to delete the files is deleting the more recent archivelog files created when there are old archivelogs in the FRA eligible to delete first.

TECHNICAL IMPACT
================
The backup of the archivelog files is failing because the archivelog file was deleted.

WORKAROUND INFORMATION
======================
The workaround recommended is add another condition to the archivelog policy in order to prevent that the archivelog files are deleted if there is not backup taken.

The Solutions
Instead of messing around our standard archivelog policy in RMAN, we identified the following approaches to get around the issue.

Approach 1:
Increase db_recovery_file_dest_size parameter from 10000G to 12800G. This approach can resolve the issue immediately for the current date. But will run into the same issue a few days later when FRA is reaching to full again.

Approach 2:
Manually remove completed applied archive logfiles using the following command:
delete archivelog all completed before ‘sysdate – n';

Approach 3:
Add DELETE ARCHIVELOG MAXSIZE in the recover command.

RMAN > recover database until sequence 343848 thread 2 delete archivelog maxsize 1000g;

So the above command will recover to a latest consistent state and while recovery as when 1TB of archives are applied, the applied archives will bedeleted thus making space in Flash recovery area.

DELETE ARCHIVELOG causes RMAN to delete restored log files after they have been applied to the datafiles, to save disk space.

MAXSIZE 1000g limits space occupied by restored logs at any given moment to 1000GB

The other feasible options to deletion policy for archivelogs can be the followings:
Configure deletion policy to none. Archivelogs don’t get deleted unless we manually delete them.
RMAN > configure archivelog deletion policy to none;

Configure deletion policy to backed up 1 times to disk: Archivelogs will not get deleted unless they are backed up.
RMAN > configure archivelog deletion policy to backed up 1 times to disk;

Moving SQL Profile during Exadata Migration

My last post discusses the steps to move stats to Exadata environment. During the same cutover window, we actually did another task: move certain SQL Profiles from UAT environment to PROD environment. Just like photo below, let’s do the moving in one run.
moving_stuff

For SQL Profile, I usually use it as the last approach to resolve production performance issue. I always recommend my clients to change application code to tune the poor performed queries first. Only at the time no code change is allowed and production database is suffering from bad queries significantly, SQL Profiles will be used. For a large legacy database suffering from poor performance in the past, it’s not surprise to see many SQL Profiles in the legacy database. If there are only 10 or 20 SQL Profiles, it can be manageable. But there is no way to maintain hundreds, even thousands SQL Profiles in the database, especially during migration from 11.2.0.3 Non-Exadata to 11.2.0.4 Exadata environment. So after we restore the legacy database into an UAT environment on Exadata, we disabled all SQL Profiles in the database. During the testing, if query performance was not good in Exadata environment, we implemented new SQL Profile or reused the old one if existing one performed better. By using strategy, we cut down the SQL Profiles from 256 before migration to 9 during the migration. During the cutover window, we only need to move these 9 SQL Profiles from UAT to PROD environment.

The followings are the sample steps for moving SQL Profiles. It uses the same db_my_stats schema created in the previous post. Here is the summary of the steps.
1. Copy the enabled SQL Profile to a staging table under the same db_my_stats schema created in the last post.
2. Export the staging table.
3. Copy the export data pump files to PROD database on Exadata.
4. Disable all SQL Profile in PROD database.
5. Import the enabled SQL Profiles in PROD database.

Step 2 and 3 are discussed in my last post and I am not going to repeat them here. Only step 4 and 5 are performed during cutover window. Here are the sample scripts to illustrate the steps:

Copy the enabled SQL Profile to a staging table
Generate necessary sql scripts.
vi gen_exp_imp_sqlprofile.sql

set echo off
set feedback off
set lines 150
set pages 0

prompt ---------------- Generate SQL Profile Export and Import -----------------

prompt
prompt -- generate SQL to export SQL profile
prompt
spool EXPORT_TO_SQLPROFILE_TABLE.SQL
select 'spool EXPORT_TO_SQLPROFILE_TABLE.log' from dual;
select
  'EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF ( ' ||
  'staging_table_name => ''PROFILE_STG'', profile_name=>''' || name || '''); ' content
  from dba_sql_profiles
 where status = 'ENABLED'
order by name;
select 'spool off' from dual;

prompt
prompt -- generate SQL to get total rows in the SQL Profile table
prompt
spool GET_EXPORT_SQL_PROFILE_ROW_COUNT.SQL
select 'spool GET_EXPORT_SQL_PROFILE_ROW_COUNT.log' from dual;
select 'prompt SQL profiles qualifed for export ' from dual;
select 'SELECT name, status FROM dba_sql_profiles where status = ''ENABLED'' order by name;' from dual;

select 'prompt SQL profiles exported ' from dual;
select 'SELECT count(*) FROM DB_MY_STATS.PROFILE_STG;' from dual;
select 'select obj_name, obj_type, sql_handle  status from dbmir.PROFILE_STG order by object_name;' from dual;
select 'spool off' from dual;

prompt
prompt -- generate SQL to import SQL profile
prompt
spool IMPORT_TO_SQLPROFILE_TABLE.SQL
select 'spool IMPORT_TO_SQLPROFILE_TABLE.log' from dual;
select
  'EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE, ' ||
  'staging_table_name => ''PROFILE_STG'', profile_name=>''' || name || '''); ' content
  from dba_sql_profiles
 where status = 'ENABLED'
order by name;
select 'spool off' from dual;

spool off

Run the gen script.
@gen_exp_imp_sqlprofile

Export SQL Profile
sqlplus / as sysdba
connect db_my_stats/db_my_stats

Create SQL profile staging table
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => ‘PROFILE_STG’, schema_name=>’DB_MY_STATS’);

Run the script to export SQL Profile to the staging table and get the row count for the exported profiles.
@EXPORT_TO_SQLPROFILE_TABLE.SQL
@GET_EXPORT_SQL_PROFILE_ROW_COUNT.SQL

At this moment, the export is done. Then follows the export and import step from last post to populate the staging table PROFILE_STG in the target database.

Disable all SQL Profiles
vi disable_all_sqlprofile.sql

BEGIN
for r in
( select name, category, status, sql_text, force_matching
    from dba_sql_profiles
   order by category, name)
  loop
     DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => r.name, attribute_name => 'STATUS', value =>  'DISABLED');
  end loop;
END;
/

Run the script.
@disable_all_sqlprofile.sql

Import SQL Profile
@IMPORT_TO_SQLPROFILE_TABLE.SQL

Verify the the enabled SQL Profile
select name, category, type, status from dba_sql_profiles
where status = ‘ENABLED';

Ok, we are done with the moving of SQL Profile here.

Moving STATS for Large Database during Exadata Migration

I have done many Exadata migrations for the past two and half years. For the majority of migrations, my clients have enough downtime to complete the statistic gathering during the cutover window. However, this strategy is not going to work for a large database. Recently we migrated a 110+ TB database to Exadata. There were many chanllenges involved in this migration, like STATS gathering, tablespace reaching 32TB limit and 1022 datafile limit, upgrade from 11.2.0.3 to 11.2.0.4 during the cutover window and critial query tuning with only a few weeks before cutover. Anyway, there were many moving parts during the migration. In this blog, I am focusing only on STATS strategy during the cutover window.

This database had issue in gathering stats in the past and run for days without completion in the legacy environment. As the stats gathering had significant impact on the database performance, the client had to turn off the stats gathering and lock the stats. So many tables’ stats were at least two years old. With the database moving from non-Exadata 11.2.0.3 environment to Exadata 11.2.0.4 environment, we need to have the new stats available before the database can be released to business users. The question was how we were going to do it? Just like the drawing below, we need to find the fastest window to get our work done.

fast_stats

Both UAT and PROD environments are using X4 full rack Exadata. Even with eight database servers, the full stats gathering with 384 parallism could still take a few days. Even incremental stats gathering could take 10+ hours. It is definitely not going to work during cutover window with limited downtime available.

For this migration, we used Data Guard and our physical standby on Exadata was exact physical copy of the legacy primary database. We also built the UAT database with RMAN restore and last build of the UAT was just two weeks away from the cutover date. So we use the following strategy to work around the stats gathering problem during cutover window.
1.  Gather stats on UAT database. It doesn’t matter whether it takes one day or two days. As long as it is outside the cutover window, we were fine with that.
2. Copy the STATS in UAT database to STATS staging tables.
3. Export the staging tables.
4. Copy the export data pump files to PROD database on Exadata
5. Import the stats to PROD DB.

Step 1 to 4 do not have downtime requirement and can be done ahead of time. Only the last step needs to be performed during the cutover window. In this blog, I am not going to discuss Step 1 and Step 4, and only on exporting and importing the stats. I used our lab to demonstrate the steps and scripts involved.

Export Stats from Source Database

cd /dbfs/work/wzhou/test_stat

sqlplus / as sysdba
create user db_my_stats identified by db_my_stats default tablespace users temporary tablespace temp; 
grant connect, resource, dba to db_my_stats;  
create directory DB_MY_STATS_DIR as '/dbfs/work/wzhou/test_stat'; 
grant read, write on directory DB_MY_STATS_DIR to system;

Generate necessary sql scripts.
vi gen_exp_imp_stats.sql

-- Generate all the necessary scripts for export and import schema stats
--set time on
--set timing on
set echo off
set feedback off
set lines 150
set pages 0

prompt
prompt generate SQL to create schema stat table
prompt
spool CREATE_SCHEMA_STATS_TABLE.SQL
select 'set timing on' from dual;
select 'spool CREATE_SCHEMA_STATS_TABLE.log' from dual;
select 'prompt '||owner||chr(10)||
  'exec DBMS_STATS.CREATE_STAT_TABLE(''DB_MY_STATS'', ''STATS_' || owner || ''');' content
  from dba_segments
 where owner not in ( 'DB_MY_STATS', 'ANONYMOUS','APPQOSSYS','DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSTEM','WMSYS','XDB','XS$NULL', 'ORDSYS','ORDDATA')
group by owner
order by owner;
select 'spool off' from dual;
spool off

prompt
prompt -- generate SQL to export schema stat to stat table
prompt
spool EXPORT_TO_SCHEMA_STATS_TABLE.SQL
select 'set timing on' from dual;
select 'spool EXPORT_TO_SCHEMA_STATS_TABLE.log' from dual;
select 'prompt '||owner||chr(10)||
  'exec DBMS_STATS.EXPORT_SCHEMA_STATS( ' ||
  'ownname => ''' || owner || ''', ' ||
  'stattab => ''STATS_' || owner || ''', ' ||
  'statid => ''' || owner || ''', ' ||
  'statown => ''DB_MY_STATS'');' content
  from dba_segments
 where owner not in ( 'DB_MY_STATS', 'ANONYMOUS','APPQOSSYS','DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSTEM','WMSYS','XDB','XS$NULL', 'ORDSYS','ORDDATA')
group by owner
order by owner;
select 'spool off' from dual;
spool off


prompt
prompt -- generate SQL to import schema stat to stat table
prompt
spool IMPORT_TO_SCHEMA_STATS_TABLE.SQL
select 'set timing on' from dual;
select 'spool IMPORT_TO_SCHEMA_STATS_TABLE.log' from dual;
select 'prompt '|| owner || chr(10) ||
  --'--exec dbms_stats.delete_schema_stats (ownname => '''||owner||''');' || chr(10)||
  'exec DBMS_STATS.IMPORT_SCHEMA_STATS( ' ||
  'ownname => ''' || owner || ''', ' ||
  'stattab => ''STATS_' || owner || ''', ' ||
  'statid => ''' || owner || ''', ' ||
  'statown => ''DB_MY_STATS'');' content
  from dba_segments
 where owner not in ( 'DB_MY_STATS', 'ANONYMOUS','APPQOSSYS','DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSTEM','WMSYS','XDB','XS$NULL', 'ORDSYS','ORDDATA')
group by owner
order by owner;
select 'spool off' from dual;

Run the gen script.
@gen_exp_imp_stats

Create stats staging table.
@CREATE_SCHEMA_STATS_TABLE.SQL

Export schema stats to the stats staging table
@EXPORT_TO_SCHEMA_STATS_TABLE.SQL

Prepare the export script
vi run_exp_stats
expdp parfile=exp_schema_stats.par

vi exp_schema_stats.par
USERID=db_my_stats/db_my_stats
PARALLEL=4
DIRECTORY=DB_MY_STATS_DIR
DUMPFILE=stats_%U.dmp
LOGFILE=stats_exp.log
METRICS=Y
FILESIZE=4G
SCHEMAS=DB_MY_STATS
JOB_NAME=stats_exp

Run the script to export the schema
run_exp_stats

Ok, at this moment, the stats export is done. Copy the datapump file to target system.

Import Stats to Target Database
On target database, do the similar step as above.

create user db_my_stats identified by db_my_stats default tablespace users temporary tablespace temp; 
grant connect, resource, dba to db_my_stats;  
create directory DB_MY_STATS_DIR as '/dbfs/work/wzhou/test_stat'; 
grant read, write on directory DB_MY_STATS_DIR to system;

Prepare the import script.
vi run_imp_stats
impdp parfile=imp_schema_stats.par

vi imp_schema_stats.par
USERID=db_my_stats/db_my_stats
PARALLEL=4
DIRECTORY=DB_MY_STATS_DIR
DUMPFILE=stats_%U.dmp
LOGFILE=stats_imp.log
METRICS=Y
TABLE_EXISTS_ACTION=REPLACE
SCHEMAS=DB_MY_STATS
JOB_NAME=stats_imp

Run the script to import the schema
run_imp_stats

Import the stats from stats staging tables.
@IMPORT_TO_SCHEMA_STATS_TABLE.SQL

Ok, we’re done with the stats import.

The above strategy worked pretty well during the cutover window. The export of STATS took about half hour and the import of STATS took slight less time than the export. We are happy the STATS work does not take significant amount of time for this 110+ TB databases during the cutover.

Out of Space Error while still Have Space

Recently I worked on a large database on a X-4 full rack Exadata for a few months. I am using sqlplus command every day on this database without any issue. Then suddenly, I got the following error on db node 1 when trying to run sqlplus.

$ sqlplus / as sysdba
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925

I know this is the issue at OS level, not at database level. The audit trail directory should be under /u01. Run the df command. Interesting, I see it still have about 12G available on /u01, just like the parking space image below.

$ df -kh /u01
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VGExaDb-LVDbOra1
                       99G   82G   12G  88% /u01

space_issue_201410
I know /u01 takes some space, but 82G out of 99G seem too much. Check out the same /u01 on db node 2. Yes, it has only 22G used. Try sqlplus there without any issue. At this moment I roughly know the cause of the issue. I saw similar space issue in another client in the past and the database there had serious performance issue. The cause of the issue was millions of audit files under the audit directory.

When we run df or du command, majority of time we are only interested in how much space we use and how much space is available. This is the space limit in the file system. There is another limit: inode limit. Inode is the metadata of a file, containing information like file size, owner, group, file access/modify/change time and much more. When a file is created, the metadata of the file is stored in an inode (or inode number). Each file has a unique inode number that is used internally by the file system. When accessing a file, the system first seraches inode table for the unique inode number. With the information from the inode, the file can be found and accessed.

We usually don’t see the inode reach to its limit quite often. Use df -i command can help to identify inode limit issue. Here is the result after running df -i command.

$ df -h -i /u01
Filesystem            Inodes   IUsed   IFree IUse% Mounted on
/dev/mapper/VGExaDb-LVDbOra1
                         13M     13M       0  100% /u01

Obviously we used up 100% of all 13M inodes in /u01 file system.

At this moment, I know where Oracle database can generate many files. It’s audit directory. Run the following command to find out space usage for audit folder and run for almost 6 minutes without the result back. Had to kill the process.

Then use another way to find out the top 10 usage. As expected, audit directory took a lot of space there with over 50G.

$ cd /u01/app/oracle/product/11.2.0.4/dbhome_1
$ du -a . | sort -n -r | head -n 10
57566392        .
52851540        ./rdbms
52722128        ./rdbms/audit
788268  ./lib
768116  ./bin
559916  ./owb
335520  ./oc4j
309500  ./assistants
303920  ./assistants/dbca
303596  ./ctx

There is another way to find out whether the directory is big. Run ls command from parent directory.

$ cd /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms
$ ls -l
total 1039920
drwxr-xr-x 2 oracle oinstall      49152 Aug 22 10:33 admin
drwxr-xr-x 2 oracle oinstall 1063747584 Oct 20 19:20 audit
drwxr-xr-x 2 oracle oinstall       4096 Jun 27 14:47 demo
drwxr-xr-x 2 oracle oinstall       4096 Jun 27 14:47 doc
drwxr-xr-x 5 oracle oinstall       4096 Jun 27 14:48 install
drwxr-xr-x 2 oracle oinstall       4096 Jun 27 14:47 jlib
drwxr-xr-x 2 oracle oinstall       4096 Jul 31 11:45 lib
drwxr-xr-x 2 oracle oinstall       4096 Aug 22 18:30 log
drwxr-xr-x 2 oracle oinstall       4096 Jun 27 14:47 mesg
drwxr-xr-x 2 oracle oinstall       4096 Jun 27 14:47 public
drwxr-xr-x 5 oracle oinstall       4096 Jun 27 14:46 xml

So the solution seems easy. Just remove aud file under the audit directory. Then I run rm command. It runs for a few minutes and finally gave an error message below.

$ rm *.aud
-bash: /bin/rm: Argument list too long

It seems having a lot of files in this directories and I would like to find out the total number of files in the directory. Tried to run ls -l | wc -l to get file count. It had never finished and taken forever to run. The reason why ls -l is so slow is that by default, ls command sorts the file alphabetically. So if you’re interested in listing some files quickly, you could use ls -f | head -100 command to get a list of files.

At this moment, I had to use rm -rf from its parent directory and it worked. Even with this method, it took more than 8 hours to completed.

In the middle of this delete process, I stopped the delete process after the free inode reached to 1%. As I have some free inodes available for other processes and don’t worry about this space issue in a short time, I would like to find out how long it take to calculate the space usage under audit directory and how many audit files under the same directory.

Here are the results:

$ cd /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms
$ df -i /u01
Filesystem            Inodes   IUsed   IFree IUse% Mounted on
/dev/mapper/VGExaDb-LVDbOra1
                     13107200 12955396  151804   99% /u01

$ time du -khs /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/audit
50G     /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/audit

real    26m6.220s
user    0m5.299s
sys     2m25.058s

$ time ls -l | wc -l
12839426

real    29m16.458s
user    2m47.544s
sys     2m9.860s

The above result shows it took 29 minutes to find out 12.8 million files in the directory and 26 minutes to get the space usage for the audit directory. So basically forget about the idea to use ls -l command when there are millions of files in a directory.

There are a few other useful command to find out inode information.

$ ls -i
4653873 admin  4949095 demo  4653875 install  4653881 lib  4653883 mesg    4653885 xml
4734977 audit  4653874 doc   4653880 jlib     4653882 log  4653884 public

$ stat audit
  File: `audit'
  Size: 1063747584      Blocks: 2079672    IO Block: 4096   directory
Device: fc03h/64515d    Inode: 4734977     Links: 2
Access: (0755/drwxr-xr-x)  Uid: ( 4085/  oracle)   Gid: ( 1140/oinstall)
Access: 2014-10-21 14:39:39.000000000 -0400
Modify: 2014-10-21 17:30:32.000000000 -0400
Change: 2014-10-21 17:30:32.000000000 -0400

As ls -l command is painfully slow to list files in a directory with millions of files, there are other ways to retrieve the files faster.

find . -type f -printf ‘%T+ %p\n’ | sort -r | head -100
This commands finds all files in the current directory, lists them from newest to oldest, then prints only the first 100 files.

find . -type f -mtime -3 -printf ‘%T+ %p\n’ | sort -r | head -100
This commands finds only files created in the last day (-mtime -3), and sorts only these files.

find . -type f -mmin -20 -printf ‘%T+ %p\n’ | sort -r | head -100
For finer control, this command selects only files created/modified less than 20 minutes ago.