block's life of Full Table Scan

When a block needs to be read into the buffer cache, Oracle must first find a free buffer in
which to store the block. The process searches the LRU list, beginning at the least recently used
end. It will search until it finds a free buffer. If it cannot find any, it will signal the DBW0 process
to flush any dirty blocks back to disk in order to make room. If no dirty blocks exist, the least
recently used block will be aged out to make room. The block will then be written to the buffer,
and the buffer moves to the most recently used end of the LRU list.

The exception to this rule is when a full table scan operation is performed. Blocks retrieved
from a full table scan are added to the least recently used end of the LRU list, so they will be aged
out quickly. This is because full table scan blocks are generally scanned quickly and are no longer
needed. This functionality can cause problems for small tables that are accessed frequently, such
as lookup tables. Small tables are meant to be accessed via full table scans. This is because a full
table scan will outperform an index lookup on a very small table.
Because Oracle 10g will put these blocks at the least recently used end of the LRU list, they
will age out quickly. As a result, the next time that the table is accessed, Oracle 10g may likely
have to perform PIO to retrieve the blocks again. In this situation, you can add the CACHE clause
to the table (either via ALTER TABLE or in the CREATE TABLE statements) to circumvent this
behavior. You can also choose to pin the table in memory.

remove external procedures

By default, Oracle creates a service for external procedures in the listener. For the majority
of users, this service is never used. Therefore, the service should be removed from the listener.

Database checkpoint

Database checkpoints are closely tied to redo log file switches. A checkpoint is an event that
flushes the modified data from the buffer cache to the disk and updates the control file and datafiles.
The CKPT process updates the headers of datafiles and control files; the actual blocks are
written to the file by the DBWn process. A checkpoint is initiated
When the redo log file is filled and a log switch occurs.
When the instance is shut down with NORMAL, TRANSACTIONAL, or IMMEDIATE.
When a tablespace status is changed to read-only or put into BACKUP mode.
When other values specified by certain parameters (discussed later in this section) are
reached.
You can force a checkpoint if needed, as shown here:
ALTER SYSTEM CHECKPOINT;
Forcing a checkpoint ensures that all changes to the database buffers are written to the datafiles
on disk.
Another way to force a checkpoint is by forcing a log file switch:
ALTER SYSTEM SWITCH LOGFILE;

Automatic Checkpoint Tuning
===========================
Oracle Database 10g supports automatic checkpoint tuning. It is enabled if
fast_start_mttr_target is explicitly set to an non-zero value, or
if fast_start_mttr_target is not set at all.
It is an advancement over the MTTR related parameter introduced in
earlier versions. The idea is to use the periods of low I/O usage
to advance checkpoints and therefore improve availability.

How it works
============
Enabling fast-start checkpointing increases the average number of writes
per transaction that DBWn issues for a given workload. However, if the
system is not already near or at its maximum I/O capacity, then
fast-start checkpointing has a negligible impact on performance

How to monitor
==============
View V$MTTR_TARGET_ADVICE will give information on the additional I/O's
on various values of FAST_START_MTTR_TARGET.

PROs and CONs:
=============
- FAST_START_MTTR_TARGET is set to a low value :
Fast-start checkpointing is more aggressive.
The average number of writes per transaction that DBWn issues
is higher in order to keep the thread checkpoint sufficiently
advanced to meet the requested MTTR.
- FAST_START_MTTR_TARGET is set to a high value:
Fast-start checkpointing in less aggressive, and the average
number of writes per transaction that DBWn issues is lower.

rows returned order

In heap-organized tables and traditional hash clusters, the order in which
rows are returned is not under user control and depends on internal algorithms and the relative
physical location of data blocks on disk.
For each hash cluster key, Oracle maintains a list of
rows sorted by one or more sort columns.

object's precedence

There is an order of precedence with regards to the use of synonyms and local objects. This is:

1. Local objects will always be accessed first.

2. If a local object does not exist, the object with a private synonym will be accessed.

3. If a private synonym does not exist or the object does not exist, then the public synonym will be used.

Note that a synonym can be created for an object that does not exist, and an object with an associated synonym can be dropped without removing the synonym. This can cause all sorts of interesting problems for DBA’s, so be careful.

Ojbect Namespace

the namespace shared by tables and views, and the database has separate
namespaces for each of the following:
Indexes
Constraints
Clusters
Database triggers
Private database links
Dimensions
Roles
Public synonyms
Public database links
Tablespaces
Profiles
Parameter files (PFILEs)

reconfig DB Console HTTP port number

cat $ORACLE_HOME/install/portlist.ini

with the arguments to configure DB Control and Agent Port number

emca -reconfig ports -DBCONTROL_HTTP_PORT 5504 -AGENT_PORT 1834

STARTED EMCA at May 12, 2008 2:08:16 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: MNPP

Do you wish to continue? [yes(Y)/no(N)]: Y
May 12, 2008 2:08:24 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /software/oramnp/product/10.2.0/cfgtoollogs/emca/MNPP/emca_2008-05-12_02-08-16-PM.log.
May 12, 2008 2:08:24 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
May 12, 2008 2:08:38 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
May 12, 2008 2:10:20 PM oracle.sysman.emcp.EMDBPostConfig performReconfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://mnp:5504/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at May 12, 2008 2:10:20 PM