Collecting Statistics Information (if statistics collection is needed)

Enter the user for which statistics collection is needed in the WHERE clause.

SQL> set linesize 150
SQL> SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'''||OWNER||''', TABNAME => '''||TABLE_NAME||''');' FROM DBA_TABLES WHERE OWNER='TIBERO'; 

SQL> Execute the query output as a result

- Example query
-------------------------------------------
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'TIBERO', TABNAME => 'A');
-------------------------------------------

Verify Statistics Information 

Check whether the statistics information has been collected properly.

SQL> SELECT 'SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='''||TABLE_NAME||''' ORDER BY OWNER;' FROM DBA_TABLES WHERE OWNER='TIBERO' ;

SQL> Execute the query output as a result


- Example query
-------------------------------------------
SQL> SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='A' ORDER BY OWNER;
-------------------------------------------

Create Table for Storing Statistics Information

SQL> Set parameters for DBMS_STATS.CREATE_STAT_TABLE ('User_Name','Table_Name','TS_Name')

SQL> Execute the query output as a result


- Example query
-------------------------------------------
SQL>  EXEC DBMS_STATS.CREATE_STAT_TABLE ('TIBERO','STATS_SAVE','USR');
-------------------------------------------

Extract Statistics Information 

Enter the table where statistics information is stored in the stattab parameter.

SQL> SELECT 'EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME =>'''||OWNER||''', TABNAME => '''||TABLE_NAME||''', stattab =>''STATS_SAVE'');' FROM DBA_TABLES WHERE OWNER='TIBERO'; 
 

SQL> Execute the query output as a result

- Example query
-------------------------------------------
ex) EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME =>'TIBERO', TABNAME => 'A', stattab =>'STATS_SAVE');
-------------------------------------------

Dump the Table Containing Statistics Information Using tbexport

$ tbexport username=tibero password=tmax sid=tibero1 port=8857 table=STATS_SAVE file=stats_save.dat log=stats_save.log

Import Statistics Information Using tbimport 

CASE 1) Import Example to a Different User (TIBERO → TIBERO1)

$ tbimport username=tibero1 password=tmax sid=tibero1 port=8857 fromuser=tibero touser=tibero1 file=stats_save.dat log=stats_save_imp.log

CASE 2) Same User (Export & Import Target are the Same)

Import statistics information extracted from TIBERO into the TIBERO user.

SQL> SELECT 'EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME =>'''||OWNER||''', TABNAME => '''||TABLE_NAME||''', STATTAB =>''STATS_SAVE'',NO_INVALIDATE => FALSE);' FROM DBA_TABLES WHERE OWNER='TIBERO'; 

SQL> Execute the query output as a result

- Example query
-------------------------------------------
ex) EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME =>'TIBERO', TABNAME => 'A', STATTAB =>'STATS_SAVE',NO_INVALIDATE => FALSE);
-------------------------------------------

CASE 3) Statistics Owner ≠ Import Target User

Import statistics information extracted from TIBERO1 into the TIBERO user.

SQL> SELECT 'EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME =>'''||OWNER||''', TABNAME => '''||TABLE_NAME||''', STATTAB =>''STATS_SAVE'', STATOWN=>''TIBERO1'', NO_INVALIDATE => FALSE);' FROM DBA_TABLES WHERE OWNER='TIBERO'; 

SQL> Execute the query output as a result

- Example query
-------------------------------------------
ex) EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME =>'TIBERO', TABNAME => 'A', STATTAB =>'STATS_SAVE', STATOWN=>'TIBERO1', NO_INVALIDATE => FALSE);
-------------------------------------------

CASE 4) Same Table Storing Statistics but Different User Name for Import

Import statistics information extracted from TIBERO into the TIBERO1 user.

1. To insert statistics information into tibero1, create the same table or dump all tables related to the tibero user using tbexport and then import.

   SQL> SELECT 'CREATE TABLE '||TABLE_NAME||' AS SELECT * FROM TIBERO.'||TABLE_NAME||';' FROM DBA_TABLES WHERE OWNER='TIBERO'; 
   
   SQL> Execute the query output as a result
   
   - Example query
   -------------------------------------------
   ex) CREATE TABLE A AS SELECT * FROM TIBERO.A;
   -------------------------------------------

2. Change the USER name in the statistics data. Enter the user of the table where statistics information will be inserted into column C5.

   SQL> update tibero1.stats_save set c5='TIBERO1';

3. Import the statistics information.

   SQL> SELECT 'EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME =>'''||OWNER||''', TABNAME => '''||TABLE_NAME||''', STATTAB =>''STATS_SAVE'',NO_INVALIDATE => FALSE);' FROM DBA_TABLES WHERE OWNER='TIBERO1'; 
   
   SQL> Execute the query output as a result

Verify Statistics Information

SQL> SELECT 'SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='''||TABLE_NAME||''' ORDER BY OWNER;' FROM DBA_TABLES WHERE OWNER='TIBERO' ;

SQL> Execute the query output as a result