sqlpl tuning

Question 1

How do you analyze an explain plan?

Answer 1

1. Driving Table
2. Join Order
3. Join Method
4. Unintentional cartesian product
5. Nested loops, merge sort, and hash join
6. Full Table Scan
7. Unused indexes
8. Access paths

Question 2

What are the steps involved in improving the SQL performance?

Answer 2

First of all, find out the areas of improvement. Explore tools like Profiler, Query execution plans, SQL tuning advisor, dynamic views, and custom stored procedures.

Question 3

What is a Latch Free Event? And when does it occur? Alos, how does the system handles it?

Answer 3

Ans. In Oracle, Latch Free wait event occurs when a session requires a latch, attempts to get it but fails because someone else has it.
So it sleeps with a wait eying for the latch to get free, wakes up and tries again. The time duration for it was inactive is the wait time for Latch Free. Also, there is no ordered queue for the waiters on a latch, so the one who comes first gets it.

Question 4

What are several SQL performance tuning enhancements in Oracle?

Answer 4

1. Automatic Performance Diagnostic and Tuning Features
2. Automatic Shared Memory Management – It gives Oracle control of allocating memory within the SGA.
3. Wait-model improvements – A number of views have come to boost the Wait-model.
4. Automatic Optimizer Statistics Collection – Collects optimizer statistics using a scheduled job called GATHER_STATS_JOB.
5. Dynamic Sampling – Enables the server to enhance performance.
6. CPU Costing – It’s the basic cost model for the optimizer (CPU+I/O), with the cost unit as time optimizer notifies.
7. Rule Based Optimizer Obsolescence – No more used.
8. Tracing Enhancements – End to End tracing which allows a client process to be identified via the Client Identifier instead of using the typical Session ID.

Question 5

When would you add more Copy Latches? What are the parameters that control the Copy Latches?

Answer 5

ns. If there is excessive contention for the Copy Latches, check from the “redo copy” latch hit ratio.
In such a case, add more Copy Latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to double the number of CPUs available.

Question 6

How Do You Know What Sql Is Currently Being Used By The Session?

Answer 6

By goind v$sql and v$sql_area.

Question 7

TOP Wait Events?

Answer 7

db file sequential reads.
db file scattered reads.
log file parallel write.
log file sync:
buffer busy waits:
free buffer waits:
enqueue waits.
Cache buffer chain latch.

Question 8

Top 5 Wait events in AWR

Answer 8

DB FILE type waits – physical IO.
BUFFER type waits – Logical IO.
LOG type waits – Redo related.
PX – Parallel Query.
GC – Global Cache (RAC related)
Undo – Undo or rollback segment related.

Question 9

Control the Behavior of the Query Optimizer

Answer 9

Here is the list of initialization parameters that can be used to control the behavior of the query optimizer.

1) CURSOR_SHARING
2) DB_FILE_MULTIBLOCK_READ_COUNT
3) OPTIMIZER_INDEX_CACHING
4) OPTIMIZER_INDEX_COST_ADJ
5) OPTIMIZER_MODE
6) PGA_AGGREGATE_TARGET
7) STAR_TRANSFORMATION_ENABLED
8) Enable Query Optimizer Feature

Question 10

ORACLE DW initial param

Answer 10

db_file_multiblock_read_count should be increa
or the parameter db_block_size 32k
pga_aggregate_ parameter pga_aggregate_limit
hash_area_size (for hash join
sort_area_size (for sort operations), th
star_transformation_enabled
query_rewrite_integrity t
parallel_min_serversp) and parallel_max_servers (

Question 11

ETL performance

Answer 11

Use Set-based Operations
Avoid Nested Loops
Drop Unnecessary Indexes
Avoid Functions in WHERE Condition
Take Care of OR in WHERE Condition
Reduce Data as Early as Possible
Use WITH to Split Complex Queries
Run Statements in Parallel
Perform Direct-Path INSERT
Gather Statistics after Loading each Table

Question 12

STAR schema index

Answer 12

bitmap index of the dimension key in the fact table

Question 13

cause of LRU contention

Answer 13

shared pool latch - same sql - use bind variable
cache buffer LRI chain - caused by Full TS and full index scan
* cause by lack of index or un selective index

Cache buffer chain - * more hot block
severel process use same unselective index

Question 14

Statistics Feedback (Formerly Cardinality Feedback) -

Answer 14

Cardinality feedback is a simple yet elegant way of correcting cardinality
*> alter system set “_optimizer_use_feedback” = true