Infolinks

Saturday 12 May 2012

ORACLE-INTERVIEW QUSANS VER 1.1


Important Questions in Oracle, Sql and Pl/Sql

ORACLE

1) What it architecture of Oracle Server and  what are the Back ground processes in Oracle and what are they.

1. System Global Area (SGA): The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. An SGA and the Oracle background processes constitute an Oracle instance.  Oracle allocates the system global area when an instance starts and deallocates it when the instance shuts down. Each instance has its own system global area. Users currently connected to an Oracle Server share the data in the system global area. For optimal performance, the entire system global area should be as large as possible (while still fitting in real memory) to store as much data in memory as possible and minimize disk I/O. The information stored within the system global area is divided into several types of memory structures, including the database buffers, redo log buffer, and the shared pool. These areas have fixed sizes and are created during instance startup.
a) Database Buffer Cache Database buffers of the system global area store the most recently used blocks of database data; the set of database buffers in an instance is the database buffer cache. These buffers can contain modified data that has not yet been permanently written to disk. Because the most recently (and often the most frequently) used data is kept in memory, less disk I/O is necessary and performance is increased.


b) Redo Log Buffer The redo log buffer of the system global area stores redo entries -- a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log file, which is used if database recovery is necessary. Its size is static.

c) Shared Pool The shared pool is a portion of the system global area that contains shared memory constructs such as shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted to a database. A shared SQL area contains information such as the parse tree and execution plan for the corresponding statement. A single shared SQL area is used by multiple applications that issue the same statement, leaving more shared memory for other uses.

Cursors A cursor is a handle (a name or pointer) for the memory associated with a specific statement. Although most Oracle users rely on the automatic cursor handling of the Oracle utilities, the programmatic interfaces offer application designers more control over cursors. For example, in precompiler application development, a cursor is a named resource available to a program and can be specifically used for the parsing of SQL statements embedded within the application. The application developer can code an application so that it controls the phases of SQL statement execution and thus improve application performance.

Program Global Area (PGA): The Program Global Area (PGA) is a memory buffer that contains data and control information for a server process. A PGA is created by Oracle when a server process is started. The information in a PGA depends on the configuration of Oracle.

Processes: A process is a "thread of control" or a mechanism in an operating system that can execute a series of steps. Some operating systems use the terms job or task. A process normally has its own private memory area in which it runs.
An Oracle Server has two general types of processes: user processes and Oracle processes.

User (Client) Processes: A user process is created and maintained to execute the software code of an application program (such as a Pro*C/C++ program) or an Oracle tool (such as Server Manager). The user process also manages the communication with the server processes. User processes communicate with the server processes through the program interface, described later in this section.

Oracle Processes: Oracle processes are called by other processes to perform functions on behalf of the invoking process. The different types of Oracle processes and their specific functions are discussed in the following sections.

Server Processes: Oracle creates server processes to handle requests from connected user processes. A server process is in charge of communicating with the user process and interacting with Oracle to carry out requests of the associated user process. For example, if a user queries some data that is not already in the database buffers of the system global area, the associated server process reads the proper data blocks from the datafiles into the system global area.
Oracle can be configured to vary the number of user processes per server process.

In a dedicated server configuration, a server process handles requests for a single user process.

A multi-threaded server configuration allows many user processes to share a small number of server processes, minimizing the number of server processes and maximizing the utilization of available system resources.

On some systems, the user and server processes are separate, while on others they are combined into a single process. If a system uses the multi-threaded server or if the user and server processes run on different machines, the user and server processes must be separate. Client/server systems separate the user and server processes and execute them on different machines.

Background Processes Oracle creates a set of background processes for each instance. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user process. The background processes asynchronously perform I/O and monitor other Oracle processes to provide increased parallelism for better performance and reliability.

An SGA and the Oracle background processes constitute an Oracle instance. 
Each Oracle instance may use several background processes. The names of these processes are DBWR, LGWR, CKPT, SMON, PMON, ARCH, RECO, Dnnn and LCKn. Each background process is described in the following sections.
1. Database Writer (DBWR) The Database Writer writes modified blocks from the database buffer cache to the datafiles. Because of the way Oracle performs logging, DBWR does not need to write blocks when a transaction commits. Instead, DBWR is optimized to minimize disk writes. In general, DBWR writes only when more data needs to be read into the system global area and too few database buffers are free. The least recently used data is written to the datafiles first.
2. Log Writer (LGWR) The Log Writer writes redo log entries to disk. Redo log data is generated in the redo log buffer of the system global area. As transactions commit and the log buffer fills, LGWR writes redo log entries into an online redo log file.
3. Checkpoint (CKPT) At specific times, all modified database buffers in the system global area are written to the datafiles by DBWR; this event is called a checkpoint. The Checkpoint process is responsible for signalling DBWR at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint. CKPT is optional; if CKPT is not present, LGWR assumes the responsibilities of CKPT.
4. System Monitor (SMON) The system monitor performs instance recovery at instance startup. In a multiple instance system (one that uses the Parallel Server), SMON of one instance can also perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online. SMON also coalesces free extents within the database to make free space contiguous and easier to allocate.
5. Process Monitor (PMON) The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on dispatcher (see below) and server processes and restarts them if they have failed.
6. Arhiver (ARCH) The archiver copies the online redo log files to archival storage when they are full. ARCH is active only when a database's redo log is used in ARCHIVELOG mode.
7. Recoverer (RECO) The recoverer is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.
8. Dispatcher (Dnnn) Dispatchers are optional background processes, present only when a multi-threaded server configuration is used. At least one dispatcher process is created for every communication protocol in use (D000, . . ., Dnnn). Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.
9. Lock (LCKn) Up to ten lock processes (LCK0, . . ., LCK9) are used for inter-instance locking when the Oracle Parallel Server is used.

2) What is a Transaction in Oracle?
2) A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.

3)  Key Words Used in Oracle
3) The Key words that are used in Oracle are:
a) Commit: A transaction is said to be committed when the transaction makes permanent    changes resulting from the SQL statements.
b) Rollback: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) SavePoint: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transaction into smaller points.
d)  Rolling Forward: Process of applying redo log during recovery is called rolling forward.
 e) Cursor: A cursor is a handle (name or a pointer) for the memory associated with a specific statement.  A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.
f) System Global Area (SGA): The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance. It consists of Database Buffer Cache and Redo log Buffer and shared pool. Shared pool contain data dictionary and library Cash.
g) Program Global Area (PGA): The PGA is a memory buffer that contains data and control information for server process. PGA contains cursor handle, memory for shorting, bind variable and if the server is a multisession then it also contain session information.
g) Database Buffer Cache: Database Buffer of SGA stores the last recently used (LRU) blocks of database data. The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process: A Process is a 'thread of control' or mechanism in Operating System that executes series of steps.

4) How many Integrity Rules are there and what are they
4) There are Three Integrity Rules. They are as follows:
a) Entity Integrity Rule: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

5) Is space acquired in blocks or extents?
5) In extents.

6) What are the Various Master and Detail Relation ships?
6) The various Master and Detail Relationship are
a) NonIsolated: The Master cannot be deleted when a child is existing.
b) Isolated: The Master can be deleted when the child is existing.
c) Cascading: The child gets deleted when the Master is deleted.

7) What are the inline and the precompiler directives?
7) The inline and precompiler directives detect the values directly

8) What are snapshots and views?
8) Snapshots are mirror or replicas of tables.  Reasons for defining Snapshot:
1. Response time improve when a local read-only copy of the table exists- this can be many times faster than reading data directory from a database.
2. Once a snapshot is built on a remote database, if the node containing the data from which the snapshot is built is not available, the snapshot can be used without the need to access the unavailable database.
Snapshot are used to dynamically replicate data between distributed databases. The master table will be updateable but the snapshot can be either read-only or updateable. Read-only snapshots are the most common types of snapshots implemented.

There are 2 types are snapshots
Simple snapshots - Each row is based on a single row in a single table.
Complex snapshots - A row may be based on more than one row in a remote table, such as via a "group by" operation or on the result of a "multi-table join".

A view is a tailored presentation of the data contained in one or more tables (or other views). A view takes the output of a query and treats it as a table; therefore, a view can be thought of as a "stored query" or a "virtual table". You can use views in most places where a table can be used.

To provide an additional level of table security by restricting access to a predetermined set of rows and/or columns of a table, To hide data complexity, To simplify commands for the user, To present the data in a different perspective from that of the base table, To isolate applications from changes in definitions of base tables, To express a query that cannot be expressed without using a view, To  save complex queries, To achieve improvements in availability and performance

Updatable Join Views
A join view is defined as a view with more than one table or view in its FROM clause and which does not use any of these clauses: DISTINCT, AGGREGATION, GROUP BY, START WITH, CONNECT BY, ROWNUM, and set operations (UNION ALL, INTERSECT, and so on).
An updatable join view is a join view, which involves two or more base tables or views, where UPDATE, INSERT, and DELETE operations are permitted. The data dictionary views, ALL_UPDATABLE_COLUMNS,  DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS, contain information that indicates which of the view columns are updatable.
lists rules for updatable join views.
Rule
Description
General Rule
Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.
UPDATE Rule
All updatable columns of a join view must map to columns of a key preserved table. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are non-updatable.
DELETE Rule
Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.
INSERT Rule
An INSERT statement must not, explicitly or implicitly, refer to the columns of a non-key preserved table. If the join view is defined with the WITH CHECK OPTION clause, then INSERT statements are not permitted

9)  What is the difference between candidate key, unique key and primary key?
9) Candidate keys are the columns in the table that could be the primary keys and the primary key
is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.

10) What is concurrency?
10) Concurrency is allowing simultaneous access of same data by different users.  Locks useful for accessing the database are
a) Exclusive:  The exclusive lock is useful for locking the row when an insert, update or delete is being done. This lock should not be applied when we do only select from the row.
b) Share lock: We can do the table as Share_Lock as many share_locks can be put on the same resource.

11) Privileges and Grants?
11) Privileges are the right to execute a particular type of SQL statements. The grant has to be given by the owner of the object.
There are 3 types of privileges
1) System Level: Right to create a new user, Right to Connect to Database - Session.
2) Schema Level: Right to create Tables in Own Schema, Right to Create Synonyms in Own Schema.
3) Object Level: Grant other permission to Tables. Grants are given to the objects so that the object might be accessed accordingly.
If any (select)  privilege on the any table is granted via a role we can not use it a stored procedure, for this it has to be granted directly to the user.

12) What is synonym & grant?
12) Synonyms and grants are independant database objects.  You can create one but not the other, and vice versa.

Synonyms are a shortcut way of refering to an object (table, view, package, procedure, etc.) usually in another schema.  You can create the following synonym: create public synonym emp for scott.emp;
to make it easy for any user in the database to refer to the "emp" table in the "scott" schema simply as "emp", instead of the "scott.emp" syntax that would be required if there was no synonym.  The existance of a synonym like this though does not by itself give other uses the right to use scott's table.

A grant is a permission (to someone else) to use an object in a schema they do not own.  For example, if you log in as scott, then: grant select on emp to public;
anyone will be able to see (select) the data in scott's emp table, although they will not be able to add, change or delete it.  You could: grant select, insert, update, delete on emp;
if you want to allow the other actions.

In addition to grants to public (all users) you may grant priviliges to specific users or to roles instead.  The roles can then be granted to users.  This centralizes grants and reduces grant maintenance complexity

There is another complexity to synonyms.  Oracle supports both "public" synonyms (like the ones I examples of above) or "private" synonyms that belong to, and can only be used in, the schema that owns them.

Public synonyms are available to any/all users and are a good way to make sure that all users see the same data when they issue a statement like: select * from emp;

If your database though has multiple "emp" tables in separate schemas, then you may need private synonyms.  For example, if schema "bob" also has an "emp" table, and users Mary and Sue should use the "emp" table in bob's schema rather the the one in the scott schema, but all other users should use the one in the scott schema, then you would still want a public synonym "emp" for "scott.emp", but in Mary's and Sue's schema they each need to:
create synonym emp for bob.emp;
(Note: no "public" in this synonym definition)

This insures that when they issue:  select * from emp;
they will see the data in the bob.emp table rather than the scott.emp table

13) TableSpace, Data Files, Parameter File, Control Files
13) TableSpace: The tablespace is useful for storing the data in the database. When a database is created two table spaces are created.
a) System Table space: This data file stores all the tables related to the system and dba tables.
b) User Table space: This data file stores all the user related tables. We should have separate table spaces for storing the tables and indexes so that the access is fast.
Data Files: Every Oracle Data Base has one or more physical data files. They store the data for the database. Every datafile is associated with only one database. Once the Data file is created the size cannot change. To increase the size of the database to store more data we have to add data file.
Parameter Files: Parameter file is needed to start an instance. A parameter file contains the list of instance configuration parameters e.g.:
db_block_buffers = 500
db_name = ORA7
db_domain = u.s.acme lang
Control Files: Control files record the physical structure of the data files and redo log files
They contain the Db name, name and location of dbs, data files, redo log files and time stamp.

14) Physical Storage of the Data
14) The finest level of granularity of the data base are the data blocks.
Data Block: One Data Block correspond to specific number of physical database space.
Extent: Extent is the number of specific number of contiguous data blocks.
Segments: Set of Extents allocated for Extents. There are three types of Segments:

a) Data Segment: Non Clustered Table has data segment data of every table is stored in cluster data segment Or Data segment contain application data and tables.
b) Index Segment: Most database will have a index segment since most application tables will have index created on it.
 c) Roll Back Segment: Temporarily store 'undo' information.
d) Temporary Segment: Whenever oracle need to perform a short Temporary Segment must be created to perform a short.
d) Bootstrap segment: This segment is internal to Oracle and the database administrator does not have to worry about them.
Tablespace: Tablespace is classified by the types of data they contain called segments.

15) What is segment ? What is Min Extend and Max Exdend ?
15) Basically, a segment is table or extent etc stored in the tablespace. A segment comprise of one or more extents- sometimes these extend besides each other (contiguous),  some times they are not.
Minimum extent - A number that should be allocated when the table space is created.
Maximun extent- A number upto this the tablespace can be extended.(It can be created with the keyword unlimited which indicate an unlimited number of extend allowed).

16) What are the PCTFREE(PF) and PCTUSED (PU)?
16) PF is used to denote the percentage of the free space that is to be left when creating a table. Similarly PU is used to denote the percentage of the used space that is to be used when creating a table

"PF: Percentage of space Oracle will leave in the current block when inserting a row into a table.  .... Set PF to allow for row expansion and its associated chaining and migration..."

"Most applications will perform optimally with a PF set high enough to avoid all chaining and migration, but  PU value can also have an impact on performance. A high PU indicates data will be stored very efficiently in terms of space usage, but the likelihood of Chaining and migration is increased."

PF and PU are parameters set in the STORAGE clause for a table.  If not specified for the table, then the values are taken from the DEFAULT STORAGE clause for the tablespace.

When rows are inserted to a table, Oracle locates a block from the "free list" which is basically a list of blocks that have free space available for new rows.

When the percentage of available free space in a block drops below the PF threshold, Oracle will not insert any more new rows into the block, and the block is removed from the "free list".  The remaining free space within the block is reserved for expansion of existing rows in the block.

As rows are deleted from a database block, more space becomes available, but Oracle will not make the block available for new inserts (that is, put the block back onto the "free list") until the percentage of space used in the block falls below the PU threshold.

To avoid a potential performance problem with Oracle flipping blocks onto and off of the "free list", you want to make sure that the sum of PF and PU is less than 100.

When a row is ready to insert, Oracle checks the block to see if inserting the row would cause the block to exceed PF free space. If so, it does not do the insert.  It then checks PU - if the block is at least PU filled, then the block is taken off the free list. That's why they tell you not to set PF + PU = 100.  Because you'll never take the block off the free list even though you can't use it for inserts.  e.g.

If PF is 20 and PU is 80 and the block is currently 75% full and the new row to be inserted is 10% of the block size. an insert is not performed because (100% - 85%) is less than PF. the block is not taken off the free list because it hasn't reached PU (80%) capacity.  This will repeat over and over. 

If you change PU to 60 then the block will be taken off the free list after the first insert attempt fails.  It will be put back on the free list when a delete is performed and the data in the block is < 60%
e.g.: Pctfree 20, Pctused 40

17) What is Row Chaining?
17) Row Chaining across Data Blocks
In some circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows (for example, rows that contain a column of datatype LONG or LONG RAW).
If a table contains a column of datatype LONG, which can hold up to two gigabytes of information, the data for a row may need to be chained to one or more data blocks. Nothing can be done to avoid this type of row chaining.
If a row in a data block is updated so that the overall row length increases and the block's free space has been completely filled, the data for the entire row is migrated to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row; the ROWID of a migrated row does not change.
When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row. For information about reducing migrated rows and improving I/O performance.

18)  What are Codd Rules?
18) Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only Rdbms to satisfy the maximum number of rules.

Rule 0 (the foundation, the others are just expansions of this): For any system that is advertised as, or claimed to be, a relational database management system, that system must be able fo manage databases entirely through its relational capabilities.
Rule 1 (information representation) : All information in a relational database is represented explicitly at the logical level and in exactly one way - by values in tables.
Rule 2 (guaranteed access): Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column name.
Rule 3 (systematic treatment of null values): Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported for representing missing information and inapplicable information in a systematic way, independent of data type.
Rule 4 (dynamic on-line catalog based on relational model): The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.
Rule 5 (comprehensive data sublanguage): A relational system may support several languages and various modes of terminal use.  However, there must be at least one language whose statements can express all of the following items: (1) data definitions,(2) view definitions, (3) data manipulation (interactive and by program), (4) integrity constraints, (5) authorization, and (6) transaction boundaries (begin, commit, and rollback).
Rule: 6 (view updating): All views that are theoretically updatable are also updatable by the system.
Rule: 7 (high-level insert, update, and delete): The capability of handling a base relation or a derived relation (that is, view) as a single operand applies not only to the retrieval of data but also to the insertion, update, and deletion of data.
Rule 8 (physical data independence): Application programs and terminal activities remain logically unimpaired whenever any changes are made either in storage representations or access methods.
Rule 9 (logical data independence): Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit unimpairment are made to the base tables.
Rule 10(integrity independence): Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and storable in the catalog, not in the application programs.
Rule 11 (distribution independence: The data manipulation sublanguage of a relational DBMS must enable application programs and inquiries to remain logically the same whether and whenever data are physically centralized or distributed.
Rule 12 (non-subversion): If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity rules and constraints expressed in the higher-level relational language (multiple-records-at-a-time).

19) What is Normalization?
19) Normalization is the process of organizing the tables to remove the redundancy.
or
Normalization is the process of decomposing relations with anomalies to produce smaller, well-structured relations.

There are mainly 5

Normalization rules.
a) 1 Normal Form: Any multivalued attributes (also called repeating groups) have been removed, so there is a single value (possible null) at the intersection of each row and column of the table.

b) 2 Normal Form: A relation that is in first normal form and has every nonkey attribute fully functionally dependent on the primary key.

For example: Employee(Emp_id, Course_Title, Name, Dept_Name, Salary, Date_Completed)
In this relation there are two functional dependencies

1. Emp_ID -> Name, Dept_Name, Salary
2. Emp_ID, Course_Title -> Date_Completed
The functional dependencies indicate that the combination of Emp_ID and Course_Title is the only candidate key(and therefore the primary key) for Employee.

c) 3rd Normal Form: A relation is in third normal form(3 NF) if it is in second normal form and no transitive dependencies exist.

For example: Sales(Cust_id, Name, Salesperson, Region)
In this relation region is functionaly dependent on salesperson and salesperson is functionally dependent on cust_no. As a result there are update anomalies in SALES.

1. Insert anomaly - A new salesperson assigned to the North region cannot be entered until a customer has been assigned to that salesperson. (since a value for CUST_ID must be provided top insert a row in the table).
2. Delete anomaly - If customer number is deleted from the table we lose information about the sales person also.
3.Modification anamaly- If salesperson is reassigned to the region, several rows must be changed to reflect that fact.

d) Boyce-Codd Normal Form: Any remaining anomalies that result from functional dependencies have been removed.

e) Forth Normal Form: Any multivalue dependencies have been removed.

f) Fifth Normal Form: Any remaining anomalies have been removed..

20) What is the Difference between a post query and a pre query?
20) A post query will fire for every row that is fetched but the pre query will fire only once.

21) What is the tab table? Or if i create a table called "TAB" oracle allows me to do so but then the problem is that when I fires the statement as "SELECT * FROM TAB" the result is retireved form the table "TAB". then the actual meaning of the "SELECT * FROM TAB" changes and many times the user finds it difficult to rectify the problem. one have to drop the table TAB then only the normal result for the SELECT * FROM TAB is obtained? How to resolve it. Ho how to restrict the user from creating the table TAB ?
21)  TAB is a view owned by SYS - not system.
Here is the example: I have created a table named tab on user USR1. When you query from user1, it shows you from your table TAB -
create table tab ( col1 number, col2 varchar2(50));
SQL> insert into tab values (1,'dasdasdd');
SQL> insert into tab values (2,'dasdasd');
SQL> commit;
SQL> select * from tab;

    COL1 COL2
--------- --------------------------------------
       1 dasdasdd
       2 dasdasd

SQL>  select object_name, owner, object_type from dba_objects where object_name like 'TAB';

OBJECT_NAME    OWNER      OBJECT_TYPE
-------------                 -------------    ---------------
TAB                          SYS             VIEW
TAB                          PUBLIC      SYNONYM
TAB                          SYSTEM     SYNONYM
TAB                          USR1           TABLE

If any other user make a query on tab they will see the view TAB owned by SYS and via public synonym. You have 2 choices -
#1. give permission and make the query:
        grant select on tab to public; (from user1)
        select * from usr1.tab; (from any user)
1
#2. drop public synonym TAB by sys:
        drop public synonym TAB;
        create public synonym tab for user1.tab;
Then all users except SYS (owns the object) and SYSTEM (has private synonym) will see user1.TAB - it will not be possible some user to see TAB view and some other to see user1.tab table in method 2.

22) What are physical structures and logical structures in oracle t are logical and phy
22) 
              logical                        physical
              ------                        ---------

             Database
                  \|/
                   |
             TebaleSpace     -----?     Datafiles
                  \|/                              /    |
                   |                           /        |
             Segment                 /          |
                  \|/                  /                |
                   |               /                    |
             Extension   /|\                    |
                  \|/                                   |
                   |                                   /|\
           Oracle Block  ------?      OS Block

23) Cluster table and index?
23)
Clusters: Clusters are an optional method of storing table data. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT tables Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks.

Clustered Table Data: Because clusters store related rows of different tables together in the same data blocks, properly used clusters offer two primary benefits:
1. Disk I/O is reduced and access time improves for joins of clustered tables.
2. In a cluster, a cluster key value is the value of the cluster key columns for a particular row. Each cluster key value is stored only once each in the cluster and the cluster index, no matter how many rows of different tables contain the value. Therefore, less storage might be required to store related table and index data in a cluster than is necessary in non-clustered table format. For example, notice how each cluster key (each DEPTNO) is stored just once for many rows that contain the same value in both the EMP and DEPT tables.
Disadvantages:
1. Performance Considerations: Clusters can reduce the performance of INSERT statements as compared with storing a table separately with its own index. This disadvantage relates to the use of space and the number of blocks that must be visited to scan a table; because multiple tables have data in each block, more blocks must be used to store a clustered table than if that table were stored non-clustered.

To identify data that would be better stored in clustered form than non-clustered, look for tables that are related via referential integrity constraints and tables that are frequently accessed together using a join. If you cluster tables on the columns used to join table data, you reduce the number of data blocks that must be accessed to process the query; all the rows needed for a join on a cluster key are in the same block. Therefore, performance for joins is improved. Similarly, it might be useful to cluster an individual table. For example, the EMP table could be clustered on the DEPTNO column to cluster the rows for employees in the same department. This would be advantageous if applications commonly process rows department by department.
Like indexes, clusters do not affect application design. The existence of a cluster is transparent to users and to applications. You access data stored in a clustered table via SQL just like data stored in a non-clustered table.
Format of Clustered Data Blocks: In general, clustered data blocks have an identical format to non-clustered data blocks with the addition of data in the table directory. However, Oracle stores all rows that share the same cluster key value in the same data block.
When you create a cluster, specify the average amount of space required to store all the rows for a cluster key value using the SIZE parameter of the CREATE CLUSTER command. SIZE determines the maximum number of cluster keys that can be stored per data block.
For example, if each data block has 1700 bytes of available space and the specified cluster key size is 500 bytes, each data block can potentially hold rows for three cluster keys. If SIZE is greater than the amount of available space per data block, each data block holds rows for only one cluster key value.
Although the maximum number of cluster key values per data block is fixed by SIZE, Oracle does not actually reserve space for each cluster key value nor does it guarantee the number of cluster keys that are assigned to a block. For example, if SIZE determines that three cluster key values are allowed per data block, this does not prevent rows for one cluster key value from taking up all of the available space in the block. If more rows exist for a given key than can fit in a single block, the block is chained, as necessary.
A cluster key value is stored only once in a data block.
The Cluster Key:
The cluster key is the column, or group of columns, that the clustered tables have in common. You specify the columns of the cluster key when creating the cluster. You subsequently specify the same columns when creating every table added to the cluster.
For each column specified as part of the cluster key (when creating the cluster), every table created in the cluster must have a column that matches the size and type of the column in the cluster key. No more than 16 columns can form the cluster key, and a cluster key value cannot exceed roughly one-half (minus some overhead) the available data space in a data block. The cluster key cannot include a LONG or LONG RAW column.
You can update the data values in clustered columns of a table. However, because the placement of data depends on the cluster key, changing the cluster key for a row might cause Oracle to physically relocate the row. Therefore, columns that are updated often are not good candidates for the cluster key.
The Cluster Index
You must create an index on the cluster key columns after you have created a cluster. A cluster index is an index defined specifically for a cluster. Such an index contains an entry for each cluster key value. To locate a row in a cluster, the cluster index is used to find the cluster key value, which points to the data block associated with that cluster key value. Therefore, Oracle accesses a given row with a minimum of two I/Os (possibly more, depending on the number of levels that must be traversed in the index).
You must create a cluster index before you can execute any DML statements (including INSERT and SELECT statements) against the clustered tables. Therefore, you cannot load data into a clustered table until you create the cluster index.
Like a table index, Oracle stores a cluster index in an index segment. Therefore, you can place a cluster in one tablespace and the cluster index in a different tablespace.
A cluster index is unlike a table index in the following ways:
Keys that are all null have an entry in the cluster index.
Index entries point to the first block in the chain for a given cluster key value.
A cluster index contains one entry per cluster key value, rather than one entry per cluster row.
The absence of a table index does not affect users, but clustered data cannot be accessed unless there is a cluster index.
If you drop a cluster index, data in the cluster remains but becomes unavailable until you create a new cluster index. You might want to drop a cluster index to move the cluster index to another tablespace or to change its storage characteristics; however, you must re-create the cluster's index to allow access to data in the cluster.

24) What is a 2 Phase Commit?
24) Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
a) Prepare Phase: Global coordinator asks participants to prepare
b) Commit Phase: Commit all participants to coordinator to Prepared, Read only or abort Reply
Two phase commit between Oracle and another Vendor's database usually requires that BOTH databases support the XA protocol. AND requires the use of some third party software known as a transaction monitor (like Tuxedo or TopEnd). A two phase commit between two Oracle databases may be supported internally by Oracle and may not require XA or a transaction monitor.

A two phase commit is actually a distributed, atomic transaction.
- A global transaction is started.
- Updates are applied to database A.
- Updates are applied to database B.
- A global commit is performed.

This is where it gets interesting...
The transaction monitor (TM) "asks" database 1, "Can you commit?". If it can commit (ie it hasn't crashed or run out of disk/table space since the updates were applied) then the TM goes over to database 2 and asks "Can you commit?". If it can then the TM goes back and "requests" each of the databases to actually commit.

If a failure occurs after a database has said "yes I can commit" and that failure prevents the database from committing (ie the power is turned off) then when the database is "recovering" the XA manager will communicate with the TM to apply the commits (from the transaction log files) that it had poreviously said "yes" to before it crashed.

If a failure occurs after a database has said "yes I can commit" and that failure prevents the database from committing (ie an aeroplane crashes into the computer system, destroying the transaction log files) then you have what is known as a "heuristic hazard" and it usually needs to be "fixed" by manual intervention.

25) What is dual table?
25) DUAL is an interesting table - it contains ONE column and ONE record.
Oracle has created this since it makes some calculations more convenient.
For example, you can use it for math: SELECT (319/212)+10 FROM DUAL;
You can use it to increment sequences: SELECT employee_seq.NEXTVAL FROM DUAL;
You can use it to play around or test some SQL: SELECT CHR(70) FROM DUAL;

26) What does Data Dictionary Contain?
26)
base tables
The foundation of the data dictionary is comprised of a set of base or underlying tables that store information about the associated database. Only Oracle should write and read these tables; users rarely access them directly because they are normalized, and most of the data is stored in a cryptic format.
User accessible views
The data dictionary contains user accessible views that summarize and conveniently display the information in the base tables of the dictionary. The views decode the information in the base tables into useful information, such as user or table names, and use joins and WHERE clauses to simplify the information. Most users are given access to the views rather than the base tables.


SYS, the Owner of the Data Dictionary
The Oracle user SYS owns all base tables and user accessible views of the data dictionary. Therefore, no Oracle user should ever alter any object contained in the SYS schema and the security administrator should keep strict control of this central account.
Note: Altering or manipulating the data in underlying data dictionary tables can permanently and detrimentally affect the operation of a database.

How Oracle Users Can Use the Data Dictionary
USER
user's view (what is in the user's schema)
ALL
expanded user's view (what the user can access)
DBA
Database administrator's view (what all users can access

The Dynamic Performance Tables
Throughout its operation, Oracle maintains a set of "virtual" tables that record current database activity. These tables are called dynamic performance tables.
Because dynamic performance tables are not true tables, they should not be accessed by most users. However, database administrators can query and create views on the tables and grant access to those views to other users.
SYS owns the dynamic performance tables and their names all begin with V_$. Views are created on these tables, and then synonyms are created for the views. The synonym names begin with V$.

27) Partition of table?
27) Partitioning in Oracle
Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a "partition". From an "Application Development" perspective, there is no difference between a partitioned and a non-partitioned table. The application need not be modified to access a partitioned table if that application was initially written on a non partitioned tables.

So now you know partitioning in oracle now the only thing that yo u need to know is little bit of syntax and that's it, and you are a partitioning guru.

Oracle introduced partitioning with 8.0. With this version only, " Range Partitioning" was supported. I will come to details later about what that means. Then with Oracle 8i " Hash and Composite Partitioning" was also introduced and with 9i " List Partitioning", it was introduced with lots of other features with each upgrade. Each method of partitioning has its own advantages and disadvantages and the decision which one to use will depend on the data and type of application. Also one can MODIFY , RENAME, MOVE, ADD, DROP, TRUNCATE, SPLIT partitions. We will go thru the details now.

Advantages of using Partition's in Table
1. Smaller and more manageable pieces of data ( Partitions )
2. Reduced recovery time
3. Failure impact is less
4. import / export can be done at the " Partition Level".
5. Faster access of data
6. Partitions work independent of the other partitions.
7. Very easy to use

1. RANGE Partitioning
This type of partitioning creates partitions based on the " Range of Column" values. Each partition is defined by a " Partition Bound" (non inclusive ) that basically limits the scope of partition. Most commonly used values for " Range Partition" is the Date field in a table. Lets say we have a table SAMPLE_ORDERS and it has a field ORDER_DATE. Also, lets say we have 5 years of history in this table. Then, we can create partitions by date for, lets say, every quarter. 
So Every Quarter Data becomes a partition in the SAMPLE_ORDER table. The first partition will be the one with the lowest bound and the last one will be the Partition with the highest bound. So if we have a query that want to look at the Data of first quarter of 1999 then instead of going through the complete data it will directly go to the Partition of first quarter 1999.

This is example of the syntax needed for creating a RANGE PARTITION.
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER)
PARTITION BY RANGE(ORDER_DATE)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE('01-APR-1999', 'DD-MON-YYYY'),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE('01-JUL-1999', 'DD-MON-YYYY'),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE('01-OCT-1999', 'DD-MON-YYYY'),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE('01-JAN-2000', 'DD-MON-YYYY'),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE('01-APR-2000', 'DD-MON-YYYY'),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE('01-JUL-2000', 'DD-MON-YYYY'),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE('01-OCT-2000', 'DD-MON-YYYY'),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE('01-JAN-2001', 'DD-MON-YYYY')
)
;
the above example basically created 8 partitions on the SAMPLE_ORDERS Table all these partitions correspond to one quarter. Partition SO99Q1 will contain the orders for only first quarter of 1999.

2. HASH Partitioning
Under this type of partitioning the records in a table, are partitions based of a Hash value found in the value of the column, that is used for partitioning. " Hash Partitioning" does not have any logical meaning to the partitions as do the range partitioning. Lets take one example.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
ORDER_ZIP_CODE)
PARTITION BY HASH (ORDER_ZIP_CODE)
(PARTITION P1_ZIP TABLESPACE TS01,
PARTITION P2_ZIP TABLESPACE TS02,
PARTITION P3_ZIP TABLESPACE TS03,
PARTITION P4_ZIP TABLESPACE TS04)
ENABLE ROW MOVEMENT;
The above example creates four hash partitions based on the zip codes from where the orders were placed.
3. List Partitioning ( Only with 9i)
Under this type of partitioning the records in a table are partitioned based on the List of values for a table with say communities column as a defining key the partitions can be made based on that say in a table we have communities like 'Government' , 'Asian' , 'Employees' , 'American', 'European' then a List Partition can be created for individual or a group of communities lets say 'American-partition' will have all the records having the community as 'American'

Lets take one example. In fact, we will modify the same example.
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
PARTITION BY LIST (SHIP_TO_STATE)
(PARTITION SHIP_TO_ARIZONA VALUES ('AZ') TABLESPACE TS01,
PARTITION SHIP_TO_CALIFORNIA VALUES ('CA') TABLESPACE TS02,
PARTITION SHIP_TO_ILLINOIS VALUES ('IL') TABLESPACE TS03,
PARTITION SHIP_TO_MASACHUSETTES VALUES ('MA') TABLESPACE TS04,
PARTITION SHIP_TO_MICHIGAN VALUES ('MI') TABLESPACE TS05)
ENABLE ROW MOVEMENT;

The above example creates List partition based on the SHIP_TO_STATE each partition allocated to different table spaces.

4. Composite Range-Hash Partitioning
This is basically a combination of range and hash partitions. So basically, the first step is that the data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data.
Lets modify the above example again:
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
CUST_NAME VARCAHR2,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE)
SUBPARTITION BY HASH(CUST_NAME)
SUBPARTITION TEMPLATE(
(SUBPARTITION SHIP_TO_ARIZONA VALUES ('AZ') TABLESPACE TS01,
SUBPARTITION SHIP_TO_CALIFORNIA VALUES ('CA') TABLESPACE TS02,
SUBPARTITION SHIP_TO_ILLINOIS VALUES ('IL') TABLESPACE TS03,
SUBPARTITION SHIP_TO_NORTHEAST VALUES ('MA', 'NY', 'NJ') TABLESPACE TS04,
SUBPARTITION SHIP_TO_MICHIGAN VALUES ('MI') TABLESPACE TS05)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE('01-APR-1999', 'DD-MON-YYYY'),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE('01-JUL-1999', 'DD-MON-YYYY'),

28) What is relational database management system?
28)
1. A Relational DBMS (or RDBMS) is data management system that implements a relational data model.
2. Data is stored in collection of tables, and the data relationship is represented by common values, not links.
3. The relational data model assumes that a table is a logical construct rather than a physical construct, so a table need not correspond to a physical file of contiguous records. Distributing one and the same data model may have many different possible physical structures. The storage of  the database depend on both hardware and software environment, and usually the concern of the system administrator.
4. The power of RDBMS is realized through relationship existing between tables. These relationships are established by including a common column(s) in each table where a relationship is required.

29 What is control file ? What are the contents of the control file?
29)
1. Control file describe physical structure of the database. The data dictionary describe the logical structure of the database.
2. Control files keep track of all the files associated with the oracle database and where they are on the server.
3. A Control file is binary file and can only be modified internally by Oracle. No database administrator  or end user can directly modify a control file. Control file can be modified indirectly.
4. The control file is read at startup of the database so Oracle can find all of the associated data files with the database.
5.  Each time that a data file or an online redo log file is added, renamed or dropped from the database, the control file is updated to reflect this physical structure change so,
a) Oracle can identify the Data Files and Online Redo Log files to open during database startup.
b)  Oracle can identify files that are required or available in case database recovery is necessary.
6. Control Files record information about checkpoints. Every three seconds the CKPT process records information in the Control Files about checkpoints in the Online Redo Log. This information is used during database recovery in the event of instance failure if ARCHIVELOG is enabled.

The location of the control file stored in the Initialization Parameters File or Server Initialization Parameter File with the CONTRAL_FILES entry. Whenever starting Oracle the Initialization Parameter File or Server Initialization Parameter File  parameter CONTRAL_FILES is read  to determine the location of the database files. After locating the database files they are mounted and opened.

If  Oracle can not find the Control File or the Control File become corrupt Oracle will not start. It is always a good idea to make a backup copy of the Control File any time the structure of the database has changed.
Always more then one copy of the Control File should be maintained and specified in the Initialization Parameters File.
Each Control File structure is Exactly the same and is used for redundancy in the event of a Control File is loss.
The content of the Control File can be exported and placed in the Trace Files for examination by the database administrator and Control File Recreation.

The command for this is to be fired by 'SYS' user
Sql> Alter Database Backup ControlFile to Trace;
The Trace directory is identified in the Initialization Parameters File by the USER_DUMP_DEST parameter. There may be multiple trace files and  you will hane to take a guess at which one is for the users session.

Control file can also be backed up to a OS file by using the syntax: Alter database backup controlfile to 'C:\Control.ctl', in SQL*Plus.

30) What is dedicated server & Multithreded server?
30) By default, whenever a user attempts to make a connection to the database, the connection is made using dedicated server processes. In other words, for each user connection there is a shadow process created that performs all the work on behalf of the user process such as loading the desired data from the database files into the database block buffers and returning the results of the query back to the user process. Such dedicated connections are very fast and would be desirable but they happen to have a problem and that is you can't have too many of those without quickly making the overhead prohibitive. In addition, the dedicated servers take up the same amount of resources on the database whether they are active or not and this can be very inefficient if the user connections access the database infrequently.
Multi-threaded server is a technique that can be used to support a large number of concurrent users without requiring a large amount of resources. It allows the user processes to share a smaller number of server processes and thereby conserve resources. Further, the overall idle time for server processes is reduced. For example, if you have 1000 simultaneous connections but at any time a maximum of 50 user connections are active then you can allocate 50 shared servers so that they are active most of the time instead of have 50 active and 950 idle server processes.
Several processes are involved in a multithreaded configuration:
A Network listener process: This functionality, provided by Net8, connects user processes to dispatchers or dedicated servers as requested.
One or more dispatcher processes: Dispatchers are protocol-specific handlers associated with a particular instance.
One or more shared server processes: Shared servers satisfy the requests submitted by the dispatchers.
In addition to the above server processes, several enhancements are made to the SGA in order to support the MTS architecture:
Request queues: that contain the requests placed by the dispatchers.
A Response queue: that is used by the shared servers to place the response to the requests
Session information is migrated from the PGA into the SGA (this section of the SGA is known as the UGA, or user global area).
MTS CONNECTION MECHANISM
When you use multithreaded servers, the connection mechanism is as follows:
1. When the listener is started, it opens and establishes a communication pathway and starts listening on the addresses listed in the listener.ora file. This channel is used by users to communicate with Oracle.
2. When an MTS-configured Oracle instance is started, each dispatcher (specified in the init.ora file) receives its random listen address.
3 The dispatchers call the listener by using the address specified by MTS_LISTENER_ADDRESS and notify the listener about the address at which the dispatcher listens for connection requests.
4 The listener updates its list of known services by adding the MTS_SERVICE and the address of the dispatchers
5. The listener process now waits for incoming connection requests.
6. A user requests a connection to the database. This could be a connection request from any type of client including a Java application.
7. The user connection request is intercepted by the listener. If the user requests a dedicated server process then the listener creates a dedicated server process and connects the user process to it. The user communicates with Oracle through this dedicated connection.
On the other hand, if an MTS connection is needed then the following steps occur:
1. The listener gives the user process the address of a dispatcher process with the lightest load
2. The user process connects to the dispatcher
3. The dispatcher creates a virtual circuit that is used by the user process (throughout its lifetime) to communicate with the shared servers In an MTS connection, when the user process issues a request, the dispatcher places the request in the request queue (part of the SGA). The request queue is common to all dispatchers, but each dispatcher has its own response queue. The user request is eventually picked up by an available shared server. The shared server process does all the necessary processing and returns the results to the dispatcher's response queue in the SGA. The dispatcher returns the result back to the user process.

31) Whether checkpoint process starts on its own? If not, how can you start it?
31)  A Checkpoint is a database event which synchronizes the data blocks in memory with the Datafiles on disk.  A Checkpoint has two purposes: To establish data consistency, enable faster database recovery.

How is recovery faster?  Because all database changes up to the Checkpoint have been recorded in the Datafiles, making it unnecessary to apply redo log entries prior to the Checkpoint.
During a Checkpoint the following occurs:
1. The database writer (DBWR) writes all modified database blocks in the buffer cache back to Datafiles,
2. Log writer (LGWR) updates both the controlfile and the Datafiles to indicate when the last Checkpoint occurred (SCN).

If the optional background process CKPT, the Checkpoint process, is enabled, then CKPT performs the operations of LGWR above. The advantages of enabling CKPT are discussed below.

A Checkpoint occurs when Oracle performs a log switch from one group to another, when the number of operating system blocks specified by   LOG_CHECKPOINT_INTERVAL have been written to the redo log, when the time  specified by LOG_CHECKPOINT_TIMEOUT has expired, or when a Checkpointhas
been forced by the DBA.

CHECKPOINTS AND PERFORMANCE:Checkpoints present a tuning dilemma for the Database Administrator. Frequent checkpoints will enable faster recovery, but can cause performance degradation. How then should the DBA address this? Depending on the number of Datafiles in a database, a Checkpoint can be a highly resource intensive operation, since all datafile headers are frozen during the Checkpoint. There is a performance trade-off regarding frequency of checkpoints. More frequent checkpoints enable faster database recovery after a crash. This is why some customer sites which have a very low   tolerance for unscheduled system downtime will often choose this option. However, the performance degradation of frequent checkpoints may not justify   this philosophy in many cases. Let's assume the database is up and running 95% of the time, and unavailable 5% of the time from infrequent instance crashes or hardware failures requiring database recovery.  For most customer sites, it makes more sense to tune for the 95% case rather than the rare 5% downtime.
This bulletin assumes that performance is your number one priority and so recommendations are made accordingly. Therefore, your goal is to minimize the frequency of checkpoints through tuning. Tuning checkpoints involves four key initialization parameters
-  CHECKPOINT_PROCESS
-  LOG_CHECKPOINT_INTERVAL
-  LOG_CHECKPOINT_TIMEOUT
-  LOG_CHECKPOINTS_TO_ALERT

CHECKPOINT_PROCESS: The CHECKPOINT_PROCESS init.ora parameter determines whether or  not the optional CKPT background process will be started to perform LGWRs tasks during Checkpoint operations of updating the datafile headers.  LGWR is then free to perform its' primary function flushing the redo log buffer to the online redo logs.

The CKPT process can improve performance significantly and decrease the Amount of time users have to wait for a Checkpoint operation to complete.  The overhead associated with starting another background process is not significant when compared to the performance benefit to be gained by enabling CKPT, therefore, Oracle recommends always enabling the Checkpoint process (CKPT).

IMPORTANT NOTES on CHECKPOINT_PROCESS ORACLE7 and ORACLE8: The CKPT process is optional in lower versions of Oracle7, but is mandatory in Oracle8. In versions 7.0 - 7.3.2, the CKPT is an optional background process which Is enabled by setting CHECKPOINT_PROCESS=TRUE in init.ora.

In versions 7.3.3 and 7.3.4, the CKPT process will be started Automatically   regardless of the CHECKPOINT_PROCESS setting if either of the following onditions exist:
1. A large value for DB_FILES (50 or higher)
2. A large value for DB_BLOCK_BUFFERS (10,000 or higher)

In version 8.0.3 and higher, the CKPT process is always enabled. Attempting to set  CHECKPOINT_PROCESS in the init.ora will give the following error: LM-101 "unknown parameter name checkpoint_process"

LOG_CHECKPOINT_INTERVAL: The LOG_CHECKPOINT_INTERVAL init.ora parameter controls how often a Checkpoint operation will be performed based upon the number of operating system blocks that have been written to the redo log.  If this value is larger than the size of the redo log, then the  Checkpoint will only occur when Oracle performs a log switch from one group to another, which is preferred.

On most UNIX systems the operating system block size is 512 bytes. This mans that setting LOG_CHECKPOINT_INTERVAL to a value of 10,000 (the default setting), causes a Checkpoint to occur after 5,120,000 (5M) bytes are written to the redo log. If the size of your redo log is 20M, you are taking 4 checkpoints for each log.

LOG_CHECKPOINT_INTERVAL influences when a Checkpoint occurs, which means careful attention should be given to the setting of this parameter, keeping it updated as the size of the redo log files is  changed.  The Checkpoint  frequency is one of the factors which impacts the time required for the    database to recover from an unexpected failure.  Longer intervals between checkpoints mean that if the system crashes, more time will be needed for the database to recover.  Shorter Checkpoint intervals mean that the database will recover more quickly, at the expense of increased resource utilization during the Checkpoint operation.

This parameter also impacts the time required to complete a database recovery operation during the roll forward phase of recovery. The actual recovery time is dependent upon this time, and other factors, such as the type of failure (instance or system crash, media failure, etc.), and the number of archived redo logs which need to be applied.

LOG_CHECKPOINT_TIMEOUT: The LOG_CHECKPOINT_TIMEOUT init.ora parameter controls how often a Checkpoint will be performed based on the number of seconds that have passed since the last Checkpoint. Checkpoint frequency impacts the time required for the database to recover from an unexpected failure.  Longer intervals between checkpoints mean that more time will be required during database recovery.

Oracle recommends using LOG_CHECKPOINT_INTERVAL to control the Checkpoint Checkpoint interval rather than LOG_CHECKPOINT_TIMEOUT, which will initiate a Checkpoint every "n" seconds, regardless of the transaction frequency.  This can cause unnecessary checkpoints in cases where transaction volumes vary. Unnecessary checkpoints must be avoided whenever possible for optimal performance.

It is a misconception that setting LOG_CHECKPOINT_TIMEOUT to a given value will initiate a log switch at that interval, enabling a recovery window used for a stand-by database configuration.  Log switches cause a  Checkpoint, but a Checkpoint does not cause a log switch.  The only way to cause a log switch is manually with ALTER SYSTEM SWITCH LOGFILE or resizing the redo logs to cause more frequent switches. This is controlled by operating system blocks, not a timed interval.

Sizing of the online redo logs is critical for performance and recovery.

LOG_CHECKPOINTS_TO_ALERT: The LOG_CHECKPOINTS_TO_ALERT init.ora parameter, when set to a value of TRUE, allows you to log Checkpoint start and stop times in the alert log. This is very helpful in determining if checkpoints are occurring at the optimal frequency and gives a chronological view of checkpoints and other database activities occurring in the background.

31) What is explain Plan?
31) Oracle, Explain Plan is a great way to tune your queries. Explain Plan executes your query and records the "plan" that Oracle devises to execute your query. By examining this plan, you can find out if Oracle is picking the right indexes and joining your tables in the most efficient manner. By utilizing Explain Plan you can explore the differences that subtle changes in your query make in the way Oracle executes your query.

32)Have you used tuning? Explain plan, Sqltrace, tkprof?
32) Explain Plan: EXPLAIN PLAN is a SQL statement that lists the access path determined by the query optimizer. Each plan has a row with ID = 0, which gives the statement type.  EXPLAIN PLAN results should be interpreted with some discretion. Just because a plan does not seem efficient on the surface does not necessarily mean that the statement will run slowly. Choose statements for tuning based upon their actual resource consumption, not upon a subjective view of their execution plan.

The Sql Trace Facility and TKPROF: The SQL trace facility can be enabled for any session. It records in an operating system text file the resource consumption of every parse, execute, fetch, commit, or rollback request made to the server by the session. TKPROF summarizes the trace files produced by the SQL trace facility, optionally including the EXPLAIN PLAN output. Since the program reports each statement executed with the resources which it has consumed, the number of times it was called and the number of rows which it processed, it is quite easy to locate those statements which are using the greatest resource. It is also possible, with experience or with baselines available, to gauge whether the resources used are reasonable, given the work accomplished.

The primary tool for monitoring the performance of Oracle is the collection of dynamic performance views.

There are additional performance diagnostic tools. These tools and the dynamic performance tables can help you monitor and tune applications that run against the Oracle Server.
1. The SQL trace facility.
2. The EXPLAIN PLAN.

The SQL trace facility: The SQL trace facility provides performance information on individual SQL statements. The SQL trace facility generates the following statistics for each statement:
a) parse, execute, and fetch counts.
b) CPU and elapsed times.
c) physical reads and logical reads.
d) number of rows processed.
e) misses on the library cache.

You can enable the SQL trace facility for a session or for an instance. When the SQL trace facility is enabled, performance statistics for all SQL statements executed in a user session or in an instance are placed into a trace file.
You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file. As options, TKPROF can also
1. Determine the execution plans of SQL statements
2. Create a SQL script that stores the statistics in the database

Because running the SQL trace facility increases system overhead, you should enable it only when tuning your SQL statements, and disable it when you are finished.
Using the SQL Trace Facility
Follow these steps to use the SQL trace facility:
1. Set initialization parameters to prepare Oracle for using the SQL trace facility.
2. Enable the SQL trace facility for the desired session and run your application. This step produces a trace file containing statistics for the SQL statements issued by the application.
3. Run TKPROF to translate the trace file created in Step 2 into a readable output file. This step can also optionally create a SQL script that stores the statistics in the database.
4. Interpret the output file created in Step 3.
5. Optionally, run the SQL script produced in Step 3 to store the statistics in the database.
Each of these steps is discussed in the following sections.
1. Setting Initialization Parameters for the SQL Trace Facility
Before running your application with the SQL trace facility enabled, be sure these initialization parameter are set appropriately:

TIMED_STATISTICS This parameter enables and disables the collection of timed statistics, such as CPU and elapsed times, by the SQL trace facility, as well as the collection of certain statistics in the dynamic performance tables. The default value of FALSE disables timing. A value of TRUE enables timing. Enabling timing causes extra timing calls for low-level operations.

MAX_DUMP_FILE_SIZE This parameter specifies the maximum size of trace files in operating system blocks. The default is 500. If you find that your trace output is truncated, increase the value of this parameter before generating another trace file.

USER_DUMP_DEST This parameter specifies the destination for the trace file. The destination must be fully specified according to the conventions of your operating system. The default value for this parameter is the default destination for system dumps on your operating system.


2. Enabling the SQL Trace Facility: You can enable the SQL trace facility for either an individual session
or the instance.
Enabling Tracing for a SessionTo: enable the SQL trace facility for your session, issue this SQL statement:
ALTER SESSION SET SQL_TRACE = TRUE;
To disable the SQL trace facility for your session, issue this SQL statement:
ALTER SESSION SET SQL_TRACE = FALSE;
You can also enable the SQL trace facility for your session by using the DBMS_SESSION.SET_SQL_TRACE procedure.

You may need to modify your application to contain the ALTER SESSION command. For example, to issue the ALTER SESSION command in Oracle Forms, invoke Oracle Forms using the -s option, or invoke Oracle Forms (Design) using the statistics option. For more information on Oracle Forms, see the Oracle Forms Reference manual.
The SQL trace facility is also automatically disabled for your session when your application disconnects from Oracle.
Calling the DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure enables the SQL trace facility for a session other than your current session. This procedure allows you to gather statistics for a different user's session. This can be useful for database administrators who are not located near their users or who do not have access to the application code to set SQL trace from within an application. This procedure requires the session id and serial number of the user session in which you wish to enable SQL trace.
You can obtain the session id and serial number from the V$SESSION view. The following is an example of a Server Manager line mode session that obtains the session id and serial number for the user JFRAZZIN and then enables SQL trace for that user's session:
SVRMGR> SELECT sid, serial#, osuser
       2>   FROM v$session
       3>   WHERE osuser = 'jfrazzin';

SID        SERIAL#    OSUSER        
---------- ---------- ---------------
         8         12 jfrazzin      
1 row selected.

SVRMGR> EXECUTE dbms_system.set_sql_trace_in_session(8,12,TRUE);
Statement processed.
Enabling the SQL Trace Facility for an Instance
To enable the SQL trace facility for your instance, set the value of the initialization parameter SQL_TRACE to TRUE. This value causes statistics to be collected for all sessions.
Once the SQL trace facility has been enabled for the instance, it may be disabled for an individual session with this SQL statement:
ALTER SESSION SET SQL_TRACE = FALSE;
Generating Trace Files
When the SQL trace facility is enabled for a session, Oracle generates a trace file containing statistics for traced SQL statements for that session. When the SQL trace facility is enabled for an instance, Oracle creates a separate trace file for each process.
Because Oracle writes these trace files to the user dump destination, be sure you know how to distinguish them by name.
If your operating system retains multiple versions of files, be sure your version limit is high enough to accommodate the number of trace files you expect the SQL trace facility to generate.
The generated trace files may be owned by an operating system user other than yourself. This user must make the trace files available to you before you can use TKPROF to format them.
Once the SQL trace facility has generated a number of trace files, you can either: run TKPROF on each individual trace file, producing a number of formatted output files, one for each session append the trace files together and then run TKPROF on the result to produce a formatted output file for the entire instance.
Note: Trace files generated immediately after instance startup contain data that reflects the activity of the startup process. In particular, they reflect a disproportionate amount of I/O activity as caches in the System Global Area (SGA) are filled. For the purposes of tuning, ignore such trace files.

Example: This example runs TKPROF, accepts a trace file named DLSUN12_JOHN_FG_SVRMGR_007.TRC, and writes a formatted output file named OUTPUTA.PRF:
TKPROF DLSUN12_JOHN_FG_SVRMGR_007.TRC OUTPUTA.PRF
EXPLAIN=SCOTT/TIGER TABLE=SCOTT.TEMP_PLAN_TABLE_A INSERT=STOREA.SQL SYS=NO SORT=(EXECPU,FCHCPU) PRINT=10

This example is likely to be longer than a single line on your terminal screen and you may have to use continuation characters, depending on your operating system.

Note the other parameters in this example:
The EXPLAIN value causes TKPROF to connect as the user SCOTT and use the EXPLAIN PLAN command to generate the execution plan for each traced SQL statement.
The TABLE value causes TKPROF to use the table TEMP_PLAN_TABLE_A in the schema SCOTT as a temporary plan table.
The INSERT value causes TKPROF to generate a SQL script named STOREA.SQL that stores statistics for all traced SQL statements in the database.
The SYS parameter with the value of NO causes TKPROF to omit recursive SQL statements from the output file.
The SORT value causes TKPROF to sort the SQL statements in order of the sum of the CPU time spent executing and the CPU time spent fetching rows before writing them to the output file.
The PRINT value of 10 causes TKPROF to write statistics for the first 10 sorted SQL statements to the output file.

Interpretting TKPROF Output: The following listing shows TKPROF output for one SQL statement as it appears in the output file:

SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
 
call   count      cpu    elapsed     disk    query current    rows
---- -------  -------  --------- -------- -------- -------  ------
Parse      1     0.16      0.29         3       13       0       0
Execute    1     0.00      0.00         0        0       0       0
Fetch      1     0.03      0.26         2        2       4      14

Misses in library cache during parse: 1
Parsing user id: 8

Rows     Execution Plan
-------  ---------------------------------------------------
     14  MERGE JOIN
      4    SORT JOIN
      4      TABLE ACCESS (FULL) OF 'DEPT'
     14    SORT JOIN
     14      TABLE ACCESS (FULL) OF 'EMP'
For this statement, TKPROF output has these parts:
the text of the SQL statement
the SQL trace statistics in tabular form
the number of library cache misses for the parsing and execution of the statement
the user initially parsing the statement
the execution plan generated by EXPLAIN PLAN

SQL Trace Facility Statistics
TKPROF lists the statistics for a SQL statement returned by the SQL trace facility in rows and columns. Each row corresponds to one of three steps of SQL statement processing:
Parse
This step translates the SQL statement into an execution plan. This step includes checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.
Execute
This step is the actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this step modifies the data. For SELECT statements, the step identifies the selected rows.
Fetch
This step retrieves rows returned by a query. Fetches are only performed for SELECT statements.


The step for which each row contains statistics is identified by the value of the call column.
The other columns of the SQL trace facility output are combined statistics for all parses, all executes, and all fetches of a statement:
count
Number of times a statement was parsed, executed, or fetched.
cpu
Total CPU time in seconds for all parse, execute, or fetch calls for the statement.
elapsed
Total elapsed time in seconds for all parse, execute, or fetch calls for the statement.
disk
Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls.
query
Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Buffers are usually retrieved in consistent mode for queries.
current
Total number of buffers retrieved in current mode. Buffers are often retrieved in current mode for INSERT, UPDATE, and DELETE statements.

The sum of query and current is the total number of buffers accessed.
rows
Total number of rows processed by the SQL statement. This total does not include rows processed by subqueries of the SQL statement.

For SELECT statements, the number of rows returned appears for the fetch step.

For UPDATE, DELETE, and INSERT statements, the number of rows processed appears for the execute step.

33)  Oracle  Optimization?
33) Cost based Optimization: optimizes the query with respect to the statistics of the table on which the query is being performed. A user have to run ANALYZE command on the tables to store the statistics of a table in database tables.

Ruled based Optimization: Optimizes queries with the help of hint user passes to Optimizer.init<dbname>.ora contains parameter to use either of these optimization.



a) Parser : The parser performs two functions:
1. Syntax analysis: This checks SQL statements for correct syntax.
2. Semantic analysis: This checks, for example, that the current database objects and object attributes referenced are correct.

b) Optimizer: The optimizer is the heart of the SQL processing engine. The Oracle server provides two methods of optimization: rule-based optimizer (RBO) and cost-based optimizer (CBO).
c) Row Source Generator: The row source generator receives the optimal plan from the optimizer. It outputs the execution plan for the SQL statement. The execution plan is a collection of row sources structured in the form of a tree. A row source is an iterative control structure. It processes a set of rows, one row at a time, in an iterated manner. A row source produces a row set.
SQL Execution: SQL execution is the component that operates on the execution plan associated with a SQL statement. It then produces the results of the query.
EXPLAIN PLAN: You can examine the execution plan chosen by the optimizer for a SQL statement by using the EXPLAIN PLAN statement. This causes the optimizer to choose the execution plan, and then insert data describing the plan into a database table.
Simply issue the EXPLAIN PLAN statement and then query the output table. The following output table describes the statement examined in the previous section:
ID    OPERATION           OPTIONS        OBJECT_NAME
------------------------------------------------------------
0     SELECT STATEMENT
1       FILTER
2         NESTED LOOPS
3           TABLE ACCESS  FULL           EMP
4           TABLE ACCESS  BY ROWID       DEPT
5             INDEX       UNIQUE SCAN    PK_DEPTNO
6           TABLE ACCESS  FULL           SALGRADE
Each box in Figure and each row in the output table corresponds to a single step in the execution plan. For each row in the listing, the value in the ID column is the value shown in the corresponding box in Figure.
What Is The Optimizer?
The optimizer determines the most efficient way to execute a SQL statement. This is an important step in the processing of any data manipulation language (DML) statement: SELECT, INSERT, UPDATE, or DELETE. There are often many different ways to execute a SQL statement; for example, by varying the order in which tables or indexes are accessed. The procedure Oracle uses to execute a statement can greatly affect how quickly the statement executes.
The optimizer considers many factors among alternative access paths. It can use either a a cost-based or a rule-based approach.
Cost-Based Optimizer (CBO): In general, you should always use the cost-based approach. The rule-based approach is available for the benefit of existing applications.
The CBO determines which execution plan is most efficient by considering available access paths and by factoring in information based on statistics for the schema objects (tables or indexes) accessed by the SQL statement. The CBO also considers hints, which are optimization suggestions placed in a comment in the statement.
The CBO consists of the following steps:
1. The optimizer generates a set of potential plans for the SQL statement based on its available access paths and hints.
2. The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement.
The cost is an estimated value proportional to the expected resource use needed to execute the statement with a particular plan. The optimizer calculates the cost of each possible access method and join order based on the estimated computer resources, including (but not limited to) I/O and memory, that are required to execute the statement using the plan.
Serial plans with greater costs take more time to execute than those with smaller costs. When using a parallel plan, however, resource use is not directly related to elapsed time.
3. The optimizer compares the costs of the plans and chooses the one with the smallest cost.
To maintain the effectiveness of the CBO, you must gather statistics and keep them current. Gather statistics on your objects using either of the following:
- For releases prior to Oracle8i, use the ANALYZE statement.
- For Oracle8i releases, use the DBMS_STATS package.
For table columns which contain skewed data (i.e., values with large variations in number of duplicates), you must collect histograms.
The resulting statistics provide the CBO with information about data uniqueness and distribution. Using this information, the CBO is able to compute plan costs with a high degree of accuracy. This enables the CBO to choose the best execution plan based on the least cost.

Cost Based Optimizer (CBO) and Database Statistics: Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:
Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favored by Oracle and will be desupported in future releases.
Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.
If new objects are created, or the amount of data in the database changes the statistics will no longer represent the real state of the database so the CBO decision process may be seriously impaired. The mechanisms and issues relating to maintenance of internal statistics are explained below:
Analyze Statement
DBMS_Utility
DBMS_Stats
Scheduling Stats
Transfering Stats
Issues
Analyze Statement: The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can be computed exactly, or estimated based on a specific number of rows, or a percentage of rows:
ANALYZE TABLE employees COMPUTE STATISTICS;
ANALYZE INDEX employees_pk COMPUTE STATISTICS;

ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;
DBMS_Utility:
The DBMS_Utility package can be used to gather statistics for a whole schema or database. Both methods follow the same format as the analyze statement:
EXEC DBMS_Utility.Analyze_Schema('SCOTT','COMPUTE');
EXEC DBMS_Utility.Analyze_Schema('SCOTT','ESTIMATE', estimate_rows => 100);
EXEC DBMS_Utility.Analyze_Schema('SCOTT','ESTIMATE', estimate_percent => 15);

EXEC DBMS_Utility.Analyze_Database('COMPUTE');
EXEC DBMS_Utility.Analyze_Database('ESTIMATE', estimate_rows => 100);
EXEC DBMS_Utility.Analyze_Database('ESTIMATE', estimate_percent => 15);

DBMS_Stats:
The DBMS_Stats package was introduced in Oracle 8i and is Oracles preferred method of gathering object statistics. Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. Once again, it follows a similar format to the other methods:
EXEC DBMS_Stats.Gather_Database_Stats;
EXEC DBMS_Stats.Gather_Database_Stats(estimate_percent => 15);

EXEC DBMS_Stats.Gather_Schema_Stats('SCOTT');
EXEC DBMS_Stats.Gather_Schema_Stats('SCOTT', estimate_percent => 15);

EXEC DBMS_Stats.Gather_Table_Stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_Stats.Gather_Table_Stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);

EXEC DBMS_Stats.Gather_Index_Stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_Stats.Gather_Index_Stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);
This package also gives you the ability to delete statistics:
EXEC DBMS_Stats.Delete_Database_Stats;
EXEC DBMS_Stats.Delete_Schema_Stats('SCOTT');
EXEC DBMS_Stats.Delete_Table_Stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_Stats.Delete_Index_Stats('SCOTT', 'EMPLOYEES_PK');
Scheduling Stats: Scheduling the gathering of statistics using DBMS_Job is the easiest way to make sure they are always up to date:
SET SERVEROUTPUT ON
DECLARE
  v_job  NUMBER;
BEGIN
  DBMS_Job.Submit(v_job,
                  'BEGIN DBMS_Stats.Gather_Schema_Stats(''SCOTT''); END;',
                  Sysdate,
                  'Sysdate + 1');
  COMMIT;
  DBMS_Output.Put_Line('Job: ' || v_job);
END;
/
The above code sets up a job to gather statistics for SCOTT for the current time every day. You can list the current jobs on the server using the DBS_JOBS and DBA_JOBS_RUNNING views.

Existing jobs can be removed using:
EXEC DBMS_Job.Remove(X);
COMMIT;
Where 'X' is the number of the job to be removed.

Transfering Stats: It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:
  SQL> EXEC DBMS_Stats.Create_Stat_Table('DBASCHEMA','STATS_TABLE');
  SQL> EXEC DBMS_Stats.Export_Schema_Stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
This table can then be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:
  SQL> EXEC DBMS_Stats.Import_Schema_Stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
  SQL> EXEC DBMS_Stats.Drop_Stat_Table('DBASCHEMA','STATS_TABLE');
Issues: Exclude dataload tables from your regular stats gathering, unless you know they will be full at the time that stats are gathered.
I've found gathering stats for the SYS schema can make the system run slower, not faster. I therefore avoid the Gather_Database_Stats procedure.
Gathering statistics can be very resource intensive for the server so avoid peak workload times.
Even if scheduled, it may be necessary to gather fresh statistics after database maintenance or large data loads.

Rule-Based Optimizer (RBO): Although Oracle supports the rule-based optimizer, you should design new applications to use the cost-based optimizer. You should also use the CBO for data warehousing applications, because the CBO supports enhanced features for DSS. Many new performance features, such as partitioned tables, improved star query processing, and materialized views, are only available with the CBO.
If OPTIMIZER_MODE=CHOOSE, if statistics do not exist, and if you do not add hints to your SQL statements, then your statements use the RBO. You can use the RBO to access both relational data and object types. If OPTIMIZER_MODE=FIRST_ROWS or ALL_ROWS and no statistics exist, then the CBO uses default statistics. You should migrate your existing applications to use the cost-based approach.
You can enable the CBO on a trial basis simply by collecting statistics. You can then return to the RBO by deleting the statistics or by setting either the value of the OPTIMIZER_MODE initialization parameter or the OPTIMIZER_MODE clause of the ALTER SESSION statement to RULE. You can also use this value if you want to collect and examine statistics for your data without using the cost-based approach.

34) What is optimization hint?
34)  As an application designer, you may know information about your data that the optimizer does not know. For example, you may know that a certain index is more selective for certain queries. Based on this information, you may be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.
Hints allow you to make decisions usually made by the optimizer. You can use hints to specify the following:
1. The optimization approach for a SQL statement.
2. The goal of the cost-based optimizer for a SQL statement.
3. The access path for a table accessed by the statement.
4. The join order for a join statement.
5. A join operation in a join statement.

Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the following criteria: Join order,  Join method, Access method, Parallelization, Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used.

The hints described in this section allow you to choose between the cost-based and the rule-based optimization approaches. With the cost-based approach, this also includes the goal of best throughput or best response time.
ALL_ROWS
FIRST_ROWS
CHOOSE
RULE
If a SQL statement has a hint specifying an optimization approach and goal, then the optimizer uses the specified approach regardless of the presence or absence of statistics, the value of the OPTIMIZER_MODE initialization parameter, and the OPTIMIZER_MODE parameter of the ALTER SESSION statement.
ALL_ROWS: The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
The syntax of this hint is as follows:

For example, the optimizer uses the cost-based approach to optimize this statement for best throughput:
SELECT /*+ ALL_ROWS */ empno, ename, sal, job
FROM emp WHERE empno = 7566;
FIRST_ROWS: The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).
This hint causes the optimizer to make the following choices:
If an index scan is available, then the optimizer may choose it over a full table scan.
If an index scan is available, then the optimizer may choose a nested loops join over a sort-merge join whenever the associated table is the potential inner table of the nested loops.
If an index scan is made available by an ORDER BY clause, then the optimizer may choose it to avoid a sort operation.
The syntax of this hint is as follows:


For example, the optimizer uses the cost-based approach to optimize this statement for best response time:
SELECT /*+ FIRST_ROWS */ empno, ename, sal, job
FROM emp WHERE empno = 7566;

The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following syntax: Set operators (UNION, INTERSECT, MINUS, UNION ALL) , GROUP BY clause,  FOR UPDATE clause, Aggregate functions, DISTINCT operator.
These statements cannot be optimized for best response time, because Oracle must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any of these statements, then the optimizer uses the cost-based approach and optimizes for best throughput.

If you specify either the ALL_ROWS or the FIRST_ROWS hint in a SQL statement, and if the data dictionary does not have statistics about tables accessed by the statement, then the optimizer uses default statistical values (such as allocated storage for such tables) to estimate the missing statistics and, subsequently, to choose an execution plan.

These estimates may not be as accurate as those gathered by the DBMS_STATS package. Therefore, use the DBMS_STATS package to gather statistics. If you specify hints for access paths or join operations along with either the ALL_ROWS or FIRST_ROWS hint, then the optimizer gives precedence to the access paths and join operations specified by the hints.

CHOOSE: The CHOOSE hint causes the optimizer to choose between the rule-based and cost-based approaches for a SQL statement. The optimizer bases its selection on the presence of statistics for the tables accessed by the statement. If the data dictionary has statistics for at least one of these tables, then the optimizer uses the cost-based approach and optimizes with the goal of best throughput. If the data dictionary does not have statistics for these tables, then it uses the rule-based approach.
The syntax of this hint is as follows:

Example
SELECT /*+ CHOOSE */ empno, ename, sal, job
FROM emp WHERE empno = 7566;

RULE: The RULE hint explicitly chooses rule-based optimization for a statement block. It also makes the optimizer ignore other hints specified for the statement block. The syntax of this hint is as follows:

Example: The optimizer uses the rule-based approach for this statement:
SELECT --+ RULE
empno, ename, sal, job FROM emp WHERE empno = 7566;

The RULE hint, along with the rule-based approach, may not be supported in future releases of Oracle.

35) Which is the configuration file for an instance?
35)  In Oracle8i, the initialization parameter file is referred to as "INIT.ORA" unlike the way it was named in earlier releases init<SID>.ora.

The file is placed in within ORACLE_HOME in "ADMIN\SID\PFILE". For example, if  ORACLE_HOME is "F:\Oracle\Ora8i", the "INIT.ORA" file will be located in "F:\Oracle\Ora8i\admin\SID\pfile" where SID is the instance name related to the database.

36) RowId?
36) Each row in the database has an address. You can examine a row's address by querying the pseudocolumn ROWID. Values of this pseudocolumn are hexadecimal strings representing the address of each row. These strings have the datatype ROWID. You can also create tables and clusters that contain actual columns having the ROWID datatype. Oracle does not guarantee that the values of such columns are valid ROWIDs.
Restricted ROWIDs: The Oracle8 Server incorporates an extended format for ROWIDs to efficiently support partitioned tables and indexes and tablespace-relative data block addresses (DBAs) without ambiguity.  Character values representing ROWIDs in Oracle7 and earlier releases are as follows:
block.row.file where:
block 
is a hexadecimal string identifying the data block of the data file containing the row. The length of this string may vary depending on your operating system. 
row 
is a four-digit hexadecimal string identifying the row in the data block. The first row of the block has a digit of 0. 
file 
is a hexadecimal string identifying the database file containing the row. The first data file has the number 1. The length of this string may vary depending on your operating system. 


In Oracle8, this kind of ROWID is called a restricted ROWID.
Extended ROWIDs: The Oracle8 extended ROWID datatype stored in a user column includes the data in the Oracle7 ROWID, plus a data object number. The data object number is an identification number assigned to every database segment. You can retrieve the data object number from data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. Objects which share the same segment (clustered tables in the same cluster, for example) have the same object number.
A supplied package, DBMS_ROWID, is used for interpreting Oracle8 extended ROWID contents. The package functions extract and provide information that would be available directly from an Oracle7 ROWID.
Function Name 
Description 
ROWID_CREATE 
Create a ROWID, for testing only. 
ROWID_TYPE 
Returns the ROWID type: 0 is restricted, 1 is extended. 
ROWID_OBJECT 
Returns the object number of the extended ROWID. 
ROWID_RELATIVE_FNO 
Returns the file number of a ROWID. 
ROWID_BLOCK_NUMBER 
Returns the block number of a ROWID. 
ROWID_ROW_NUMBER 
Returns the row number. 
ROWID_TO_ABSOLUTE_ FNO 
Returns the absolute file number associated with the ROWID for a row in a specific table. 
ROWID_TO_EXTENDED 
Converts a ROWID from restricted format to extended. 
ROWID_TO_RESTRICTED 
Converts an extended ROWID to restricted format. 
ROWID_VERIFY 
Checks if a ROWID can be correctly extended by the ROWID_TO_EXTENDED function. 
37)  Data Block?
37) 
Block Size
Advantages
Disadvantages
Small (2KB-4KB) 
Reduces block contention.
Good for small rows, or lots of random access.
Has relatively large overhead.
You may end up storing only a small number of rows, depending on the size of the row.
Medium (8KB) 
If rows are medium size, then you can bring a number of rows into the buffer cache with a single I/O.
With 2KB or 4KB block size, you may only bring in a single row.
Space in the buffer cache is wasted if you are doing random access to small rows and have a large block size. For example, with an 8KB block size and 50 byte row size, you are wasting 7,950 bytes in the buffer cache when doing random access. 
Large (16KB-32KB) 
There is relatively less overhead; thus, there is more room to store useful data.
Good for sequential access or very large rows.
Large block size is not good for index blocks used in an OLTP type environment, because they increase block contention on the index leaf blocks.

Data Block Format: The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data.

1. Header (Common and Variable): The header contains general block information, such as the block address and the type of segment (for example, data, index, or rollback).
Table Directory: This portion of the data block contains information about the tables having rows in this block.
2. Row Directory: This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area). 
Once the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block.
3. Overhead: The data block header, table directory, and row directory are referred to collectively as overhead. Some block overhead is fixed in size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.
4. Row Data: This portion of the data block contains table or index data. Rows can span blocks;
5. Free Space: Free space is allocated for insertion of new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a non-null value). Whether issued insertions actually occur in a given data block is a function of current free space in that data block and the value of the space management parameter PCTFREE.
In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. The space required for transaction entries is operating system dependent; however, transaction entries in most operating systems require approximately 23 bytes.

38)  Transaction Entry Settings (INITRANS and MAXTRANS)
38) The INITRANS and MAXTRANS transaction entry settings for the data blocks allocated for a table, cluster, or index should be set individually for each object based on the following criteria:
The space you would like to reserve for transaction entries compared to the space you would reserve for database data
The number of concurrent transactions that are likely to touch the same data blocks at any given time
For example, if a table is very large and only a small number of users simultaneously access the table, the chances of multiple concurrent transactions requiring access to the same data block is low. Therefore, INITRANS can be set low, especially if space is at a premium in the database.
Alternatively, assume that a table is usually accessed by many users at the same time. In this case, you might consider preallocating transaction entry space by using a high INITRANS (to eliminate the overhead of having to allocate transaction entry space, as required when the object is in use) and allowing a higher MAXTRANS so that no user has to wait to access necessary data blocks.
INITRANS: Specifies the number of DML transaction entries for which space should be initially reserved in the data block header. Space is reserved in the headers of all data blocks in the associated data or index segment. The default value is 1 for tables and 2 for clusters and indexes.
MAXTRANS: As multiple transactions concurrently access the rows of the same data block, space is allocated for each DML transaction's entry in the block. Once the space reserved by INITRANS is depleted, space for additional transaction entries is allocated out of the free space in a block, if available. Once allocated, this space effectively becomes a permanent part of the block header. The MAXTRANS parameter limits the number of transaction entries that can concurrently use data in a data block. Therefore, you can limit the amount of free space that can be allocated for transaction entries in a data block using MAXTRANS.
The default value is an operating system-specific function of block size, not exceeding 255.

39) What is the meaning of the INTERNAL account of the database administrator and where is it used.
39) For the oracle 8 version the user internal is nearly the same as the user sys. The user sys owns all the dictionary tables and views. Normally you cannot start (or shutdown the database) as sys. Why? The
user sys is defined in the database (which is not running when you start it up). That is the reason you cannot start a database with the user sys. You have to login as internal. For the user internal there is a
password file located in the dbs directory. When it the database is down you can therefore connect as internal. However, you can make the user sys to start and shutdown the database and it makes use of the password file. Log in as internal.

SQL> grant sysdba to sys.
with the statement an entry is made in the password file for the user sys. shutdown the database. Startup the database with sqlplus /nolog

SQL> connect sys/<passord> as sysdba
the database should start However, in future releases of oracle internal will become obsolete. you would only have the user sys and system. Note that it is even possible to give sysdba to the user system. You have to alter the init.ora file if necessary. System in the owner of some additional tables used by Oracle Tools.

1. Sys is the owner of  system tables and views. You are not supposed to do anything whitin this schema.
2. System is the default admin for the database. You can use it to manage users, security and database organisation.
3.Internal is not a database user. It can be used to start, stop and maintain the database. You can also use sys or system to do that (connect as SYSOPER or SYSDBA) or any other user if you allow it.

40) Can you give a grant on particular column?
40) Yes,  The GRANT UPDATE (ColumnName) statement allows a user to pefrom updates on a specific column in another person's schema.
For Example: GRAND UPDATE(CompanyName1) On Companies to Markiting;

41) What is the user public?
41) PUBLIC keyword is used to grant permission to everyone.
For example: Grant Select on Companies to PUBIC;]

42) What are the predefined roles?
42) Oracle provides for easy and controlled privilege management through roles. Roles are named groups of related privileges that you grant to users or other roles. Roles are designed to ease the administration of end-user system and object privileges. However, roles are not meant to be used for application developers, because the privileges to access objects within stored programmatic constructs need to be granted directly.

The roles CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, and IMP_FULL_DATABASE are defined automatically for Oracle databases. These roles are provided for backward compatibility to earlier versions of Oracle and can be modified in the same manner as any other role in an Oracle database.

43) What is dirty memory?
43) Modified data blocks which are in the Batabase buffer but not written permanently to the databse  are know as dirty blocks (memory).

44) What is the difference between a snapshot and a view?
44)  View is a virtual table for the underlying table(s) and the Snapshots are mirror or replicas of tables.

45) Who can grant and revoke the role?
45)  Any user with the GRANT ANY ROLE system privilege can grant or revoke any role (except a global role) to or from other users or roles of the database. You should grant this system privilege conservatively because it is very powerful


46)  What are the new future in Oracle8i?
46) New PL/SQL Features in Oracle 8i: Part I
a) Support for Java: PL/SQL is Oracle's procedural extension to industry-standard SQL. Prior to Oracle 8i, PL/SQL was in a sort of static condition. It has not been changed much since Oracle 7. I believe one of the main factors that pushed the Oracle PL/SQL team to make significant enhancements was the introduction of Java in Oracle. With the release of Oracle 8i, the database server now supports two major programming languages, PL/SQL and Java. Oracle believes that PL/SQL is still the best solution for SQL developers and SQL-centered applications, while Java is a general-purpose language for more general applications.
Oracle 8i introduces multiple new features that extend the power, performance, and flexibility of PL/SQL. Knowing what is new and what has changed is extremely important for both developers and DBAs.
b) Native Dynamic SQL: Native Dynamic SQL is one of the most significant new PL/SQL features in Oracle 8i. Prior to Oracle 8i, DDL (Data Definition Language) and dynamic DML (Data Manipulation Language) statements could be only executed utilizing the DBMS_SQL package. Oracle 8i introduces the EXECUTE IMMEDIATE command, which provides a much simpler way of creating and executing DDL statements, dynamic SQL, and dynamic PL/SQL. Only one statement is needed, rather than multiple statements, which the DBMS_SQL package required.

Native Dynamic SQL allows developers to write much more compact code, which is easier to read and maintain. This code executes faster because it's embedded into the PL/SQL engine; it's not external to PL/SQL DBMS_SQL package calls. According to Oracle, Native Dynamic SQL provides 30 to 60 percent performance improvements over DBMS_SQL.

The EXECUTE IMMEDIATE command accepts any SQL statement except SELECT ones that retrieve multiple rows. It is important to note that it could accept bind variables with a USING clause, thereby improving code performance significantly by allowing you to reuse parsed SQL statements from an SGA (System Global Area) pool.

In my example, Customer data is divided by region for performance reasons and stored in four tables with an identical structure: Customer_West, Customer_East, Customer_North, and Customer_South. The Create_Customer stored procedure is used to insert records with customer data in one of the tables determined by the first parameter passed to the procedure.
Here is a simplified version of the Create_Customer stored procedure written before Oracle 8i with the DBMS_SQL package:
CREATE OR REPLACE PROCEDURE Create_Customer
(Table_Name VARCHAR2,
Customer_ID INTEGER,
Customer_Lastname VARCHAR2,
Customer_Firstname VARCHAR2,
Customer_Address VARCHAR2,
Customer_City VARCHAR2,
Customer_State VARCHAR2,
Customer_Zip VARCHAR2,
Customer_Phone VARCHAR2) IS

csr_handle INTEGER;
cSQL_Statement VARCHAR2(200);
cnt_rows BINARY_INTEGER;

BEGIN
   cSQL_Statement := 'INSERT INTO ' || LTRIM(RTRIM(Table_Name)) ||
      ' VALUES(:Id, :Last, :First, :Address, :City,
      :State, :Zip, :Phone)';

-- Step 1: open cursor.
csr_handle := DBMS_SQL.OPEN_CURSOR;

-- Step 2: parse cursor.
DBMS_SQL.PARSE(csr_handle, cSQL_Statement, DBMS_SQL.NATIVE);

-- Step 3: bind values to the variables.
DBMS_SQL.BIND_VARIABLE(csr_handle, ':Id',      Customer_ID);
DBMS_SQL.BIND_VARIABLE(csr_handle, ':Last',    Customer_Lastname);
DBMS_SQL.BIND_VARIABLE(csr_handle, ':First',   Customer_Firstname);
DBMS_SQL.BIND_VARIABLE(csr_handle, ':Address', Customer_Address);
DBMS_SQL.BIND_VARIABLE(csr_handle, ':City',    Customer_City);
DBMS_SQL.BIND_VARIABLE(csr_handle, ':State',   Customer_State);
DBMS_SQL.BIND_VARIABLE(csr_handle, ':Zip',     Customer_Zip);
DBMS_SQL.BIND_VARIABLE(csr_handle, ':Phone',   Customer_Phone);

-- Step 4: execute cursor.
cnt_rows := DBMS_SQL.EXECUTE(csr_handle);

-- Step 5: close cursor.
DBMS_SQL.CLOSE_CURSOR(csr_handle);
 
EXCEPTION
 
   WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20101,
         'Error in procedure Create_Customer.');

END Create_Customer;
Here is the same procedure rewritten in Oracle 8i with Native Dynamic SQL:
CREATE OR REPLACE PROCEDURE Create_Customer
(Table_Name VARCHAR2,
Customer_ID INTEGER,
Customer_Lastname VARCHAR2,
Customer_Firstname VARCHAR2,
Customer_Address VARCHAR2,
Customer_City VARCHAR2,
Customer_State VARCHAR2,
Customer_Zip VARCHAR2,
Customer_Phone VARCHAR2) IS

cSQL_Statement VARCHAR2(200);

BEGIN
   cSQL_Statement := 'INSERT INTO ' || LTRIM(RTRIM(Table_Name)) ||
      ' VALUES(:Id, :Last, :First, :Address, :City,
      :State, :Zip, :Phone)';

   EXECUTE IMMEDIATE cSQL_Statement
      USING Customer_ID, Customer_Lastname, Customer_Firstname,
      Customer_Address, Customer_City, Customer_State, Customer_Zip,
      Customer_Phone;

EXCEPTION
  
   WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20101,
         'Error in procedure Create_Customer.');

END Create_Customer;
As you can see, this syntax is much more concise and easy to read.

c) NOCOPY Parameter Option: Prior to Oracle 8i there were three types of parameter-passing options to procedures and functions:
IN: parameters are passed by reference
OUT: parameters are implemented as copy-out
IN OUT: parameters are implemented as copy-in/copy-out

The technique of OUT and IN OUT parameters was designed to protect original values of them in case exceptions were raised, so that changes could be rolled back. Because a copy of the parameter set was made, rollback could be done. However, this method imposed significant CPU and memory overhead when the parameters were large data collections-for example, PL/SQL Table or VARRAY types.

With the new NOCOPY option, OUT and IN OUT parameters are passed by reference, which avoids copy overhead. However, parameter set copy is not created and, in case of an exception rollback, cannot be performed and the original values of parameters cannot be restored.

According to Oracle, benchmarking showed 30 to 200 percent improvements for medium-to-large PL/SQL tables passed as parameters.
Here is an example of using the NOCOPY parameter option:
TYPE Note IS RECORD
(
   Title VARCHAR2(15),
   Created_By VARCHAR2(20),
   Created_When DATE,
   Memo VARCHAR2(2000)
);

TYPE Notebook IS VARRAY(2000) OF Note;

CREATE OR REPLACE PROCEDURE Update_Notes
(Customer_Notes IN OUT NOCOPY Notebook) IS

BEGIN
   ...
END;
d) Profiler: An Oracle 8i PL/SQL programmer develops a large number of packages, so the need to identify and solve performance problems becomes critical. Oracle 8i provides a profiler that analyzes PL/SQL code and locates bottlenecks. The DBMS_PROFILER package is an API that provides the capability to gather statistics related to the execution of the PL/SQL program and identify performance problems. The DBMS_PROFILER package is not created by default with the database; you have to generate it with Oracle's ProfLoad.sql script. This script has to be executed by the SYS user and access has to be granted to PUBLIC. This script calls two other scripts: Dbmspbp.sql and Prvtpbp.plb.
There are three steps in a typical profiler session:
Start the profiler to collect statistics for a session.
Execute PL/SQL program unit to generate profiling data.
Stop the profiler.
To use the DBMS_PROFILER package, database tables and other structures must be created through the Proftab.sql script. There are three tables to be created:
PLSQL_PROFILER_DATA
PLSQL_PROFILER_RUNS
PLSQL_PROFILER_UNITS
This script can be executed under the schema executing the DBMS_PROFILER package or under a centralized schema. In case of a centralized schema, you need to create a PUBLIC synonym on the objects and grant SELECT and INSERT privileges on the tables.
The statistics gathered by the profiler include the number of times each line was executed, the total execution time for each line, and minimum and maximum execution times of each line. This information is logged in the database tables and can be retrieved via a SQL query or any report facility. To show how to retrieve this information, Oracle provides two scripts (Profrep.sql and Profsum.sql) located in the PLSQL\Demo subdirectory under the Oracle_Home directory.
For further information related to the DBMS_PROFILER package, please refer to the Oracle8i Server On-Line Documentation or the Dbmspbp.sql script comments.
New PL/SQL Features in Oracle 8i: Part II

In my last article, "New PL/SQL Features in Oracle 8i: Part I", I described some of the new features in Oracle 8i that extend the power, performance, and flexibility of PL/SQL, Oracle's procedural extension to industry-standard SQL. This article covers some of the other new features: bulk binds, autonomous transactions, and new database triggers.

e) Bulk Binds:
Oracle 8i introduces new PL/SQL FORALL and BULK COLLECT statements to support bulk binds.
The FORALL statement is specifically used for processing DML (INSERT, DELETE, and UPDATE) statements to improve performance by reducing the overhead of SQL processing. The PL/SQL interpreter executes all procedural statements. However, all SQL statements in the PL/SQL block are sent to the SQL engine, which parses and executes them. The PL/SQL-to-SQL context switch adds some overhead, which could become significant when SQL statements are nested in loops. For instance, if it's repeated in a loop-say, 1,000,000 times-that could slow down code execution substantially.
Look at this code:
-- Assume PL/SQL tables Invoice_Id_Tab, Invoice_Date_Tab,
-- and Invoice_Amount_Tab have been populated
-- with invoice information (Invoice ID, Date and Amount)
-- that needs to be inserted to the Invoice table.

FOR nCount IN 1..10000
LOOP
   INSERT INTO Invoices (Invoice_Id, Invoice_Date, Invoice_Amount)
      VALUES (Invoice_Id_Tab(nCount),
              Invoice_Date_Tab(nCount),
              Invoice_Amount_Tab(nCount));
END LOOP;
Here 10,000 rows are created in the Invoice table and each INSERT is done individually, which results in 10,000 context switches between PL/SQL and SQL engines.
Oracle 8i eliminates all but one of these context switches by passing the entire PL/SQL table to the SQL engine in one step:
FORALL nCount IN 1..10000
   INSERT INTO Invoices (Invoice_Id, Invoice_Date, Invoice_Amount)
      VALUES (Invoice_Id_Tab(nCount),
              Invoice_Date_Tab(nCount),
              Invoice_Amount_Tab(nCount));
The FORALL statement has a structure similar to FOR LOOP with a range. However, it doesn't contain an END LOOP statement and it cannot contain any statements other than the index, lower and upper bound, and actual SQL statement (which refers to the index). The range specified by lower and upper bounds (in my example, it's 1 to 10,000) must be contiguous and all the elements within that range must exist, otherwise an ORA-22160 exception will be raised.
The FORALL clause is used for DML statements. The equivalent statement for a bulk fetch is the BULK COLLECT clause, which can be used as a part of SELECT INTO, FETCH INTO, or RETURNING INTO clauses:
SELECT Invoice_Id, Invoice_Date, Invoice_Amount
   BULK COLLECT INTO Invoice_Id_Tab, Invoice_Date_Tab, Invoice_Amount_Tab
   FROM Invoice;
The BULK COLLECT clause can be used for both explicit (FETCH INTO) and implicit (SELECT INTO) cursors. It fetches the data into the collection (PL/SQL table, varray) starting with element 1 and overwrites all consequent elements until it retrieves all the rows. If the collection is varray, it has to be declared large enough to accommodate all fetched rows, otherwise an ORA-22160 exception will be raised.
The bulk binds features allow users to increase the performance and reduce the overhead of SQL processing by operating on multiple rows in a single DML statement. The entire collection-not just one collection element at a time-is passed back and forth between the PL/SQL and SQL engines. According to Oracle, during internal benchmark tests there was a 30 percent performance improvement as a result of using these new features.

f) Autonomous Transactions:
Because Oracle is a transactional database, every INSERT, DELETE, and UPDATE operation takes place in the context of a transaction, which is either committed to the database or rolled back as a whole. Prior to Oracle 8i, there was no way to commit individual a SQL operation that was separate from the whole transaction. Oracle 8i introduces a new compiler directive through the

g) AUTONOMOUS_TRANSACTION: pragma (a compiler directive), enabling PL/SQL program units to maintain their own transaction states.
An autonomous transaction starts within the context of another transaction, known as a parent transaction, but it is independent of it. This feature allows developers to handle transactions with more ease and finer granularity. Nested transactions can be committed or rolled back without affecting the parent one.

Here is an example of a Create_Invoice procedure with AUTONOMOUS_TRANSACTION pragma:
CREATE OR REPLACE PROCEDURE Create_Invoice
   (Inv_Id INTEGER,
    Inv_Amount NUMBER) IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO Invoice
      (Invoice_Id, Invoice_Date, Invoice_Amount)
      VALUES
      (Inv_Id, SYSDATE, Inv_Amount);
   COMMIT;
END;
/
The next PL/SQL block is trying to create two new rows in the Invoice table: one directly via INSERT command, and one via the Create_Invoice stored procedure:
BEGIN
   INSERT INTO Invoice
      (Invoice_Id, Invoice_Date, Invoice_Amount)
      VALUES
      (1, SYSDATE, 25.50);
   Create_Invoice(2, 30.20);
   ROLLBACK;
END;
/
As a result, the invoice with ID = 2 for $30.20 was created; the invoice with ID = 1 for $25.50 was not. Without the AUTONOMOUS_TRANSACTION pragma, both rows would have been committed to the database because the COMMIT command in the Create_Invoice procedure would have applied to the whole transaction.

h) New Database Triggers: Prior to Oracle 8i, database triggers could be applied to tables only. Essentially, they were table triggers. Oracle 8i introduces eight new database triggers, which extend beyond previous limitation. Table 1 lists these triggers.
Table 1. New Oracle8i Triggers
Trigger Event
Executes Before/After
Trigger Description
STARTUP
AFTER
Executes when the database is started
SHUTDOWN
BEFORE
Executes when the database is shut down
SERVERERROR
AFTER
Executes when a server-side error occurs
LOGON
AFTER
Executes when a session connects to the database
LOGOFF
BEFORE
Executes when a session disconnects from the database
CREATE
AFTER
Executes when a database object is created; could be created to apply to the schema or to the entire database
ALTER
AFTER
Executes when a database object is altered; could be created to apply to the schema or to the entire database
DROP
AFTER
Executes when a database object is dropped; could be created to apply to the schema or to the entire database
All eight triggers are executed implicitly, which is the same as triggers in previous Oracle versions. Once a database trigger is created and enabled, it executes upon a trigger event and no explicit calls are necessary.
New Oracle 8i database triggers are especially useful for DBAs because they provide coverage for the entire database. Now DBAs can move code from SQL scripts executed upon database startup to the STARTUP trigger. An object audit trail can be created using the CREATE, ALTER, and DROP triggers. STARTUP and SHUTDOWN triggers can record all users connected to the database with their log-on and log-off time.
Oracle 8i database triggers provide a new, convenient way of performing monitoring activities.

47) Determining the Size of an Oracle Database?
47) The size of the database is the total size of the datafiles that make up the tablespaces of the database. These details are found in the dba_extents view.

Type the following lines at the SQL-PLUS prompt:
select sum(bytes/1048576) from   dba_extents;

This will give the total number of Mb used by your database.

48) Optimize Oracle's SQL Statement Cache?
48)  When Oracle receives a SQL statement to execute, it first looks to see if the execution plan already exists in its cache. If the statement does exist in the cache, the previous execution plan is retrieved and used. This reduces the cost of recreating the execution plan, thus saving execution time. The cache can be viewed via the V$SQL table. Looking at the V$SQL table's SQL_TEXT column shows what statements are currently in cache.
One thing to note about how the cache works is that the SQL statements it places in the cache are case-sensitive. Therefore, the following two statements perform the same query, but are seen as two different queries:

SELECT * FROM V$SQL
SELECT * FROM v$sql

They are considered different because the first query references the V$SQL table in all uppercase letters, whereas the second uses all lowercase letters. To prove this, run the following query (note that two entries are returned):

SELECT * FROM V$SQL WHERE SQL_TEXT =
'select * from V$SQL'
or
SQL_TEXT = 'select * from v$sql'

This can flood the cache with the same query, thus making the cache less useful. To prevent this, always ensure that applications that issue the same query use the same letter case in the syntax.

49) Using Temp Tables in Oracle8i?
49 Oracle 8i introduces the concept of temp tables. This feature long since enjoyed by SQL Server developers enables data to be visible on a Session or Transaction basis. To create an Oracle 8i temp table use the following syntax:

Create global temporary table tempTable
(
id number,
value number
);
This creates a table that is visible to all sessions, but only the data placed in the table is visible for the current working session. The temp table is resident until the session that created it is closed.
Additionally, temp tables can be created for use on a per-transaction basis. The following syntax creates a temp table that purges all data once a transaction is committed. Or to preserve data once a transaction has been committed, use the "on commit preserve rows" syntax.
Create global temporary table tempTable
(
id number,
value number
) on commit delete rows;

50)  Who is using the Oracle Database?
50)  There is a table called v$session, which is visible if you have DBA privileges. This table contains an entry for each session connected to the Oracle database. To see who is connected to the database, use the following command:
SELECT username
FROM v$session;
51) What is the difference between RBS and Redo Log?
51) RBS - Each database contains one or more rollback segments. A rollback segment records the old values of data that was changed by each transaction (whether or not committed). Rollback segments are used to provide read consistency, to roll back transactions, and to recover the database.

The Redo Log - The redo log, present for every Oracle database, records all changes made in an Oracle database. The redo log of a database consists of at least two redo log files that are separate from the datafiles (which actually store a database's data). As part of database recovery from an instance or media failure, Oracle applies the appropriate changes in the database's redo log to the datafiles, which updates database data to the instant that the failure occurred.

A database's redo log can consist of two parts: the online redo log and the archived redo log.

A) The Online Redo Log : Every Oracle database has an associated online redo log. The Oracle background process LGWR uses the online redo log to immediately record all changes made through the associated instance. The online redo log consists of two or more pre-allocated files that are reused in a circular fashion to record ongoing database changes.

B) The Archived (Offline) Redo Log : Optionally, you can configure an Oracle database to archive files of the online redo log once they fill. The online redo log files that are archived are uniquely identified and make up the archived redo log. By archiving filled online redo log files, older redo log information is preserved for operations such as media recovery, while the pre-allocated online redo log files continue to be reused to store the most current database changes. Datafiles that were restored from backup, or were not closed by a clean database shutdown, may not be completely up to date. These datafiles must be updated by applying the changes in the archived and/or online redo
logs. This process is called recovery.

52) Is it possible to link tables from one Oracle instance to a second Oracle instance? Is the data stored on the linked table or is it a replication with data stored on both instances?
52) It is possible to accomplish this via a database link and triggers. We did it once to keep critical tables in sync without the overhead of replication. The problem of what happens when one of the instances goes down is a problem with this type of transaction processing. We wrote an exception handler and a 'pinger' to handle loss of comm. It really gets complicated since now you have to put everything in transaction tables, etc. Another thing we had to do is put a 'last_update' column in every table so we knew when rows were in sync. To this day I still put this column into every table I design, it has saved my butt many times. You're much better off using Replication since most of the hard work is then done for you.

You can create a database link from one database to another (a link can be either PUBLIC or owned by a user). With this link you can access tables in the remote or target database (i.e., the database you are linking TO). The link actually logs into the remote database with a username and password that you specify when you create the link. This username/password has to exist in the remote database. You can control what the link can see by setting access privileges on this username/password account in the remote db that is being used by the link. To simplify things you can create synonyms in the source database (the db you are linking FROM) for the objects you wish to access in the remote database.

Here are some questions and answers regarding this procedure:
1. Do you have to create the tables in the "linking from " database that match the tables in the "linking to" database?
It depends on what you're trying to accomplish. The short answer is no - you don't have to. Some people will create a copy of the table as a sort of backup or to have in case there's a problem with the link.

2. Is this only a map with data remaining in the "linking to" database?
It's not even a map. Don't think about the tables as being linked -- just think about the databases being linked with access provided between the linked databases through the database link. All a db link does is give you access into a database from another database. A bit analogous to joining 2 tables -- you can query data in both tables but the 2 tables actually don't interact in any way.

3. If the "linking to" database is brought down, will the "linking from"
database also need to be brought down or will the mapped tables simply not be accessible?
The status of one database will not affect the status of the other database in any way except that the link no longer works.

4. Is there a means of including the mapped tables in backups on the "linking from" database?
No, not that I'm aware of. Remember the tables still 'belong' to their host database and host tablespaces -- they have nothing to do with the database in anyway that they are linked.

53) PL/SQL Table?
53) PL/SQL tables are useful in that they can be used to store large quantities of bulk data and can be passed to functions and procedures as parameters and returned from functions. They can be based on many types of variables such as:-

1) x number;
   type pltab is table of x%type; /* Based on a simple variable */

2) type pltab is table of testtab.col1%type /* based on a table column */

3) type pltab is table of testtab%rowtype  /* Based on a table row */

4) type plrec is record (x number,y date, z varchar2(10));
   type pltab is table of plrec            /* Based on a record */

We can return a PL/SQL table from a function

5) function pl_func(x IN number) return pltab as ...

To reference an element of a single type PL/SQL table use an index number
6) for i in 1..100
   loop
  pltab(i) := salary *1.1
   end loop;

For a table of records use

7) pltab(i).x := salary + bonus;


For functions that return PL/SQL tables

8) declare
       type pltab is table of testtab%rowtype index by binary_integer;
       function plfunc(max_num IN integer) return pltab as
tt pltab;
       begin
       ...
...
return tt;
       end;

    begin
if plfunc(90)(3).y = 'ABC' then ... end if;
    end;
Some ways to get data into PL/SQL tables are:-

9)  select emp_name into pltab(1) from emptab where emp_no = 999;

10) cursor c1 is select * from testtab;
    open c1;
    loop
        i := i + 1;
        fetch c1 into testtab(i);
    end loop;

11) for c1_rec in (select * from testtab)
    loop
        n := n + 1;
        pltab(n).x = c1_rec.x;
    end loop;

In embedded C, PL/SQL tables have only one column and give array-like access

12)     type pltab is tbale of number index by binary_integer;
        mytab pltab;
        ...
        '''
        pltab(8) = 9.99;
        ...
        ...

PL/SQL tables have the following useful attributes associated with them

EXISTS            - if pltab(i).exists then ... end if;
COUNT            - if pltab.count > 0 then ... end if;
FIRST/LAST   - if pltab.first = 1 then ... end if; (NULL if table is empty)
PRIOR/NEXT  - if pltab.prior(2) = 2 then ... end if;
DELETE          - pltab.delete(1,10) ( empty items 1 through 10 )
                          - pltab.delete(3)    ( empty item 3 only)
                          - pltab.delete     ( empty whole table )


54) How do you count the number of columns in a database table?
54) Select COUNT(COLUMN_NAME)
from dba_tab_columns
where table_name = 'NAME_OF_THE_TABLE_CAPITALS'
  AND OWNER='SCHEMA_NAME_CAPITALS'
or
Select COUNT(*) from dba_tab_columns
where table_name = YourTableNameHere;







No comments:

Post a Comment