Gather system (CPU) stats using DBMS_STATS

In Oracle 9i and up, you can generate CPU cost information during a workload with a procedure of the dbms_stats package.

The dbms_stats.gather_system_stats packaged procedure can be used during a certain interval to measure the actual I/O and CPU usage during a workload, typically a days work.

SQL> exec dbms_stats.gather_system_stats('START')

PL/SQL procedure successfully completed.

SQL> -- days processing

SQL> exec dbms_stats.gather_system_stats('STOP')

PL/SQL procedure successfully completed.

Now, when you gathered workload CPU statistics, one can query the sys.aux_stats$ data dictionary table to see the actual values that will be used when generating your Sql plan:

select sname, pname, pval1

from sys.aux_stats$;

SNAME                PNAME      PVAL1

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

SYSSTATS_INFO   STATUS

SYSSTATS_INFO   DSTART

SYSSTATS_INFO   DSTOP

SYSSTATS_INFO   FLAGS                1

SYSSTATS_MAIN   CPUSPEEDNW  502.005

SYSSTATS_MAIN   IOSEEKTIM   10

SYSSTATS_MAIN   IOTFRSPEED  4096

SYSSTATS_MAIN   SREADTIM    7.618

SYSSTATS_MAIN   MREADTIM    14.348

SYSSTATS_MAIN   CPUSPEED    507

SYSSTATS_MAIN   MBRC                6

SYSSTATS_MAIN            MAXTHR      32768

SYSSTATS_MAIN            SLAVETHR     

13 rows selected.

CPUSPEEDNW, IOSEEKTIM, and IOTFRSPEED are noworkload statistics;

SREADTIM, MREADTIM, CPUSPEED, MBRC, MAXTHR, and SLAVETHR represent workload statistics.



When you have both workload and noworkload statistics, the optimizer will use workload statistics.

When you have both workload and noworkload statistics, the optimizer will use workload statistics.

SREADTIM – single block read time (msec): the average time Oracle takes to read a single block

MREADTIM – multiblock read time (msec): the average time taken to read sequentially

MBRC – multiblock read count: the average amount of blocks read during multiblock sequential reads. This value is used instead of the db_ multiblock_read_count parameter during query optimization to compute costs for table and fast full index scans

MAXTHR – maximum I/O system throughput: is captured only if the database runs parallel queries

SLAVETHR – maximum slave I/O throughput: is captured only if the database runs parallel queries

Oracle Password File

In order to startup an Oracle database, we need to have an Oracle password file or on Operating system authentication.

If the init.ora parameter remote_login_passwordfile is set to exclusive or shared, Oracle will search for a password file to do the authentication.

The default location for an Oracle password file is the %ORACLE_HOME%/database on Windows and $ORACLE_HOME/dbs on Unix flavours.

On windows, the filename should be: PWD%ORACLE_SID%.ora

On Unix, the filename should be: orapw$ORACLE_SID

You can use the orapwd command to create a new password file.

Usage: orapwd file= password= entries= force=

  where

    file - name of password file (mand),

    password - password for SYS (mand),

    entries - maximum number of distinct DBA,

    force - whether to overwrite existing file (opt),

  There are no spaces around the equal-to (=) character.

DBA Interview Questions Part I

1. Explain the $ ORACLE_HOME and $ ORACLE_BASE the difference? 

Answer: ORACLE_BASE is the root directory of oracle, ORACLE_HOME is the oracle product directory.

2. How to increase buffer cache hit rate? 

Answer: more busy in the database, the application buffer cache advisory tool, query v $ db_cache_advice. If necessary changes, you can use the command alter system set db_cache_size.

3. How to generate explain plan? 

Answer: Run utlxplan.sql. Create plan table for specific SQL statement, using the explain plan set statement_id = 'tst1' into plan_table run utlxplp.sql or utlxpls.sql View explain plan.

4. Which column can be used to distinguish the V $ views, and GV $ views?

Answer: INST_ID indicate a specific cluster environment instance.

5. Gives the database starts normally experienced several states?

Answer: STARTUP NOMOUNT - database instance startup. STARTUP MOUNT - Database mounted. STARTUP OPEN - database open.

6. How to create a backup control file?

Answer: Alter database backup control file to trace.

7. Explain the archive and the differences between non-archive mode and their respective advantages and disadvantages. 

Answer: archive mode means that you can back up all database transactions and restore to any point in time. Non-archive mode, by contrast, can not restore to any point in time. But the non-archive mode can bring a little on the database to improve performance.

8. How the premise does not affect the child table, the reconstruction of a parent table.

Answer: the child table foreign key force effectiveness, re-master table, activate the foreign key.

9. Give two related constraints? 

Answer: primary keys and foreign keys.

10. In the STAR SCHEMA given in two tables and the data they contain, respectively.

Answer: Fact tables and dimension tables. Fact table contains a number of major information and dimension tables on the fact table storing the information described some of the properties.

11. FACT Table on the need to establish what kind of index? 

Answer: Bitmap indexes (bitmap index). 

12. Use reason for the index.

Answer: quick access to table data block.

13. Comparison of truncate and delete command.

Answer: Both can be used to delete all records in the table. The difference is: truncate is a DDL operation, it moves HWK, do not need to rollback segment. And Delete is a DML operation, you need to rollback segment and take a long time.

14. How can I view the database engine error?

Answer: alert log.

15. Given the structure of the two methods checklist.

Answer: 1.DESCRIBE command 2.DBMS_METADATA.GET_DDL package.

16. Explain the data block, extent, and the difference between segment.

Answer: data block is the smallest logical database storage unit. When the database objects need more physical storage space, a continuous data block to form the extent. A database object is called with all the extents of the object segment.

17. How to convert init.ora to spfile? 

Answer: Use the create spfile from pfile command.

18. If you must use a database backup and recovery, but you do not have control file, how to solve the problem? 

Answer: rebuilding the control file, with a backup control file clause of the recover command to recover the database.

19. Cold backup and hot backup and the respective merits of different points 

Answer: hot backup mode for the archive database is still in the database backupis in working condition. The cold backup means the database is closed, the backup, the database for all modes. The advantages of hot backup when the backup, the database can still be used and can restore the database to any point in time. Cold backup is that it's quite simple backup and recovery operations, and because of the cold backup of the database can work in non archive mode, the database performance was slightly better than the archive mode. (Because do not have to write the archive log disk ). 

20. ORA-01555 to address the challenges?

Answer: The exact error message is a snapshot too old within rollback seg, usually by increasing the rollback seg to solve the problem. Of course, should check the SQL error text of the specific cause.