Using DBMS_JOB to submit a Job

Syntax of Using DBMS_JOB to Submit a Job

The difinition of the DBMS_JOB.SUMIT procedure is as follows:

DBMS_JOB.SUBMIT(
job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULTSYSDATE,
interval IN VARCHAR2 DEFAULT 'NULL',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
force IN BOOLEAN DEFAULT FALSE);


We can find an example of using this procedure in the script spauto.sql


dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1/24,'HH'),
'trunc(SYSDATE+1/24,''HH'')',
TRUE,
:instno);



Automate the collection of STATPACK statistics using dbms_job
(1) check job_queue_processes paramter, it should be > 0
(2) Execute the sqlplus script ( collect every 15 min)


Viewing job information from the following views:
DBA_JOBS
DBA_JOBS_RUNNING
USR_JOBS


This procedure changes how often a job runs:

DBMS_JOB.INTERVAL (
job IN BINARY_INTEGER,
interval IN VARCHAR2);



e.g.
execute dbms_job.interval(21, 'sysdate+15/1440')

Lab: Using Statspack Under Oracle XE

1. Install - Run the create scripts (under Oracle 10g XE version)

SQL> connect / as sysdba
SQL> @C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN>spcreate.sql

Notes: prompted for enter password (oracle), the default tablespace SYSAUX
       was chosed, the temporary tablespace was also chosen as TEMP

2. Adjusting the STATSPACK Collection Level

   STATSPACK has two types of collection options: level and threshold.
     level     - controls the type of data collected from Oracle
     threshold - as a filter for the collection of SQL statements into
                 the stats$sql_summary table

   To show the description of different level, issue the following statement:
   SQL> select * from stats$level_description order by snap_level;

   To change the level of a snapshot, you can use statspack.snap function.
   The i_modify_parameter=> 'true' changes the level permanent for all
   snapshots in the future. e.g.
   SQL> exec statspace.snap(i_snap_level => 6, i_modify_parameter => 'true');

3. Create, View and Delete Snapshots


SQL> connect perfstat/oracle
Connected.
SQL> exec statspack.snap
PL/SQL procedure successfully completed.


SQL> select name, snap_id, to_char(snap_time, 'MM-DD-YYYY:HH24:MI:SS')
  2  "Date/Time" from stats$snapshot, v$database;

NAME         SNAP_ID Date/Time
--------- ---------- -------------------
XE                 1 09-29-2006:15:03:40
XE                 2 09-29-2006:15:04:31
XE                11 09-29-2006:15:06:09

   
  
To delete
SQL> @?\rdbms\admin\sppurge;

Notes: prompted to enter low and high snapshot ID


4. Create the Report


SQL> @?\rdbms\admin\spreport.sql

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11
Begin Snapshot Id specified: 11

Enter value for end_snap: 21
End   Snapshot Id specified: 21

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_11_21.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name:


5. Statspack at a Glance  - understading the report



5.1. Statspack Report Header

STATSPACK report for

Database    DB Id    Instance     Inst Num Startup Time    Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          2481462586 xe                  1 29-Sep-06 14:10 10.2.0.1.0  NO

Host  Name:   NTBK             Num CPUs:    1        Phys Memory (MB):      494
~~~~

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- -------------------
Begin Snap:         11 29-Sep-06 15:06:09       16       5.4
  End Snap:         21 29-Sep-06 15:14:59       16       3.8
   Elapsed:                8.83 (mins)

Cache Sizes                       Begin        End
~~~~~~~~~~~                  ---------- ----------
               Buffer Cache:        72M             Std Block Size:         8K
           Shared Pool Size:        52M                 Log Buffer:     2,792K


Notes:
Curs/Sess column - shows the number of open cursors per session.
the item we are most interested in is the elapsed time. We want that to be
large enough to be meaningful, but small enough to be relevant (15 to 30
minutes is OK).

5.2 Statspack Load Profile

Load Profile                            Per Second       Per Transaction
~~~~~~~~~~~~                       ---------------       ---------------
                  Redo size:              5,410.54            716,896.00
              Logical reads:                 71.40              9,460.25
              Block changes:                 33.27              4,408.75
             Physical reads:                  0.03                  4.50
            Physical writes:                  4.69                621.25
                 User calls:                  0.21                 28.25
                     Parses:                  6.08                805.50
                Hard parses:                  0.86                114.50
                      Sorts:                  3.84                509.25
                     Logons:                  0.01                  0.75
                   Executes:                 13.62              1,804.00
               Transactions:                  0.01

  % Blocks changed per Read:   46.60    Recursive Call %:    99.88
 Rollback per transaction %:    0.00       Rows per Sort:     6.65

Notes:
 Three items are important:
   - The Hard parses (we want very few of them)
   - Executes (how many statements we are executing per second/transaction
   - Transactions (how many transactions per second we process).

 This gives an overall view of the load on the server.
 a fairly light system load (1-4 transactions per second is low).

5.3 Statspack Instance Efficiency Percentage

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.95    In-memory Sort %:  100.00
            Library Hit   %:   86.15        Soft Parse %:   85.79
         Execute to Parse %:   55.35         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   77.17     % Non-Parse CPU:   68.03

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   77.74   79.31
    % SQL with executions>1:   91.71   70.29
  % Memory for SQL w/exec>1:   98.44   90.15


Notes:

Most important: Library Hit, Soft Parse and Execute to Parse. All of these
have to do with how well the shared pool is being utilized.

If the library Hit ration was low, it could be indicative of a shared pool
that is too small, or just as likely, that the system did not make correct
use of bind variables in the applicaiton

OLTP System
   The Soft Parase % value is one of the most important ratio in the database.
   For a typical OLTP system, it should be as near to 100% as possible. You
   quite simply do not hard parse after the database has been up for a while
   in your typical transactional / general-purpose database. The way you
   achieve that is with bind variables.

Data Warehouse
   In a data warehouse, we would like to generally see the Soft Parse ratio
   lower. We don't necessarily want to use bind variables in a data warehouse.
   This is because they typically use materialized views, hustograms, and
   other things that are easily thwarted by bind variables. In a data warehouse,
   we may have many seconds between executions, so hard parsing is not evil;
   in fact, it is good in those environments


5.4 Statspack Top 5 Timed Events

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
db file parallel write                             487           7     15   45.7
CPU time                                                         6          37.4
log file parallel write                            116           1      9    6.5
control file parallel write                        177           1      4    4.8
control file sequential read                       247           1      3    3.8
          -------------------------------------------------------------


Notes:
  CPU time - the sum of the CPU used by this session, or the amount of CPU
             time used during the snapshot window. In a heavily loaded system,
             if the CPU time event is the biggest event, that could point to
             some CPU-intensive processing

5.5 SQL ordered by Gets

Here you will find the most CPU-Time consuming SQL statements

unix tool vmstat - show how the machine is utilzed

This article showed how to interpret the vmstat on AIX
Automated Oracle Tuning Initial Procedures - Part 2

Solaris OS commands

Some command under Solaris

# prtdiag -- Show some hardware info
# swap –s 查看交换区空间大小
# df -k tmp 查看tmp空间大小
# prtconf grep Memory size 查看内存大小
# df –k 查看系统磁盘空间
# isainfo –kv 查看系统内核是否是64位
# pkginfo -i SUNWarc SUNWlibms SUNWil0f SUNWbtool SUNWsprot SUNWi1cs SUNWhea SUNWsprox

This article describes how to set swap memory:
Note: swap files != SWAP memory

Setting SWAP Memory Under Solaris

Fundamental II Ch 9 - User-Managed and RMAN-Based Backups

Chapter 9 - User-Managed and RMAN-Based Backups

OCP: Oracle9i DBA Fundamentals II Study Guide

Review Questions


1. Which type of backup most closely represents an opened backup? (Choose all that apply.)


A. Online backup
B. Offline backup
C. Hot backup
D. Cold backup
----
An opened backup is performed when the database is opened or available for access (online). A hot backup and online backup are synonymous.
Ans: A, C


2. In the event of a database failure that requires a full database restore, how would you perform a recovery for a read-only tablespace? (Choose all that apply.)


A. No recovery is necessary, if the restored copy was made when the tablespace was read-only.
B. You would recover by applying redo log entries to the read-only tablespace, regardless of the state of the tablespace copy.
C. You would recover by applying redo log entries to the read-only tablespace if the tablespace was in read-write mode at the time of the backup used for the restore.
D. You would recover by applying redo log entries to the read-only tablespace if the tablespace was in read-only mode at the time of the backup used for the restore.
---
In a read-only tablespace, the SCN doesn’t change or if it does, none of the changes get applied. So if the backup of the tablespace was taken when the tablespace was read-only, no recovery would be necessary. On the other hand, if the backup was taken when the database was read-write, then redo logs would need to be applied. The redo logs in this case would also contain the command that puts the tablespace into read-only mode.
Ans: A, C.


3. What type of backup is consistent?


A. Online backup
B. Opened backup
C. Hot backup
D. Cold backup
----
A cold backup ensures that all the SCNs in the data files are consistent for a single point in time.
Ans: D.


4. What type of backup is inconsistent? (Choose all that apply.)


A. Cold backup
B. Online backup
C. Opened backup
D. Closed backup
---
Opened and online backups both back up the data files with different SCNs in the headers, which makes recovery necessary during a restore operation.
Ans: B, C.


5. If a read-only tablespace is restored from a backup when the data file is read-only, what type of recovery is necessary?


A. Data file recovery
B. Tablespace recovery
C. Database recovery
D. No recovery is needed.
-----
No recovery is needed because the tablespace was read-only during backup and at the time of failure.
Ans: D.


6. What are valid ways to back up a control file while the database is running? (Choose all that apply.)


A. Back up to trace file
B. Back up to binary control file
C. OS copy to tape
D. Back up to restore file
----
Backing up both to a trace file and to a binary control file are valid backups of the control file. The other references are made up.
Ans: A, B.


7. Which of the following statements is true about a user-managed backup?


A. This type of backup is conducted using the RMAN utility.
B. A user-managed backup can be customized using a combination of OS and database commands.
C. A user-managed backup is a new type of backup in Oracle9i.
D. A user-managed backup is one of the backup options within RMAN.
----
A user-managed backup is a customizable backup that uses OS and database commands, and it is usually written in some sort of native scripting language.

Ans B.


8. If a tablespace was backed up shortly after it was made read-only, what would need to be done with archived logs during a recovery of that tablespace?


A. All archived logs would need to be applied.
B. Only the archived logs that were added after the backup would need to be applied.
C. Only the archived logs that were added before the backup would need to be applied.
D. No archived logs would need to be applied.
----
No archived logs would need to be applied because the tablespace was backed up after it was made read-only.
Ans: D.


9. Which of the following is a true statement about a RMAN image copy?


A. It can be backed up to tape or disk.
B. It can be backed up to disk only.
C. It can be backed up to tape only.
D. It can be copied to tape only.
---
An image copy can be backed up only to disk.
Ans B.


10. A cold backup requires the database to be in what condition? (Choose all that apply.)


A. ARCHIVELOG mode
B. NOARCHIVELOG mode
C. The database must be started.
D. The database cannot contain any read-only tablespaces.
---
Ans: A cold backup occurs when the database is shutdown. The database can be in ARCHIVELOG mode or NOARCHIVELOG mode.



11. To perform an open or hot backup, what state must the database be in?


A. ARCHIVELOG mode
B. NOARCHIVELOG mode
C. Shutdown
D. Automatic archiving must be enabled.
----
The database must be in ARCHIVELOG mode. Archiving can be set to manual or automatic.
Ans: A.


12. Hot backups are best run when what is occurring in the database?


A. Heavy DML activity
B. Heavy batch processing
C. The database is being shut down.
D. Low DML activity
----
More transactional activity gets written to redo logs when a tablespace is in backup mode. It is a good idea to do hot backups when you have the lowest transactional activity.
Ans: D.


13. What method can be used to clean up a failed online or hot backup?


A. Shutting down the database
B. Querying V$TABLESPACE
C. Querying V$BACKUP
D. Querying V$DATAFILE
----
It is a good idea to query V$BACKUP to check to see if any data files are being actively backed up. If they are, you can execute ALTER TABLESPACE END BACKUP to change the status from ACTIVE to INACTIVE.
Ans: C.


14. What utility can be used to check to see if a data file has block corruption?

A. DBVALIDATE
B. DBVERIFY
C. DBVERIFIED
D. DBVALID
----
The DBVERIFY utility is used to check whether or not a data file has any block corruption.
Ans: B.


15. Which of the following are types of RMAN backups? (Choose all that apply.)

A. Open and closed backups
B. Full and incremental backups
C. Consistent and inconsistent backups
D. Control file backups
-----
Ans: A, B, C. Open and closed, full and incremental, and consistent and inconsistent backups are the different type of RMAN backups.