Useful Gather Statistics Commands In Oracle

 

Useful Gather Statistics Commands In Oracle

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS, DBMS_STATS.GATHER_DICTIONARY_STATS and DBMS_STATS.GATHER_SYSTEM_STATS

 

======================================
General 
======================================

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
DBMS_STATS.GATHER_DICTIONARY_STATS
DBMS_STATS.GATHER_SYSTEM_STATS

======================================
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
======================================
 

This would collect statistics about Fixed objects. 
These are the X$ and K$ tables and their indexes. 
The V$ views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN).

How to identify when DBMS_STATS.GATHER_FIXED_OBJECTS_STATS was executed in the database ?

SELECT v.name, ts.analyzetime 
FROM V$FIXED_TABLE v, SYS.TAB_STATS$ ts 
WHERE v.object_id = ts.obj#; 

no rows returned

SELECT COUNT(*) FROM sys.tab_stats$
count(*) was 0.

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);
This takes few minutes.

SELECT COUNT(*) FROM sys.tab_stats$
returns 761

======================================
DBMS_STATS.GATHER_DICTIONARY_STATS
======================================
 

This procedure gathers statistics for dictionary schemas 'SYS', 'SYSTEM'.

When was it last run?

Check the the last_analyzed column for tables owned by SYS.

SELECT MAX(LAST_ANALYZED) FROM DBA_TABLES WHERE owner = 'SYSTEM';

MAX(LAST_ANALYZED)
------------------
27-JUN-20

SELECT MAX(LAST_ANALYZED) FROM DBA_TABLES WHERE owner = 'SYS';

MAX(LAST_ANALYZED)
------------------
27-JUN-20

 

======================================
DBMS_STATS.GATHER_SYSTEM_STATS
======================================

This procedure gathers system statistics.

 

The actual gathered statistics would depend upon system being under workload, or not.

 

DBMS_STATS.GATHER_SYSTEM_STATS procedure gathers statistics relating to the performance of your systems I/O and CPU. 

Giving the optimizer this information makes its choice of execution plan more accurate, since it is able to weigh the relative costs of operations using both the CPU and I/O profiles of the system.

 

When was DBMS_STATS.GATHER_SYSTEM_STATS last run?

The output from DBMS_STATS.GATHER_SYSTEM_STATS is stored in the AUX_STATS$ table.

 

SELECT * FROM SYS.AUX_STATS$;

 

NAME                 PNAME                     PVAL1 PVAL2

-------------------- -------------------- ---------- --------------------

SYSSTATS_INFO        STATUS                          COMPLETED

SYSSTATS_INFO        DSTART                          10-26-2020 13:08

SYSSTATS_INFO        DSTOP                           10-26-2020 13:08

SYSSTATS_INFO        FLAGS                         1

SYSSTATS_MAIN        CPUSPEEDNW           1108.95499

SYSSTATS_MAIN        IOSEEKTIM                    10

SYSSTATS_MAIN        IOTFRSPEED                 4096

SYSSTATS_MAIN        SREADTIM

SYSSTATS_MAIN        MREADTIM

SYSSTATS_MAIN        CPUSPEED

SYSSTATS_MAIN        MBRC

SYSSTATS_MAIN        MAXTHR

SYSSTATS_MAIN        SLAVETHR

 

How to execute DBMS_STATS.GATHER_SYSTEM_STATS?

 

Option A. – no workload

All databases come bundled with a default set of no-workload statistics, but they can be replaced with more accurate information. 

When gathering no-workload stats, the database issues a series of random I/Os and tests the speed of the CPU. 

As you can imagine, this puts a load on your system during the gathering phase.

 

EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

 

Option B. - Workload

When initiated using the start/stop or interval parameters, the database uses counters to keep track of all system operations, giving it an accurate idea of the performance of the system. 

If workload statistics are present, they will be used in preference to no-workload statistics.

 

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');

-- Wait some time, say 120 minutes, during workload hours

 

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

or

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval => 120); 

 

======================================

When to run these procedures?

======================================

 

- When there was a change to init.ora Instance parameters

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

- When there was a change to dictionary structure - new schema, etc.

DBMS_STATS.GATHER_DICTIONARY_STATS

 

- When there was a major change to the host hardware.

DBMS_STATS.GATHER_SYSTEM_STATS

 

 

 

Senior Oracle DBA Q&A

 

Q) My incremental bkp is running more time than usual any particular reason for that?

If you haven't seen this issue before and assuming database size didn't changed then you have to check the speed between database server and media/backup server.

Sometimes if we change the NIC card configuration linked with backup server then  such issues pop-ups

Also check with OS team first and ask them if they have seen same issues with OS backups also (if Scheduled).

 

Q) Why voting is disk is odd in number?

More 50 % always available concept by Oracle...

2 when you divide by 2 is 1 and 3 when divided by 2 is 1.5 (consider it 2 as a whole Number) .

So odd number will always be higher in case of a voting disk availability.

So more than 50% is availability achieved by it then.

Whoever scores more will survive, else will get eliminated.

 

Q) How long SQL_ID will be in shared pool ?

Till it is not flushed out

If there is a pressure on shared pool, old SQLs might get flushed

 

 

Q) Why SQL_plan_hash value is changed and why child_number change for SQLID?

The answer for why SQL PHV change is .

When a SQL of a particular SQL ID is parsed optimizer generate multiple plans and CBO to select the best plan among multiple. Plan based on the cost. After some days if you insert loads of data or stats gather the object then optimizer will again generate the best plan and if this plan is best in terms of cost it will start using this new plan.

 You can also manually change the phv of a SQL id by using DBMS _spm package

 

Table EMP with 1 Million Rows.

Index on GENDER Column. M is 98% and F is 2 %.

SELECT * FROM EMP WHERE GENDER=:B1 for M will be a Full Table Scan with a PLAN_HASH_VALUE and CHILD_NUMBER as 0.

The same query with value as F will be an Index Scan and therefore, will have different PLAN_HASH_VALUE and CHILD_NUMBER as 1.

A query can have multiple plans depending upon the bind variable values and each plan will create a child cursor.

 

Another example...

Table EMP with 1 Million Rows. No Index on Gender Column. M is 98% and F is 2%.

Query 1 : select * from emp where gender=:b1 and the value is M.

It goes for a Full Table Scan and Plan_hash_value is some value with child_number 0.

Now, I set optimizer_index_cost_adj to 10 at session level and re-run the same query for M.

Again a Full Table Scan as I do not have an Index on Gender.

The Plan_hash_value remains same, but child_number is now 1..so, same plan_hash_value and 2 Child_numbers..

 

Oracle will use histogram to identify skewedness of data in order to decide whether child no 0 or 1 is appropriate for execution.

Yes. Histogram will play a critical role here

Nice example for new child cursor creation. This is one case where new child cursor is created for same plan_hash_value.. there are multiple factor which cause new child creation like length of bind from application, different character sets for the bind, different cardinality estimate for bind aware cursor.

 

 

Q) How can we decide, when to use SQL profiling and SQL Baseline ?

We can decide to use SQL plan baseline when your SQL statement have multiple plane hash value. Out of those multiple phv if optimizer is using bad phv when there are good phv present you can use baseline to force optimizer to use good phv to avoid performance regression

Dba_sql_plan_baseline

See how phv that SQL has

Check the columns enabled accepted fixed

Of enabled is yes then that phv is currently in use

If accepted is no then that phv is accepted in baseline but not used

If you want to permanent allocate phv to a SQL then fix it

But in SQL profiling, we can use good phv based on elapsed time and IO.

 

Q) SQL profiling good or SQL Baseline?

SQL baseline will be good

SQL Profile for reactive situation and SQL baseline for Proactive

 

                           

Q) Why to rebuid indexes?

               Index data constantly changes due to the underlying table’s DML activity. Indexes often become too large if there are many deletions, because the space used by the deleted values is not reused automatically by the index. You can use the REBUILD command on a periodic basis to reorganize indexes to make them more compact and thus more efficient.

 

Q) What happens in background during patching?

               1.Opatch takes backup of those files from O_H which it is going to update

               2.It takes this backup in O_H/.patch_storage directory

               3.It then copies files from /etc and /files to O_H

               4.After that it takes backup of Inventory in /files

               5.Relinks all O_H binaries and update inventory with patch ID

               6.Finally it displays- patch applied

 

Q) Facing performance issues with these wait events not completing the processing requests.

 

Oracle Troubleshooting Questions & Answers

 1) Will RMAN take backups of read-only tablespaces?

    No

2)  Which log would you check if a database has a problem?

      Alert log

3)  How can you check the structure of a table from sqlplus?

     DESCRIBE or DESC1.

4) What command will you start to run the installation of Oracle software on Linux?

     runInstaller

5) What should you do if you encounter an ORA-600?

    Contact Oracle Support

6) Where can you find official Oracle documentation?

     tahiti.oracle.com

7)  What is the address of the Official Oracle Support?

     metalink.oracle.com or support.oracle.com

8)  What file will you use to establish Oracle connections from a remote client?

     tnsnames.ora

9) How can you check if the database is accepting connections?

    lsnrctl status or lsnrctl services

10) In which Oracle version was Data Pump introduced?

      Oracle 10g

11) What is the most important requirement in order to use RMAN to make consistent hotbackups?

      Your database has to be in ARCHIVELOG mode

12) Can you connect to a local database without a listener?

      Yes, you can

13) How can you view all the users account in the database?

      SELECT USERNAME FROM DBA_USERS;

14) How can you view all the current users connected in your database in this moment?

      SELECT COUNT(*),USERNAME FROM V$SESSION GROUP BY USERNAME;

15) Is it possible to backup your database without the use of an RMAN database to store thecatalog?

      Yes, but the catalog would be stored in the controlfile

16) Will RMAN take backups of read-only tablespaces?

      No

17) How to clear database buffer cache ,redolog buffer cache and shared pool.

      shared Pool-ALTER SYSTEM FLUSH SHARED_POOL;

      database buffer cache-

15) What are the scripts run at the time  of manual daataabase creation.

      1.Catalog.sql

      2.catproc.sql

         conn / as sysdba

      3.pupbld.sql

16) How to check how many databases are runing on OS level.

      ps -ef|grep pmon

17) How to open database in archive log mode.

      shutdown immediate

      alter database nomount

      alter database mount

      alter database archivelog;

      alter database open

18) Where Alert logfile locted.

       Background_dump_dest

19) What is being written into the Redo Log Files?

      Redo log records all changes made in datafiles.

20) What are the network file in oracle.

      sqlnet.ora tns.ora,Listener.ora

21) What is password  file;

       Password file is the authitication secure file to login to dataabse if it is present on the server .It is not case sensitive.

22) How to resize the size of tablespace.

      Alter database datafile '/u01/app/oracle/oradtaa/db01.dbf' resize 20m;

---------------With Backup-------------

      If datafile is deleted how to recover it by backup or without backup;

      RMAN>restore datafile 2;

      RMAN>Recover datafile 2;         (Backup)

      RMAN>sql'Alter database open';

------------Without Backup-------------

     1.tablespace offline.

     2.alter tablespace add datafile /u01/app/oracle/oradtaa/db01.dbf' size 20m;

     3.recover datafile 2;

     4.alter tablespace online

22) Can we create dictionary managed tablespace in 10g/11g?

      Yes, but SYSTEM tablespace also should be dictionary for that

23) Can we take SYSTEM and SYSAUX tablespace offline?

      We can take SYSAUX, but not SYSTEM

24) Can we make temporary tablespace read only?

      Not possible

25) Is it possible to reduce the size of datafile?

      Yes, but data level should not have been crossed the limit

26) If a transaction is running and DBA has taken particular rollback segment offline. What happens to that transaction?

      It will start using SYSTEM tablespace

27) How to convert dictionary managed permanent tablespace to temporary tablespace?

      It is not possible

28) You are trying to add a datafile, but its not allowing and saying you cannot add it. What might be the reason?

      Either MAXDATAFILES controlfile parameter or DB_FILES parameter in pfile might have reached its max value

29) Your standby database was out of reach because of network issue. How will you synchronize it with primary database again?

      Data Guard automatically resynchronizes the standby following network or standby outages using redo data that has been archived at the primary.

30) Can we use same target database as catalog?

      No, The recovery catalog should not reside in the target database (database should be backed up), because the database can’t be recovered in the mounted state.

31) You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week/day old and don’t have backup of this (newly created) datafile. How do you restore/recover file?

      Create data file and recover datafile.

32) In catalog database, if some of the blocks are corrupted due to system crash, How will you recover?

      using RMAN BLOCK RECOVER command

33) There was a media failure. How can you find which files you must recover?

       By querying the V$RECOVER_FILE view, which lists all files that need media recovery.

34) Can one FRA directory be used for more than one database?

       You can use the same flash recovery area for as many databases as you want. The RMAN backup process will create a subdirectory called <SID_NAME>, the same name as the database you are backing up.

35) Can Recovery Catalog database also be shutdown from RMAN prompt like TARGET Database can be done?

      All the shutdown and startup commands applies only to the target database. You can’t start and stop the recovery catalog instance from RMAN. The only way to start up and shut down the recovery catalog instance is by connecting to the recovery catalog database as the target database and by issuing the relevant commands to start or stop the instance.

36) Does using recovery catalog means RMAN won’t use the control file to store information?

      No. Even when you choose to use a recovery catalog, backup information will continue to be stored in the control file as well by default.

37) You want to move your recovery catalog from one database to another. How you can do it?

      a) Create a new recovery catalog in the target database. You don’t need to register any database to it

      b) Use the import catalog command in RMAN after connecting to the target database:

38) Is there a way to force rman to use these obsolete backups or once it is marked obsolete?                                                          

      As per my understanding it is just a report, they are still there until you delete them.   

                                                                          

39) Can I use the same snapshot controlfile to backup multiple databases(one after another) running on the same server?                

      This file is only use temporarily like a scratch file.  Only one rman session can access the snapshot controlfile at any time so this would tend to serialize your backups if you do that.          

40) Why does not oracle keep RMAN info after recreating the controlfile?  

      Creating the new controlfile from scratch how do you expect the create controlfile to "make up" the missing data?

      that would be like saying similarly we have drop and recreated my table and now it is empty similarly here recreating from the scratch means the contents there will be naturally will be gone. 

      Use the rman catalog to deal this situation. It is just a suggestion.  

41) Can RMAN write to disk and tape Parallel? Is it possible?

      Rman currently won't do tape directly, you need a media manager for that, regarding disk and tape parallel not as far as I know, you would run two backups separately (not sure). 

      May be trying to maintain duplicate like that could get the desired. 

42) You recreated the control file by “using backup control file to trace” and using alter database backup controlfile to ‘location’ command what have you lost in that case?                                 

      You lost all of the backup information as using backup controlfile to trace where as using other ALTER DATABASE BACKUP CONTROLFILE to ‘D:\Backup\control01.ctl’. 

      All backup information is retained when you take binary control file backup. 

43) If a backup is issued after “shutdown abort” command what kind of backup is that? 

      It is an inconsistent backup. 

      If you are in noarchivelog mode ensure that you issue the shutdown immediate command or startup force is another option that you can issue: 

      startup force->shutdown abort; 

      followed by 

     shutdown immediate;

44) Can I have archivelogs and datafile backup in a single backupset?

      No.  We can not put datafiles and archive logs in the same backupset

45)  Can I have datafiles and contolfile backup in a single backup set?

       Yes, If the controlfile autobackup is not ON then RMAN takes backup of controlfile along with the datafile 1, whenever you take backup of the database or System tablespace

46)  Can I regulate the size of backup piece and backup set?

        Yes! 

        You can set max size of the backupset as well as the backup piece 

        By default one RMAN channel creates a single backupset with one backup piece in it 

        You can use the MAXPIECESIZE channel parameter to set limits on the size of backup pieces 

        You can also use the MAXSETSIZE parameter on the BACKUP and CONFIGURE commands to set a limit for the size of backup sets

47)  Can I take RMAN backup when the database is down?

       No!

       You can take RMAN backup only when the target database is Open or in Mount stage

       It’s because RMAN keep the backup metadata in controfile

       Only in open or mount mode controlfile is accessible

48)  Do I need to place the database in begin backup mode while taking RMAN inconsistent backup?

       RMAN does not require extra logging or backup mode because it knows the format of data blocks

       RMAN is guaranteed not to back up fractured blocks

       No extra redo is generated during RMAN backup

49) If an oracle database is crashed? How would you recover that transaction which is not in backup?

      If the database is in archivelog we can recover that transaction otherwise we cannot recover that transaction which is not in backup.

50) Can you backup the online redo log files?

      We cannot backup the online redo logs; online redo logs are protected by multiplexing and optionally by archiving

51) Can we take incremental backup without taking the full backup?

      No, full backup should be taken before the incremental backup.

52) Can you take offline backup with RMAN?

      Yes, you can take offline backup with RMAN

53) Can you us RMAN without recovery catalog?

      Yes, RMAN can be used without reocvery catalog.

54) If the database is running in the noarchivelog mode, then which type of backups can you take?

      In this case,  you can take only cold backups

55) Can you take online backup of a control file?

      Yes, by using the following statement: Alter database backup controlfile to '<location>' or trace.

 

56) Can you backup the online redo log files?

      We cannot backup the online redo logs; however online redo logs are protected by multiplexing and optionally by archiving.

57) Can you take online backups if the database is running in the NOARCHIVELOG mode?

      No

58) Can we take incremental backup without taking the full backup?

      No, full backup should be taken before the incremental backup.

59) Can you track changes to blocks during hot backup?

      Oracle database 11g offers block change tracking feature.

      It records the information in block change tracking file.

      RMAN uses this information to determine specific blocks to be backed up again without re-scanning the entire datafile.

 

60) Can you take the backup of log files if the database is running in the ARCHIVELOG mode?

      No, you cannot take the backup of log files if the database is running in the ARCHIVELOG mode.

 

61) How can you manage storage options during export or import?

      You can manage storage options during export or import by using the compress option. It ensures that the storage is compressed whenever the parameter value is Y.

 

61) Can you use RMAN without recovery catalog?

      Yes

 

62) How to check database is in archivelog mode or not.?

      sql>archivelog list;

      sql>select log_mode from v$database

63) Can we use backup set create by rman with any other utility.?

      NO

64) Can we take image backup in rman how.?

       Yes,

      rman>backup database as image copy device type to disk

65) Can we take online rman backup if database is runing in noarchivelog mode.?

      No database wants to be in archivelog mode.

66) Should u place catalog in same database.?

      No 

67) Loss of controlfile .how will u recover it.

      alter database recover using backup controlfile untill cancel;