Checklist for Analyzing Slow-Running Queries?
# Use SQL Server Profiler to help identify the slow query or queries. For more information. Use the sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views to find similar queries that collectively consume a large number of resources.
# How do I analyze the performance of a slow-running query? After you have identified the slow-running query or queries, you can further analyze query performance by producing a Showplan, which can be a text, XML, or graphical representation of the query execution plan that the query optimizer generates. (You can produce a Showplan using Transact-SQL SET options, SQL Server Management Studio, or SQL Server Profiler.)
# Are suitable indexes available? Would adding one or more indexes improve query performance?
# Are there any data or index hot spots? Consider using disk striping. Disk striping can be implemented by using RAID (redundant array of independent disks) level 0, where data is distributed across multiple disk drives.
# Is the query optimizer provided with the best opportunity to optimize a complex query?
# If you have a large volume of data, do you need to partition it? Data manageability is the main benefit of partitioning, but if your tables and indexes on them are partitioned similarly, partitioning can also improve query performance. For more information, see Understanding Partitioning and Tuning the Physical Database Design.
Showing posts with label Index. Show all posts
Showing posts with label Index. Show all posts
Q&A: Oracle Basic
How to make a procedure private?
Don't declare it in package specification.
What is ref cursor?
It is cursor variable like a pointer in C. can point to any query. Used to pass data set between program units.
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php
When a ref cursor open, who use and close this ref cursor?
The program unit who receive the data set will use and close the cursor, it may be other packages or front end code or Reporting tools.
What is an autonomous transaction?
Autonomous transaction is the transaction initiated by another transaction. The key point is it allows the separate commit/rollback transactions.
What are two exceptions to be careful of when using a select INTO statement?
1. When records not return, it would generate NOT_DATA_FOUND Found error message.
2. TOO_NAMY_ROWS can be another problem.
What is %type? What are the advantages of using this over datatypes?
%type means matching the date type of original data source. It can make sure there would be no problem with date type matching issue. On other hand, if declare an own datatype, when source data by chance is different from this declaration, there would be a data type consistence issue.
In PL/SQL how can you test that an update statement updated no rows?
SQL%ROWCOUNT can be used to test for this purpose.
Give examples of collections in oracle and their differences?
# Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
# Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
# Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.
Have you used Bulk Collection? If yes then:"Why is bulk collection more efficient"?
Without bulk collection, every single row would request PL/SQL server to call SQL server one by one. By using bulk collection, say 5000 in a patch, the PL/SQL server will call SQL server for every 5000 rows. This can be very efficient.
What is the difference between a function and a procedure?
1. Functions are normally used for computations where as procedures are normally used for executing business logic.
2. Function is mainly used in the case where it must return 1 value, which can be scalar value or table or table values. Where as a procedure may not return a value (except through out parameter). If procedure mistakenly called, is returns always integer zero.
3. Procedure can call in another project but function work in same project.
4. We can't have any DDL, DML and TLC command inside a function, if that function is called from a SQL query as a UDF(user defined function). But if the function is not called from SQL query then we can have all transactional statement(DDL, DML and TLC) inside a function. Procedure can not be called from the SQL statements.
When a procedure within a package (containting multiple procedures) is called, what will get loaded into memory, the whole package or just the procedure?
Whole package will be loaded into memory.
How many triggers exist?
Trigger Types
A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on. An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The database fires the INSTEAD OF trigger instead of executing the triggering DML statement.
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.
A simple trigger can fire at exactly one of the following timing points:
•Before the triggering statement executes
•After the triggering statement executes
•Before each row that the triggering statement affects
•After each row that the triggering statement affects
A compound trigger can fire at more than one timing point. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data.
How to know if index is being used?
One can use the index monitoring feature to check if indexes are used by an application or not. When the MONITORING USAGE property is set for an index, one can query the v$object_usage to see if the index is being used or not. Here is an example: SELECT table_name, index_name, monitoring, used FROM v$object_usage;
Don't declare it in package specification.
What is ref cursor?
It is cursor variable like a pointer in C. can point to any query. Used to pass data set between program units.
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php
When a ref cursor open, who use and close this ref cursor?
The program unit who receive the data set will use and close the cursor, it may be other packages or front end code or Reporting tools.
What is an autonomous transaction?
Autonomous transaction is the transaction initiated by another transaction. The key point is it allows the separate commit/rollback transactions.
What are two exceptions to be careful of when using a select INTO statement?
1. When records not return, it would generate NOT_DATA_FOUND Found error message.
2. TOO_NAMY_ROWS can be another problem.
What is %type? What are the advantages of using this over datatypes?
%type means matching the date type of original data source. It can make sure there would be no problem with date type matching issue. On other hand, if declare an own datatype, when source data by chance is different from this declaration, there would be a data type consistence issue.
In PL/SQL how can you test that an update statement updated no rows?
SQL%ROWCOUNT can be used to test for this purpose.
Give examples of collections in oracle and their differences?
# Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
# Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
# Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.
Have you used Bulk Collection? If yes then:"Why is bulk collection more efficient"?
Without bulk collection, every single row would request PL/SQL server to call SQL server one by one. By using bulk collection, say 5000 in a patch, the PL/SQL server will call SQL server for every 5000 rows. This can be very efficient.
What is the difference between a function and a procedure?
1. Functions are normally used for computations where as procedures are normally used for executing business logic.
2. Function is mainly used in the case where it must return 1 value, which can be scalar value or table or table values. Where as a procedure may not return a value (except through out parameter). If procedure mistakenly called, is returns always integer zero.
3. Procedure can call in another project but function work in same project.
4. We can't have any DDL, DML and TLC command inside a function, if that function is called from a SQL query as a UDF(user defined function). But if the function is not called from SQL query then we can have all transactional statement(DDL, DML and TLC) inside a function. Procedure can not be called from the SQL statements.
When a procedure within a package (containting multiple procedures) is called, what will get loaded into memory, the whole package or just the procedure?
Whole package will be loaded into memory.
How many triggers exist?
Trigger Types
A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on. An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The database fires the INSTEAD OF trigger instead of executing the triggering DML statement.
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.
A simple trigger can fire at exactly one of the following timing points:
•Before the triggering statement executes
•After the triggering statement executes
•Before each row that the triggering statement affects
•After each row that the triggering statement affects
A compound trigger can fire at more than one timing point. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data.
How to know if index is being used?
One can use the index monitoring feature to check if indexes are used by an application or not. When the MONITORING USAGE property is set for an index, one can query the v$object_usage to see if the index is being used or not. Here is an example: SELECT table_name, index_name, monitoring, used FROM v$object_usage;
Subscribe to:
Posts (Atom)
Posts
