SQL Processing Concepts
Before tuning the SQL in your applications, you should understand the Oracle Server SQL (Structured Query Language) processing scheme. This appendix introduces the basics of SQL processing and outlines the general phases through which each SQL statement goes. Topics include
SQL Statement Execution
DML Statement Processing
DDL Statement Processing
SQL Statement Execution



Figure B-1 outlines the stages commonly used to process and execute a SQL statement. In some cases, Oracle might execute these steps in a slightly different order. For example, the DEFINE stage could occur just before the FETCH stage, depending on how you wrote your code.
The following sections describe each phase of SQL statement processing for each type of SQL statement. As you read this information, remember that for many Oracle tools, several of the phases are performed automatically. Most users need not be concerned with or aware of this level of detail. However, you might find this information useful when writing Oracle applications.

Figure B-1: The Stages in Processing a SQL Statement
DML Statement Processing
This section describes a simplified look at what happens during the execution of a SQL statement. Queries (SELECTs) require additional steps as shown in Figure B-1; refer to the section "Query Processing" on page B-6 for more information.
Assume that you are using a Pro*C program to increase the salary for all employees in a department. Also assume that the program you are using has made a connection to Oracle and that you are connected to the proper schema to update the EMP table. You might embed the following SQL statement in your program: EXEC SQL UPDATE emp SET sal = 1.10 * sal
WHERE deptno = :dept_number;
DEPT_NUMBER is a program variable containing a value for department number. When the SQL statement is executed, the value of DEPT_NUMBER is used, as provided by the application program.
The next four sections explain what happens in each of the first four phases of DML statement processing. The same four phases are also necessary for each type of statement processing.
Stage 1: Create a Cursor
A program interface call creates a cursor. The cursor is created independently of any SQL statement; it is created in expectation of any SQL statement. In most applications, cursor creation is automatic. However, in precompiler programs, cursor creation can occur implicitly, or explicitly by declaring a cursor.
Stage 2: Parse the Statement
During parsing, the SQL statement is passed from the user process to Oracle and a parsed representation of the SQL statement is loaded into a shared SQL area. Many errors can be caught during this phase of statement processing. Parsing is the process of
translating a SQL statement, verifying it to be a valid statement
performing data dictionary lookups to check table and column definitions
acquiring parse locks on required objects so that their definitions do not change during the statement's parsing
checking privileges to access referenced schema objects
determining the optimal execution plan for the statement
loading it into a shared SQL area
for distributed statements, routing all or part of the statement to remote nodes that contain referenced data
A SQL statement is parsed only if a shared SQL area for an identical SQL statement does not exist in the shared pool. In this case, a new shared SQL area is allocated and the statement is parsed. For more information about shared SQL, refer to Chapter 10, "Managing SQL and Shared PL/SQL Areas".
The parse phase includes processing requirements that need to be done only once no matter how many times the statement is executed. Oracle translates each SQL statement only once, re-executing that parsed statement during subsequent references to the statement.
Although the parsing of a SQL statement validates that statement, parsing only identifies errors that can be found before statement execution. Thus, certain errors cannot be caught by parsing. For example, errors in data conversion or errors in data (such as an attempt to enter duplicate values in a primary key) and deadlocks are all errors or situations that can only be encountered and reported during the execution phase.
Query Processing
Queries are different from other types of SQL statements because they return data as results if they are successful. Whereas other statements return simply success or failure, a query can return one row or thousands of rows. The results of a query are always in tabular format, and the rows of the result are fetched (retrieved), either a row at a time or in groups.
Several issues relate only to query processing. Queries include not only explicit SELECT statements but also the implicit queries in other SQL statements. For example, each of the following statements requires a query as a part of its execution: INSERT INTO table SELECT ...
UPDATE table SET x = y WHERE ...
DELETE FROM table WHERE ...
CREATE table AS SELECT ...
In particular, queries
require read consistency
can use temporary segments for intermediate processing
can require the describe, define, and fetch phases of SQL statement processing
Stage 3: Describe Results
The describe phase is only necessary if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user.
In this case, the describe phase is used to determine the characteristics (datatypes, lengths, and names) of a query's result.
Stage 4: Defining Output
In the define phase for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. Oracle performs datatype conversion if necessary.
Stage 5: Bind Any Variables
At this point, Oracle knows the meaning of the SQL statement but still does not have enough information to execute the statement. Oracle needs values for any variables listed in the statement; in the example, Oracle needs a value for DEPT_NUMBER.
This process is called binding variables. A program must specify the location (memory address) where the value can be found. End users of applications might be unaware that they are specifying bind variables because the Oracle utility might simply prompt them for a new value.
Because you specify the location (binding by reference), you need not rebind the variable before re-execution. You can change its value and Oracle looks up the value on each execution, using the memory address.
Unless they are implied or defaulted, you must also specify a datatype and length for each value if Oracle needs to perform datatype conversion. For more information about specifying a datatype and length for a value, refer to the following publications:
the Programmer's Guide to the Oracle Precompilers when using an Oracle precompiler (see "Dynamic SQL Method 4")
the Programmer's Guide to the Oracle Call Interface
Stage 6: Execute the Statement
At this point, Oracle has all necessary information and resources, so the statement is executed. If the statement is a query or an INSERT statement, no rows need to be locked because no data is being changed. If the statement is an UPDATE or DELETE statement, however, all rows that the statement affects are locked from use by other users of the database, until the next COMMIT, ROLLBACK or SAVEPOINT for the transaction. This ensures data integrity.
For some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.
Stage 7: Parallelize the Statement
When using the parallel query option, Oracle can parallelize queries and certain DDL operations. Parallelization causes multiple query servers to perform the work of the query so that the query can complete faster. Index creation and creating a table with a subquery can also be parallelized. Refer to Chapter 18, "Parallel Query Tuning", for more information on parallel query processing.
Stage 8: Fetch Rows of a Query Result
In the fetch phase, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched.
DDL Statement Processing
The execution of DDL statements differs from the execution of DML statements and queries because the success of a DDL statement requires write access to the data dictionary. For these statements, the parse phase actually includes the parsing, data dictionary lookup, and execution.
Transaction management, session management, and system management SQL statements are processed using the parse and execute phases. To re-execute them, simply perform another execute.
In addition to determining which types of actions form a transaction, when you design an application you must also determine when it is useful to use the BEGIN_DISCRETE_TRANSACTION procedure to improve the performance of short, non-distributed transactions.

The administrator needs to do additional configuration specifically on MS Windows is:

A. Set the OS username as a value for "Log on as a batch job" privilege.
For Windows 2000, go to Control Panel -> Administrative Tools -> Local Security Policy -> Local
Policies -> User Right Assignment and set the the OS username as a value for
"Log on as a batch job" privilege.

B. Check the check box "Allow service to interact with desktop" for BI Forms 10g Process Manager.
For Windows 200, go to Control Panel -> Administrative Tools -> Services ->
OraclexxxxxxProcessManager (the process manager service for BI Forms Home) -> Double
click it -> Click "Log on" tab page and check "Allow service to interact with desktop" check box


oracle 10.2.0 g on window vista. i can not set the host credential for EM. I Tried with following steps :
1. Control Panel, Administrative Tools.
2. Click on Local Security Policy.
3. Local Policies, User Rights Assignment.
4. Double click, 'Log on as a batch job'
here add username
i use 2 users : samir and oracle both users are member of ora_dba group.
First time it accepts the username and password but thenafter windows display an error message
"OEM executable stopped working and was closed. A problem caused the application to stop working correctly. Window will notify you if solution is available"

Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.
By declaring a tablespace as DICTIONARY managed, you are specifying that extent management for segments in this tablespace will be managed using the following dictionary tables: sys.fet$, sys.uet$
A Locally Managed Tablespace is a tablespace that manages its own extents by maintaining a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks

Oracle's password file

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password can not be stored in the database, because Oracle can not access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA: using the password file or through the operating system.
The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.
Default location and file name
The default location for the password file is: $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.
Deleting a password file
If password file authentication is no longer needed, the password file can be deleted and the init parameter remote_login_passwordfile set to none.
Password file state
If a password file is shared or exclusive is also stored in the password file. After its creation, the state is shared. The state can be changed by setting remote_login_passwordfile and starting the database. That is, the database overwrites the state in the password file when it is started up.
A password file whose state is shared can only contain SYS.
Creating a password file
Password files are created with the orapwd tool.
Adding Users to the password file
Users are added to the password file when they're granted the SYSDBA or sysoper privilege.
SYS@ora10> show user;
USER is "SYS"
SYS@ora10> select * from
v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

SYS@ora10>
grant SYSDBA to rene;

Grant succeeded.

SYS@ora10> select * from
v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
RENE TRUE FALSE

SYS@ora10> grant SYSOPER to rene;

Grant succeeded.

SYS@ora10> select * from
v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
RENE TRUE TRUE

SYS@ora10>
revoke SYSDBA from rene;

Revoke succeeded.

SYS@ora10> select * from
v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
RENE FALSE TRUE

SYS@ora10> revoke SYSOPER from rene;

Revoke succeeded.

SYS@ora10> select * from
v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

OS authentication
It's possible to have the operating system check if a user may log on a database or not. The idea is, if the user already knew the password to log on to the operating system, he doesn't have to know a password on the database. Such a user is created like so:
create user ops$ identified externally
Note, the string ops$ must be identical to the value of os_authent_prefix in the initialization parameter.

Setting REMOTE_LOGIN_ PASSWORDFILE
In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are:
· NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.
· EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.
· SHARED: A SHARED password file can be used by multiple databases running on the same server. However, the file cannot be modified. This means that you cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.
Nonsecure Remote Connections
To connect to Oracle Database as a privileged user over a nonsecure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database usernames that have been granted the SYSDBA or SYSOPER system privilege. This form of authentication is discussed in "Using Password File Authentication".
Local Connections and Secure Remote Connections
You can connect to Oracle Database as a privileged user over a local connection or a secure remote connection in two ways:
· If the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege, then you can connect and be authenticated by a password file.
· If the server is not using a password file, or if you have not been granted SYSDBA or SYSOPER privileges and are therefore not in the password file, you can use operating system authentication. On most operating systems, authentication for database administrators involves placing the operating system username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted SYSDBA privileges. A similar group, OSOPER, is used to grant SYSOPER privileges to users.
Using Operating System Authentication
This section describes how to authenticate an administrator using the operating system.
OSDBA and OSOPER
Two special operating system groups control database administrator connections when using operating system authentication. These groups are generically referred to as OSDBA and OSOPER. The groups are created and assigned specific names as part of the database installation process. The specific names vary depending upon your operating system and are listed in the following table:
Operating System Group
UNIX User Group
Windows User Group
OSDBA
dba
ORA_DBA
OSOPER
oper
ORA_OPER

The default names assumed by the Oracle Universal Installer can be overridden. How you create the OSDBA and OSOPER groups is operating system specific.
Membership in the OSDBA or OSOPER group affects your connection to the database in the following ways:
· If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.
· If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER system privilege.
· If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER, the CONNECT command fails.
See Also:
Your operating system specific Oracle documentation for information about creating the OSDBA and OSOPER groups
Preparing to Use Operating System Authentication
To enable operating system authentication of an administrative user:
1. Create an operating system account for the user.
2. Add the account to the OSDBA or OSOPER operating system defined groups.
Connecting Using Operating System Authentication
A user can be authenticated, enabled as an administrative user, and connected to a local database by typing one of the following SQL*Plus commands:CONNECT / AS SYSDBACONNECT / AS SYSOPER
For a remote database connection over a secure connection, the user must also specify the net service name of the remote database:CONNECT /@net_service_name AS SYSDBACONNECT /@net_service_name AS SYSOPER

Creation Of Temporary Tables
The data in a temporary table is private for the session that created it and can be session-specific or transaction-specific. If the data is to deleted at the end of the transaction the table should be defined as follows:
CREATE GLOBAL TEMPORARY TABLE my_temp_table ( column1 NUMBER, column2 NUMBER) ON COMMIT DELETE ROWS;

If on the other hand that data should be preserved until the session ends it should be defined as follows:
CREATE GLOBAL TEMPORARY TABLE my_temp_table ( column1 NUMBER, column2 NUMBER) ON COMMIT PRESERVE ROWS;

v$archive_dest
Shows all archived redo log destinations. Use this view to find out to which place archived redo logs are copied: select dest_id,destination from v$archive_dest
These values correspond to the init parameter log_archive_dest_n.
v$archive_dest_status
This view allows to find status and errors for each of the defined
v$archived_log
Displays successfully archived redo logs.
shows received logs on a primary standby database.
v$archive_gap
Lists sequence numbers of the archived los that are known to be missing for each thread on a (physical?) standby database (highest gap only).
v$archive_processes
This view provides information on the archive processes. It can be used to find out if an ARCH process is active or not.
v$controlfile
Displays the location and status of each controlfile in the database.
v$controlfile_record_section
See sections in a controlfile.
v$bh
This dynamic view has an entry for each block in the database buffer cache.
The column status can be:
· freeThis block is not in use
· xcurBlock held exclusively by this instance
· scurBlock held in cache, shared with other instance
· crBlock for consistent read
· readBlock being read from disk
· mrecBlock in media recovery mode
· irecBlock in instance (crash) recovery mode
v$buffer_pool
See buffer pools.
This view's column id can be joined with x$kcbwds.indx
See also x$kcbwbpd
v$buffer_pool_statistics
v$database
This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode:
ADPDB>select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG
checkpoint_change# records the SCN of the last checkpoint.
switchover_status: can be used to determine if it is possible to perform a switchover operation Only available for physical standby databases. Can be:
· NOT ALLOWED,
· SESSIONS ACTIVE,
· SWITCHOVER PENDING,
· SWITCHOVER LATENT,
· TO PRIMARY,
· TO STANDBY or
· RECOVERY NEEDED.
See protection modes in data guard for the columns protection_mode and protection_level.
database_role determines if a database is a primary or a logical standby database or a physical standby database.
force_logging tells if a database is in force logging mode or not.
v$datafile
This view contains an entry for each datafile of the database.
This view can be used to find out which datafiles must be backed up in a cold backup: select name from v$datafile
v$datafile_header
Various information about datafile headers. For example, if you're interested in when the a file's last checkpoint was:
select name, checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header
v$dataguard_status
Shows error messages in a data guard environment.
v$db_object_cache
This view displays objects that are cached (pinned) in the library cache. See also dbms_shared_pool.
v$enqueue_stat
If there are a lot of enqueue waits "in" v$session_event or v$system_event, v$enqueue_stat allows to break down those enqueues in enqueue classes. For each such class, the gets, waits, failures and the cumulative sum of waited time can be found. For a list of enqueue types, refer to enqueue types in x$ksqst.
The column cum_wait_time stems from x$ksqst.ksqstwtim.
v$eventmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$event_name
Contains a record for each wait event.
v$filemetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$filestat
v$fixed_table
This view contains the name of all V$, X$ and GV$ tables. In oracle 8.1.7, there are 187 different v$ tables:
ORA81> select count(*) from v where name like 'V$%';

COUNT(*)
----------
185
If you want to know, which x$ tables there are, do a select name from v$fixed_table where name like 'X$%';
v$fixed_view_definition
Contains the defintion in its attribute view_definition for the views of v$fixed_table.
v$flash_recovery_area_usage
See also v$recovery_file_dest
v$instance
instance_role can be used to determine if an instance is an active instance (=primary instance) or a secondary instance (in a standby environment.
dbms_utility.db_version can be used to retrieve the same version as the field version in v$instance.
v$instance_recovery
Can, for example, be used to determine the optimal size of redo logs.
v$latch
Oracle collects statistics for the activity of all latches and stores these in this view. Gets is the number of successful willing to wait requests for a latch. Similarly, misses is how many times a process didn't successfully request a latch. Spin_gets: number of times a latch is obtained after spinning at least once. Sleeps indicates how many times a willing to wait process slept. Waiters_woken tells how often a sleeping process was 'disturbed'.
v$librarycache
v$lock
This view stores all information relating to locks in the database. The interesting columns in this view are sid (identifying the session holding or aquiring the lock), type, and the lmode/request pair.
Important possible values of type are TM (DML or Table Lock), TX (Transaction), MR (Media Recovery), ST (Disk Space Transaction).
Exactly one of the lmode, request pair is either 0 or 1 while the other indicates the lock mode. If lmode is not 0 or 1, then the session has aquired the lock, while it waits to aquire the lock if request is other than 0 or 1. The possible values for lmode and request are:
· 1: null,
· 2: Row Share (SS),
· 3: Row Exclusive (SX),
· 4: Share (S),
· 5: Share Row Exclusive (SSX) and
· 6: Exclusive(X)
If the lock type is TM, the column id1 is the object's id and the name of the object can then be queried like so: select name from sys.obj$ where obj# = id1
A lock type of JI indicates that a materialized view is being refreshed.
A more detailed example can be found here
See also x$kgllk.
v$locked_object
Who is locking what:
select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id
v$log
Contains information on each log group. See also online redo log.
Comman values for the status column are:
· UNUSED: Oracle8 has never written to this group,
· CURRENT: This is the active group.
· ACTIVE: Oracle has written to this log before, it is needed for instance recovery.The active log is the one with the current log sequence number
· INACTIVE:Oracle has written to this log before; it is not needed for instance recovery.
v$logfile
This view can be queried to find the filenames, group numbers and states of redo log files. For example, to find all files of group 2, use select member from v$logfile where group# = 2
v$logmnr_contents
See dbms_logmnr.
v$log_history
This view contains an entry for each Log Switch that occured. The column first_time indicates the time of the first entry???
On physical standby databases, this view shows applied logs.
v$logstdby
Can be used to verify that archived redo logs are being applied to standby databases.
v$managed_standby
Monitors the progress of a standby database in managed recovery mode, more exactly, it displays information about the activities of log transport service and log apply service.
see here
select process, pid, status, client_process, group# "Stdby Redo Log Gr", block# from v$managed_standby;
client_process: the corresponding primary database process. If lgwr log transmission is chosen, one row should have client_process=LGWR. If ARCH transmission is chosen, one row should have ARCH.
v$mystat
This view records statistical data about the session that accesses it. Join statistic# with v$statname. v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
See also recording statistics with oracle.
v$nls_parameters
The NLS parameters that are in effect for the session quering this view. The view NLS_SESSION_PARAMETERS is based on v$nls_parameters. See also v$nls_valid_values.
v$nls_valid_values
This view can be used to obtain valid values for NLS parameters such as
· supported character sets
· languages
· territories
· sorting orders
v$object_usage
v$object_usage gathers information about used (accessed) indexes when an index is monitored using alter index ... monitoring usage.
See On verifying if an index is used.
v$open_cursor
v$option
This view lets you see which options are installed in the server.
See also dba_registry.
v$parameter
Lists the name-value pairs of the init.ora file (or their default, if not in the init.ora). For example, if you need to know what your block size is:
select value from v$parameter where name = 'db_block_size'
The columns isses_modifiable and issys_modifiable can be used to determine if a parameter can be changed at session level using alter session or at system level using alter system. A parameter is modifiable at session level if isses_modifiable = 'TRUE'. A parameter is modifiable at system level if issys_modifiable = 'DEFERRED' or issys_modifiable = 'IMMEDIATE'. However, if a parameter is changed at system level if issys_modifiable = 'DEFERRED' it only affects sessions that are started after chaning the parameter. Additionally, the alter system set ... deferred option must be used.
There are also some undocumented (or hidden?) parameters.
v$pgastat
See also pga.
Thanks to Oleg who notified me of a typo (v$pgastat instead of v$pga_stat).
v$process
Join v$process's addr with v$session paddr.
The column traceid is equal to the value used in alter session set .
v$pwfile_users
Lists all users who have been granted sysdba or sysoper privileges. See adding user to a password file.
v$recover_file
Useful to find out which datafiles need recovery.
Join with v$datafile to see filenames instead of numbers....
v$recovery_file_dest
See also v$flash_recovery_area_usage
v$reserved_words
This view can be consulted if one is in doubt wheter a particular word is a reserved word (for example when writing PL/SQL Code or assigning a password to a user).
Until 10g, the view only consist of two columns: keyword and length. From 10gR2 onwards, it has also the columns reserved, res_type, res_attr, res_semi and duplicate. Each of these new columns can only be either 'Y' (meaning: yes) or 'N' (meaning: no)
See also reserved words in SQL and reserved words in PL/SQL.
v$resource_limit
v$rollname
The names of online rollback segments. This view's usn field can be joined with v$rollstat's usn field and with v$transaction's xidusn field.
v$transaction can be used to track undo by session.
v$rollstat
Statistics for rollback segements
v$session
The column audsid can be joined with sys_context('userenv','SESSIONID') to find out which session is the "own one". Alternatively, dbms_support.mysid can be used.
The fields module and action of v$session can be set with dbms_application_info.set_module. (See v$session_longops for an example.
The field client_info can be set with dbms_application_info.set_client_info
Join sid with v$sesstat if you want to get some statistical information for a particular sesssion.
A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session).
A client can set some information in client_info. For example, RMAN related sessions can be found with
.... where client_info like 'rman%';
What a session is waiting for can be queried with v$session_wait. However, with Oracle 10g, this is not nessessary anymore, as v$session_wait's information will be exposed within v$session as well.
See also sessions.
v$sessmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$session_event
This views is similar to v$system_event. However, it breaks it down to currently connected sessions.
v$session_event has also the column max_wait that shows the maximum time waited for a wait event.
v$session_longops
Use v$session_longops if you have a long running pl/sql procedure and want to give feedback on how far the procedure proceeded.
If the following Procedure is run, it will report its progress in v$session_longops. The Procedure will also set the module attribute in v$session which makes it possible to find the sid and serial# of the session.
create table f(g number);

create or replace procedure long_proc as
rindex pls_integer := dbms_application_info.set_session_longops_nohint;
slno pls_integer;
-- Name of task
op_name varchar2(64) := 'long_proc';

target pls_integer := 0; -- ie. The object being worked on
context pls_integer; -- Any info
sofar number; -- how far proceeded
totalwork number := 1000000; -- finished when sofar=totalwork

-- desc of target
target_desc varchar2(32) := 'A long running procedure';

units varchar2(32) := 'inserts'; -- unit of sofar and totalwork
begin

dbms_application_info.set_module('long_proc',null);

dbms_application_info.set_session_longops (
rindex,
slno);

for sofar in 0..totalwork loop

insert into f values (sofar);

if mod(sofar,1000) = 0 then
dbms_application_info.set_session_longops (
rindex,
slno,
op_name,
target,
context,
sofar,
totalwork,
target_desc,
units);

end if;

end loop;
end long_proc;
If the procedure long_proc is run, you can issue the following query to get feedback on its progress:
select time_remaining,sofar,elapsed_seconds
from v$session_longops l, v$session s
where l.sid=s.sid and l.serial# = s.serial# and s.module='long_proc'
v$session_wait
This views shows what wait event each session is waiting for, or what the last event was that it waited for. In contrast, v$session_event lists the cumulative history of events waited for in a session.
The columns P1, P2 and P3 are parameters that are dependant on the event. With Oracle 10g, v$session_wait's information will be exposed within v$session as well.
Since 10g, Oracle displays the v$session_wait information also in the v$session view.
v$session_wait_history
This view is new in Oracle 10g and allows improved timing and statistics.
v$sesstat
This view is similar to v$mystat except that it shows cumulated statistics for all sessions.
Join sid with v$session and join statistic# with v$statname.
v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
v$sga
Shows how much memory the shared global area uses. Selecting * from v$sga is roughly the same as typing show sga in sql plus with the exeption that the latter also show the total.
v$sgastat
Showing free space in the sga:
select * from v$sgastat where name = 'free memory'
v$sga_dynamic_components
Information about SGA resize operations since startup.
This view can also be used to find out the granule size of SGA components.
v$sga_resize_ops
v$sort_usage
See temporary tablespaces
v$sort_segment
See Temporary Tablespaces
v$spparameter
Returns the values for the spfile.
v$sql
v$sql is similar to v$sqlarea, the main difference being that v$sql drills down to select * from x$kglob whereas v$sqlarea drills down to select sum from x$kglob. See also here.
v$sqlarea
Join v$sqlarea's address with v$session's sql_address.
Find the SQL-text of currently running SQL statements:
select sql_text from v$sqlarea where users_executing > 0;
The field version_count indicates how many versions an sql statement has.
v$sqltext
v$sql_plan
variable addr varchar2(20)
variable hash number
variable child number

exec :addr := '&sqladdr'; :hash := &hashvalue; :child := &childno;

select lpad(' ', 2*(level-1))operation' '
decode(id, 0, 'Cost = 'position) "OPERATION",
options, object_name
from v$sql_plan
start with (address = :addr
and hash_value = :hash
and child_number = :child
and id=0 )
connect by prior id = parent_id
and prior address = address
and prior hash_value = hash_value
and prior child_number = child_number
order by id, position ;
In order to find valid values for sqladdr, hashvalue and childno, this SQL statement can be used:
select sql_text,address,hash_value,child_number from v$sql where users_executing > 0;
v$sql_text_with_newlines
This view can be used to construct the entire text for each session's actual SQL statement. Use the following statement to to that:
set serveroutput on size 1000000

declare
v_stmt varchar2(16000);
v_sql_text v$sqltext_with_newlines.sql_text%type;
v_sid v$session.sid%type;
begin
for r in (
select
sql_text,s.sid
from
v$sqltext_with_newlines t,
v$session s
where
s.sql_address=t.address
order by s.sid, piece) loop

v_sid := nvl(v_sid,r.sid);

if v_sid <> r.sid then
dbms_output.put_line(v_sid);
put_line(v_stmt,100);
v_sid := r.sid;
v_stmt := r.sql_text;
else
v_stmt := v_stmt r.sql_text;
end if;

end loop;
dbms_output.put_line(v_sid);
dbms_output.put_line(v_stmt,100);

end;
/
Thanks to Sarmad Zafar who notified me of an error in this PL/SQL Block.
Note: the function put_line is found here and can be used to prevent ORU-10028.
v$sql_bind_data
Join cursor_num with cno of v$sql_cursor.
v$sql_bind_capture
New with Oracle 10g
This view captures bind variables for all sessions and is faster than setting 10046 on level 4.
v$sql_cursor
Join parent_handle with address of v$sql or v$sqlarea.
v$sql_workarea
v$sql_workarea can be joined with v$sqlarea on address and hash_value, and it can be joined with v$sql on address, hash_value and child_number.
v$standby_log
v$statname
Use this view to get decoded names for the statistic# field of v$mystat, v$sysstat and v$sesstat.
v$sysaux_occupants
v$sysaux_occupants doesn't exist in Oracle versions prior to Oracle 10g.
See occupants in the sysaux tablepsaces.
v$sysmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$sysmetric_history
This view is new in Oracle 10g and allows improved timing and statistics.
v$sysstat
v$sysstat is similar to v$sesstat. While v$sesstat displays statitics for the current session, v$sysstat displays the cumulated statitics since startup of the database.
For example, it is possible to find out the CPU time (name = 'CPU used by this session')
This view is (among others) used to calculate the Hit Ratio.
v$system_event
This view displays the count (total_waits) of all wait events since startup of the instance.
If timed_statistics is set to true, the sum of the wait times for all events are also displayed in the column time_waited.
The unit of time_waited is one hundreth of a second. Since 10g, an additional column (time_waited_micro) measures wait times in millionth of a second.
total_waits where event='buffer busy waits' is equal the sum of count in v$waitstat.
v$enqueue_stat can be used to break down waits on the enqueue wait event.
While this view totals all events in an instance, v$session_event breaks it down to all currently connected sessions.
v$undostat
undo tablespaces
v$tempfile
v$tempseg_usage
v$tempseg_usage is a public synonym for v$sort_usage.
v$tempstat
v$thread
The Oracle SID can be retrieved through select instance from v$thread
v$timer
This view has only one column (hsecs) which counts hundreths of seconds. Whenever it overflows four bytes, it starts again with 0.
v$transaction
Important fields of v$transaction are used_ublk and used_urec. They tell of how many blocks and records the undo for a transaction consists. In order to find out the name of the corresponding rollback segemnt, join the xidusn field with the usn field of v$rollname. This is demonstrated in
Transactions generate undo
v$timezone_names
See also timezones for some values of tzabbrev.
v$transportable_platform
Which platforms are supported for cross platform transportable tablespaces.
v$version
Use this view to find out what version you actually work on: select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
v$waitstat
total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.
Thanks
Thanks to Elizabeth Seager who made me aware of an error on this page.
Thanks also to Mark Ramsay who corrected and improved several things on this page.
Thanks also to Shaik Abdul Raheem who found a link that didn't work.
Thanks also to Simon Kissane whoe helped improve this page.

Bitmap indexes are most suitable for columns having very few unique values (very low cardinality). This is best for multiple combination of low to medium cardinality columns.
B-tree indexes are appropriate for medium to high cardinality columns having many distinct values.
Bitmap indexes are more advantageous than btree when the table has million of rows and the key column has low cardinality. bitmap indexes provide better performance than b tree indexes when queries often use a combination of multiple where conditions involving the OR operator, the table is read-only, or when there is low update activity on the key columns.

True statement regarding space management is:
In a locally managed tablespace free space are managed by bitmap
Coalescing of free space is not required in a locally managed tablespace
n.b.: Coalescing free space is not necessary for locally managed tablespaces, because bitmaps automatically track adjacent free space.
But free space in a dictionary managed tablespace can become fragmented, making it difficult to allocate new extents. For this way coalescing of free space is needed in a dictionary managed tablespace.
SQL > ALTER TABLESPACE tabsp_4 COALESCE;
Coalescing operation reduces one of the index partitions, which are partitioned using hash method. This method redistributes the index entries of an indexed partition.
SQL > ALTER INDEX parts1_hgidx COALESCE PARTITION PARALLEL

gfhfhghg

ttt