Q1) Define Performance Tuning
Answer: Enabling ideal usage of the system with the prevailing resources is known as Performance Tuning.
Q2) List out the type of tunings
Answer: Different types of tunings are IO Tuning, Database Tuning, CPU Tuning, Application Tuning, and Memory Tuning.
Q3) Explain IO Tuning
Answer: Database files must be sized correctly and located on the right place to afford supreme disk subsystem quantity. Also, look out for missing indexes, regular disk sorts, row chaining, data fragmentation, complete table scans, and so on.
Q4) Explain Memory Tuning
Answer: Properly sizing the database buffers such as buffer cache, log buffer, shared pool, buffer cache by analyzing the ratios of buffer hit. Large objects are pinned into memory to avoid recurrent refills.
Q5) Explain Application Tuning
Answer: As per various experiences, it is well displaying that about 80 percent of Oracle system performance issues are fixed using optimal SQL code. Batch tasks must be properly scheduled on time.
Q6) Explain proactive tuning
Answer: Application engineers will be able to determine which grouping of oracle features and system resources will best fulfill the requirements at the time of development and planning. This process is called proactive tuning.
Q7) Explain reactive tuning
Answer: Bottom-up method is employed to discover and resolve the blockages. This process is called reactive tuning. The goal of reactive tuning is to run Oracle quicker.
Q8) Define optimizer
Answer: Optimizer, a mechanism that makes the SQL statement execution plan
Q9) What are the types of Optimizer?
Answer: Cost Based Optimizer (CBO) and Rule-Based Optimizer (RBO)
Q10) What is Rule-Based Optimizer (RBO)?
Answer: When a server does not have internal statistics with respect to the objects influenced by the statement, here RBO plays its role.
Q11) What is Cost Based Optimizer (CBO)?
Answer: If internal statistics are available, CBO method is employed over there. CBO performs various checks on all the possible execution plans and picks one that has the bottommost charge depending on the system resources.
Q12) To use Optimizer, what are the pre-requisites?
Answer: Initially set the optimizer mode and collect the object statistics
Q13) How to collect table statistics?
Answer: Examine the table emp estimate statistics (or) the table emp compute statistics
Q14) What is the abbreviation of FTS?
Answer: Abbreviation of FTS is “Full Table Scan”
Q15) Provide the difference between the estimate and compute?
Answer: FTS happens if using compute. Only 10% of the table gets read if using the estimate.
Q16) Name the best optimizer
Answer: Best optimizer is Cost Based Optimizer (CBO)
Q17) Name the optimizer which is followed by Data Dictionary
Answer: Rule-Based Optimizer (RBO) is the optimizer followed by Data Dictionary
Q18) Define Chain Rows
Answer: Chain rows are the rows that span in various blocks
Q19) Define Execution Plan
Answer: Execution plan is a road map giving details on how oracle DB executes SQL
Q20) Mention the count of Hits
Answer: There are two (2) hits
Q21) Provide the hits of Oracle Performance Tuning
Answer: Two hits of Oracle Performance Tuning are Library hit and Buffer hit
Q22) Provide the Wait Events types
Answer: Direct path read, and CPU time is the Wait Event types
Q23) Provide the quickest query method for a table?
Answer: The quickest query method for a table is to fetch by rowid
Q24) How will you fix if there is a contention for Library Caches?
Answer: If there is a contention for library caches, just increase the shared pool size
Q25) Is it a problem to have various extents in the temporary tablespace?
Answer: If all the extents are of the same size, there is no problem at all. It can even expand performance as Oracle does not require to generate a fresh extent when there is a need.
Q26) How to gather schema/user statistics?
Answer: Use ‘exec dbms_stats.gather_schema_stats(Scott)’ to gather schema/user statistics
Q27) What is the process to set up tablespaces at the time of Oracle installation?
Answer: Try to use Oracle Flexible Architecture standard or any other segregating scheme to confirm on the appropriate separation of Data, System, Temporary, Redo Log, Rollback, and Index segments.
Q28) How to find whether a tablespace has extreme or unnecessary fragmentation?
Answer: When a select against the table ‘dba_free_space’ displays that the tablespaces extents count is larger than the data files count, you can surely mention that as fragmented.
Q29) Mention the cause of a high value for recursive calls?
Answer: Extreme dynamic space administration activities, unnecessary statement re-parses, and inappropriate cursor practice can lead to a high value for recursive calls.
Q30) How to fix when seeing a high value for recursive calls?
Answer: Initially it is important to determine the causes and correct it with any of the following activities:
Make use of appropriate space management procedures
Relinking applications to grip the cursors
Confirm the repeated questions are in packages for proper reuse.
Q31) If a tablespace contains a table with 30 extents, do you think it is bad?
Answer: Multiple extents within the table is not bad. Though, if there are chained rows, it can easily wound Performance Tuning.
Q32) If there is a statistics dealing with “undo”, what do you mean by that?
Answer: It means they are talking about rollback segments and connected structures.
Q33) What will happen when a tablespace has a standard pct increase of zero with respect to the smon process?
Answer: The SMON process will not mechanically combine the available free space fragments.
Q34) Define Oracle correlated sub-queries
Answer: Oracle correlated sub-queries are the ones that make use of the values from the external query. These subqueries are executed only once and use the results of the entire assessment in the external query.
Q35) Give an example for Oracle correlated sub-queries
Answer: select student_id, fees_id, fees_amount From student
where fees_amount < (select max(fees_amount)
from student y
where student_id = y. student_id);
Q36) What is Intersect?
Answer: ‘Intersect’ permits to combine the outcomes of two or more select queries. If you can see a record available in a query and not visible on the other, those records get removed from Intersect results.
Q37) What is Union?
Answer: ‘Union’ operator is for combining the outcomes which are a set of two or more Select statements. Both the select statement of that table should have a similar count of columns along with the same data types as the duplicates are eliminated.
Q38) Give an example for Union concept
Answer: SELECT school_address FROM school_chennai
UNION
SELECT school_address FROM school_bangalore
Q39) List out the tools provided by Oracle to help in performance tuning
Answer: Below is the tools provided by Oracle to help in performance tuning:
Statspack
TKProf
Oracle Enterprise Manager
SQL and UTLESTAT.SQL
Q40) What is decoding in Oracle?
Answer: The Decode function in Oracle performs a comparison with one expression to one or more other expressions. If the search expression is equal to base expression, the function returns the equivalent result. If there is no match found, the default expression is returned. If the default expression is not specified, then it returns a value as ‘NA’.
Q41) In the SYSTEM tablespace if you find multiple fragments, what should be checked first?
Answer: Make sure that users are not having the System tablespace as their default or temporary tablespace assignment by inspecting the view of DBA_USERS.
Q42) Provide the difference between latches and locks?
Answer: Maintaining read steadiness on instance memory structures is called latches. Maintaining read steadiness on tables is called locks.
Q43) When will you say a database object is invalid?
Answer: A database object is said to be invalid when the fundamental base objects are modified.
Q44) Mention a query to make use of the entire full table scan though the table has an index
Answer: Use the query “hint” to use the entire full table scan
Q45) Provide the regularity of performing database re-organization?
Answer: Database re-organization is not required if ASSM is used. If ASSM is not used, good to perform the re-organization once in every 6 months.
Q46) Why indexes are becoming unusable while moving a table?
Answer: When a table is moved, the row ids are changed, and the index is dependent on that row id. Hence, indexes become unusable.
Q47) What is the purpose of partitioning?
Answer: Partitioning helps in hunting data simply as the data are stockpiled in numerous partitions.
Q48) What is the reason to opt composite partitioning?
Answer: If there is a large table and faster access is required, composite portioning helps much.
Q49) Is it possible to move a table to the same tablespace?
Answer: Yes, it is possible to move a table to the same tablespace if we have adequate free space
Q50) Define ADDM in 10g
Answer: ADMM (Automatic Database Diagnostic Monitor) is a utility helpful in affording few endorsements depending on the statistics gathered for every 1 hour.
No comments:
Post a Comment