DBA Performence Tuning Tips
DBA Performence Tuning Tips:
========================
Latches are like locks for RAM memory structures to prevent concurrent access and ensure serial
execution of kernel code. The LRU (least recently used) latches are used when seeking, adding,
or removing a buffer from the buffer cache, an action that can only be done by one process at a
time Latches occur and removed automatically internally and fully managed by Oracle database. i.e.,
A latch is a low-level internal lock used by Oracle to protect memory structures
Column names should be provided instead of * in SELECT statements.
Joins should be used in the place of sub-queries.
EXISTS should be used instead of IN to verify the existence of data.
UNION ALL should be used in the place of UNION.
HAVING should be used only for filtering the resulted rows from the SQL query.
The column CLUSTER_WAIT_TIME in V$SQLAREA represents the wait time incurred by individual SQL
statements for global cache events and will identify the SQL which may need to be tuned.
My query was fine last week and now it is slow. Why?
The likely cause of this is because the execution plan has changed.
Generate a current explain plan of the offending query and compare it to a previous one that
was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:
Which tables are currently analyzed? Were they previously analyzed?
(ie. Was the query using RBO and now CBO?)
Has OPTIMIZER_MODE been changed in INIT.ORA?
Has the DEGREE of parallelism been defined/changed on any table?
Have the tables been re-analyzed? Were the tables analyzed using estimate or compute?
If estimate, what percentage was used?
Have the statistics changed?
Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
Have any other INIT.ORA parameters been changed?
What do you think the plan should be? Run the query with hints to see if this produces the
required performance.
The optimizer can study the distribution of data by collecting and storing optimizer statistics.
This enables intelligent execution plans. Choice of db_block_size, db_cache_size,
and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance.
Tuning SQL Access workload with physical indexes and materialized views.
How do you delete statistics of an object?
analyze table emp delete statistics
What is an optimizer?
Optimizer is a mechanizm which will make the execution plan of an sql statement
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
Column names should be provided instead of * in SELECT statements.
Joins should be used in the place of sub-queries.
EXISTS should be used instead of IN to verify the existence of data.
UNION ALL should be used in the place of UNION.
HAVING should be used only for filtering the resulted rows from the SQL query.
An explain plan is a statement that displays the execution plan selected by the Oracle optimizer
for SELECT, INSERT, UPDATE & DELETE statements. By looking at this plan, one can figure out if
Oracle is selecting right indexes, proper joins & sorts operations etc.
ANALYZE statement can be used to collect the statistics of various database objects like tables,
indexes, partitions, cluster or object references. Using this statement we can also identify
migrated as well as chained rows within a table or cluster.
How would you identify the SHARED_POOL_SIZE parameter that needs to be adjusted?
Getting ORA-04031 error.
Degrading the performance even when all the other parameters are already optimized.
Poor library cache/data dictionary hits.
Row Chaining?
When a row is too large that it cannot fit in a block, then it will end up using consequent
blocks which lead to the concept of Row Chaining. It can be avoided by updating the storage
parameters to an appropriate value.
How do you know what sql is currently being used by the session?
by goind v$sql and v$sql_area
How do you get the index of a table and on which column the index is?
dba_indexes and dba_ind_columns
How do you flush the shared pool?
alter system flush shared_pool
When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad, how do you correct it?
If you get excessive disk sorts this is bad. This indicates you need to tune the sort area
parameters in the initialization files. The major sort are parameter is the SORT_AREA_SIZe parameter.
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.
If you see contention for library caches how can you fix it?
Increase the size of the shared pool.
How can you tell if a tablespace has excessive fragmentation?
If a select against the dba_free_space table shows that the count of a tablespaces extents
is greater than the count of its data files, then it is fragmented.
Find out SQL id fo that query and using the query to find out query plan with timings, select SQL_ID,PLAN_HASH_VALUE, OPERATION, COST, TIMESTAMP from DBA_HIST_SQL_PLAN where SQL_ID='g9ks2772vgzdy'
========================
Latches are like locks for RAM memory structures to prevent concurrent access and ensure serial
execution of kernel code. The LRU (least recently used) latches are used when seeking, adding,
or removing a buffer from the buffer cache, an action that can only be done by one process at a
time Latches occur and removed automatically internally and fully managed by Oracle database. i.e.,
A latch is a low-level internal lock used by Oracle to protect memory structures
Column names should be provided instead of * in SELECT statements.
Joins should be used in the place of sub-queries.
EXISTS should be used instead of IN to verify the existence of data.
UNION ALL should be used in the place of UNION.
HAVING should be used only for filtering the resulted rows from the SQL query.
The column CLUSTER_WAIT_TIME in V$SQLAREA represents the wait time incurred by individual SQL
statements for global cache events and will identify the SQL which may need to be tuned.
My query was fine last week and now it is slow. Why?
The likely cause of this is because the execution plan has changed.
Generate a current explain plan of the offending query and compare it to a previous one that
was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:
Which tables are currently analyzed? Were they previously analyzed?
(ie. Was the query using RBO and now CBO?)
Has OPTIMIZER_MODE been changed in INIT.ORA?
Has the DEGREE of parallelism been defined/changed on any table?
Have the tables been re-analyzed? Were the tables analyzed using estimate or compute?
If estimate, what percentage was used?
Have the statistics changed?
Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
Have any other INIT.ORA parameters been changed?
What do you think the plan should be? Run the query with hints to see if this produces the
required performance.
The optimizer can study the distribution of data by collecting and storing optimizer statistics.
This enables intelligent execution plans. Choice of db_block_size, db_cache_size,
and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance.
Tuning SQL Access workload with physical indexes and materialized views.
How do you delete statistics of an object?
analyze table emp delete statistics
What is an optimizer?
Optimizer is a mechanizm which will make the execution plan of an sql statement
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
Column names should be provided instead of * in SELECT statements.
Joins should be used in the place of sub-queries.
EXISTS should be used instead of IN to verify the existence of data.
UNION ALL should be used in the place of UNION.
HAVING should be used only for filtering the resulted rows from the SQL query.
An explain plan is a statement that displays the execution plan selected by the Oracle optimizer
for SELECT, INSERT, UPDATE & DELETE statements. By looking at this plan, one can figure out if
Oracle is selecting right indexes, proper joins & sorts operations etc.
ANALYZE statement can be used to collect the statistics of various database objects like tables,
indexes, partitions, cluster or object references. Using this statement we can also identify
migrated as well as chained rows within a table or cluster.
How would you identify the SHARED_POOL_SIZE parameter that needs to be adjusted?
Getting ORA-04031 error.
Degrading the performance even when all the other parameters are already optimized.
Poor library cache/data dictionary hits.
Row Chaining?
When a row is too large that it cannot fit in a block, then it will end up using consequent
blocks which lead to the concept of Row Chaining. It can be avoided by updating the storage
parameters to an appropriate value.
How do you know what sql is currently being used by the session?
by goind v$sql and v$sql_area
How do you get the index of a table and on which column the index is?
dba_indexes and dba_ind_columns
How do you flush the shared pool?
alter system flush shared_pool
When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad, how do you correct it?
If you get excessive disk sorts this is bad. This indicates you need to tune the sort area
parameters in the initialization files. The major sort are parameter is the SORT_AREA_SIZe parameter.
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.
If you see contention for library caches how can you fix it?
Increase the size of the shared pool.
How can you tell if a tablespace has excessive fragmentation?
If a select against the dba_free_space table shows that the count of a tablespaces extents
is greater than the count of its data files, then it is fragmented.
Find out SQL id fo that query and using the query to find out query plan with timings, select SQL_ID,PLAN_HASH_VALUE, OPERATION, COST, TIMESTAMP from DBA_HIST_SQL_PLAN where SQL_ID='g9ks2772vgzdy'
Comments
Post a Comment