Feeds:
Posts
Comments

Posts Tagged ‘Redo log’

What is Latch?

Well, the hardware definition of latch is – a window or door lock. The electronic definition of latch is – an electronic circuit used to store information.

Then what is Latch in Oracle? Very uncommon as the problem is super sophisticated.

Latch is one kind of very quick (could be acquired and released in nanoseconds) lock or serialization mechanism (makes more sense) to protect Oracle’s shared memory in SGA. Basically latch protects the same area of SGA being updated by more than one process.

Now question comes, what are protected and why?

Each Oracle operation needs to read and update SGA. For example –

1. When a query reads a block from disk, it will modify a free block in buffer cache and adjust the buffer cache LRU chain
2. When a new SQL statement is parsed, it will be added to the library cache within SGA
3. When DML issued and modifications are made in blocks, changes are placed in redo buffer
4. Database writer periodically (after commit, after SCN change or after each 3 sec) writes buffers from memory to disk and updates their status from dirty to clean.
5. Redo log writer writes blocks from redo buffer to redo logs.

Latch prevents any of these operations from colliding and possibly corrupting the SGA.

If the specific latch is already in use by another process, oracle will retry very frequently with a cumulative delay up to certain times (controlled by hidden parameter) called spin count. First time one process fails to acquire the latch, it will attempt to awaken after 10 milliseconds up to its spin count. Subsequent waits will increase in duration – might be seconds in extreme cases. This affects response time and throughput.

Most common type latch is Cache Buffer Latch and Cache buffer LRU chain latch which are caused for highly accessing blocks, called hot blocks. Contention on these latches is typically caused by concurrent access to a very hot block. The most common type of such hot block is index root or block branch.

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Advertisements

Read Full Post »

What is Latch?

Well, the hardware definition of latch is – a window or door lock. The electronic definition of latch is – an electronic circuit used to store information.

Then what is Latch in Oracle? Very uncommon as the problem is super sophisticated.

Latch is one kind of very quick (could be acquired and released in nanoseconds) lock or serialization mechanism (makes more sense) to protect Oracle’s shared memory in SGA. Basically latch protects the same area of SGA being updated by more than one process.

Now question comes, what are protected and why?

Each Oracle operation needs to read and update SGA. For example –

1. When a query reads a block from disk, it will modify a free block in buffer cache and adjust the buffer cache LRU chain
2. When a new SQL statement is parsed, it will be added to the library cache within SGA
3. When DML issued and modifications are made in blocks, changes are placed in redo buffer
4. Database writer periodically (after commit, after SCN change or after each 3 sec) writes buffers from memory to disk and updates their status from dirty to clean.
5. Redo log writer writes blocks from redo buffer to redo logs.

Latch prevents any of these operations from colliding and possibly corrupting the SGA.

If the specific latch is already in use by another process, oracle will retry very frequently with a cumulative delay up to certain times (controlled by hidden parameter) called spin count. First time one process fails to acquire the latch, it will attempt to awaken after 10 milliseconds up to its spin count. Subsequent waits will increase in duration – might be seconds in extreme cases. This affects response time and throughput.

Most common type latch is Cache Buffer Latch and Cache buffer LRU chain latch which are caused for highly accessing blocks, called hot blocks. Contention on these latches is typically caused by concurrent access to a very hot block. The most common type of such hot block is index root or block branch.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

* Create an index if you frequently want to retrieve less than 15% of the rows in a large table.
* To improve performance on joins of multiple tables, index columns used for joins.

* Small tables do not require indexes.

Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:

* Values are relatively unique in the column.
* There is a wide range of values (good for regular indexes).
* There is a small range of values (good for bitmap indexes).
* The column contains many nulls, but queries often select all rows having a value. In this case, use the following phrase:

WHERE COL_X > -9.99 * power(10,125)

Using the preceding phrase is preferable to:

WHERE COL_X IS NOT NULL

This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).

Columns with the following characteristics are less suitable for indexing:

* There are many nulls in the column and you do not search on the not null values.

The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block.

Other Considerations:

1. The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first.If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also speeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.

2. There is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.

3. Drop Index that are no longer required.

4. Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace. Disk contention is reduced.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

————————————
DATABASE PERFORMANCE
————————————-
FREQUENTLY ASKED QUESTIONS
———————————————-
QUESTIONS
—————
1. What are the prioritized tuning steps to implement a database with optimal performance?
2. What are the tools that can be used to monitor database performance?
3. Who should be involved in the database tuning process?
4. What are the major tuning areas in database performance tuning?
5. How to identify which tuning area contributes to the database performance problem?
6. How to improve the identified poor performing area?
7. Where can one find the descriptions of v$ views and Wait Events?
8. What is the appropriate size of SGA?
9. How to analyze and interpret results from utlbstat/utlestat or statspack?
10. Why does the database performance change after upgrade or migration?
11. Where can one learn more about database performance tuning?

ANSWERS
————–

1. What are the prioritized tuning steps to implement a database with optimal performance?

Proactive tuning during the database design and development stage is the most effective way to achieve optimal
database performance. The following list has been ordered according to their general return on effort.

– Tune the business rules.
– Tune the data design
– Tune the application design.
– Tune the logical structure of the database.
– Tune the database operations.
– Tune the access paths.
– Tune memory allocation.
– Tune the I/O and physical structure.
– Tune the resource contention.
– Tune the underlying platform(s).

The reactive tuning approach is driven by identifying the most significant bottleneck and making the appropriate
changes to reduce or eliminate the effect of that bottleneck It requires to gather statistical information to detect
where the bottleneck is taking place and then apply corrective actions. It is recommended that changes be made
to a system only after you have confirmed that there is a bottleneck. The detailed steps are described in the section:
How to identify which tuning area contributes to the database performance problem

2. What are the tools that can be used to monitor database performance?

The database has a complete set of statistics and mechanisms to alert when problems are occurring.
Gathering information to tune an Oracle database can be obtained with the following tools and database resources:

– Alert log and trace files: The first step in detecting a performance problem is searching for errors or warnings
issued by the database. These files keep track of this information.
– V$ views: Database statistical information is stored in the V$ Views.
– Utlbstat/utlestat and STATSPACK (available from 8.1.6): These tools bundled with the Oracle Server
generate complete reports of the database activity. The new STATSPACK utility bundled with Oracle 8.1.6
and above provides more flexibility in managing statistical snapshots.
– OEM Performance Pack. The Performance pack offers a complete set of graphical tools to monitor the
performance of the database.

3. Who should be involved in the database tuning process?

The tuning process is usually viewed as a task of the database administrator. Ideally, the database tuning process
should involve database administrators, system administrators, application developers, and end users.
Involving these resources, will ensure to set performance targets and user expectations according with the business
needs and available resources.

4. What are the major tuning areas in database performance tuning?

– Memory – shared pool, large pool, buffer cache, redo log buffer, and sort area size.
– I/O – distributing I/O, striping, multiple DBWn processes, and DBWn I/O slaves.
– CPU – CPU utilization.
– Space management – extent allocation and Oracle block efficiency.
– Redo log and checkpoint – redo log file configuration, redo entries, and checkpoint.
– Rollback segment – sizing rollback segments.
– Network

5. How to identify which tuning area contributes to the database performance problem?

– Gather Database statistical information using STATSPACK or UTLSTAT reports when
the performance is both good and bad.
– Obtain operating system and application statistical information using the same approach.
Oracle relies on the hardware capacity and when the system resources are permanently
busy you can be facing a capacity problem.
– Then Examine the Host System and Oracle Statistics for any evidence.
– Define actions to solve bottlenecks prioritizing those actions that will impact the most
the performance of the application/database. Some simple actions can improve dramatically
the performance, so consider to implement them first. (See Note:148373.1)

6. How to improve the identified poor performing area?

Although each tuning area requires its specific tuning methods, the underlying tuning methodology for all areas is
the same. The general tuning process can be divided into the following steps:

– Set a realistic and quantitative performance target. The target should match the business rules and users’
expectations, but also be bounded by available resources of the system.
– Change one thing at a time. Evaluating the effect from manipulating one variable at a time is more efficient
than manipulating more than one variable at the same time, especially when there could be more than one
factor contributing to the performance problem.
– Evaluate the effect of change. Examine the new results from utlbstat/utlestat or statspack.
Is there any improvement from the change?
> If there is improvement resulting from the change, more change to the same factor can be made.
> If there is no improvement resulting from the change, the change needs to be reversed, and go back to
Step 2 to change another factor.
– Stop when the performance target is reached.

Multiple cycles of the tuning process may be needed to reach the final performance target.

7. Where can one find the descriptions of v$ views and wait events?

8. What is the appropriate size of SGA?

The appropriate size of SGA is system dependent, and it is limited by the available system resources. For optimal
performance, SGA should fit into real memory avoiding the need for swapping. When sizing the SGA consider to
tune all memory structures (the shared pool, the buffer cache and the redolog buffer cache) together so you can
assign appropriate resources to each according with the database requirements.

9. How to analyze and interpret results from utlbstat/utlestat or statspack?

10. Why does the database performance change after an upgrade or migration?

Database performance tuning is an ongoing process throughout the life of a database. Any changes to the system
could disrupt the balance of a previously well-tuned database. Database upgrades or migrations usually come with
a great deal of changes to the system, so one expects to see some changes to the database performance. It is best to
perform intensive testing on a development database before upgrading or migrating the production database.

11. Where can one learn more about database performance tuning?

– Oracle 7/8/8i Designing and Tuning for Performance Manual – Note 152140.1
– Oracle9i Database Performance Methods Manual
– Oracle9i Database Performance Guide and Reference
– Oracle Education Instructor-Led Training, Enterprise DBA Part 2: Performance Tuning.

Read Full Post »

set feedback off
set termout on
set pagesize 56
set linesize 800
ttitle off
spool out.log

set heading off
set verify off
column today NEW_VALUE p_currdate noprint
select TO_CHAR(SYSDATE,’fmMonth ddth, yyyy’) today from dual;
clear breaks
clear computes
clear columns
set heading off
column name heading ” justify center format a74
select ‘Database Name (SID): ‘ || name “name” from v$database;
prompt
prompt
prompt Version/SGA Information:
set heading off
select * from v$version;
column sgatot justify left format 9,999,999,999
select ‘Total System Global Area as of &p_currdate.:’ hd1, sum(value) sgatot, ‘bytes’ hd2 from v$sga;

— ************* TABLESPACES/DATAFILES **************

prompt
prompt
prompt Tablespaces and Datafiles:
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading ‘Tablespace’ justify left format a15
column file_id heading ‘File|ID’ justify center format 9999
column file_name heading ‘Datafile’ justify center format a60 word_wrapped
column size Heading ‘Size|in MB.’ justify center format 9,99990.99
break on tablespace_name skip 1 on report skip 2
compute sum label ‘TS SIZE:’ of size on tablespace_name
compute sum label ‘DB SIZE:’ of size on report
select tablespace_name,file_id,file_name,bytes/1024/1024 “size” from dba_data_files order by tablespace_name, file_id, file_name;

— ************* SPACE USAGE **************

prompt Space usage (as of &p_currdate.):
prompt
clear breaks
clear computes
clear columns
set heading on
column tspace heading ‘Tablespace’ justify left format a20 truncated
column tot_ts_size heading ‘Size|in MB.’ justify left format 9,99999999990
column free_ts_size heading ‘Used|in MB.’ justify right format 9,9999999990
column tbusedpct heading ” justify left format a6
column tbfreepct heading ” justify left format a6
break on report
compute sum label ‘Totals:’ of tot_ts_size free_ts_size on report
col tspace form a25 Heading “Tablespace”
col tot_ts_size form 99999999999999 Heading “Size (Mb)”
col free_ts_size form 99999999999999 Heading “Free (Mb)”
col ts_pct form 999 Heading “% Free”
col ts_pct1 form 999 Heading “% Used”

SELECT df.tablespace_name tspace,df.bytes/(1024*1024) tot_ts_size,
sum(fs.bytes)/(1024*1024) free_ts_size,round(sum(fs.bytes)*100/df.bytes) ts_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1
FROM dba_free_space fs,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df
WHERE fs.tablespace_name = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes;

— ************* REDO LOG FILES **************

prompt
prompt
prompt Online Redo Logfiles:
clear breaks
clear computes
clear columns
column member heading ‘Logfile’ justify center format a60 word_wrapped
column group heading ‘Group|Number’ justify center format 9999
column size heading ‘Size|in MB.’ justify center format 999990.99
select f.member “member”,f.group# “group”,l.bytes/1024/1024 “size” from v$logfile f, v$log l where f.group#=l.group# order by f.group#,f.member;

— ************* CONTROL FILES ************** —
prompt
prompt
prompt Control files:
clear breaks
clear computes
clear columns
column name heading ‘File Name’ format a60 word_wrapped
select name from v$controlfile;

— ************* ROLLBACK SEGMENTS ************** —
prompt
prompt
prompt Rollback Segments (sizes as of &p_currdate.):
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading ‘Tablespace’ justify left format a15 truncated
column segment_name heading ‘Seg|Name’ justify center format a7
column status heading ‘Status’ justify center format a8
column initial_extent heading ‘Initial|(in M)’ justify center format 99990.9
column next_extent heading ‘Next|(in M)’ justify center format 99990.9
column min_extents heading ‘Min|Ext’ justify center format 99990
column max_extents heading ‘Max|Ext’ justify center format 999999999990
column pct_increase heading ‘Pct|Inc’ justify center format 99990
column rbsize heading ‘Curr Size|(in M)’ justify left format 9,99990
break on tablespace_name skip 1 on report skip 2
select r.tablespace_name,r.segment_name,r.status,
r.initial_extent/1024/1024 “initial_extent”,
r.next_extent/1024/1024 “next_extent”,
r.min_extents,r.max_extents,
r.pct_increase,
sum(e.bytes)/1024/1024 “rbsize”
from dba_rollback_segs r, dba_extents e
where e.segment_name = r.segment_name
group by r.tablespace_name, r.segment_name, r.status,
r.initial_extent/1024, r.next_extent/1024,
r.min_extents, r.max_extents, r.pct_increase;


— ************* PARAMETERS **************

prompt
prompt Parameters (non-defaults):
clear breaks
clear computes
clear columns
column name heading ‘Name’ format a35 word_wrapped
column pvalue heading ‘Value’ format a50 word_wrapped
select name, rtrim(value) “pvalue” from v$parameter order by name;


— ************* USER DETAILS **************

prompt
prompt user details :
clear breaks
clear computes
clear columns
select username,profile,default_tablespace,temporary_tablespace from dba_users;
select * from nls_database_parameters;
select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
select owner,object_type,count(1) from dba_objects group by owner,object_type order by owner;
SELECT file_name,tablespace_name,autoextensible,maxbytes/1048576 FROM dba_data_files;

Read Full Post »

If we are handling any new database for first time then these scripts are useful.Provide the initial required details of database.

set pagesize 500
set linesize 130
Prompt
Prompt Control Files Location >>>>
col name format a60 heading “Control Files”

select name
from sys.v_$controlfile
/

Prompt
Prompt Redo Log File Locations >>>>
Prompt

col Grp format 9999
col member format a50 heading “Online REDO Logs”
col File# format 9999
col name format a50 heading “Online REDO Logs”
break on Grp
select group#,member
from sys.v_$logfile
/

Prompt Data Files Locations >>>>

col Tspace format a25
col status format a3 heading Sta
col Id format 9999
col Mbyte format 999999999
col name format a50 heading “Database Data Files”
col Reads format 99,999,999
col Writes format 99,999,999

break on report
compute sum label ‘Total(MB)’ of Mbyte on report

select F.file_id Id,
F.file_name name,
F.bytes/(1024*1024) Mbyte,
decode(F.status,’AVAILABLE’,’OK’,F.status) status,
F.tablespace_name Tspace
from sys.dba_data_files F
order by tablespace_name;

Read Full Post »