How does Oracle aid performance tuning?
Oracle provides its users with several options to aid performance tuning, such as partitioning LAR tables using materialized views, using tools like Automatic Optimizer statistics collection feature, packages like DBMS_STATS, SQL Tuning Advisor to tune SQL statements etc.
What are the approaches towards performance tuning?
There are two types of approaches that we can follow
Systematic Approach and Reactive Approach
Systematic approach or a reactive approach for performance tuning.It is compulsory to design the database perfectly at starting 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 tuning database at inception stage. This is systematic approach to performance tuning.
What are the Oracle’s suggestions towards systematic tuning?
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
What are the effects of poor database design?
A bad design of database 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. An application performs well in development and testing. Will there be any performance problem when it is moved to production?
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.
What is reactive performance tuning?
Performance tuning is an iterative process. We as a DBA may have to tune in to different applications which is designed and implemented in production. The performance tuning at this stage is referred to as reactive performance tuning.
What is query processing?
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.
What are the Types of Tunings?
- CPU Tuning
- Memory Tuning
- IO Tuning
- Application Tuning
- Database Tuning
What is an optimizer?
Optimizer is a mechanism which will make the execution plan of asql statement
Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?
The tkprof tool is a tuning tool used to determine CPU and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
When should you increase copy latches? What parameters control copy latches?
When you get excessive contention for the copy latches as shown by the “redo copy” latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.
Discuss row chaining, how does it happen? How can you reduce it? How do you correct it?
Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won’t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.
What Are The Types Of Tunings?
- Memory Tuning
- IO Tuning
- Application Tuning
- Database Tuning
- CPU Tuning
You See Multiple Fragments In The System Tablespace, What Should You Check First?
Ensure that users don’t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.
What Is The Fastest Query Method For A Table?
Fetch by rowid.
When Should You Increase Copy Latches? What Parameters Control Copy Latches?
When you get excessive contention for the copy latches as shown by the “redo copy” latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.
What can cause a high value for recursive calls? How can this be fixed?
A high value for recursive calls is cause by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it by either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.
You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem?
A large number of wraps indicates that your extent size for your rollback segments are probably too small. Increase the size of your extents to reduce the number of wraps. You can look at the average transaction size in the same view to get the information on transaction size.
How do you collect statistics of a table?
Analyze table emp compute statistics or analyze table emp estimate statistics
What is the diff between compute and estimate?
If you use compute, The FTS will happen, if you use estimate just 10% of the table will be read
Data Dictionary follows which optimizer mode?
RBO
How do you delete statistics of an object?
Analyze table emp delete statistics
How do you collect statistics of a user/schema?
execdbms_stats.gather_schema_stats(scott)
How do you see the statistics of a table?
select num_rows,blocks,empty_blocks from dba_tables where tab_name=’emp’
What are chained rows?
These are rows, it spans in multiple blocks
How do you collect statistics of a user in Oracle Apps?
fnd_stats
package
How do you know what sql is currently being used by the session?
bygoindv$sql
and v$sql_area
What is an execution plan?
It’s a road map how sql is being executed by Oracle Database.