Explore topic-wise InterviewSolutions in .

ASM(AUTOMATIC storage MANAGEMENT), raw disk devices, network FILE system(NFS), OCFS2 and OCFS(Oracle Cluster Fie systems).

ASM(automatic storage management), raw disk devices, network file system(NFS), OCFS2 and OCFS(Oracle Cluster Fie systems).

All INSTANCES of an ORACLE RAC can access all the datafiles, control files, SPFILE's, redolog files when these files are HOSTED out of cluster-aware shared STORAGE which are group of shared disks.

All instances of an Oracle RAC can access all the datafiles, control files, SPFILE's, redolog files when these files are hosted out of cluster-aware shared storage which are group of shared disks.

All datafiles, controlfiles, SPFIles, REDO LOG files MUST reside on cluster-aware SHRED STORAGE.

All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.

This process is called as REMOTE Slave Monitor. This process MANAGES background slave process creation ANDD communication on remote instances. This is a background slave process. This process PERFORMS tasks on behalf of a co-ordinating process RUNNING in another instance.

This process is called as Remote Slave Monitor. This process manages background slave process creation andd communication on remote instances. This is a background slave process. This process performs tasks on behalf of a co-ordinating process running in another instance.

This PROCESS is called as Oracle RAC MANAGEMENT process. These pocesses perform managability TASKS for Oracle RAC. Tasks include CREATION of resources related Oracle RAC when new instances are added to the cluster.

This process is called as Oracle RAC management process. These pocesses perform managability tasks for Oracle RAC. Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.

This process is CALLED as Instance enqueue process. This process manages non-CACHE FUSION RESOURCE requests such as libry and row cache requests.

This process is called as Instance enqueue process. This process manages non-cache fusion resource requests such as libry and row cache requests.

This process is called as Global Cache service process. This process maintains statuses of datafiles and each cahed BLOCK by recording INFORMATION in a Global Resource Directory(GRD). This process also controls the flow of messages to remote instances and manages global data block access and TRANSMITS block images between the buffer caches of different instances.This processing is a part of cache fusion feature.

This process is called as Global Cache service process. This process maintains statuses of datafiles and each cahed block by recording information in a Global Resource Directory(GRD). This process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances.This processing is a part of cache fusion feature.

This process is called as global enqueue SERVICE daemon. This process manages incoming REMOTE resource REQUESTS within each INSTANCE.

This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.

This PROCESS monitors GLOBAL enqueues and resources across the CLUSTER and performs global enqueue recovery OPERATIONS. This is called as Global Enqueue SERVICE Monitor.

This process monitors global enqueues and resources across the cluster and performs global enqueue recovery operations. This is called as Global Enqueue Service Monitor.

The process provides transparent support for XA global transactions in a RAC environment. The database auto tunes the NUMBER of these PROCESSES based on the WORKLOAD of XA global transactions.

The process provides transparent support for XA global transactions in a RAC environment. The database auto tunes the number of these processes based on the workload of XA global transactions.

ACMS stands for ATOMIC Controlfile Memory Service. In an Oracle RAC environment ACMS is an agent that ENSURES a DISTRIBUTED SGA memory update. SGA updates are globally committed on success or globally aborted in event of a failure.

ACMS stands for Atomic Controlfile Memory Service. In an Oracle RAC environment ACMS is an agent that ensures a distributed SGA memory update. SGA updates are globally committed on success or globally aborted in event of a failure.

ORACLE RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and ANOTHER instance is in need of the same block, it is easy to get the block image from the insatnce which has the block in its SGA rather than reading from the DISK. To enable INTER instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process MANAGES the cache fusion.

Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block, it is easy to get the block image from the insatnce which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cache fusion.

GRD stands for Global Resource DIRECTORY. The GES and GCS maintains records of the statuses of each datafile and each cahed BLOCK using global resource directory. This PROCESS is referred to as cache fusion and HELPS in DATA integrity.

GRD stands for Global Resource Directory. The GES and GCS maintains records of the statuses of each datafile and each cahed block using global resource directory. This process is referred to as cache fusion and helps in data integrity.

Oracle RAC is composed of two or more DATABASE instances. They are composed of Memory structures and background processes same as the single instance database.Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global CACHE Service) that enable cache fusion.Oracle RAC instances are composed of following background processes:

• ACMS—Atomic Controlfile to Memory Service (ACMS).
• GTX0-j—Global Transaction Process.
• LMON—Global Enqueue Service Monitor.
• LMD—Global Enqueue Service Daemon.
• LMS—Global Cache Service Process.
• LCK0—Instance Enqueue Process.
• RMSn—Oracle RAC MANAGEMENT Processes (RMSn).
• RSMN—Remote Slave Monitor.

Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database.Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache Service) that enable cache fusion.Oracle RAC instances are composed of following background processes:

RAC STANDS for Real Application cluster. It is a clustering solution from ORACLE Corporation that ENSURES HIGH availability of DATABASES by providing instance failover, media failover features.

RAC stands for Real Application cluster. It is a clustering solution from Oracle Corporation that ensures high availability of databases by providing instance failover, media failover features.

INDEXES prevent a full table scan, so it is INHERENTLY a faster means to TRAVERSE a table's ROW.

Indexes prevent a full table scan, so it is inherently a faster means to traverse a table's row.

We need to INDEX tables only when the QUERIES will be selecting a small portion of the table. If our query is retriving rows that are greater than 10 or 15 percent of the total rows in the table, we MAY not need an index.

We need to index tables only when the queries will be selecting a small portion of the table. If our query is retriving rows that are greater than 10 or 15 percent of the total rows in the table, we may not need an index.

The EFFICIENCY of an index comes from the fact that it lets us FIND necessary rows without having to scan all the rows of a table. They need a fewer disk I/O's than if we had to scan the table and hence are EFFICIENT.

The efficiency of an index comes from the fact that it lets us find necessary rows without having to scan all the rows of a table. They need a fewer disk I/O's than if we had to scan the table and hence are efficient.

The job of an optimizer is to FIND the optimal/best PLAN to execute our DML STATEMENTS such as SELECT, INSERT, UPDATE and DELETE. Oracle USES CBO to help determine efficient methods to execute queries.

The job of an optimizer is to find the optimal/best plan to execute our DML statements such as SELECT, INSERT, UPDATE and DELETE. Oracle uses CBO to help determine efficient methods to execute queries.

Of the THREE steps INVOLVED in the SQL statement PROCESSING, the optimization process is the CRUCIAL one because it determines the all important question of how fast our data will be retrived.

Of the three steps involved in the SQL statement processing, the optimization process is the crucial one because it determines the all important question of how fast our data will be retrived.

During the final STAGE of a query processing, the optimized query(the physical query plan that has been selected) is executed. If it's a SELECT statement the rows are RETURNED to the user. If it's an INSERT,UPDATE or DELETE statement ,the rows are MODIFIED. The SQL execution engine takes the execution plan provided by the OPTIMIZATION phase and executes it.

During the final stage of a query processing, the optimized query(the physical query plan that has been selected) is executed. If it's a SELECT statement the rows are returned to the user. If it's an INSERT,UPDATE or DELETE statement ,the rows are modified. The SQL execution engine takes the execution plan provided by the optimization phase and executes it.

1. PERFORM selection operation early so that we can eliminate a MAJORITY of the CANDIDATE rows early in the operation. If we leave most rows in until the end, we're going to do needless comparisons with the rows we're going to get rid of later.
2. Perform projection operations early so that we limit the number of columns we have to deal with.
3. If we need to perform consecutive join operation,perform the operations that produce the smaller join first.
4. COMPUTE common expressions once and save the RESULTS.

In general an optimal strategy is to PERFORM unary operations FIRST so the more complex and timeconsuming binary operations use smaller operands. Performing as many of the POSSIBLE unary operations first reduces the ROW sources of the join operations.

In general an optimal strategy is to perform unary operations first so the more complex and timeconsuming binary operations use smaller operands. Performing as many of the possible unary operations first reduces the row sources of the join operations.

A join OPERATION is CALLED a BINARY operation, an operation like selection is called a UNARY operation.

A join operation is called a binary operation, an operation like selection is called a unary operation.

The database USES a less systematic QUERY optimiation technique known as the HEURISTIC STRATEGY.

The database uses a less systematic query optimiation technique known as the heuristic strategy.

The OPTIMIZER passes the low-cost physical QUERY PLAN to the Oracle's query execution ENGINE.

The optimizer passes the low-cost physical query plan to the Oracle's query execution engine.

The cost of executing a PLAN is directly proportional to the amount of RESOURCES such as I/O, MEMORY and CPU NECESSARY to execute the proposed plan.

The cost of executing a plan is directly proportional to the amount of resources such as I/O, memory and CPU necessary to execute the proposed plan.

The optimizer generates several physical query plans that are potential execution plans. The optimizer then chooses among them by estimating the cost of each POSSIBLE physical plan based on the table and index STATISTICS available to it, and selecting the plan with the lowest ESTIMATED cost. This evaluation of the possible physical query plans is called cost-based query OPTIMIZATION.

The optimizer generates several physical query plans that are potential execution plans. The optimizer then chooses among them by estimating the cost of each possible physical plan based on the table and index statistics available to it, and selecting the plan with the lowest estimated cost. This evaluation of the possible physical query plans is called cost-based query optimization.

The OPTIMIZER GENERATES several valid physical query PLANS.All the physical query plans are POTENTIAL EXECUTION plans.

The optimizer generates several valid physical query plans.All the physical query plans are potential execution plans.

Following factors are considered by a physical QUERY or an execution plan:

1. The VARIOUS operations(eg:joins) to be PERFORMED during the query.
2. The ORDER in which the operations are performed.
3. The algorithm to be used for performing each operation.
4. The best WAY to retrieve data from disk or memory.
5. The best way to pass data from one operation to another during the query.

Following factors are considered by a physical query or an execution plan:

During this phase, Oracle transforms the logical query plan into a physical query plan. The OPTIMIZER MAY be faced with a choice of SEVERAL ALGORITHMS to solve a query. It needs to choose the most efficient algorithm to answer a query, and it needs to determine the most efficient way to implement the operations. The optimizer determines the order in which it will perform the steps.

During this phase, Oracle transforms the logical query plan into a physical query plan. The optimizer may be faced with a choice of several algorithms to solve a query. It needs to choose the most efficient algorithm to answer a query, and it needs to determine the most efficient way to implement the operations. The optimizer determines the order in which it will perform the steps.

In this phase ,the parse TREE is converted into an ABSTRACT logical query plan. This is an INITIAL pass at an actual query plan, and it contains only a general algebraic reformulation of the initial query. The VARIOUS nodes and branches of the parse tree are replaced by OPERATORS of relational algebra.

In this phase ,the parse tree is converted into an abstract logical query plan. This is an initial pass at an actual query plan, and it contains only a general algebraic reformulation of the initial query. The various nodes and branches of the parse tree are replaced by operators of relational algebra.

An optimizer PHASE can be divided into TWO DISTINCT PARTS: the QUERY rewrite phase and the physical execution plan generation phase.

An optimizer phase can be divided into two distinct parts: the query rewrite phase and the physical execution plan generation phase.

Using the STATISTICS we PROVIDE and the HINTS specified in the SQL queries, the CBO produces an optimal EXECUTION plan for the SQL statement.

Using the statistics we provide and the hints specified in the SQL queries, the CBO produces an optimal execution plan for the SQL statement.

During the optimization phase, Oracle uses its optimizer(CBO(cost-based optimizer)) to CHOOSE the BEST ACCESS method for retriving data for the TABLES and indexes referred to in the query.

During the optimization phase, Oracle uses its optimizer(CBO(cost-based optimizer)) to choose the best access method for retriving data for the tables and indexes referred to in the query.

The parse tree GENERATION takes PLACE in the library CACHE portion of the SGA(system GLOBAL Area).

The parse tree generation takes place in the library cache portion of the SGA(system global Area).

1) The SQL statement is decomposed into relational algebra query that 's analyzed to see whether it's syntactically correct.
2) The query then undergoes semantic checking.
3) The data dictionary is consulted to ensure that the tables and the individual columns that are REFERENCED in the query do exist,as well as all the object privileges.
4) The column types are checked to ensure that the data MATCHES the column definition.
5) The statement is normalized so that it can be processed more efficiently
6) The query is rejected if it is incorrectly formulated
7) Once the parse tree passes all the syntactic and semantic checks,it is CONSIDERED a valid parse tree,and it's sent to the logical query plan generation stage.

1) The SQL statement is decomposed into relational algebra query that 's analyzed to see whether it's syntactically correct.
2) The query then undergoes semantic checking.
3) The data dictionary is consulted to ensure that the tables and the individual columns that are referenced in the query do exist,as well as all the object privileges.
4) The column types are checked to ensure that the data matches the column definition.
5) The statement is normalized so that it can be processed more efficiently
6) The query is rejected if it is incorrectly formulated
7) Once the parse tree passes all the syntactic and semantic checks,it is considered a valid parse tree,and it's sent to the logical query plan generation stage.

Parsing primarily consists of checking the SYNTAX and semantics of the SQL statements. The end PRODUCT of the parse stage of query compilation is the creation of a parse TREE, which represents the query structure. The parse tree is then sent to the logical query PLAN GENERATION stage.

Parsing primarily consists of checking the syntax and semantics of the SQL statements. The end product of the parse stage of query compilation is the creation of a parse tree, which represents the query structure. The parse tree is then sent to the logical query plan generation stage.

An user's SQL statement goes through the PARSING, optimizing, and execution STAGES. If the SQL statement is a QUERY(SELECT), data has to be retrived so there's an additional fetch STAGE before the SQL PROCESSING is complete.

An user's SQL statement goes through the parsing, optimizing, and execution stages. If the SQL statement is a query(SELECT), data has to be retrived so there's an additional fetch stage before the SQL processing is complete.

Cost-based OPTIMIZATION, HEURISTIC STRATEGY are USED for QUERY optimization.

Cost-based optimization, heuristic strategy are used for query optimization.

Query optimization is the process of choosing the most efficient execution PLAN. The goal is to achieve the result with least cost in terms of resource usage. Resources INCLUDE I/O and CPU usage on the server where the DATABASE is running. This is a MEANS to reduce the execution times of the query, which is the SUM of the execution times of the all component operations of the query.

Query optimization is the process of choosing the most efficient execution plan. The goal is to achieve the result with least cost in terms of resource usage. Resources include I/O and CPU usage on the server where the database is running. This is a means to reduce the execution times of the query, which is the sum of the execution times of the all component operations of the query.

When a user starts a data retrieval operation, the user's SQL statement GOES through several SEQUENTIAL steps that together constitute query PROCESSING. Query processing is the transformation of the SQL statement into efficient execution plan to RETURN the requested data from the database.

When a user starts a data retrieval operation, the user's SQL statement goes through several sequential steps that together constitute query processing. Query processing is the transformation of the SQL statement into efficient execution plan to return the requested data from the database.

SQL isn't a procedural LANGUAGE in which we have to SPECIFY the steps to be followed to achieve the STATEMENT goal. We don't have to specify how to accomplish a task(say DATA retrival) using SQL, rather we can specify as to what needs to be done.

SQL isn't a procedural language in which we have to specify the steps to be followed to achieve the statement goal. We don't have to specify how to accomplish a task(say data retrival) using SQL, rather we can specify as to what needs to be done.

We can improve the application performance WITHOUT changing BASE SQL code by optimizing the SQL performance. Oracle has come up with SQL ADVISOR tool that helps SQL performance. We can make use of SQL Advisor TOOLS' SQL Profiles to improve performance, though we can't touch the underlying SQL.

We can improve the application performance without changing base SQL code by optimizing the SQL performance. Oracle has come up with SQL Advisor tool that helps SQL performance. We can make use of SQL Advisor tools' SQL Profiles to improve performance, though we can't touch the underlying SQL.

The PERFORMANCE tuning STEPS to improve the performance of a database DEPENDS on the stage at which we get the INPUT and on the nature of the application. DBA's can assist the developers to write optimal code that is scalable based on systematic APPROACH. Mostly the real life problems that are encountered after production moves have to be solved by reactive performance tuning.

The performance tuning steps to improve the performance of a database depends on the stage at which we get the input and on the nature of the application. DBA's can assist the developers to write optimal code that is scalable based on systematic approach. Mostly the real life problems that are encountered after production moves have to be solved by reactive performance tuning.

Performance tuning is an iterative PROCESS. We as a DBA MAY have to tune applications which is designed and IMPLEMENTED in production. The performance tuning at HTIS stage is referred to as reactive performance tuning.

Performance tuning is an iterative process. We as a DBA may have to tune applications which is designed and implemented in production. The performance tuning at htis stage is referred to as reactive performance tuning.

Production moves may cause PROBLEMS DUE to scalability. We can't simulate the original load in test and DEVELOPMENT. So problems may crop up at times as the APPLICATION may be performing poor due to scalability problems.

Production moves may cause problems due to scalability. We can't simulate the original load in test and development. So problems may crop up at times as the application may be performing poor due to scalability problems.

A poor database design results in poor application PERFORMANCE. We have to tune the application code and some database RESOURCES such as MEMORY, CPU, I/O OWING to performance degradation.

A poor database design results in poor application performance. We have to tune the application code and some database resources such as memory, CPU, I/O owing to performance degradation.

Oracle suggests a specific DESIGN APPROACH with the following STEPS. This is a top down approach:

1. Design the application correctly.
2. Tune the application SQL code.
3. Tune memory.
4. Tune I/O.
5. Tune contention and other issues.

Oracle suggests a specific design approach with the following steps. This is a top down approach:

It is mandatory to design the database properly at initial stages to avoid potential problems. It is mandatory to know the nature of application that a database is going to SUPPORT. With a clear IDEA on the application's nature database can be optimally created by allocating appropriate resources to avoid problems when the application is moved to production. Most production moves cause problem because of the scalability problems with the applications. So, oracle RECOMMENDS to tune database at inception stage. this is systematic approach to performance TUNING.

It is mandatory to design the database properly at initial stages to avoid potential problems. It is mandatory to know the nature of application that a database is going to support. With a clear idea on the application's nature database can be optimally created by allocating appropriate resources to avoid problems when the application is moved to production. Most production moves cause problem because of the scalability problems with the applications. So, oracle recommends to tune database at inception stage. this is systematic approach to performance tuning.