Oracle: wait events
log file sync: is a client wait event. It is the wait event your clients wait on when they say "commit". It is the wait for LGWR to actually write their redo to disk and return back to them. You can "tune" this by making lgwr faster (no raid 5 for example) and committing less frequently and generating less redo (BULK operations generate less redo than row by row do)
LGWR wait for redo copy: is a background wait. LGWR is waiting for forgrounds to finish a current copy affecting the data LGWR is about to process.
enqueue: is your wait and that is all about application design - those are heavyweight locks induced by the application logic itself. You would be best served by looking at the *application* not at the "system" at this point.
do a level 12 10046 trace event on your application and see what it is truly waiting for. it is all about tuning the application.
Oracle: v$bh - who is using buffer cache?
column c0 heading 'Owner' format a15
column c1 heading 'ObjectName' format a30
column c2 heading 'NumberofBuffers' format 999,999
column c3 heading 'Percentageof DataBuffer' format 999,999,999
select
owner c0,
object_name c1,
count(1) c2,
(count(1)/(select count(*) from v$bh)) *100 c3
from
dba_objects o,
v$bh bh
where
o.object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM','AURORA$JIS$UTILITY$')
group by
owner,
object_name
order by
count(1) desc
;
Oracle: Bulk Collect
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
emp_cv EmpCurTyp;
empnos NumList;
enames NameList;
sals NumList;
BEGIN
OPEN emp_cv FOR 'SELECT empno, ename FROM emp';
FETCH emp_cv BULK COLLECT INTO empnos, enames;
CLOSE emp_cv;
EXECUTE IMMEDIATE 'SELECT sal FROM emp'
BULK COLLECT INTO sals;
END;
--- only insert/update/delete can have output bind variables.
--- to bulk-bind them, use BULK RETURNING INTO clause in an EXECUTE IMMEDIATE.
DECLARE
TYPE NameList IS TABLE OF VARCHAR2(15);
enames NameList;
bonus_amt NUMBER := 500;
sql_stmt VARCHAR(200);
BEGIN
sql_stmt := 'UPDATE emp SET bonus = :1 RETURNING ename INTO :2';
EXECUTE IMMEDIATE sql_stmt
USING bonus_amt RETURNING BULK COLLECT INTO enames;
END;
Oracle: binding variable or non-binding variable
No-bind
--------------
set timing on
declare
x number;
begin
for i in 1 .. 50000 loop
execute immediate
'select count(*) from dual where dummy = 'i into x;
end loop;
end;
with-bind
---------------
set timing on
declare
x number;
begin
for i in 1 .. 50000 loop
select count(*) into x from dual where dummy = i;
end loop;
end;
or
select count(*) into x from dual where dummy = :i using i;
Oracle: DBMS_SYSTEM Package
1) ksdwrt
exec dbms_system.ksdwrt(n, message);
"n" indicates the destination
1 - write to trace file
2 - write to alert log
3 - write to both
2) set_sql_trace_in_session
exec dbms_system.set_sql_trace_in_session(sid, serial#, true);
3) set_ev
exec dbms_system.set_ev(sid,serial#, event, level, name);
exec dbms_system.set_ev(31, 97, 10046, 4, '');
level indicates:
1 - standard sql_trace functionality.
4 - as level 1 plus tracing of bind variables
8 - as level 1 plus wait events
12 - as level1 plus bind variables and wait events.
4) read_ev
exec dbms_system.read_ev(event,output);
if output = 1 the event is being traced.
Oracle: time/date operation
1. date interval
select sysdate, sysdate - interval '7' MINUTE from dual
select sysdate, sysdate - interval '7' day from dual
select sysdate, sysdate - 8*interval '2' hour from dual
2. date to char conversion
select sysdate, to_char(sysdate, 'yyy-mm-dd hh24:mi:ss') from dual
3. char to date conversion
select to_date('2005-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
4. trunk/round functions
select trunc(sysdate,'YEAR') from dual
5. using mili-second date type
select to_char(current_timestamp(9),'HH24:MI:SSxFF') from dual
6. calculate process runtime
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
begin
for i in 1 .. 1000
loop
open l_rc for 'select object_name from all_objects '
'where object_id = ' i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
(round( ( dbms_utility.get_time-l_start)/100, 2) ' seconds...');
end;
Oracle: how to skip bad block
First thing you need to do is find the bad block ids. To do this you can run dbverify. Lets assume our block id is 1234 .
Now find the segment name by running this query
select segment_name, segment_type, block_id, blocks from dba_extents where (1234 between block_id and (block_id + blocks - 1))
Once you have the bad segment name, its better to create a temporary table with good data if the segment is a table. If the segment is an index, drop the index and recreate it.
create table good_table as select * from bad_table where rowid not in ( select /*+index(bad_table, any_index)*/ rowid from bad_table where substr( rowid, 1, 8 ) = 1234)
Oracle: Oradebug - view bind value
1) get sid
select username,sid,serial#
from v$session
where audsid = userenv('sessionid');
2) oradebug setorapid 15
oradebug EVENT 10046 trace name context forever,level 12
Java: GC tunning
http://www.petefreitag.com/articles/gctuning/3.2 The Young Generation
The bigger the young generation the less minor GC's, but this implies a smaller tenured generation which increases the frequency of major collections.
You need to look at your application and determine how long your objects live for to tune this.
-XX:NewRatio=3 - the young generation will occupy 1/4 the overall heap
-XX:NewSize - Size of the young generation at JVM init. Calculated automatically if you specify -XX:NewRatio
-XX:MaxNewSize - The largest size the young generation can grow to (unlimited if this value is not specified at command line)
4 Types of Collectors
Everything to this point talks about the default garbage collector, there are other GC's you can use
Throughput Collector - Uses a parallel version of the young generation collector
-XX:+UseParallelGC
Tenured collector is the same as in default
Concurrent Low Pause Collector
Collects tenured collection concurrently with the execution of the app.
The app is paused for short periods during collection
-XX:+UseConcMarkSweepGC
To enable a parallel young generation GC with the concurrent GC add -XX:+UseParNewGC to the startup. Don't add -XX:+UseParallelGC with this option.
Incremental Low Pause Collector
Sometimes called Train Collector
Collects a portion of the tenured generation at each minor collection.
Tries to minimize large pause of major collections
Slower than the default collector when considering overall throughput
Good for client apps (my observation)
-Xincgc
Don't mix these options, JVM may not behave as expected.
Oracle: Runtime Row Source Statistics
9iR2 introduces dynamic performance table: v$sql_plan_statistics.
it provides for each cached cursor the execution statistics of
each operation in the execution plan.
to use this view, parameter STATISTICS_LEVEL must be set to ALL.
additional information, see
v$sql_plan
v$sql_plan_statistics_all
v$sql_workarea ( contains memory usage statistics for row sources that use sql memory, for instance, hash-join and sort)
• v$sql_plan - This view shows the same information as shown by Explain Plan except it is the actual execution plan and not the predicted one – just like tkprof and even better than Explain Plan.
• v$sql_plan_statistics - This view contains the execution statistics for each operation (step) in the v$sql_plan. Queries should access this view and look for poor SQL operations including TABLE ACCESS FULL – full-table scans.
• v$sql_plan_statistics_all - This view combines data from v$sql_plan, v$sql_plan_statistics and v$sql_workarea.
Both v$sql_plan_statistics and v$sql_plan_statistics_all are not populated by default. The option statistics_level=all must be set.
Or turn on sql_trace will also populates these views.
Oracle: 9i segment level statistics
9i release 2 and higher,
segment statistics could be retrieved from following views.
v$segment_statistics
v$segstat
v$segstat_name
select owner, object_name,statistic_name,value
from v$segment_statistics
where owner='SYSTEM'
and object_name='TABLE'
