• Small shops: Small installations that can't afford a trained Oracle DBA.
• Shops with over-worked DBAs: Large shops with hundreds of instances where the DBA does not have time to properly tune each system.

• With Program, With Schedule 
• With Program, Without Schedule
• Without Program, With Schedule
• Without Program, Without Schedule

• External - no mirroring
• Normal - data is mirrored on two separate disks. This is the default setting.
• High Redundancy - data is mirrored on there separate disks providing three-way mirroring capabilities.

• Simplified daily administration
• The performance of raw disk I/O for all ASM files
• Compatibility with any type of disk configuration, be it JBOD or complex SAN
• Use of a specific file-naming convention to name files, enforcing an enterprise-wide file-naming convention
• Prevention of the accidental deletion of files, since there is no file system interface and ASM is solely responsible for file management
• Load balancing of data across all ASM managed disk drives, which helps improve performance by removing disk hot spots
• Dynamic load balancing of disks as usage patterns change and when additional disks are added or removed
• Ability to mirror data on different disks to provide fault tolerance
• Support of vendor-supplied storage offerings and features
• Enhanced scalability over other disk-management techniques

• When creating disk groups and allocating storage, most customers will be best served by using just two disk groups – one for a recovery area and one for their work area. However, customers with intensive OLTP applications but no disk cache may also need a separate disk group for online log files.
• When creating disk groups, you will need to tell ASM whether the storage is already mirrored by the hardware, or whether ASM should mirror the data

select ....
from ....
model [main] 
   [ reference models ] 
   [ partition by (<cols>)] 
   dimension by (<cols>) 
   measures (<cols>)
    [ ignore nav ] | [ keep nav ]
  [ rules
    [ upsert | update]
    [ automatic order | sequential order ]
    [ iterate (n) [ until <condition>] 
  ]
    (   <cell_assignment> = <expression> ...)

• The "RETURN UPDATED ROWS" clause following the keyword MODEL limits the results returned  to just those rows that were created or updated in this query.  Using this clause is a convenient way to limit result sets to just the newly calculated values.  We will use the RETURN UPDATED ROWS clause throughout our examples.
• The keyword  "RULES," shown in all our examples at the start of the formulas, is optional, but we include it for easier reading. 
• Likewise, many of our examples do not require ORDER BY on the Country column, but we include the specification for convenience in case readers want to modify the examples and use multiple countries.

Table 1: Anchoring Metacharacters Metacharacter Description ^ Anchor the expression to the start of a line $ Anchor the expression to the end of a line Table 2: Quantifiers, or Repetition Operators Quantifier Description * Match 0 or more times ? Match 0 or 1 time + Match 1 or more times {m} Match exactly m times {m,} Match at least m times {m, n} Match at least m times but no more than n times Table 3: Predefined POSIX Character Classes Character Class Description [:alpha:] Alphabetic characters [:lower:] Lowercase alphabetic characters [:upper:] Uppercase alphabetic characters [:digit:] Numeric digits [:alnum:] Alphanumeric characters [:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed [:punct:] Punctuation characters [:cntrl:] Control characters (nonprinting) [:print:] Printable characters Table 4: Alternate Matching and Grouping of Expressions Metacharacter Description | Alternation Separates alternates, often used with grouping operator () ( ) Group Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing [char] Character list Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters Table 5: The REGEXP_LIKE Operator Syntax Description REGEXP_LIKE(source_string, pattern [, match_parameter]) source_string supports character datatypes (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB but not LONG). The pattern parameter is another name for the regular expression. match_parameter allows optional parameters such as handling the newline character, retaining multiline formatting, and providing control over case-sensitivity. Example: SELECT ename FROM emp WHERE REGEXP_LIKE (ename, '^J.(N|M),S$'); ENAME ----- JONES JAMES In this example, we tell Oracle to retrieve any values that start with J, followed by any letter, then N or M, then any letter, then S Another one, the following regular expression would match fly, flying, flew, flown, and flies: SELECT c1 FROM t1 WHERE REGEXP_LIKE(c1, ‘fl(y(ing)?|(ew)|(own)|(ies))’); Table 6: The REGEXP_INSTR Function Syntax Description REGEXP_INSTR(source_string, pattern [, start_position [, occurrence [, return_option [, match_parameter]]]]) This function looks for a pattern and returns the first position of the pattern. Optionally, you can indicate the start_position you want to begin the search. The occurrence parameter defaults to 1 unless you indicate that you are looking for a subsequent occurrence. The default value of the return_option is 0, which returns the starting position of the pattern; a value of 1 returns the starting position of the next character following the match. Example: SELECT REGEXP_INSTR('5035 Forest Run Trace, Alpharetta, GA', '[^ ]+', 1, 6] "Test" FROM dual;       TEST         36 In this example, we are telling Oracle to examine the string, looking for occurrences of one or more non-blank characters and to return the sixth occurrence of one or more non-blank character. Table 7: Explanation of 5-digit + 4 Zip-Code Expression Syntax Description   Empty space that must be matched [:digit:] POSIX numeric digit class ] End of character list {5} Repeat exactly five occurrences of the character list ( Start of subexpression - A literal hyphen, because it is not a range metacharacter inside a character list [ Start of character list [:digit:] POSIX [:digit:] class [ Start of character list ] End of character list {4} Repeat exactly four occurrences of the character list ) Closing parenthesis, to end the subexpression ? The ? quantifier matches the grouped subexpression 0 or 1 time thus making the 4-digit code optional $ Anchoring metacharacter, to indicate the end of the line Table 8: The REGEXP_SUBSTR Function Syntax Description REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_parameter]]]) The REGEXP_SUBSTR function returns the substring that matches the pattern. Example: SELECT REGEXP_SUPSTR('5035 Forest Run Trace, Alpharetta, GA',',[^,]+,') "Test"    FROM dual; Test ------------------ , Alpharetta, In this example we search for a comma, followed by one or more characters immediately followed by a comma. Table 9: The REGEXP_REPLACE Function Syntax Description REGEXP_REPLACE(source_string, pattern [, replace_string [, position [,occurrence, [match_parameter]]]]) This function replaces the matching pattern with a specified replace_string, allowing complex search-and-replace operations. Example: SELECT  REGEXP_REPLACE(emp_phone,     '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',     '(\1) \2-\3') "Test"     FROM emp; Test ---------------- (404) 444-4321 (404) 555-5432 (404) 666-6543 In this eample we search for a pattern of numbers that looks like a European phone number listing such as 111.222.3333 and convert it to a normal USA format listing of (111) 222-3333. Table 10: Backreference Metacharacter Metacharacter Description \digit Backslash Followed by a digit between 1 and 9, the backslash matches the preceding digit-th parenthesized subexpression. (Note: The backslash has another meaning in regular expressions; depending on the context it can also mean the Escape character Table 11: Explanation of Pattern-Swap Regular Expression Regular-Expression Item Description ( Start of first subexpression . Match any single character except a newline * Repetition operator, matches previous . metacharacter 0 to n times ) End of first subexpression; result of the match is captured in \1 (In this example, it's Ellen.)   Empty space that needs to be present ( Start of the second subexpression . Match any single character except a newline * Repetition operator matches the previous . metacharacter 0 to n times ) End of second subexpression; result of this match is captured in \2 (In this example, it stores Hildi.)   Empty space ( Start of third subexpression . Match any single character except a newline * Repetition operator matches . metacharacter 0 to n times ) End of third subexpression; result of this match is captured in \3 (In this example, it holds Smith.) Table 12: Explanation of the Social Security Number Regular Expression Regular-Expression Item Description ^ Start of line character (Regular expression cannot have any leading characters before the match.) ( Start subexpression and list alternates separated by the | metacharacter [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {3} Repeat exactly three occurrences of character list - A hyphen [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {2} Repeat exactly two occurrences of character list - Another hyphen [ Start of character list [:digit:] POSIX numeric digit class ] End of character list {4} Repeat exactly four occurrences of character list | Alternation metacharacter; ends the first choice and starts the next alternate expression [ Start of character list [:digit:] POSIX numeric digit class. ] End of character list {9} Repeat exactly nine occurrences of character list ) Ending parenthesis, to close the subexpression group used for alternation $ Anchoring metacharacter, to indicate the end of the line; no extra characters can follow the pattern

Oracle creates dump and log files through DIRECTORY objects. So before you can use Data Pump you must create a DIRECTORY object. Example:
CREATE DIRECTORY datapump AS 'C:\user\datafile\datapump';

Now the Examples

We are all familiar with the FULL database export. Data Pump easily performs this with the following command line:
expdp ananda/abc123 tables=CASES directory=DPDATA1 dumpfile=expCASES.dmp job_name=CASES_EXPORT

Notice there are just a few name changes and instead of specifying the directory path in the file locations the additional parameter for your DIRECTORY is supplied. This command line assumes you are on the database server and environment variables are properly set for a direct connection. Note the parameter job_name above, a special one not found in the original export. All Data Pump work is done though jobs. Data Pump jobs, unlike DBMS jobs, are merely server processes that process the data on behalf of the main process. The main process, known as a master control process, coordinates this effort via Advanced Queuing; it does so through a special table created at runtime known as a master table.

Export monitoring:
While Data Pump Export (DPE) is running, press Control-C; it will stop the display of the messages on the screen, but not the export process itself. Instead, it will display the DPE prompt as shown below. The process is now said to be in "interactive" mode:

Export>

This approach allows several commands to be entered on that DPE job. To find a summary, use the STATUS command at the prompt:

Export> status
Job: CASES_EXPORT
  Operation: EXPORT                        
  Mode: TABLE                         
  State: EXECUTING                     
  Degree: 1
  Job Error Count: 0
  Dump file:  /u02/dpdata1/expCASES.dmp
      bytes written =  2048

Worker 1 Status:
  State: EXECUTING                     
  Object Schema: DWOWNER
  Object Name: CASES
  Object Type: TABLE_EXPORT/TBL_TABLE_DATA/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1
  Completed Rows: 4687818

Remember, this is merely the status display. The export is working in the background. To continue to see the messages on the screen, use the command CONTINUE_CLIENT from the Export> prompt.

• Make sure disk_asynch_io remains TRUE: It has no effect on those platforms whose file systems already support asynchronous I/O, but those that don’t are significantly impacted by a value of FALSE.
• db_block_checksum’s default value is FALSE, but if an integrity issue is being investigated requiring this to be set to TRUE, its impact on data loading and unloading is minimal; less than 5%.

Introduction to Monitoring Data Pump

A simple way to gain insight into the status of a Data Pump job is to look into a few views maintained within the Oracle instance the Data Pump job is running. These views are DBA_DATAPUMP_JOBS, DBA_DATAPUMP_SESSIONS, and V$SESSION_LOGOPS. These views are critical in the monitoring of your export jobs so, as we will see in a later article, you can attach to a Data Pump job and modify the execution of the that job.

DBA_DATAPUMP_JOBS
This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.

select * from dba_datapump_jobs

OWNER_NAME JOB_NAME               OPERATION  JOB_MODE   STATE         DEGREE    ATTACHED_SESSIONS
---------- ---------------------- ---------- ---------- ------------- --------- -----------------
JKOOP      SYS_EXPORT_FULL_01     EXPORT     FULL       EXECUTING     1          1
JKOOP      SYS_EXPORT_SCHEMA_01   EXPORT     SCHEMA     EXECUTING     1          1

DBA_DATAPUMP_SESSIONS
This view give gives the SADDR that assist in determining why a Data Pump session may be having problems. Join to the V$SESSION view for further information.

SELECT * FROM DBA_DATAPUMP_SESSIONS

OWNER_NAME JOB_NAME                       SADDR
---------- ------------------------------ --------
JKOOPMANN  SYS_EXPORT_FULL_01             225BDEDC
JKOOPMANN  SYS_EXPORT_SCHEMA_01           225B2B7C

V$SESSION_LONGOPS
This view helps determine how well a Data Pump export is doing. It also shows you any operation that is taking long time to execute.
Basically gives you a progress indicator through the MESSAGE column.

select username, opname, target_desc,
       sofar, totalwork, message 
from V$SESSION_LONGOPS

USERNAME OPNAME               TARGET_DES SOFAR TOTALWORK  MESSAGE
-------- -------------------- ---------- ----- ---------- ------------------------------------------------
JKOOP    SYS_EXPORT_FULL_01   EXPORT       132        132 SYS_EXPORT_FULL_01:EXPORT:132 out of 132 MB done
JKOOP    SYS_EXPORT_FULL_01   EXPORT        90        132 SYS_EXPORT_FULL_01:EXPORT:90 out of 132 MB done
JKOOP    SYS_EXPORT_SCHEMA_01 EXPORT        17         17 SYS_EXPORT_SCHEMA_01:EXPORT:17 out of 17 MB done
JKOOP    SYS_EXPORT_SCHEMA_01 EXPORT        19         19 SYS_EXPORT_SCHEMA_01:EXPORT:19 out of 19 MB done

set serveroutput on size 999999
exec print_table('select * from v$session_longops where sid = 9');

SID                           : 9
SERIAL#                       : 68
OPNAME                        : Transaction Rollback
TARGET                        :
TARGET_DESC                   : xid:0x000e.01c.00000067
SOFAR                         : 10234
TOTALWORK                     : 20554
UNITS                         : Blocks
START_TIME                    : 07-dec-2003 21:20:07
LAST_UPDATE_TIME              : 07-dec-2003 21:21:24
TIME_REMAINING                : 77
ELAPSED_SECONDS               : 77
CONTEXT                       : 0
MESSAGE                       : Transaction Rollback: xid:0x000e.01c.00000067 :
                                 10234 out of 20554 Blocks done
USERNAME                      : SYS
SQL_ADDRESS                   : 00000003B719ED08
SQL_HASH_VALUE                : 1430203031
SQL_ID                        : 306w9c5amyanr
QCSID                         : 0

• Metadata (WRM$)
• Historical data (WRH$)
• AWR tables related to advisor functions (WRI$)

EXEC dbms_workload_repository.create_snapshot;

• Statistics Analysis – the utility checks for stale or missing statistics, which may have a detrimental effect on the query’s optimization.
• SQL Profiling – reviews past executions of the SQL statement to provide further information for recommendations.
• Access Path Analysis – determines if additional objects (indexes, materialized views) can be created to improve the statement’s performance.
• SQL Structure Analysis – reviews the SQL statement’s coding structure to determine if it can be altered to increase performance.

select account_no from accounts where old_account_no = 11

declare
   l_task_id     varchar2(20);
   l_sql         varchar2(2000);
begin
   l_sql := 'select account_no from accounts where old_account_no = 11';
   dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
   l_task_id := dbms_sqltune.create_tuning_task (
      sql_text  => l_sql,
      user_name  => 'ARUP',
      scope      => 'COMPREHENSIVE',
      time_limit => 120,
      task_name  => 'FOLIO_COUNT'
   );
   dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
end;
/

set serveroutput on size 999999
set long 999999
spool recommendations.txt
select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;
spool off;

select account_no from accounts a 
where account_name = 'HARRY' 
and sub_account_name not in 
  ( select account_name from accounts 
    where account_no = a.old_account_no and status is not null);

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  The optimizer could not unnest the subquery at line ID 1 of the execution
  plan.

  Recommendation
  --------------
    Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used
    on both sides of the "NOT IN" operator are declared "NOT NULL" by adding
    either "NOT NULL" constraints or "IS NOT NULL" predicates.

  Rationale
  ---------
    A "FILTER" operation can be very expensive because it evaluates the
    subquery for each row in the parent query. The subquery, when unnested can
    drastically improve the execution time because the "FILTER" operation is
    converted into a join. Be aware that "NOT IN" and "NOT EXISTS" might
    produce different results for "NULL" values.

begin
   dbms_sqltune.accept_sql_profile (
      task_name   => 'FOLIO_COUNT',
      name        => 'FOLIO_COUNT_PROFILE'
      description => 'Folio Count Profile',
      category    => 'FOLIO_COUNT');
end;

• Database Buffer cache (The Default pool) (db_cache_size)
• Shared pool (shared_pool_size)
• Large pool (large_pool_size)
• Java pool (java_pool_size)

• Log Buffer
• Other Buffer Caches (KEEP/RECYCLE, other   block sizes)
• Streams Pool (new in Oracle Database 10g)
• Fixed SGA and other internal allocations

begin
   dbms_stats.restore_table_stats (
      'ARUP',
      'REVENUE',
      '31-MAY-04 10.00.00.000000000 PM -04:00');
end;

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------
17-MAY-04 03.21.33.594053000 PM -04:00

execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)

• output specifies the file where the output is generated. When this option is not specified, the standard output is used for the output.
• session consolidates the trace information for the session specified. The session Id is a combination of session index and session serial number.
• clientid consolidates the trace information given client Id.
• service consolidates the trace information for the given service name.
• action consolidates the trace information for the given action name.
• module consolidates the trace information for the given module name.
• trace_files is a list of all trace file names, separated by spaces, in which trcsess will look for trace information. The wild card character * can be used to specify the trace file names. If trace files are not specified, all the files in the current directory are checked by trcsess.

• Helps the debugging of performance problems in multi-tier environments. In multi-tier environments, a request from an end client is routed to different database sessions by the middle tier.
• Proper accounting of workload for applications using services. Service Name, Module, and Action name provide a means to set apart important transactions in an application.

• client_id_stat_enable: Used to enable statistics accumulation for a given client identifier
• client_id_stat_disable: Used to disable statistics accumulation for a given client identifier
• serv_mod_act_stat_enable: Used to enable statistics accumulation for a given hierarchy of service name, module name, and action name
• serv_mod_act_stat_disable: Used to disable statistics accumulation for a given hierarchy of service name, module name, and action name

• sql_trace—Shows (TRUE/FALSE) if SQL tracing has been enabled in the session
• sql_trace_waits—If session tracing is enabled, you can have the trace write wait information to the trace file; very useful in diagnosing performance issues.
• sql_trace_binds—If the session uses bind variables, you can have the trace write the bind variable values to the trace file. This column shows TRUE/FALSE.

• New columns in the v$session and v$session_wait views that track the resources sessions are waiting for.
• A history of waits per session, enabling diagnosis of performance problems for a desired time frame and period.
• Maintaining wait statistics of each SQL statement in the library cache
• Histograms of wait durations, rather than a simple accumulated average

    BLOCKS
----------
         8

    BLOCKS
----------
         2

Optional Clauses

-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status;

-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID';

MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  DELETE WHERE (b.status = 'VALID');

--Whitespace Support in Windows Path and File Names

SPOOL "My Report.txt"
@"My Report.sql"

• _DATE - Contains the current date (dynamic) by default or else a fixed string
• _PRIVILEGE - Contains privilege level such as AS SYSDBA, AS SYSOPER or blank.
• _USER - Contains the current username (like SHOW USER).
• _CONNECT_IDENTIFER contains the connection identifer used to connect
  

SET FEED OFF
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SET FEED ON
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "

-- SHOW RECYCLEBIN

show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
BONUS            BIN$F5d+By1uRvieQy5o0TVxJA==$0 TABLE        2004-03-23:11:03:38
DEPT             BIN$Ie1ifZzHTV6bDhFraYImTA==$0 TABLE        2004-03-23:11:03:38
EMP              BIN$Vu5i5jelR5yPGTP2M99vgQ==$0 TABLE        2004-03-23:11:03:38
SALGRADE         BIN$L/27VyBRRP+ZGWnZylVbZg==$0 TABLE        2004-03-23:11:03:38
TEST1            BIN$0lObShnuS0+6VS1cvLny0A==$0 TABLE        2004-03-24:15:38:42

show recyclebin test1
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1            BIN$0lObShnuS0+6VS1cvLny0A==$0 TABLE        2004-03-24:15:38:42

utl_i18n.string_to_raw (p_in_val, 'AL32UTF8');

typ => dbms_cryptio.encrypt_aes256 + 
       dbms_cryptio.chain_cbc + 
	   dbms_cryptio.pad_pkcs5

utl_i18n.raw_to_char (l_enc_val, 'AL32UTF8')

Oracle10G's DBMS_FILE_TRANSFER PL/SQL package provides administrators with a mechanism to copy binary files between Oracle databases without using OS commands or FTP.   The transfer package can be executed locally to transfer files to another database server or can be executed remotely to transfer files between two remote databases.  Currently, the only files that can be copied using this mechanism are Data Pump dump sets and tablespace data files.  In addition, the file size must be a multiple of 512 bytes and less than 2 terabytes.  Using the file transfer package in conjunction with Oracle's transportable tablespace feature allows administrators to totally automate tablespace data transfers from one database to another.   The process to unplug tablespace data files from the source database, copy the files to the destination server and plug the tablespace data files into the target database can now be executed on a recurring basis by batch jobs initiated by DBMS_JOBS, OEM, KRON, AT and third-party schedulers. The transferred files created on the target platforms are owned by the Oracle account and can be accessed by all database processes. For long copy operations, progress is displayed in the V$SESSION_LONGOPS view

In previous release of Oracle, all parameters were considerd equally important. This made the administration and tuning of the database very difficult because database administrators need to become familiar with over 200 parameters. Oracle 10g introduces two classes of parameter: basic and advanced. In most cases, you need only set up the basic parameters for an Oracle 10g instance.

These basic parameters include:

• cluster_database
• compatible
• control_files
• db_block_size
• db_create_file_dest
• db_create_online_log_dest_n
• db_domain
• db_name
• db_recovery_file_dest
• db_recovery_file_dest_size
• instance_number
• job_queue_processes
• log_archive_dest_n
• log_archive_dest_state_n
• nls_language
• nls_territory
• open_cursors
• pga_aggregate_target
• processes
• remote_listener
• remote_login_passwordfile
• rollback_segments
• sessions
• sga_target
• shared_servers
• star_transformation_enabled
• undo_management
• undo_tablespace

The following is an example of the parameter file generated by DBCR utility (The basic parameters are in bold):

###########################################
# Archive
###########################################
log_archive_dest_1='LOCATION=/u03/arch/grid'
log_archive_format=%t_%s_%r.dbf

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=world
db_name=grid

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u01/app/oracle/admin/grid/bdump
core_dump_dest=/u01/app/oracle/admin/grid/cdump
user_dump_dest==/u01/app/oracle/admin/grid/udump
timed_statistics=TRUE

###########################################
# File Configuration
###########################################
control_files=("/u02/ctl/grid/control01.ctl", "/u02/ctl/grid/control02.ctl")

###########################################
# Job Queues
###########################################
job_queue_processes=10

###########################################
# Compatibility
###########################################
compatible=10.1.0.0.0

###########################################
# Optimizer
###########################################
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE

###########################################
# Pools
###########################################
java_pool_size=104857600
large_pool_size=8388608
shared_pool_size=104857600

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300

###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=524288

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1

In Oracle9i (9.2.0.3), there are 258 parameters in the v$parameter view. In Oracle 10g (10.1.0.0), there are more than 250 parameters in the v$parameter view. There are 233 parameters in both Oracle9i Release 2 and Oracle 10g Release 1.

Old Parameters

Twenty-five of the 258 parameters no longer exist in Oracle 10g’s v$parameter view; these are:

New Parameters

There are twenty more new parameters in Oracle 10g Release 1:

V$SQLSTATS Performance View
Before we discuss the new V$SQLSTATS view, let's review some tuning information. V$SQLAREA is one of the best SQL tuning views. I use the two queries below to identify poorly performing SQL. I take the traditional "top down" tuning approach and start tuning the highest resource consuming SQL idenfified by the scripts below.

The following query dentifies the SQL responsible for the most disk reads:

SELECT disk_reads, executions, disk_reads/executions, hash_value, sql_text
  FROM v$sqlarea
  WHERE disk_reads > 5000
  ORDER BY disk_reads;

The following query dentifies the SQL responsible for the most buffer hits:

SELECT buffer_gets, executions, buffer_gets/executions, hash_value, sql_text
  FROM v$sqlarea
  WHERE buffer_gets > 100000
  ORDER BY buffer_gets;

You can create a more readable report in SQLPLUS by inserting report breaks between the output lines. To generate the report breaks in SQLPLUS, issue the following statement before running the query:

BREAK ON disk_reads SKIP 2 --- for the disk read report and
BREAK ON buffer_gets SKIP 2 --- for the buffer get report

It's common knowledge that poorly performing SQL is responsible for the majority of database performance problems. The first query returns SQL statements responsible for generating disk reads greater than 5,000 while the second query returns SQL statements responsible for generating buffer reads greater than 100,000. These are good numbers to start with and you can adjust them according to the size of the system you are tuning. You'll notice that I divide the number of disk and buffer reads by the number of statement executions. If a statement is generating 1,000,000 disk reads but is executed 500,000 times, it probably doesn't need tuning. Heavy disk reads per statement execution usually means a lack of proper indexing. Heavy buffer reads usually means the exact opposite - indexes are being used when they shouldn't be.

But the SQLTEXT column in V$SQLAREA does not provide the entire text of the SQL statement. That's why I include the HASH_VALUE column in the report. I can use that value to dump the entire SQL statement from V$SQLTEXT using the statement below (where xxxxxxxx is the value in the HASH_VALUE column from the V$SQLAREA reports above):

SELECT sql_text FROM v$sqltext WHERE hash_value = 'xxxxxxxxx' ORDER BY piece;

Oracle 10G R2 provides a new view called V$SQLSTATS that contains a combination of columns that appear in V$SQL and V$SQLAREA. The benefits that V$SQLSTATS provides are as follows:

• Since V$SQLSTATS contains the entire text of the SQL statement AND its associated performance statistics, we are no longer required to access both the V$SQLTEXT and V$SQLAREA to obtain the information we need.
• Oracle states that V$SQLSTATS is faster and more scalable.
• The data in V$SQLAREA has a tendency to get its contents flushed out just when you need to get additional information from it. The V$SQLSTATS view provides users with a longer access window.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE

The table and its associated objects are placed in a logical container known as the "recycle bin," which is similar to the one in your PC. However, the objects are not moved from the tablespace they were in earlier; they still occupy the space there. The recycle bin is merely a logical structure that catalogs the dropped objects. Use the following command from the SQL*Plus prompt to see its content (you'll need SQL*Plus 10.1 to do this):

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST      BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE        2004-02-16:21:13:31

This shows the original name of the table, RECYCLETEST, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop. (Note: the exact name may differ by platform.) To reinstate the table, all you have to do is use the FLASHBACK TABLE command:

SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;
FLASHBACK COMPLETE.

SQL> SELECT * FROM TAB;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
RECYCLETEST                    TABLE

Voila! The table is reinstated effortlessly. If you check the recycle bin now, it will be empty.

Remember, placing tables in the recycle bin does not free up space in the original tablespace. To free the space, you need to purge the bin using:

PURGE RECYCLEBIN;

DROP TABLE RECYCLETEST PURGE;

PURGE TABLE TEST;

PURGE TABLE "BIN$04LhcpndanfgMAAAAAANPw==$0";

purge index in_test1_01;

PURGE TABLESPACE USERS;

PURGE TABLESPACE USERS USER SCOTT;

PURGE RECYCLEBIN;

PURGE DBA_RECYCLEBIN;

Querying Changes to a Table
In this example, I have used a bank's foreign currency management application. The database has a table called RATES to record exchange rate on specific times.

SQL> desc rates
 Name              Null?    Type
 ----------------- -------- ------------
 CURRENCY                   VARCHAR2(4)
 RATE                       NUMBER(15,10)

declare
   l_used_bytes number;
   l_alloc_bytes number;
begin
   dbms_space.create_index_cost (
      ddl => 'create index in_bookings_hist_01 on bookings_hist '||
        '(booking_id, cust_name) tablespace users',
      used_bytes => l_used_bytes,
      alloc_bytes => l_alloc_bytes
   );
   dbms_output.put_line ('Used Bytes      = '||l_used_bytes);
   dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/   

Used Bytes      = 7501128
Allocated Bytes = 12582912

• Modify the storage parameters of the table
• Move the table to a different tablespace in the same schema
• Add support for parallel queries
• Add or drop partitioning support
• Re-create the table to reduce fragmentation
• Change the organization of a normal table (heap organized) to an index-organized table and vice versa
• Add or drop a column

1. The original table is redefined such that it has all the attributes, indexes, constraints, grants and triggers of the interim table

2. The referential constraints involving the interim table now involve the post redefined table and are enabled.

3. Dependent objects that were registered, either explicitly using REGISTER_DEPENDENT_OBJECT or implicitly using COPY_TABLE_DEPENDENTS, are renamed automatically.

insert into accounts
  select * from accounts_ny;

insert into accounts
*
ERROR at line 1:
ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated

	exec dbms_errlog.CREATE_ERROR_LOG ('ACCOUNTS','ERR_ACCOUNTS');

insert into accounts
  select * from accounts_ny
  log errors into err_accounts
  reject limit 200;

select ORA_ERR_NUMBER$, ORA_ERR_MESG$, ACC_NO
  from err_accounts;

ORA_ERR_NUMBER$   ORA_ERR_MESG$                                            ACC_NO
---------------  --------------------------------------------------------  ------
              1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated   9997
              1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated   9998
              1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated   9999
              1  ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated  10000

• Deferred constraints are violated.
• Direct-path INSERT or MERGE operations raise unique constraint or index violations.
• UPDATE or MERGE operations raise a unique constraint or index violation.

set serveroutput on

show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 115
ORA-06512: at line 2

  1  begin
  2     DBMS_SWRF_INTERNAL.AWR_EXTRACT (
  3        dmpfile   => 'awr_data.dmp',
  4        dmpdir    => 'TMP_DIR',
  5        bid       => 302,
  6        eid       => 305
  7     );
  8* end;

  1  begin
  2     DBMS_SWRF_INTERNAL.AWR_LOAD (
  3        SCHNAME => 'ARUP',
  4        dmpfile => 'awr_data',
  5        dmpdir => 'TMP_DIR'
  6     );
  7* end;

  1  begin
  2     DBMS_SWRF_INTERNAL.MOVE_TO_AWR (
  3        SCHNAME => 'ARUP'
  4     );
  5* end;