82)What is meant by External Tables in Oracle?
The external tables feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database.
Prior to Oracle Database 10g, external tables were read-only. However, as of Oracle Database 10g, external tables can also be written to.
A feature has been added to external tables that allows users to preprocess input data before it is sent to the access driver.Preprocessing also requires some special precautions on the part of the DBA. Because the executables called by preprocessing programs will be executed under the privileges of the Oracle software owner and malicious executable code can cause a lot of damage, the DBA should be extremely careful in monitoring executables for potentially harmful code.
with external tables you can
o merge a flat file with an existing table in one statement.
o sort a flat file on the way into a table you want compressed nicely.
o do a parallel direct path load -- without splitting up the input file, writing
umpteen scripts and so on
o run sqlldr in effect from a stored procedure or trigger (insert is not sqlldr)
o do multi-table inserts
o flow the data through a pipelined plsql function for cleansing/transformation
Differences you find between SQL Loader and External Tables
===========================================================
* If there are multiple primary input datafiles with SQL*Loader loads, a bad file and a discard file are created for each input datafile. With external table loads, there is only one bad file and one discard file for all input datafiles.
Unloading in the context of external tables means creating an external table using the CTAS method.
83)Can we fire DML Statements on External Tables?
NO DML and Indexing is supported for External tables even in 11G.
84)When should you go in for an External Table?
External tables has wide variety of advantages when compare to sql loader and also in the case of external tables, the data resides outside the database where as the data loaded using sql loader will reside inside the database. So when space is a constraint, then prolly going in for an external table is much better than loading data using sql loader.
Also data in the external table doesn't need an oracle backup since the flat file resides outside the database and a normal file backup is fine which can be taken care by the OS Admin.
85)What is meant by PLSQL Profiler?
The DBMS_PROFILER package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. You can then collect and persistently store the PL/SQL profiler data.
86)What is the use of SQL Profiler Tool in Oracle?
The DBMS_PROFILER package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks.With this interface, you can generate profiling information for all named library units that are executed in a session. The profiler gathers information at the PL/SQL virtual machine level. This information includes the total number of times each line has been executed, the total amount of time that has been spent executing that line, and the minimum and maximum times that have been spent on a particular execution of that line.
87)Have you heard of SQL Tuning Advisor and SQL Access Advisor?
Use the SQL Tuning Advisor for tuning SQL statements. Typically, you can run this advisor in response to an ADDM performance finding that recommends its use.
Additionally, you can run the SQL Tuning Advisor on the most resource-intensive SQL statements, as well as on a SQL workload.
The SQL Access Advisor helps define appropriate access structures such as indexes and materialized views to optimize SQL queries. The advisor takes a SQL workload as an input and recommends which indexes, materialized views, or logs to create, drop, or retain for faster performance. You can select your workload from different sources including current and recent SQL activity, a SQL repository, or a user-defined workload such as from a development environment.
The recommendations that this advisor makes include possible indexes, materialized views, or materialized view logs that can improve your query performance for the given workload.
90)What is the use of the SQL Performance Analyzer?
Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:
•Database, operating system, or hardware upgrades.
•Database, operating system, or hardware configuration changes.
•Database initialization parameter changes.
•Schema changes, such as adding indexes or materialized views.
•Refreshing optimizer statistics.
•Creating or changing SQL profiles.
91)What do you mean by Fragmentation?
Tom kyte's defininition of fragmentation is that you have many "small" holes (regions of contigous free space) that are too small to be the NEXT extent of any object. These holes of free space resulted from dropping some objects (or truncating them) and the resulting free extents cannot be used by any other object in that tablespace. This is a direct result of using a pctincrease that is not zero and having many wierd sized extents (every extent is a unique size and shape)
92)What is meant by Shared pool Fragmentation?
Performance degradation occurs in the shared pool in situations where pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request.Fragmentation of the shared pool can lead to memory not being allocated.
Shared Pool Latch Contention is almost always due to Shared pool fragmentation. Process will hold the shared pool latch for a long time while searching for a free chunk of memory when the shared pool is fragmented.The shared pool may be fragmented into many smaller free chunks. But whenever there is a request for a large piece of memory, then the recreatable or freeable chunks of memory have to flushed from the shared pool so that they can be coalesced to a large chunk of free memory which is able to statisfy the request.That coalescing might involve walking thro shared pool freelists many times and flushing freeable chunks until a large chunk able to statisfy the request is created.
93)What happens on the Oracle Backend whenever you fire a Query?
94)What is parsing?
Whenever a statement is executed, Oracle follows a methodology to evaluate the statement in terms of syntax, validity of objects being referred and of course, privileges to the user. Apart from this, Oracle also checks for identical statements that may have been fired, with the intention of reducing processing overheads. All this takes place in a fraction of a second, even less, without the user knowing what is happening to the statement that was fired. This process is known as Parsing.
Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.
Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data.
95)Can you explain the Parse, Bind , Execute and Fetch Phases?
We can refer to the DBA Study guide for more information.
96)What is a Cursor?
To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type.
Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They provide information about the execution of data manipulation statements. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement.
97)What are the different Types of Cursors?
Implicit Cursor
Explicit Cursor
REF Cursor
BULK COLLECT and FOR ALL Cursor
Parameterized Cursor
98)What is a parameterized Cursor?
99)What is a reference Cursor?
A cursor variable is a pointer that distinguishes the current row in a resultset from a multi-row query. Cursor variables have several advantages over explicit cursors including:
* Cursor variables can point to a variety of queries provided the queries have a suitable return type. In contrast, explicit cursors are tied to individual queries.
* Cursor variables allow cursors to be opened independently of being processed.
* Cursor variables can be passed as parameters between application layers, as well as between server side components.
* Cursor variables can be used to reduce client-server network traffic by allowing several cursors to be opened on the server in a single round trip.
100)What is meant by Context Switching?
When we consider "CONTEXT SWITCHING" in relation to Oracle Database, we specifically refer to the exchange of processing control between the SQL and PL/SQL engines (without necessarily understanding what occurs in such a process). These two engines are separate and distinct but we use them interchangeably. This means that when we call SQL from PL/SQL or vice versa, the calling context needs to store its process state and hand over control and data to its counterpart engine (which may or may not be picking up from an earlier switch). This switching cycle is computationally intensive and can typically be repeated so many times that its effects on response times can become quite noticeable.
WHEN A SQL STATEMENT IS WRAPPED IN A PL/SQL FUNCTION AND CALLED FROM SQL QUERIES, THE COST OF CONTEXT-SWITCHING IS AUTOMATICALLY DOUBLED—IT NOW OCCURS BOTH INSIDE AND OUTSIDE THE FUNCTION.
SQL statements are called from PL/SQL in a loop, so the execution will switch back and forth between the PL/SQL engine and the SQL engine. This switch between two environments is known as a context switch. Context switches increase elapsed time of your programs and introduce unnecessary CPU overhead. You should reduce the number of context switches by eliminating or reducing the switching between these two environments.
The external tables feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database.
Prior to Oracle Database 10g, external tables were read-only. However, as of Oracle Database 10g, external tables can also be written to.
A feature has been added to external tables that allows users to preprocess input data before it is sent to the access driver.Preprocessing also requires some special precautions on the part of the DBA. Because the executables called by preprocessing programs will be executed under the privileges of the Oracle software owner and malicious executable code can cause a lot of damage, the DBA should be extremely careful in monitoring executables for potentially harmful code.
with external tables you can
o merge a flat file with an existing table in one statement.
o sort a flat file on the way into a table you want compressed nicely.
o do a parallel direct path load -- without splitting up the input file, writing
umpteen scripts and so on
o run sqlldr in effect from a stored procedure or trigger (insert is not sqlldr)
o do multi-table inserts
o flow the data through a pipelined plsql function for cleansing/transformation
Differences you find between SQL Loader and External Tables
===========================================================
* If there are multiple primary input datafiles with SQL*Loader loads, a bad file and a discard file are created for each input datafile. With external table loads, there is only one bad file and one discard file for all input datafiles.
Unloading in the context of external tables means creating an external table using the CTAS method.
83)Can we fire DML Statements on External Tables?
NO DML and Indexing is supported for External tables even in 11G.
84)When should you go in for an External Table?
External tables has wide variety of advantages when compare to sql loader and also in the case of external tables, the data resides outside the database where as the data loaded using sql loader will reside inside the database. So when space is a constraint, then prolly going in for an external table is much better than loading data using sql loader.
Also data in the external table doesn't need an oracle backup since the flat file resides outside the database and a normal file backup is fine which can be taken care by the OS Admin.
85)What is meant by PLSQL Profiler?
The DBMS_PROFILER package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. You can then collect and persistently store the PL/SQL profiler data.
86)What is the use of SQL Profiler Tool in Oracle?
The DBMS_PROFILER package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks.With this interface, you can generate profiling information for all named library units that are executed in a session. The profiler gathers information at the PL/SQL virtual machine level. This information includes the total number of times each line has been executed, the total amount of time that has been spent executing that line, and the minimum and maximum times that have been spent on a particular execution of that line.
87)Have you heard of SQL Tuning Advisor and SQL Access Advisor?
Use the SQL Tuning Advisor for tuning SQL statements. Typically, you can run this advisor in response to an ADDM performance finding that recommends its use.
Additionally, you can run the SQL Tuning Advisor on the most resource-intensive SQL statements, as well as on a SQL workload.
The SQL Access Advisor helps define appropriate access structures such as indexes and materialized views to optimize SQL queries. The advisor takes a SQL workload as an input and recommends which indexes, materialized views, or logs to create, drop, or retain for faster performance. You can select your workload from different sources including current and recent SQL activity, a SQL repository, or a user-defined workload such as from a development environment.
The recommendations that this advisor makes include possible indexes, materialized views, or materialized view logs that can improve your query performance for the given workload.
90)What is the use of the SQL Performance Analyzer?
Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:
•Database, operating system, or hardware upgrades.
•Database, operating system, or hardware configuration changes.
•Database initialization parameter changes.
•Schema changes, such as adding indexes or materialized views.
•Refreshing optimizer statistics.
•Creating or changing SQL profiles.
91)What do you mean by Fragmentation?
Tom kyte's defininition of fragmentation is that you have many "small" holes (regions of contigous free space) that are too small to be the NEXT extent of any object. These holes of free space resulted from dropping some objects (or truncating them) and the resulting free extents cannot be used by any other object in that tablespace. This is a direct result of using a pctincrease that is not zero and having many wierd sized extents (every extent is a unique size and shape)
92)What is meant by Shared pool Fragmentation?
Performance degradation occurs in the shared pool in situations where pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request.Fragmentation of the shared pool can lead to memory not being allocated.
Shared Pool Latch Contention is almost always due to Shared pool fragmentation. Process will hold the shared pool latch for a long time while searching for a free chunk of memory when the shared pool is fragmented.The shared pool may be fragmented into many smaller free chunks. But whenever there is a request for a large piece of memory, then the recreatable or freeable chunks of memory have to flushed from the shared pool so that they can be coalesced to a large chunk of free memory which is able to statisfy the request.That coalescing might involve walking thro shared pool freelists many times and flushing freeable chunks until a large chunk able to statisfy the request is created.
93)What happens on the Oracle Backend whenever you fire a Query?
94)What is parsing?
Whenever a statement is executed, Oracle follows a methodology to evaluate the statement in terms of syntax, validity of objects being referred and of course, privileges to the user. Apart from this, Oracle also checks for identical statements that may have been fired, with the intention of reducing processing overheads. All this takes place in a fraction of a second, even less, without the user knowing what is happening to the statement that was fired. This process is known as Parsing.
Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.
Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data.
95)Can you explain the Parse, Bind , Execute and Fetch Phases?
We can refer to the DBA Study guide for more information.
96)What is a Cursor?
To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type.
Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They provide information about the execution of data manipulation statements. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement.
97)What are the different Types of Cursors?
Implicit Cursor
Explicit Cursor
REF Cursor
BULK COLLECT and FOR ALL Cursor
Parameterized Cursor
98)What is a parameterized Cursor?
99)What is a reference Cursor?
A cursor variable is a pointer that distinguishes the current row in a resultset from a multi-row query. Cursor variables have several advantages over explicit cursors including:
* Cursor variables can point to a variety of queries provided the queries have a suitable return type. In contrast, explicit cursors are tied to individual queries.
* Cursor variables allow cursors to be opened independently of being processed.
* Cursor variables can be passed as parameters between application layers, as well as between server side components.
* Cursor variables can be used to reduce client-server network traffic by allowing several cursors to be opened on the server in a single round trip.
100)What is meant by Context Switching?
When we consider "CONTEXT SWITCHING" in relation to Oracle Database, we specifically refer to the exchange of processing control between the SQL and PL/SQL engines (without necessarily understanding what occurs in such a process). These two engines are separate and distinct but we use them interchangeably. This means that when we call SQL from PL/SQL or vice versa, the calling context needs to store its process state and hand over control and data to its counterpart engine (which may or may not be picking up from an earlier switch). This switching cycle is computationally intensive and can typically be repeated so many times that its effects on response times can become quite noticeable.
WHEN A SQL STATEMENT IS WRAPPED IN A PL/SQL FUNCTION AND CALLED FROM SQL QUERIES, THE COST OF CONTEXT-SWITCHING IS AUTOMATICALLY DOUBLED—IT NOW OCCURS BOTH INSIDE AND OUTSIDE THE FUNCTION.
SQL statements are called from PL/SQL in a loop, so the execution will switch back and forth between the PL/SQL engine and the SQL engine. This switch between two environments is known as a context switch. Context switches increase elapsed time of your programs and introduce unnecessary CPU overhead. You should reduce the number of context switches by eliminating or reducing the switching between these two environments.
No comments:
Post a Comment