RE: Checkpoint not complete error
- From: "Sweetser, Joe" <JSweetser@xxxxxxxx>
- To: <saibabu_d@xxxxxxxxx>, "free" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Apr 2009 09:18:25 -0600
Nice. I like this. And a quick decode statement will help with the
command type.
-joe
select parsing_user_id,
DECODE(command_type,
2, 'INSERT',
3, 'SELECT',
6, 'UPDATE',
7, 'DELETE',
26, 'LOCK',
42, 'DDL',
44, 'COMMIT',
47, 'PL/SQL BLOCK',
command_type) CommandType, sum(executions), sum(rows_processed) from
V$sql where command_type not in (0, 3) group by parsing_user_id,
command_type order by 4;
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Saibabu Devabhaktuni
Sent: Wednesday, April 29, 2009 9:13 AM
To: free
Subject: Re: Checkpoint not complete error
It is better to start with understanding which job is causing high redo
generation and optimize that job before making changes to the redo log
file size, checkpoint related init parameters, etc.
select parsing_user_id, command_type, sum(executions),
sum(rows_processed) from V$sql where command_type not in (0, 3) group by
parsing_user_id, command_type order by 4;
Above query can give you high level view about amount and frequency of
changes happening on your system.
things to check:
1) Check if you have any LOBS and DML's on LOBS, if their data is not
needed on DR standby, make them nocache and nologging
2) Check if you have any batch jobs doing mass inserts or deletes, if
so, look into the possibility of dropping index, DML, and creating
index.
3) If you are creating any temporary or staging tables, check if they
can be made nologging or global temporary tables.
4) Convert statements like, delete * from emp, to truncate table emp;
......etc..
You can also look into top waitevents, top sql, etc from awr report and
go from there.
Thanks,
Sai
http://sai-oracle.blogspot.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
