25+ Oracle Performance Tuning Interview Questions [ 95% SUCCESS ] | 2020
Oracle Performance Tuning Interview Questions and Answers

25+ Oracle Performance Tuning Interview Questions [ 95% SUCCESS ]

Last updated on 04th Jul 2020, Blog, Interview Questions

About author

Pranavkumar (Lead Data Engineer )

High level Domain Expert in TOP MNCs with 8+ Years of Experience. Also, Handled Around 20+ Projects and Shared his Knowledge by Writing these Blogs for us.

(5.0) | 15212 Ratings 11371

Oracle performance tuning, which aims to maximise the effectiveness and responsiveness of Oracle databases, is a crucial component of database administration. To improve overall database performance, a combination of strategic practises are used in the process. Optimising indexes is a crucial component of Oracle performance optimisation. Faster data retrieval is facilitated by properly indexed tables. Make sure indexes are effective by regularly analysing and rebuilding them, especially when data volumes fluctuate. Performance of databases depends on SQL queries that are efficient. Evaluate and improve queries by utilising hints, avoiding pointless joins, and applying the proper indexing.

1. What is proactive tuning or performance tuning?

Ans:

Proactive Tuning, application designers can determine which combination of a system coffers and available Oracle features best meet the requirements during design and development. In reactive tuning, a bottom-up approach is used to find and fix the backups.

Performance tuning

2. Describe level of tuning in Oracle?

Ans:

  • Monitoring operating system counters using the similar tool are top, gtop, and GKrellM or the VTune analyzer’s counter monitor data collector for applications running on the Windows.
  • Interpreting counter data to detect system-level performance bottlenecks and opportunities for increasing the way application interacts with the system.

3. What is Database design level tuning?

Ans:

Determination of data required by an application:

  • Analysis of the data followed by a normalisation to remove a data redundancy.
  • Avoiding data contention.
  • A Localising access to the data to partition, process and case situations. Using synchronisation points in Oracle Parallel Server.

4. Explain rule-based optimizer and cost-based optimizer?

Ans:

Rule-Based Optimizer: If the server has no internal statistics relating to objects referenced by a statement, then the RBO method is used.

Cost-Based Optimizer: The CBO system is used if internal statistics are the present. The CBO checks several possible plans and selects one with the smallest cost based on the system resources.

5. What are object data types?

Ans:

Object data types are user-defined data types. Both column and row can represent the object type. Object types instances can be stored in a database. Object data types make it easier to work with the complex data, like images, audio, and video.

6. What does Oracle translate and decode mean?

Ans:

Translate: translate function replaces sequence of characters in the string with another set of characters.

Decode: The DECODE function compares an expression to one or more additional expressions and provides the result expression corresponding to the base expression.

7. What are Oracle correlated sub-queries?

Ans:

A query using values from the external query is called the correlated subquery. The subquery is executed formally once and uses the results of all evaluations in the external query.

8. Explain union and intersect with examples.

Ans:

UNION:To merge the result-set of two or more SELECT queries, use the UNION operator.

INTERSECT: allows combining the results of two or further select queries.

9. What is new_form built-in in oracle form?

Ans:

  • A new_form is called, a new indicated form is opened, and an old one is excited by a releasing memory.
  • The new form is run using the same as Run form options as a parent form.

10. Difference between DBFile Sequential and Scattered Reads?

Ans:

  Metric DBFile Sequential Reads DBFile Scattered Reads
Definition

reads successive data blocks in a sequential manner

reads non-sequential blocks of non-adjacent data
Scenario sequential index range scans and full table scans Sequential access to data and simultaneous readings
Indication High values could indicate ineffective querying. High values could be a sign of contention or non-sequentially accessed queries
Tools

Oracle SQL monitoring and tuning tools

Oracle Enterprise Manager, personalised scripts for monitoring

11. Explain performance tuning enhancements?

Ans:

  • Automatic Optimizer Statistics Collection – gathers an optimizer statistics using the scheduled job called GATHER_STATS_JOB
  • Dynamic Sampling – enables server to improve performance
  • CPU Costing – default cost model for optimizer (CPU+I/O), with the cost unit as a time Optimizer Hints

12. Define multiple fragments in SYSTEM tablespace?

Ans:

Ensure that users don’t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.

13. What indications are needed to increase SHARED_POOL_SIZE parameter?

Ans:

A Poor data dictionary or library cache hit the ratios, getting error ORA-04031. Another indication is steadily decreasing the performance with all other tuning parameters the same.

14. When should I increase copy latches?

Ans:

When get an excessive contention for copy latches as shown by the redo copy latch hit ratio. And can increase the copy latches by the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on the system.

15. Why and when should one tune?

Ans:

One of the DBA’s primary tasks is to guarantee that the Oracle database is correctly adjusted. The Oracle RDBMS is more adaptable, allowing the database to be monitored and modified to improve performance.

16. What database aspects should be monitored?

Ans:

One should implement the monitoring system to constantly monitor the following aspects of the database. Writing custom scripts, implementing Oracle’s Enterprise Manager, or buying the third-party monitoring product can achieve this.

17. Where should tuning effort be directed?

Ans:

To avoid tuning adverse effects, the order in which steps are described must be preserved. For example, extending the buffer cache is pointless if you can minimise I/O by changing the SQL expression.

18. What tuning indicators can one use?

Ans:

Buffer Cache Hit Ratio: A Hit Ratio = (Logical Reads – Physical Reads) / Logical Reads Action: Increase the DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) to increase hit ratio

Library Cache Hit Ratio: Action: Increase SHARED_POOL_SIZE to increase hit ratio

19. What tools/utilities does Oracle provide to assist with performance tuning?

Ans:

Oracle provide following tools/ utilities to assist with the performance monitoring and tuning:

  • TKProf
  • UTLBSTAT.SQL and UTLESTAT.SQL – Start and conclude your statistic tracking Statspack
  • Oracle Enterprise Manager – Tuning Pack

20. What is STATSPACK?

Ans:

Statspack is the set of performance monitoring and reporting utilities provided by an Oracle from Oracle 8i and above. Statspack provides the improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available.

    Subscribe For Free Demo

    [custom_views_post_title]

    21. When is cost-based optimization triggered?

    Ans:

    It’s important to have statistics on all the tables for CBO (Cost Based Optimizer) to work correctly. If one table involved in the statement does not have statistics, Oracle has to revert to rule-based optimization for that statement.

    22. How can I optimise %XYZ% queries?

    Ans:

    It is possible to enhance the %XYZ% queries by instructing the optimizer to scan all of the index items rather than a table. This can be accomplished by supplying hints.

    23. Where can I find I/O statistics per table?

    Ans:

    • The UTLESTAT report shows the I/O per tablespace but one cannot see what tables in a tablespace have the most I/O.
    • To obtain the relevant information, the $ORACLE_HOME/rdbms/admin/catio.sql script constructs the sample_io procedure and table.
    • After executing procedure, one can do the simple SELECT * FROM io_per_object; to extract required information.

    24. Why is Oracle not using a damn index?

    Ans:

    • The typical reason is because CBO determines that using an index to retrieve a table would be slower than performing a full table scan.
    • USER_TAB_COLUMNS.NUM_DISTINCT – This column lists the various values that it contains.
    • USER_TABLES.NUM_ROWS: Using an index rather than doing a FULL TABLE SCAN is recommended if NUM_DISTINCT = NUM_ROWS.

    25. When should I rebuild an index?

    Ans:

    ‘ANALYSE INDEX VALIDATE STRUCTURE’ should be executed on the affected indexes; each time this command is used, one row is produced in the INDEX_STATS view.

    26. Difference between local partitioned index and global partitioned index?

    Ans:

    In local partitioned Index, there is a one to one relation between data partition and index partition.

    But in the global partitioned Index, there is no one to one relation.

    27. What is a prefixed and non-prefixed partitioned Index?

    Ans:

    • If the index column is the same as the partition key or a leftmost column of the partition keys , Then call it prefix.
    • If the indexed column is either not leading edge of the partitioned keys or not part of a partitioned key, then can call it non-prefixed.
    • For a global partitioned Index, there is only a prefixed index. Oracle doesn’t support the non prefixed global partitioned index

    28. How to improve the speed of index creation on a large table?

    Ans:

    • First can use the parallel in the creation index syntax to improve the speed. However make sure to revert parallelism to default once index is created.
    • Create an index TEST_INDX_object on the test(object_name) parallel 12

    29. What is the clustering factor?

    Ans:

    • low CLUSTERING_FACTOR means, data is in the ordered manner in the table. I.e can say it is a good clustering factor. The minimum clustering_factor is equal to the number of blocks of the table.
    • High CLUSTERING_FACTOR means data is randomly distributed. i.e bad clustering factor. Maximum clustering factor is equal to the number of rows of a table.

    30. How does the oracle calculate the clustering factor of an index?

    Ans:

    For calculating CF, Oracle will fully scan index leaf blocks. These leaf blocks contain rowid, So from rowid, it can find block details of a table. So while scanning the index, whenever a block id, it will increment a CF by one.

    31. What is rowid ?

    Ans:

    Rowid is a pseudocolumn, which contains the information.ROWID is used to get an exact location of a row in the database.It is the fastest way to locate a row.

    32. What data is stored in the index leaf block?

    Ans:

    Particularly in the realm of relational database management systems (RDBMS), an index leaf block (or leaf node) is a part of index structure. Indexes are used to optimise retrieval of rows from tables based on the values in one or more columns.

    33. How does Oracle use indexes to retrieve data?

    Ans:

    When a query hits a database, The optimizer creates the execution plan involving the index. Then the index is used to retrieve a rowid. And using the rowid, the row is located in a datafile and block.

    34. What is the covering index?

    Ans:

    Covering index means, when a query is trying to fetch only indexed column data, then call it covering index. Because, in this case, access to the table is not required, as all column data is already inside the index.

    35. What is cardinality in oracles?

    Ans:

    Cardinality refers to the uniqueness of the data in the particular column of a table. If the table column has more distinct values, then cardinality is high. If distinct values are the least, then cardinality is low.

    36. Why is the query doing a full table scan , despite having an index on predicate?

    Ans:

    • condition where the index_column like ‘%id’
    • Huge data is requested from a table.
    • Very high degree of parallelism is defined in a table.
    • Also when searching for the null value.
    Course Curriculum

    Get Practical Oriented Oracle Performance Tuning Certification Course

    Weekday / Weekend BatchesSee Batch Details

    37. Difference between invisible index and unusable index?

    Ans:

    When making the index invisible, optimisers will not use it . But the index will be maintained by an oracle internally.i.e for all dml activities on a table, the index will be updated. If I want the optimizer to use it , and can make it visible using the alter table command.

    38. Why does moving the table make the index unusable?

    Ans:

    When moving the table , the rows move to the different location and get the new rowid. But the Index still points to old rowids. So I need to rebuild the index, which will make the index entries use the new set of rowids for a table row.

    39. Difference between primary key and unique key?

    Ans:

    Primary Key:The unique values for each row in a table must be contained in a primary key.There can be no two rows with the identical primary key value.

    Unique Key:Like a primary key, a unique key enforces uniqueness of values in the column or a set of columns. No two rows can have the same values in the unique key column(s).

    40. What are different index scanning methods ?

    Ans:

    • INDEX UNIQUE SCAN
    • INDEX RANGE SCAN
    • INDEX FULL SCAN
    • INDEX FAST FULL SCAN
    • INDEX SKIP SCAN

    41. What is a functional Index?

    Ans:

    • If the query contains a function on the index column in which then normal index will not be used.
    • i.e query like select * from emp where lower(emp_name)=’VIKRAM’; — Then the optimizer will not use the normal index.

    42. What does index rebuild regularly?

    Ans:

    It is advised not to do the index rebuild regularly or not at all ( unless performance assessment finds the issue with index).

    The general belief is that index rebuild balances index tree, and improves the clustering factor and to reuse deleted leaf blocks.

    43. Explain the difference between SGA and PGA in Oracle.

    Ans:

    The System Global Area (SGA) is the shared memory region that contains data and control information shared among the multiple Oracle processes. The Program Global Area (PGA) is the memory region dedicated to each individual Oracle process and holds data and control structures specific to that process.

    44. What are key components of Oracle SGA?

    Ans:

    The primary components of Oracle SGA include the Database Buffer Cache, Shared Pool, Redo Log Buffer, Large Pool, and a Java Pool. These components collectively manage the data and control information to optimise database performance.

    45. What is the Oracle Index and how does it impact performance?

    Ans:

    An Oracle Index is the data structure that enhances speed of data retrieval operations on a database table. By providing a quick lookup mechanism, indexes improve query performance by reducing the amount of data that needs to be scanned.

    46. Explain Oracle Optimizer.

    Ans:

    The Oracle Optimizer is the component responsible for determining the most efficient way to execute the SQL statement. It analyses the various execution plans and chooses the one with lowest cost, considering factors like index usage, join methods, and available statistics.

    47. How can identify and resolve performance bottlenecks in Oracle?

    Ans:

    Performance bottlenecks can be identified through the tools like Oracle Enterprise Manager, AWR (Automatic Workload Repository) reports, and monitoring SQL execution plans. To resolve the bottlenecks, actions like optimising SQL queries, adding indexes, adjusting memory allocation, and partitioning tables can be taken.

    48. Explain Oracle Partitioning and its impact on performance.

    Ans:

    Oracle Statspack is the performance diagnostic tool that collects and stores the performance statistics over a specified time interval. It provides the snapshot of the database performance, allowing the database administrators to analyse trends and identify areas that may need tuning.

    49. What are the benefits of using bind variables in SQL queries?

    Ans:

    Bind variables in SQL queries enhance the performance by allowing Oracle to reuse query execution plans. This reduces overhead of re-parsing and re-optimizing SQL statements, leading to more efficient use of resources.

    50. Explain Database Buffer Cache Hit Ratio.

    Ans:

    The Database Buffer Cache Hit Ratio is the measure of how often Oracle can find a requested data block in a buffer cache without having to read it from disk.

    51. Explain Materialised Views in Oracle performance tuning.

    Ans:

    Materialised Views are precomputed, stored result sets that can enhance the query performance by allowing the database to retrieve data from view rather than executing complex queries. They are particularly useful for the aggregations and summarizations.

    52. Explain impact of database normalisation on Oracle performance.

    Ans:

    Database normalisation, while essential for data integrity, can impact performance due to the increased join operations. Striking balance between normalisation and denormalization is crucial, depending on specific use cases and performance requirements.

    53. What is Database Design Level Performance Tuning?

    Ans:

    Steps used in the Database Design Level Performance Tuning are:

    • Determining the data needed by the Application.
    • Analysing data that is followed normally for an eliminating data redundancy.
    • By avoiding a data contention.
    • By localising access to the data for Partition, Process and Instance Level.
    • By using synchronisation points in a Oracle Parallel Server.

    54. What is Oracle Performance Tuning?

    Ans:

    Performance Tuning helps in optimising Oracle Performance through streamlining the execution of SQL Statements. It is used for simplifying the process of Altering and Accessing Information contained by Database with the intention of improving Query Response Times and Application Operations.

    55. What are types of Performance Tunings?

    Ans:

    Types of Performance Tunings are:

    • CPU Tuning
    • Memory Tuning
    • IO Tuning
    • Application Tuning
    • Database Tuning

    56. What is Query Optimization?

    Ans:

    Query Optimization helps in choosing an Execution Plan. It also helps in achieving the result by least cost in terms of resource usage. Resources can include the I/O and CPU Usage on the server where the data can be running. It helps in reducing execution times of the query.

    Course Curriculum

    Get In Depth Knowledge on Oracle Performance Tuning Training

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum

    57. What is Parsing?

    Ans:

    The parsing includes checking the syntax and the semantics of the SQL Statements. The end product of parsing stage of the query compilation is creation of the Parsing Tree, which is used for representing a query structure.

    58. What are Oracle Correlated Sub-Queries?

    Ans:

    Queries that use values from the outer query are known as Correlated Sub-Queries. It can be executed once and the uses of results of all evaluations in Outer Query.

    59. What Oracle Database Resource Manager?

    Ans:

    A Resource Manager allocates the resources like CPU and memory to different user sessions and groups, ensuring fair and an efficient resource utilisation.

    60. What Is Oracle’s Automatic Database Diagnostic Monitor (ADDM)?

    Ans:

    ADDM is the self-diagnostic engine that identifies performance issues in the Oracle database and provides recommendations.

    61. What Oracle Optimizer Statistics?

    Ans:

    The Optimizer Statistics provide the information about the data distribution in tables and indexes, helping to be optimizer to make better decisions.

    62. What Are Oracle Wait Events?

    Ans:

    Wait events are the statistics that indicate the time spent by the session waiting for the specific event, like I/O or locks.

    63. What Oracle AWR Reports?

    Ans:

    The AWR (Automatic Workload Repository) Reports provide the detailed snapshot of the database performance over the specified time period.

    64. What is Oracle SQL Tuning Advisor?

    Ans:

    The SQL Tuning Advisor is the feature of Oracle Database that examines high-load SQL statements and provides the recommendations to improve their performance. It suggests indexes, restructuring of SQL statements, and other optimizations.

    65. What is the Oracle Wait Interface?

    Ans:

    The Oracle Wait Interface is the mechanism for identifying performance bottlenecks by examining the types and durations of waits experienced by database sessions. This information helps in the diagnosing and resolving performance issues.

    66. How does use of bind variables affect SQL statement caching?

    Ans:

    The use of bind variables enhances the SQL statement caching by allowing the Oracle to reuse existing execution plans. This reduces the need for re-parsing and re-optimizing SQL statements, leading to improved performance.

    67. How can partitioning be used for performance optimization in Oracle?

    Ans:

    Partitioning involves dividing large tables into the smaller, more manageable pieces. It improves the performance by allowing the database to operate on smaller data subsets, reducing the amount of data that needs to be scanned or processed.

    68. Explain Oracle Index-Organised Tables (IOTs).

    Ans:

    Oracle Index-Organised Tables (IOTs) are the tables where the data is stored in the B-tree index structure rather than a separate table. They are useful for certain types of the queries, particularly range queries, and can contribute to improved query performance.

    69. How does use of histograms impact Oracle performance?

    Ans:

    Histograms provide the statistics on the distribution of data in column, helping the Oracle Optimizer make better decisions when generating execution plans. They can improve the query performance, especially for columns with the skewed data distributions.

    70. What is Oracle Automatic Storage Management (ASM) in performance tuning?

    Ans:

    ASM simplifies a storage management process by abstracting the physical storage details. It helps in optimising performance by managing the disk I/O and ensuring efficient distribution of data across disks.

    71. How can Oracle Flashback Technology for performance optimization?

    Ans:

    Oracle Flashback Technology allows the user to view and analyse past states of the database. It can be useful for a performance tuning by providing historical insights, enabling comparisons, and assisting in identifying the changes that impact performance.

    72. Explain Oracle Database Compression in performance tuning.

    Ans:

    Oracle Database Compression reduces a storage footprint of data, leading to reduced I/O and improved performance. It is particularly beneficial for large databases where storage efficiency is crucial.

    73. Explain Oracle Architecture briefly.

    Ans:

    Instance: A set of memory structures and background processes.

    Database: Physical files on a disk storing data and metadata.

    Listener: Listens for the incoming connection requests.

    SQL*Net/Net8: Oracle’s networking protocol.

    74. What is the SQL Execution Plan?

    Ans:

    A SQL Execution Plan is the roadmap that Oracle uses to execute a SQL statement. It outlines the steps the database takes to retrieve the required data, including the order in which tables are accessed, access methods used (e.g., full table scan or index access), and join methods.

    75. What is the difference between B-tree index and Bitmap index?

    Ans:

    B-tree index is suitable for the columns with high cardinality (unique values), while Bitmap index is effective for columns with low cardinality (few distinct values). B-tree indexes are typically used for primary keys, whereas Bitmap indexes are useful for data warehousing scenarios.

    76. Explain Oracle AWR (Automatic Workload Repository).

    Ans:

    AWR is the built-in repository that collects and maintains performance statistics related to a database workload. It helps in diagnosing performance problems, understanding system activity, and generating reports for performance analysis over the specific time intervals.

    Oracle Performance Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

    77. Explain importance of Oracle PGA (Program Global Area) in performance tuning.

    Ans:

    The PGA is the memory region dedicated to each Oracle session for sorting, hashing, and other session-specific operations. A Proper configuration of PGA parameters is crucial for the optimal memory usage and efficient processing of the SQL operations.

    78. Explain materialised views.

    Ans:

    Materialised views are the precomputed result sets stored as physical objects. They can improve query performance by allowing the users to access aggregated or summarised data without re-executing complex queries. Refresh strategies can be employed to keep the materialised views up-to-date.

    79. Explain caching and how it can improve Oracle performance.

    Ans:

    Caching involves the storing frequently accessed data in memory for a quicker retrieval. In Oracle, the Shared Pool and Buffer Cache are examples of caches. Properly configuring and sizing these caches can significantly improve the performance by reducing the need to read data from disk.

    80. Explain benefits of using bind variables in SQL queries.

    Ans:

    Bind variables improve the performance by allowing the reuse of existing execution plans. When a query uses bind variables, Oracle can avoid the hard parsing and reuse execution plan for similar queries, reducing overhead associated with the parsing and optimising SQL statements.

    81. Explain Oracle Database Buffer Cache in performance tuning.

    Ans:

    The Buffer Cache is the portion of the SGA used to store copies of a data blocks read from datafiles. It helps in reducing a disk I/O by allowing frequently accessed data to be retrieved from the memory.

    82. Explain Oracle SQL Plan Baselines.

    Ans:

    SQL Plan Baselines are the sets of accepted execution plans that are used to maintain a plan stability. They help prevent the plan regressions caused by changes in the statistics or database structures. SQL Plan Baselines can be managed using DBMS_SPM package.

    83. Explain Oracle In-Memory Database option.

    Ans:

    The In-Memory Database option allows the data to be stored in both the traditional row format and in-memory columnar format. This feature can significantly improve the query performance for analytic and reporting workloads by leveraging faster access times of in-memory data.

    84. How can optimise performance of parallel queries in Oracle?

    Ans:

    Optimising parallel queries involves setting appropriate parameters (PARALLEL_DEGREE, PARALLEL_MIN_SERVERS, etc.), ensuring tables and indexes are parallel-enabled, and configuring parallel query options.

    85. Explain database partitioning pruning.

    Ans:

    Partition pruning is a process of eliminating unnecessary partitions from being scanned during the query execution. It improves the performance by reducing the amount of data that needs to be processed.

    86. Explain Adaptive Cursor Sharing in Oracle

    Ans:

    Adaptive Cursor Sharing allows the database to adapt execution plans based on the different bind variable values. It helps to address scenarios where a single SQL statement has varying execution requirements. The optimizer can use the multiple execution plans based on bind variable values to improve performance.

    87. What are considerations for optimising PL/SQL performance in Oracle?

    Ans:

    Optimising PL/SQL involves minimising context switches, using the bulk processing for DML operations, reducing the unnecessary database calls, and optimising loop processing.

    88. Explain Oracle Database In-Memory Expression Filters on query performance.

    Ans:

    In-Memory Expression Filters allow to specify conditions for loading data into In-Memory Column Store. Using these filters helps optimise memory usage by loading only relevant data into In-Memory area, improving the query performance for a specific subsets of data.

    90. Explain Oracle PGA_AGGREGATE_LIMIT parameter

    Ans:

    PGA_AGGREGATE_LIMIT controls the maximum amount of the memory used by all individual PGAs in instance.

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free