123)What is the basic Structure of a PLSQL Block?
Declare
Begin
Exception
End
124)What are the different types of PLSQL Objects that you have come across?
Stored Procedures
Stored Functions
Packages
125)What is the use of a Package?
A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec.
126)When should you go in for a Package?
Packages offer several advantages: modularity, easier application design, information hiding, added functionality, and better performance.
Modularity
Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.
Easier Application Design
When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.
Information Hiding
With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.
Added Functionality
Packaged public variables and cursors persist for the duration of a session. So, they can be shared by all subprograms that execute in the environment. Also, they allow you to maintain data across transactions without having to store it in the database.
Better Performance
When you call a packaged subprogram for the first time, the whole package is loaded into memory. So, later calls to related subprograms in the package require no disk I/O. Also, packages stop cascading dependencies and thereby avoid unnecessary recompiling. For example, if you change the implementation of a packaged function, Oracle need not recompile the calling subprograms because they do not depend on the package body.
127)What is the difference between a Procedure and a Function?
A Procedure can have out values but a Function cannot.
A Fucntion can return a value whereas a procedure cannot.
A User defined Function can be used in a SQL Statement whereas a procedure cannot.
128)Can a Function return more than one value?
No
129)Can a Function have OUT Parameters?
No
130)What is meant by a Fucntion Based Index?
A function-based index allows you to match any WHERE clause in an SQL statement and remove unnecessary large-table full-table scans with super-fast index range scans.
You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
This is because the Functional Index is created by making use of a SQL Function and therefore there is a possibility that some of the SQL Funcions dont work based on the data stored in the Columns on which the functional index is created.
Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index
131)What is benefit of using Functions?
The use of the RESULT_CACHE parameter while declaring a function helps in caching the results of the user defined functions for the input values. The Function should be declared determinstic for caching the results of the function and making use of a functional index on the user defined function.
132)Can we create an index on an user defined Fucntions?
Yes, the user defined function needs to be declared determinstic.
133)What is the use of the RESULT_CACHE Keyword in Functions?
This parameter is applicable to the user defined functions in oracle for caching the results of the function along with the input parameters.
134)What do you mean by a global Parameter?
Any Parameter which is defined in the package specification instead of defining them within the procedure or function while defining the body of the package are defined as global variables.
135)Can a Global parameter be referenced by some other packages or PLSQL?
Yes, by refering the parameter using the pacakage name in which the parameter is defined.
136)What is the use of an UNDO Tablespace?
Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.
Undo records are used to:
* Roll back transactions when a ROLLBACK statement is issued
* Recover the database
* Provide read consistency
* Analyze data as of an earlier point in time by using Oracle Flashback Query
* Recover from logical corruptions using Oracle Flashback features
137)What is meant by FLASHBACK?
Oracle Flashback Technology is a group of Oracle Database features that that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.
With flashback features, you can do the following:
* Perform queries that return past data
* Perform queries that return metadata that shows a detailed history of changes to the database
* Recover tables or rows to a previous point in time
* Automatically track and archive transactional data changes
* Roll back a transaction and its dependent transactions while the database remains online
138)How do you flashback a Query?
Using the "AS OF TIMESTAMP" clause, you will be able to flashback a Query.
Eg:-
SELECT * FROM <Table_name>
AS OF TIMESTAMP
TO_TIMESTAMP('2012-06-06 00:00:00', 'YYYY-MM-DD HH:MI:SS')
139)Can we flashback a Table and database?
You can flashback a Table which is dropped to before drop using the "FLASHBACK TABLE TO BEFORE DROP".
You can flashback the Database using the "TO TIMESTAMP" Clause after shutting the Database and opening it in the MOUNT Mode.
Eg:-
SHUTDOWN DATABASE
STARTUP MOUNT
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-2;
140)What is Snapshot too Old Error?
While setting a high value for the UNDO_RETENTION parameter can potentially minimize the possibility of receiving "snapshot too old" error, it doesn't guarantee that the database won't overwrite older undo data that may be needed by a running transaction. You can move long-running batch jobs to a separate time interval when other programs aren't running in the database, to avoid these errors.
Regardless, while you can minimize the occurrence of "snapshot too old" errors with these approaches, you can't completely eliminate such errors without specifying the guaranteed undo retention feature. Oracle will keep new DML statements from executing when you set up guaranteed undo retention.
The "guarantee" part of the undo retention guarantee is real—Oracle will certainly retain undo at least for the time you specify and will never overwrite any of the unexpired undo extents that contain the undo required to satisfy the undo retention period.
141)What is the main use of a Global Temporary Table?
Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.
Also the data residing within the Global Temporary Tables can be session specific or transaction specific. Also Global Temporary Tables allows the creation of indexes on the temporary data.
142)What do you mean by Cursor Sharing?
EXACT: The database doesn't replace any literals, and the optimizer sees the query as it's presented to the optimizer. The optimizer generates a different plan for each execution of the statement, based on the literal values in the statement. The plan would thus be an optimal one, but each statement has its own parent cursor, and therefore a statement that's executed numerous times can use a considerable amount of space in the shared pool. This could potentially lead to latch contention and a slowdown in performance.
FORCE: Regardless of whether there's a histogram, the optimizer will replace the literal values with a bind value and optimize this query as if it were in the following form:
select * from <Table_name> where <Column_name>=:b
The optimizer uses a single plan for each SQL statement, regardless of the literal values. Thus, the execution plan won't be optimal, as the plan is generic, and not based on the literal values. If a query uses literal values, the optimizer will use those values to find the most efficient execution plan. If there are no literals in the SQL statement, it's very hard for the optimizer to figure out the best execution plan. By "peeking" at the value of the bind variables, the optimizer can get a better idea of the selectivity of the where clause condition—it is almost as if literals had been used in the SQL statement. The optimizer peeks at the bind values during the hard parse state. Since the execution plan is based on the specific value of the bind variable that the optimizer happened to peek at, the execution plan may not be optimal for all possible values of the bind variable.
In this example, the optimizer uses bind peeking based on the specific value of the JOB column it sees. In this case, the optimizer uses the value Clerk to estimate the cardinality for the query. When it executes the same statement (with a different value in the JOB column, say, Manager), the optimizer will use the same plan that it generated the first time (JOB=Clerk). Since there is only one parent cursor and just child cursors for the distinct statements, there's less pressure on the shared pool. Note that a child cursor uses far less space in the shared pool than a parent cursor. Often, setting the cursor_sharing parameter to FORCE immediately resolves serious latch contention in the database, making this one of the few magic bullets that can help you quickly reduce latch contention.
SIMILAR (without a histogram on the JOB column): The database will use literal replacement—it uses a system-generated bind value instead of the literal value for the JOB column (Clerk). This is because the absence of a histogram on the JOB column tells the optimizer that the data in the JOB column isn't skewed, and therefore the optimizer chooses the same plan for each execution of the statement, even though the literal values are different. The optimizer thinks it shouldn't make any changes to the execution plans for the statements that differ only in literal values because the data is uniformly distributed. The SIMILAR setting without a histogram on the columns in a query provides the same query performance and a similar impact on the shared pool as when you specify the FORCE setting.
SIMILAR (with a histogram on the JOB column): When the optimizer sees the histogram in the JOB column, it realizes that the column is skewed—this tells the optimizer that the results of the query may vary widely depending on the literal value of the JOB column. Consequently, the optimizer generates a different plan for each statement based on the literal values—thus the plans are very efficient, as in the case when you specify the EXACT setting. The SIMILAR option with a histogram in place does use more space in the shared pool, but not as much as when you use the EXACT setting. The reason for this is that each statement has its own child cursor instead of a parent cursor.
The choice among the various settings of the cursor_sharing parameter really boils down to an assessment of what's more critical to database performance: using the default EXACT setting or SIMILAR (with a histogram on the relevant column) does provide better query performance but leads to the generation of numerous parent cursors (EXACT setting) or child cursors (SIMILAR setting). If there's a severe pressure in the shared pool, and consequent latch contention, the entire database will perform poorly. Under these circumstances, you're better off implementing a system-wide solution by setting the cursor_sharing parameter to FORCE, as this guarantees that there's only a single child cursor for each SQL statement. If you're concerned about the impact of a single SQL statement, just drop the histogram on the relevant columns used in the SQL statement and set the cursor_sharing parameter to FORCE—this will ensure that the optimizer uses system-generated bind values for the column(s) and ensures that the SQL statement uses much less space in the shared pool.
Declare
Begin
Exception
End
124)What are the different types of PLSQL Objects that you have come across?
Stored Procedures
Stored Functions
Packages
125)What is the use of a Package?
A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec.
126)When should you go in for a Package?
Packages offer several advantages: modularity, easier application design, information hiding, added functionality, and better performance.
Modularity
Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.
Easier Application Design
When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.
Information Hiding
With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.
Added Functionality
Packaged public variables and cursors persist for the duration of a session. So, they can be shared by all subprograms that execute in the environment. Also, they allow you to maintain data across transactions without having to store it in the database.
Better Performance
When you call a packaged subprogram for the first time, the whole package is loaded into memory. So, later calls to related subprograms in the package require no disk I/O. Also, packages stop cascading dependencies and thereby avoid unnecessary recompiling. For example, if you change the implementation of a packaged function, Oracle need not recompile the calling subprograms because they do not depend on the package body.
127)What is the difference between a Procedure and a Function?
A Procedure can have out values but a Function cannot.
A Fucntion can return a value whereas a procedure cannot.
A User defined Function can be used in a SQL Statement whereas a procedure cannot.
128)Can a Function return more than one value?
No
129)Can a Function have OUT Parameters?
No
130)What is meant by a Fucntion Based Index?
A function-based index allows you to match any WHERE clause in an SQL statement and remove unnecessary large-table full-table scans with super-fast index range scans.
You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
This is because the Functional Index is created by making use of a SQL Function and therefore there is a possibility that some of the SQL Funcions dont work based on the data stored in the Columns on which the functional index is created.
Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index
131)What is benefit of using Functions?
The use of the RESULT_CACHE parameter while declaring a function helps in caching the results of the user defined functions for the input values. The Function should be declared determinstic for caching the results of the function and making use of a functional index on the user defined function.
132)Can we create an index on an user defined Fucntions?
Yes, the user defined function needs to be declared determinstic.
133)What is the use of the RESULT_CACHE Keyword in Functions?
This parameter is applicable to the user defined functions in oracle for caching the results of the function along with the input parameters.
134)What do you mean by a global Parameter?
Any Parameter which is defined in the package specification instead of defining them within the procedure or function while defining the body of the package are defined as global variables.
135)Can a Global parameter be referenced by some other packages or PLSQL?
Yes, by refering the parameter using the pacakage name in which the parameter is defined.
136)What is the use of an UNDO Tablespace?
Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.
Undo records are used to:
* Roll back transactions when a ROLLBACK statement is issued
* Recover the database
* Provide read consistency
* Analyze data as of an earlier point in time by using Oracle Flashback Query
* Recover from logical corruptions using Oracle Flashback features
137)What is meant by FLASHBACK?
Oracle Flashback Technology is a group of Oracle Database features that that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.
With flashback features, you can do the following:
* Perform queries that return past data
* Perform queries that return metadata that shows a detailed history of changes to the database
* Recover tables or rows to a previous point in time
* Automatically track and archive transactional data changes
* Roll back a transaction and its dependent transactions while the database remains online
138)How do you flashback a Query?
Using the "AS OF TIMESTAMP" clause, you will be able to flashback a Query.
Eg:-
SELECT * FROM <Table_name>
AS OF TIMESTAMP
TO_TIMESTAMP('2012-06-06 00:00:00', 'YYYY-MM-DD HH:MI:SS')
139)Can we flashback a Table and database?
You can flashback a Table which is dropped to before drop using the "FLASHBACK TABLE TO BEFORE DROP".
You can flashback the Database using the "TO TIMESTAMP" Clause after shutting the Database and opening it in the MOUNT Mode.
Eg:-
SHUTDOWN DATABASE
STARTUP MOUNT
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-2;
140)What is Snapshot too Old Error?
While setting a high value for the UNDO_RETENTION parameter can potentially minimize the possibility of receiving "snapshot too old" error, it doesn't guarantee that the database won't overwrite older undo data that may be needed by a running transaction. You can move long-running batch jobs to a separate time interval when other programs aren't running in the database, to avoid these errors.
Regardless, while you can minimize the occurrence of "snapshot too old" errors with these approaches, you can't completely eliminate such errors without specifying the guaranteed undo retention feature. Oracle will keep new DML statements from executing when you set up guaranteed undo retention.
The "guarantee" part of the undo retention guarantee is real—Oracle will certainly retain undo at least for the time you specify and will never overwrite any of the unexpired undo extents that contain the undo required to satisfy the undo retention period.
141)What is the main use of a Global Temporary Table?
Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.
Also the data residing within the Global Temporary Tables can be session specific or transaction specific. Also Global Temporary Tables allows the creation of indexes on the temporary data.
142)What do you mean by Cursor Sharing?
EXACT: The database doesn't replace any literals, and the optimizer sees the query as it's presented to the optimizer. The optimizer generates a different plan for each execution of the statement, based on the literal values in the statement. The plan would thus be an optimal one, but each statement has its own parent cursor, and therefore a statement that's executed numerous times can use a considerable amount of space in the shared pool. This could potentially lead to latch contention and a slowdown in performance.
FORCE: Regardless of whether there's a histogram, the optimizer will replace the literal values with a bind value and optimize this query as if it were in the following form:
select * from <Table_name> where <Column_name>=:b
The optimizer uses a single plan for each SQL statement, regardless of the literal values. Thus, the execution plan won't be optimal, as the plan is generic, and not based on the literal values. If a query uses literal values, the optimizer will use those values to find the most efficient execution plan. If there are no literals in the SQL statement, it's very hard for the optimizer to figure out the best execution plan. By "peeking" at the value of the bind variables, the optimizer can get a better idea of the selectivity of the where clause condition—it is almost as if literals had been used in the SQL statement. The optimizer peeks at the bind values during the hard parse state. Since the execution plan is based on the specific value of the bind variable that the optimizer happened to peek at, the execution plan may not be optimal for all possible values of the bind variable.
In this example, the optimizer uses bind peeking based on the specific value of the JOB column it sees. In this case, the optimizer uses the value Clerk to estimate the cardinality for the query. When it executes the same statement (with a different value in the JOB column, say, Manager), the optimizer will use the same plan that it generated the first time (JOB=Clerk). Since there is only one parent cursor and just child cursors for the distinct statements, there's less pressure on the shared pool. Note that a child cursor uses far less space in the shared pool than a parent cursor. Often, setting the cursor_sharing parameter to FORCE immediately resolves serious latch contention in the database, making this one of the few magic bullets that can help you quickly reduce latch contention.
SIMILAR (without a histogram on the JOB column): The database will use literal replacement—it uses a system-generated bind value instead of the literal value for the JOB column (Clerk). This is because the absence of a histogram on the JOB column tells the optimizer that the data in the JOB column isn't skewed, and therefore the optimizer chooses the same plan for each execution of the statement, even though the literal values are different. The optimizer thinks it shouldn't make any changes to the execution plans for the statements that differ only in literal values because the data is uniformly distributed. The SIMILAR setting without a histogram on the columns in a query provides the same query performance and a similar impact on the shared pool as when you specify the FORCE setting.
SIMILAR (with a histogram on the JOB column): When the optimizer sees the histogram in the JOB column, it realizes that the column is skewed—this tells the optimizer that the results of the query may vary widely depending on the literal value of the JOB column. Consequently, the optimizer generates a different plan for each statement based on the literal values—thus the plans are very efficient, as in the case when you specify the EXACT setting. The SIMILAR option with a histogram in place does use more space in the shared pool, but not as much as when you use the EXACT setting. The reason for this is that each statement has its own child cursor instead of a parent cursor.
The choice among the various settings of the cursor_sharing parameter really boils down to an assessment of what's more critical to database performance: using the default EXACT setting or SIMILAR (with a histogram on the relevant column) does provide better query performance but leads to the generation of numerous parent cursors (EXACT setting) or child cursors (SIMILAR setting). If there's a severe pressure in the shared pool, and consequent latch contention, the entire database will perform poorly. Under these circumstances, you're better off implementing a system-wide solution by setting the cursor_sharing parameter to FORCE, as this guarantees that there's only a single child cursor for each SQL statement. If you're concerned about the impact of a single SQL statement, just drop the histogram on the relevant columns used in the SQL statement and set the cursor_sharing parameter to FORCE—this will ensure that the optimizer uses system-generated bind values for the column(s) and ensures that the SQL statement uses much less space in the shared pool.
No comments:
Post a Comment