use of db_file_multiblock_read_count parameter in oracle

db_file_multiblock_read_count parameter in oracle
================================================


"Multiple block sizes were implemented to transport tablespaces."


That is what they primarily were implemented to facilitate it .

--------------------------------------------------

Questions:-
===================

1) We have LOB column tables and I see lot of chainig/migration.
If we move the LOB column tables to a bigger blocksize tablespace.
Will that not reduce the migration?
2) I think the The max block size possible is 32k on 9i.
How do reduce the migration for such big column tables as average
row length is more than 100k?

Ans:-

answer from Tom katys :
===================

1) lob columns are stored inline ONLY if they are 4000 bytes or less.
If they are bigger than a varchar2 - the are automagically moved out
of line into the lob segment.

It could be that a simple alter table t move with a larger pctfree
to set aside sufficient space for updates is what is needed
(and that moving to a larger blocksize without changing pctfree
would not *fix a darn thing*).

If you are using lobs - I feel pretty confident in stating
"the average row length is NOT 100K!!!!",
the lobs are stored OUT OF LINE, in the lob segment,
when they exceed 4000 bytes.

(and if they were 100k, just give up, I don't care what your
block size is, it won't matter)


to move indexes to a tablespace of 16k, helps me to keep memory
only for indexes, and control better the index memory usage.
For me (a small database) makes sense.

Consider (if ANYTHING) the keep and recycle pools, but
using multiple block sizes for "tuning" isn't really something recommended.


I've not advised to use multiple blocksizes, use the keep and recycle pools
if you want to segregate things out.
use keep and recycle pools if you want multiple buffer caches

ROW_NUMBER, RANK, DENSE_RANK IN ORACLE

Top-n Query or 3th highest row by sql quiery in oracle .

Using ROW_NUMBER, RANK, DENSE_RANK

=====================================

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 13 13:06:27 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott@test107
Enter password:
Connected.
SQL>
SQL>
SQL> break on deptno skip 1
SQL>
SQL> select deptno, ename,sal,
2 row_number()
3 over (partition by deptno
4 order by sal desc)rn,
5 rank()
6 over (partition by deptno
7 order by sal desc)rnk,
8 dense_rank()
9 over (partition by deptno
10 order by sal desc)drnk
11 from emp
12 order by deptno, sal desc
13 /

DEPTNO ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ---------- ----------
10 KING 5000 1 1 1
CLARK 2450 2 2 2
MILLER 1300 3 3 3

20 SCOTT 3000 1 1 1
FORD 3000 2 1 1
JONES 2975 3 3 2
ADAMS 1100 4 4 3
SMITH 1000 5 5 4

30 BLAKE 2850 1 1 1

DEPTNO ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ---------- ----------
30 ALLEN 1600 2 2 2
TURNER 1500 3 3 3
MARTIN 1250 4 4 4
WARD 1250 5 4 4
JAMES 950 6 6 5


14 rows selected.

SQL>
SQL> select ename,sal,
2 row_number()
3 over (order by sal desc)rn,
4 rank()
5 over (order by sal desc)rnk,
6 dense_rank()
7 over (order by sal desc)drnk
8 from emp
9 order by sal desc
10 /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
KING 5000 1 1 1
FORD 3000 2 2 2
SCOTT 3000 3 2 2
JONES 2975 4 4 3
BLAKE 2850 5 5 4
CLARK 2450 6 6 5
ALLEN 1600 7 7 6
TURNER 1500 8 8 7
MILLER 1300 9 9 8
WARD 1250 10 10 9
MARTIN 1250 11 10 9

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
ADAMS 1100 12 12 10
SMITH 1000 13 13 11
JAMES 950 14 14 12

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select ename,sal,rn,rnk,drnk
2 from (
3 select ename,sal,
4 row_number()
5 over (order by sal desc)rn,
6 rank()
7 over (order by sal desc)rnk,
8 dense_rank()
9 over (order by sal desc)drnk
10 from emp
11 order by sal desc
12 )
13* where rn=5
SQL> /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
BLAKE 2850 5 5 4

SQL> ed
Wrote file afiedt.buf

1 select ename,sal,rn,rnk,drnk
2 from (
3 select ename,sal,
4 row_number()
5 over (order by sal desc)rn,
6 rank()
7 over (order by sal desc)rnk,
8 dense_rank()
9 over (order by sal desc)drnk
10 from emp
11 order by sal desc
12 )
13* where rn=3
SQL>
SQL>
SQL> /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
FORD 3000 3 2 2

SQL>
SQL>
SQL>