The CBO method is used if internal statistics are present. 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. Q4) Explain rule-based optimizer and cost-based optimizer.?Īns: Oracle decides how to retrieve the necessary data whenever a valid SQL training Hyderabad statement is processed.This decision can be made using one of two methods: Consideration of using local or global indexes.Implementation of 8i enhancements that can help avoid contention are: Using synchronization points in Oracle Parallel Server. Localizing access to the data to the partition, process and instance levels. Q3) What is Database design level tuning?Īns: The steps involved in database design level tuning are:ĭetermination of the data needed by an application (what relations are important, their attributes and structuring the data to meet the performance goals best)Īnalysis of data followed by normalization to eliminate data redundancy. Tuning SQL Access workload with physical indexes and materialized views.ĭo you want to master oracle performance tuning? Then enrol in 'Oracle Performance Tuning Training' This course will help you to master Oracle Performance Tuning Choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance. This enables intelligent execution plans. The optimizer can study the distribution of data by collecting and storing optimizer statistics. 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.Interpreting the counter data to locate system-level performance bottlenecks and opportunities for improving the way your application interacts with the system.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.Q2) Describe the level of tuning in Oracle?Īns: System-level tuning involves the following steps: In reactive tuning, the bottom-up approach is used to find and fix the bottlenecks. Q1) What is proactive tuning and reactive tuning?Īns: 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. Performance Tuning In Oracle Interview Questions.Oracle Performance Tuning Interview Questions For Developers.Categories of Oracle Performance Tuning Interview Questions

Oracle performance tuning tips