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