A.Oracle includes many performance tuning enhancements like:
1.Automatic Performance Diagnostic and Tuning Features
2.Automatic Shared Memory Management - Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA
3.Wait Model Improvements - A number of views have been updated and added to improve the wait model.
4.Automatic Optimizer Statistics Collection - gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB
5.Dynamic Sampling - enables the server to improve performance
6.CPU Costing - default cost model for the optimizer (CPU+I/O), with the cost unit as time Optimizer Hints
7.Rule Based Optimizer Obsolescence - No more used
8.Tracing Enhancements - End to End Application Tracing which allows a client process to be identified via the client identifier rather than the typical session id
9.SAMPLE Clause Enhancements Hash Partitioned Global Indexes
2.Automatic Shared Memory Management - Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA
3.Wait Model Improvements - A number of views have been updated and added to improve the wait model.
4.Automatic Optimizer Statistics Collection - gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB
5.Dynamic Sampling - enables the server to improve performance
6.CPU Costing - default cost model for the optimizer (CPU+I/O), with the cost unit as time Optimizer Hints
7.Rule Based Optimizer Obsolescence - No more used
8.Tracing Enhancements - End to End Application Tracing which allows a client process to be identified via the client identifier rather than the typical session id
9.SAMPLE Clause Enhancements Hash Partitioned Global Indexes
Q.What is proactive tuning and reactive tuning?
A.In Proactive Tuning, the application designers can then determine which combination of system resources and available Oracle features best meet the needs during design and development.In reactive tuning the bottom up approach is used to find and fix the bottlenecks. The goal is to make Oracle run faster.
A.In Proactive Tuning, the application designers can then determine which combination of system resources and available Oracle features best meet the needs during design and development.In reactive tuning the bottom up approach is used to find and fix the bottlenecks. The goal is to make Oracle run faster.
Q.Describe the level of tuning in oracle
A.System-level tuning involves the following steps:
A.System-level tuning involves the following steps:
1.Monitoring the operating system counters using a tool such as top, gtop, and GKrellM or the VTune analyzer’s counter monitor data collector for applications running on Windows.
2.Interpreting the counter data to locate system-level performance bottlenecks and opportunities for improving the way your application interacts with the system.
3.SQL-level tuning:Tuning disk and network I/O subsystem to optimize the I/O time, network packet size and dispatching frequency is called the server kernel optimization.
2.Interpreting the counter data to locate system-level performance bottlenecks and opportunities for improving the way your application interacts with the system.
3.SQL-level tuning:Tuning disk and network I/O subsystem to optimize the I/O time, network packet size and dispatching frequency is called the server kernel optimization.
Distribution of data can be studied by the optimizer by collecting and storing optimizer statistics. This enables intelligent execution plans. Choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance. Tuning SQL Access workload with physical indexes and materialized views.
Q.What is Database design level tuning?
A.The steps involved in database design level tuning are:
A.The steps involved in database design level tuning are:
1.Determination of the data needed by an application (what relations are important, their attributes and structuring the data to best meet the performance goals)
2.Analysis of data followed by normalization to eliminate data redundancy.
3.Avoiding data contention.
4.Localizing access to the data to the partition, process and instance levels.
5.Using synchronization points in Oracle Parallel Server.
6.Implementation of 8i enhancements that can help avoid contention are:
a.Consideration on partitioning the data
b.Consideration over using local or global indexes.
2.Analysis of data followed by normalization to eliminate data redundancy.
3.Avoiding data contention.
4.Localizing access to the data to the partition, process and instance levels.
5.Using synchronization points in Oracle Parallel Server.
6.Implementation of 8i enhancements that can help avoid contention are:
a.Consideration on partitioning the data
b.Consideration over using local or global indexes.
Q.Explain rule-based optimizer and cost-based optimizer.
A.Oracle decides how to retrieve the necessary data whenever a valid SQL statement is processed.This decision can be made using one of two methods:
A.Oracle decides how to retrieve the necessary data whenever a valid SQL statement is processed.This decision can be made using one of two methods:
1.Rule Based Optimizer
If the server has no internal statistics relating to the objects referenced by the statement then the RBO method is used.This method will be deprecated in the future releases of oracle.
If the server has no internal statistics relating to the objects referenced by the statement then the RBO method is used.This method will be deprecated in the future releases of oracle.
2.Cost Based Optimizer
The CBO method is used if internal statistics are present.The CBO checks several possible execution plans and selects the one with the lowest cost based on the system resources.
The CBO method is used if internal statistics are present.The CBO checks several possible execution plans and selects the one with the lowest cost based on the system resources.
Q.What are object datatypes? Explain the use of object datatypes.
A.Object data types are user defined data types. Both column and row can represent an object type. Object types instance can be stored in the database. Object datatypes make it easier to work with complex data, such as images, audio, and video. Object types provide higher-level ways to organize and access data in the database.The SQL attributes of Select into clause, i.e. SQL % Not found, SQL % found, SQL % Isopen, SQL %Rowcount.
1.% Not found: True if no rows returned
E.g. If SQL%NOTFOUND then return some_value
A.Object data types are user defined data types. Both column and row can represent an object type. Object types instance can be stored in the database. Object datatypes make it easier to work with complex data, such as images, audio, and video. Object types provide higher-level ways to organize and access data in the database.The SQL attributes of Select into clause, i.e. SQL % Not found, SQL % found, SQL % Isopen, SQL %Rowcount.
1.% Not found: True if no rows returned
E.g. If SQL%NOTFOUND then return some_value
2.% found: True if at least one or more rows returned
E.g. If SQL%FOUND then return some_value
E.g. If SQL%FOUND then return some_value
3.%Isopen: True if the SQL cursor is open. Will always be false, because the database opens and closes the implicit cursor used to retrieve the data
4.%Rowcount: Number of rows returned. Equals 0 if no rows were found (but the exception is raised) and a 1, if one or more rows are found (if more than one an exception is raised).
Q.What is translate and decode in oracle?
A.
1.Translate: translate function replaces a sequence of characters in a string with another set of characters. The replacement is done single character at a time.Syntax:
translate( string1, string_to_replace, replacement_string )
A.
1.Translate: translate function replaces a sequence of characters in a string with another set of characters. The replacement is done single character at a time.Syntax:
translate( string1, string_to_replace, replacement_string )
Example:
translate ('1tech23', '123', '456);
translate ('1tech23', '123', '456);
2.Decode: The DECODE function compares one expression to one or more other expressions and, when the base expression is equal to a search expression, it returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or NA when it is not.
Syntax:
DECODE (expr , search, result [, search , result]... [, default])
Syntax:
DECODE (expr , search, result [, search , result]... [, default])
Example:
SELECT employee_name, decode(employee_id, 10000, ‘tom’, 10001, ‘peter’, 10002, ‘jack’ 'Gateway') result FROM employee;
SELECT employee_name, decode(employee_id, 10000, ‘tom’, 10001, ‘peter’, 10002, ‘jack’ 'Gateway') result FROM employee;
Q.What is oracle correlated sub-queries? Explain with an example.
A.A query which uses values from the outer query is called as a correlated sub query. The subquery is executed once and uses the results for all the evaluations in the outer query.Example:
Here, the sub query references the employee_id in outer query. The value of the employee_id changes by row of the outer query, so the database must rerun the subquery for each row comparison. The outer query knows nothing about the inner query except its results.
A.A query which uses values from the outer query is called as a correlated sub query. The subquery is executed once and uses the results for all the evaluations in the outer query.Example:
Here, the sub query references the employee_id in outer query. The value of the employee_id changes by row of the outer query, so the database must rerun the subquery for each row comparison. The outer query knows nothing about the inner query except its results.
select employee_id, appraisal_id, appraisal_amount From employee
where
appraisal_amount < (select max(appraisal_amount)
from employee e
where employee_id = e. employee_id);
where
appraisal_amount < (select max(appraisal_amount)
from employee e
where employee_id = e. employee_id);
Q.Explain union and intersect with examples.
A.
1.UNION: The UNION operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement must have the same number of columns with similar data types. It eliminates duplicates.Syntax:
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
A.
1.UNION: The UNION operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement must have the same number of columns with similar data types. It eliminates duplicates.Syntax:
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
Example:
SELECT emp_Name FROM Employees_india
UNION
SELECT emp_Name FROM Employees_USA
SELECT emp_Name FROM Employees_india
UNION
SELECT emp_Name FROM Employees_USA
2.INTERSECT allows combining results of two or more select queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Q.What is difference between open_form and call_form? What is new_form built-in in oracle form?
A.Open_form opens the indicated form. Call_form not just opens the indicated form, but also keeps the parent form alive.When new_form is called, the new indicted form is opened and the old one is exited by releasing the memory. The new form is run using the same Run form options as the parent form.
A.Open_form opens the indicated form. Call_form not just opens the indicated form, but also keeps the parent form alive.When new_form is called, the new indicted form is opened and the old one is exited by releasing the memory. The new form is run using the same Run form options as the parent form.
Q.What is advantage of having disk shadowing/ Mirroring in oracle?
A.Fast recovery of data in case of Disk failure.Improved performance since most OS supports volume shadowing that can direct file I/O request to use the shadow set of files instead of the main set of files.
A.Fast recovery of data in case of Disk failure.Improved performance since most OS supports volume shadowing that can direct file I/O request to use the shadow set of files instead of the main set of files.
