Difference between SCN and checkpoint.
SCN and checkpoint:
System change number(SCN) is represented with SCN_WRAP and SCN_BASE. Whenever SCN_BASE reaches 4294967290 (2 power 32), SCN_WRAP goes up by one and SCN_BASE will be reset to 0. This way you can have a maximum SCN at 1.8E+19.
SCN = (SCN_WRAP * 4294967290) + SCN_BASE
Checkpoint number is the SCN number at which all the dirty buffers are written to the disk, there can be a checkpoint at object/tablespace/datafile/database level.
Checkpoint number is never updated for the datafiles of readonly tablespaces.
Oracle doesn't use any sequence to generate SCN_BASE numbers, it make calls to "kcmgas" function (per Steve Adams), which is a permanent memory structure for the instance. It is reported in v$sysstat under "calls to kcmgas" name.
Does Oracle do either crash recovery or transaction recovery after shutdown abort if the checkpoint was taken right before the instance crash?
Yes, Oracle perform rollforward first if there are any changes beyond last checkpoint and rollback any uncommitted transactions.
SCN numbers are being reported at frequent intervals by SMON in "SMON_SCN_TIME" table.
You can also query v$transaction to arrive at the SCN for that transaction.
Controlfile records information about last checkpoint and archived sequence along with other information.
What is "index scans kdiixs1 " statistic?
Statistic "index scans kdiixs1"On Oracle 9.2.0.5:
stat# 206 -> Index fetch by key
stat# 207 -> index scans kdiixs1
Index fetch by key:
This statistic will be incremented for each "INDEX (UNIQUE SCAN)". This also applies to all DML statements which has "INDEX(UNIQUE SCAN)" in the execution plan.
Index scans kdiixs1:
This statistic is incremented for each index range scan operation, except for index fat full scans, index full scan, and index unique scan.
Both of the above statistics will be incremented for index lookups against recursive statements also.
create table t(a number not null, b number);
create index t_idx1 on t(a):
create unique index t_idx2 on t(b);
analyze table t compute statistics;
insert into t select rownum, rownum+50000 from dba_objects;
23:29:56 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:29:56 2 /
SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 66
23:29:56 SQL> select count(*) from t where a=17;
COUNT(*)
----------
1
23:30:16 SQL> @t
23:30:17 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:30:17 2 /
SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 67
23:30:17 SQL> select a.a, b.a from t a, t b where a.a=b.a and a.a=100;
A A
---------- ----------
100 100
23:30:24 SQL> @t
23:30:26 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:30:26 2 /
SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 69
23:30:26 SQL> select a.a, b.a from t a, t b where a.a=b.a and a.a=100;
A A
---------- ----------
100 100
23:30:29 SQL> @t
23:30:31 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:30:31 2 /
SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 71
How to predict Index blevel and when to rebuild.
Determining Index blevel'sDepth of an index(blevel plus one) would determine the minimum number of consistent gets required for Index lookups in any joins and in index range scans. In nested loop joins, Oracle would always read index root block into process PGA memory and hence one less block to read from an index in every loop iteration. For queries like "where x between :a and :b" Oracle would always do leaf block level walk throughs, hence caching any index blocks is not required here.
So, index blevel is one of the key indicators of performance of sql queries doing Index range scans.
Index depth is one of the prime factor involved in determining whether to rebuild an index. In most cases, you wouldn't need to rebuild any index. You may consider doing it when you have one off major delete/update done on that table.
You can more or less predict when would blevel of an index would go from one level to another level. If you have right hand index it is very easy to predict blevel, otherwise it becomes very hard. Here is a simple test case.
create table t(a number);
create index t_idx on t(a);
insert into t select rownum from all_objects where rownum <= 500;
analyze index t_idx compute statistics;
select blevel from user_indexes where index_name='T_IDX';
You would see around 540 rows to fit in one Index 8K blocksize. So, Index blevel would change when the number of index records reach power(540, blevel). Bear in mind that the number of indexed columns, their datatype, and the column size would dictate the number of records fit in one index block.
Blevel #Records
0 540
1 29K
2 150 million
3 8.5 billion
See the difference in number of records from blevel 2 to 3, that is the reason why you would hardly see index blevel going beyond 3.
If you have one off major deletes/updates done on your table, then consider rebuilding the affected indexes on that table. It all depends on the way you access and modify key values of that index.
Weirdness of v$datafile on a standby db.
Here are some oddities of v$datafile on a standbyOracle gets content of v$datafile from controlfile, but on a standby database "STATUS" column wouldn't get updated in some scenarios. All datafiles created after the standby controlfile creation timestamp would get "RECOVER" status as they get pushed to the standby database. But how to find out if the datafile is offline or online?
V$datafile_header is the answer. Whenever we query v$datafile_header, Oracle would visit all datafile header blocks, and it gives us the right "STATUS" of datafiles.
What you see in v$datafile for Checkpoint_change# and Checkpoint_time may not be correct if you refresh that datafile from the primary and the recovery on those datafiles haven't started yet, and here also v$datafile_header is an answer.
If a datafile is offline, it's tablespace number will be "0" in v$datafile_header, and you need to use v$datafile for this purpose.
Also, Unrecoverable_change# and Unrecoverable_time columns would never get updated in v$datafile on any standby database.
How do you know where to start for the recovery, you can in general use v$recover_file. But on a standby database, you may not see entry for some datafiles in v$recover_file, and v$datafile_header is an answer here also.
You may occasionally get ORA-235 "controlfile fixed table inconsistent due to concurrent update" while querying v$datafile, then again you can get most of that information with more correctness from v$datafile_header.
You may sometimes find using rule hint may help your query run faster against v$datafile, but try it only if you get ORA-235 error.
How to split a table into rowid ranges.
This script helps immensely to copy any table with "out of row" lob columns in it across dblink. This can also be used for CTAS over dblinks, any parallel insert/delete/update operations.
This is not a duplicate to what Oracle parallel query option does, it basically fills in the gaps of Oracle parallel query option.
For example:
Out of row lobs takes hell lot of time to be copied over to another table, Oracle parallel query doesn’t help here across dblinks.
You can’t get parallel queries on both source and target tables of CTAS operation across db links.
Good amount of limitations on Oracle parallel DML operations.
By using this script you can split any table into up to 255 rowid ranges.
-- This script spits desired number of rowid ranges to be used for any parallel operations.
-- Best to use it for copying a huge table with out of row lob columns in it or CTAS/copy the data over db links.
-- This can also be used to simulate parallel insert/update/delete operations.
-- Maximum number of rowid ranges you can get here is 255.
-- Doesn't work for partitioned tables, but with minor changes it can be adopted easily.
-- Doesn't display any output if the total table blocks are less than rowid ranges times 128.
-- It can split a table into more ranges than the number of extents
set verify off
undefine rowid_ranges
undefine seg_name
undefine owner
set head off
set pages 0
set trimspool on
select decode(rn3, &&rowid_ranges, 'rowid >= ''', 'rowid between ''')||
sys.dbms_rowid.rowid_create(1, id, file_id, new_bid, 0)||
decode(rn3, &&rowid_ranges, '''', ''' and ''')||
decode(rn3, &&rowid_ranges, null, sys.dbms_rowid.rowid_create(1, id, fid2, bid2-1, 99999))||
decode(rn3, &&rowid_ranges, null, '''') from (select id, file_id, new_bid, rn3, lead(file_id) over (order by rn3) fid2,
lead(new_bid) over (order by rn3) bid2
from
(select file_id, block_id, rn3,
round(block_id + (equi_blks * (rn5 - 1))) new_bid, id
from
(select file_id, block_id, equi_blks, rn3,
row_number() over (partition by rn1 order by rn3) rn5
from
(select file_id, block_id, equi_blks, rn1, rn3,
row_number() over (partition by rn3 order by rn1) rn4
from
(select file_id, block_id, sum2, equi_blks, rn1
from
(select file_id, block_id, sum2, rn1, equi_blks,
row_number() over (partition by rn1 order by sum2) rn2
from
(select file_id, block_id, sum1, sum2, (sum1/&&rowid_ranges) equi_blks,
trunc((sum2 * &&rowid_ranges)/(sum1 + 1))+1 rn1
from
(select relative_fno file_id, block_id, sum(blocks) over () sum1,
sum(blocks) over (order by relative_fno, block_id) sum2
from dba_extents
where
segment_name = upper('&&seg_name') and
owner = upper('&&amp;amp;owner') and
256 > &&rowid_ranges
)
where
sum1 > (128 * &&rowid_ranges)
)
)
where rn2 = 1
) a,
(select rownum rn3 from (select 1 from dual group by cube (1, 1, 1, 1, 1, 1, 1, 1))
where rownum <= &&rowid_ranges ) b where b.rn3 <= a.rn1 ) where rn4 = 1 ) c, (select max(data_object_id) id from dba_objects where object_name = upper('&seg_name') and owner = upper('&owner') and data_object_id is not null ) d ) ) /
-------------------------> End of the sql script
-------------------------> <-----------------------
--- Update on April 28th 2009, above script has a bug, please use the corrected script below:
select
'rowid between '''||sys.dbms_rowid.rowid_create(1, d.oid, c.fid1, c.bid1, 0)||''' and '''||
sys.dbms_rowid.rowid_create(1, d.oid, c.fid2, c.bid2, 9999)||''''
from
(select
distinct b.rn,
first_value(a.fid) over (partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid1,
last_value(a.fid) over (partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid2,
first_value(decode(sign(range2-range1), 1, a.bid+((b.rn-a.range1)*a.chunks1), a.bid)) over
(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid1,
last_value(decode(sign(range2-range1), 1, a.bid+((b.rn-a.range1+1)*a.chunks1)-1, (a.bid+a.blocks-1))) over
(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid2
from
(select
fid,
bid,
blocks,
chunks1,
trunc((sum2-blocks+1-0.1)/chunks1) range1,
trunc((sum2-0.1)/chunks1) range2
from
(select /*+ rule */
relative_fno fid,
block_id bid,
blocks,
sum(blocks) over () sum1,
trunc((sum(blocks) over ())/&&rowid_ranges) chunks1,
sum(blocks) over (order by relative_fno, block_id) sum2
from dba_extents
where
segment_name = upper('&&segment_name') and
owner = upper('&&owner')
)
where
sum1 > &&rowid_ranges
) a,
(select rownum-1 rn from dual connect by level <= &&rowid_ranges) b
where
b.rn between a.range1 and a.range2
) c,
(select max(data_object_id) oid from dba_objects
where object_name = upper('&&segment_name') and owner = upper('&&owner') and data_object_id is not null
) d
/
Quick summary of locks and skip v$lock.
Lock summary without accessing v$lock/*
Script to report summary of lock activity by waiters on the database.
OBJ -> Table: waitevent: p1: p2: locktype: ROWID.
CMD -> Type of command being waited on.
SES_CNT -> Number of sessions waiting.
HASH -> Hash value being waited on.
MODULE -> Module they are coming from.
Written by Sai */
col obj for a70
col cmd for a19
col module for a15
set lines 160
set trimspool on
select obj, cmd, count(*) as ses_cnt, min(hash_value) hash, module
from
(select decode(sign(ROW_WAIT_OBJ#), 1,
(select object_name from dba_objects where object_id=ROW_WAIT_OBJ# and data_object_id is not null)
,-1, 'UNKNOWN', 'UNKNOWN') || ':' ||
w.event || ':' || w.p2 || ':' || w.p3 || ':' ||
chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1, 16711680)/65535) || ':''' ||
decode(sign(ROW_WAIT_OBJ#), 1, dbms_rowid.rowid_create(1, (select data_object_id from dba_objects
where object_id = ROW_WAIT_OBJ# and data_object_id is not null),
ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#), 'No rowid') || '''' as obj,
decode(command, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', 182, 'UPDATE INDEXES', 3, 'SELECT FOR UPDATE', command) as cmd,
decode(sign(sql_hash_value), 1, sql_hash_value, prev_hash_value) hash_value,
module
from
v$session s, v$session_wait w
where
status = 'ACTIVE' and
lockwait is not null and
s.sid = w.sid)
group by obj, cmd, module
order by 3, 1
/
