softcream.kr

2010년 4월 12일 월요일

DB_CACHE_SIZE

Property	Description
Parameter type	Big integer
Syntax	`DB_CACHE_SIZE =` `integer` `[K \| M \| G]`
Default value	If `SGA_TARGET` is set: If the parameter is not specified, then the default is `0` (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool. If `SGA_TARGET` is not set, then the default is either 48 MB or 4 MB * number of CPUs, whichever is greater
Modifiable	`ALTER SYSTEM`
Basic	No

DB_CACHE_SIZE specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).

The value must be at least 4M * number of cpus * granule size (smaller values are automatically rounded up to this value). A user-specified value larger than this is rounded up to the nearest granule size. A value of zero is illegal because it is needed for the DEFAULT memory pool of the primary block size, which is the block size for the SYSTEM tablespace.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams049.htm

I got a help request from a client who was running Oracle 11.1.0.6 64-bit. Their memory_target parameter was set to 5G, with sga_target and all pool parameters set to 0. pga_aggregate_target was explicitly set to 750MB. However, Oracle would not allocate more than 64MB RAM to the Buffer Cache. We looked through multiple snapshots and never found a case where db_cache_size was above 64MB, despite a staggering amount of disk reads. So we tried to change the db_cache_size manually to set the minimum:

SQL> alter system set db_cache_size = 1024M;
alter system set db_cache_size = 1024M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache

I wanted to see if I could duplicate the issue, so I tested it on my 11.1.0.6 Windows 64-bit instance. I prepared the system by setting pga_aggregate_target to 128M, sga_target to 512M, and memory_target = 1648M.

NOTE: Throughout these examples, I’ve removed the irrelevant results from “show parameter target”.

Setting It Up

SQL> alter system set pga_aggregate_target = 128M;

System altered.

SQL> alter system set sga_target = 512M;

System altered.

SQL> alter system set memory_target = 1648M;

System altered.

SQL> show parameter target

NAME                             TYPE        VALUE
-------------------------------- ----------- -----
memory_max_target                big integer 1648M
memory_target                    big integer 1648M
pga_aggregate_target             big integer 128M
sga_target                       big integer 512M

SQL> show sga

Total System Global Area 1720328192 bytes
Fixed Size                  2115656 bytes
Variable Size            1426065336 bytes
Database Buffers          285212672 bytes
Redo Buffers                6934528 bytes

The next step was to test the change:

Testing the Change

SQL> alter system set db_cache_size = 1024M;
alter system set db_cache_size = 1024M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache

So I tried turning off memory_target, and look what happens to the PGA:

Turn off memory_target

SQL> alter system set memory_target = 0;

System altered.

SQL> show parameter target

NAME                             TYPE        VALUE
-------------------------------- ----------- -----
memory_max_target                big integer 1648M
memory_target                    big integer 0
pga_aggregate_target             big integer 1136M
sga_target                       big integer 512M

The PGA was given all of the extra space! This was pretty strange, so I went ahead and changed the PGA to 128M and tried my test again:

Try again

SQL> alter system set pga_aggregate_target = 128M;

System altered.

SQL> show parameter target

NAME                             TYPE        VALUE
-------------------------------- ----------- -----
memory_max_target                big integer 1648M
memory_target                    big integer 0
pga_aggregate_target             big integer 128M
sga_target                       big integer 512M

SQL> alter system set db_cache_size = 1024M;
alter system set db_cache_size = 1024M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache

Even then, it would not let me grow the buffer cache. I had to disable sga_target for 11g to finally allow it:

Solution

SQL> alter system set sga_target = 0;

System altered.

SQL> alter system set db_cache_size = 1024M;

System altered.

SQL> alter system set memory_target = 1648M;

System altered.

SQL> show parameter target

NAME                             TYPE        VALUE
-------------------------------- ----------- -----
memory_max_target                big integer 1648M
memory_target                    big integer 1648M
pga_aggregate_target             big integer 128M
sga_target                       big integer 0

Is this expected behavior from memory_target? Did I miss something? Is it a bug in 11.1.0.6? I was not able to find any notes on Metalink regarding the issue. The Oracle Documentation states that this should work just fine. When memory_target is enabled, sga_target and pga_aggregate_target should only work as minimums if explicitly set.

I will play around with it some more if I get the chance. In the meantime, I have to wonder how many DBAs have confidently set memory_target, all the while not knowing their individual pools weren’t being sized properly?

Update 1

I just did another test where I “primed the pump” so to speak. To do so, I set the sga_target higher. It allowed me to grow the db_cache_size at that point. However, I then set db_cache_size down to 0, set sga_target back to 512M, and was still able to set db_cache_size back up to 1024M afterwards.

NAME                         TYPE        VALUE
---------------------------- ----------- -----
memory_max_target            big integer 1648M
memory_target                big integer 1648M
pga_aggregate_target         big integer 128M
sga_target                   big integer 512M

SQL> alter system set db_cache_size = 1024M;
alter system set db_cache_size = 1024M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache

SQL> alter system set sga_target = 1300M;

System altered.

SQL> alter system set db_cache_size = 1024M;

System altered.

SQL> alter system set db_cache_size = 0;

System altered.

SQL> alter system set sga_target = 512M;

System altered.

SQL> show parameter target

NAME                         TYPE        VALUE
---------------------------- ----------- -----
memory_max_target            big integer 1648M
memory_target                big integer 1648M
pga_aggregate_target         big integer 128M
sga_target                   big integer 512M

SQL> alter system set db_cache_size = 1024M;

System altered.

SQL> show parameter target

NAME                         TYPE        VALUE
---------------------------- ----------- -----
memory_max_target            big integer 1648M
memory_target                big integer 1648M
pga_aggregate_target         big integer 128M
sga_target                   big integer 512M

SQL> show parameter db_cache_size

NAME                             TYPE        VALUE
-------------------------------- ----------- -----
db_cache_size                    big integer 1G

The end looks just like the beginning. The only difference would be an internal barrier being lifted.

http://www.oraclealchemist.com/oracle/strange-behavior-with-memory_target/

2010년 4월 10일 토요일

SYS_OP_COUNTCHG

SYS_OP_COUNTCHG
Counts the number of blocks in a table but the results between these two methods were different when tested with sys.tab$ and very different from looking at BLOCKS in dba_tables and dba_segments.	SYS_OP_COUNTCHG(rowid, integer_between_1_and_255)
	conn uwclass/uwclass SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid)) FROM airplanes; SELECT sys_op_countchg(SUBSTRB(ROWIDTOCHAR("AIRPLANES".ROWID),1,15),1) FROM airplanes; EXPLAIN PLAN FOR SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid)) FROM airplanes; SELECT * FROM TABLE(dbms_xplan.display); EXPLAIN PLAN FOR SELECT sys_op_countchg(SUBSTRB(ROWIDTOCHAR("AIRPLANES".ROWID),1,15),1) FROM airplanes; SELECT * FROM TABLE(dbms_xplan.display); -- note the difference between the plans.

클러스터링 팩터

TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS DISTINCT_KEYS
------------------------------ ---------- ----------- ----------------- ---------- --

T2 2 5781 2499738 2500000 2500000

ex)

select /*+ cursor_sharing_exact
dynamic_sampling(0)
no_monitoring
no_expand
index(T2,"T2_IDX")
noparallel_index(T2,"T2_IDX")
*/ sys_op_countchg(SUBSTRB(ROWIDTOCHAR("T2".ROWID),1,15),5)
FROM PROC3.T2
WHERE "A" is not null;

SYS_OP_COUNTCHG(SUBSTRB(ROWIDTOCHAR("T2".ROWID),1,15),5)
--------------------------------------------------------
166998

이전의 방문한 블럭만 참조하는 클러스터링 팩터의 계산을 보정할때 사용한다.

sys_op_countchg 함수를 사용하여 이전의 방문한 블록의 목록의 일부를 저장하여 비교사용한다.

따라서 숫자에(빨간색표시) 값에 따라서 블록 목록의 윈도우가 정해지며,

값이 높을수록 클러스터링 팩터는 줄어들게 된다.

클러스터링 팩터는 메모리 사용량과도 관련이 있으므로 ( 디스크 성능 등 복합적인 튜닝 요구됨 ) , 너무 높은 값으로 하는것은 의미가 없다.

조정 값의 Max 값은 255

http://www.morganslibrary.org/reference/undocumented.html

What is a “Clustering Factor”?

http://oracleact.com/papers/cf.html

Ever since Oracle introduced Cost Based Optimizer, DBAs and developers tried to understand
how Oracle computes the “CLUSTERING FACTOR” metric when ever an index has been
analyzed. Here, I presented a small test case for computing the “CF”.

What is a “Clustering Factor”?

CF is a metric that tells Oracle how the rows in a table are randomly distributed with respect to
index key values.

I emphasis “with respect to index key values” words one more time because that’s how Oracle
computes the “CF” – lowest index key value to the highest index key value (when not using
parallel degree) which you will see later in this article.

Good CF Vs Bad CF OR Good Index Vs Bad Index

Over the years, we have been told if the CF value is close to number of blocks used by table
rows, then it is a “Good” index meaning the table rows are sorted with respect to the index keys.
On the other hand if the rows are distributed randomly, then it is a bad index, and you will
normally see a very high CF value that is very close the number of rows in the table. When the
optimizer computes execution plan for a SQL statement, it uses the CF value very much. On
many occasions the optimizer would not choose index access path simply because the CF value
is too high. This is mainly due to bug in calculating CF value, rounding errors,

lack of understanding in CF by Oracle kernel developers, usage of FREELISTS, FREELIST
GROUPS,
table partition etc. In this paper I stick with only “CF” calculation.

I will not talk about performance issues.

First I created a test table in which I stored 50 rows randomly selected from dba_objects.
SQL> create table clf_test
pctfree 95 pctused 5
tablespace tamil_large_data
as select object_id, object_name, status from dba_objects
where rownum < 51
order by dbms_random.value ;

Table created.

I used PCTFREE 95 so that atleast the rows are populated more than 1 block.
The next SQL verifies that rows are selected randomly.
Look at the OBJECT_ID column values.
SQL> select * from clf_test order by rowid ;

OBJECT_ID OBJECT_NAME STATUS
--------- --------------------------------- -------
48598 /1240abae_JdbcOperations VALID
49148 /1240f5cf_LessExpression VALID
53072 /10744837_ObjectStreamClass2 VALID
47880 /1225525_PlainTextInputStream VALID
60864 /1261d15c_CompilerOptions VALID
48658 /12579bb9_ResolutionDriverReso VALID
58824 /10e48aa3_StringExpressionCons VALID
59234 /1005bd30_LnkdConstant VALID
56842 /11144507_CharConv12ByteBuilde VALID
47666 /1236a7cd_SignatureFileBlock VALID
50556 /10845320_TypeMapImpl VALID

------ many lines are omitted --------------

61991 /10c906a0_ProfilePrinterErrors VALID
53168 /112785cc_FVDCodeBaseImpl VALID
51294 /1146b53d_BasicSliderUIScrollL VALID
61373 /122e6c15_OracleSqljXADataSour VALID
57102 /11799933_SchemaProtectionDoma VALID
62683 /11ba27f1_CustomizerHarnessBea VALID
49052 /10804ae7_Constants VALID

50 rows selected.

SQL> analyze table clf_test compute statistics ;
Table analyzed.

SQL> select table_name, tablespace_name , blocks, num_rows
from user_tables where table_name = 'CLF_TEST' ;

TABLE_NAME TABLESPACE_NAME BLOCKS NUM_ROWS
------------ -------------------- ---------- ----------
CLF_TEST TAMIL_LARGE_DATA 7 50

SQL> create index clf_test_idx on clf_test(object_id)
tablespace tamil_large_index compute statistics ;

Index created.

SQL> select index_name, tablespace_name, blevel, leaf_blocks,
distinct_keys, clustering_factor
from user_indexes where index_name = 'CLF_TEST_IDX' ;

INDEX_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------ --------------- ------ ----------- ------------- -----------------
CLF_TEST_IDX TAMIL_LARGE_INDEX 0 1 50 45

SQL> select object_id, dbms_rowid.rowid_block_number(rowid) blk_num
from clf_test order by object_id;

Note CLF_MANUAL column is computed by me manually.

OBJECT_ID BLK_NUM CLF_MANUAL

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

47666 25611 1
47736 25613 2
47880 25610 3
48402 25613 4
48598 25610 5
48658 25610 5 - Clustering Factor remains same when Previous BLOCK # is same
48866 25613 6
49034 25613 6 - Clustering Factor remains same when Previous BLOCK # is same
49052 25616 7
49148 25610 8
49258 25612 9
49542 25612 9 - Clustering Factor remains same when Previous BLOCK # is same.
49714 25614 10
50132 25612 11
50556 25611 12
51156 25612 13
51294 25615 14
51480 25613 15
52160 25612 16
52414 25612 16 - Clustering Factor remains same when Previous BLOCK # is same.
52462 25614 17
53072 25610 18
53168 25615 19
54186 25613 20
55536 25612 21
56194 25611 22
56208 25615 23
56346 25614 24
56842 25611 25
57102 25615 26
57104 25611 27
57240 25614 28
58824 25610 29
58834 25614 30
59234 25610 31
59860 25613 32
60044 25615 33
60298 25612 34
60314 25614 35
60696 25611 36
60698 25614 37
60864 25610 38
60981 25613 39
61373 25615 40
61991 25615 40 - Clustering Factor remains same when Previous BLOCK # is same
62005 25611 41
62109 25614 42
62683 25616 43
63606 25611 44
64234 25615 45

My manual computation for “CLUSTERING FACTOR” value 45 is same as Oracle computed
value.

How Oracle gathers index statistics

First I used the “COMPUTE STATISTICS” along with CREATE INDEX command and enabled the
SQL trace. I didn’t see any useful information in the trace file.

If you use DBMS_STATS.gather_index_stats procedure to analyze index and also enable the
SQL trace, you will see the SQL statements for gathering statistics on index.

An example is given below for the index I created in my test case.
The tkprof output shows:
=====================
PARSING IN CURSOR #14 len=347 dep=1 uid=31 oct=3 lid=31 tim=1111951510293891 hv=2407722251 ad='8755ccf0'

select /*+ cursor_sharing_exact
dynamic_sampling(0)
no_monitoring
no_expand
index(t,"CLF_TEST_IDX")
noparallel_index(t,"CLF_TEST_IDX") */
count(*) as nrw,
count(distinct sys_op_lbid(67975,'L',t.rowid)) as nlb,
count(distinct "OBJECT_ID") as ndk,
sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from "TAMIL"."CLF_TEST" t
where "OBJECT_ID" is not null

END OF STMT
PARSE#14:c=10000,e=1256,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1111951510293885
EXEC#14:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1111951510294037
FETCH#14:c=0,e=375,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1111951510294433
STAT #14 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT GROUP BY '
STAT #14 id=2 cnt=50 pid=1 pos=1 obj=67975 op='INDEX FULL SCAN CLF_TEST_IDX '

Oracle uses a function/procedure sys_op_countchg to calculate the “CLUSTERING FACTOR”.

The substr(rowid,1,15) is for object_id (6 bytes), file_id (3 bytes) and block number (6 bytes) and
they form a unique value. When gathering statistics on index without using parallel degree
option, Oracle reads all the index key values starting from the lowest leaf block to the last leaf
block and at the same time the sys_op_countchg function increments a counter whenever the
block number changes. It stores the previous block number.

(See previous page how I did it manually).

Till date Oracle has not published the code for sys_op_countchg.

2010년 4월 9일 금요일

Dynamic Query with Dynamic SQL

Building a Dynamic Query with Dynamic SQL

You use three statements to process a dynamic multi-row query: OPEN-FOR, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result set one at a time. When all the rows are processed, you CLOSE the cursor variable. (For more information about cursor variables, see "Using Cursor Variables (REF CURSORs)".)

Examples of Dynamic SQL for Records, Objects, and Collections

Example 7-3 Dynamic SQL Fetching into a Record

As the following example shows, you can fetch rows from the result set of a dynamic multi-row query into a record:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  emp%ROWTYPE;
   sql_stmt VARCHAR2(200);
   my_job   VARCHAR2(15) := 'CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM emp WHERE job = :j';
   OPEN emp_cv FOR sql_stmt USING my_job;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      -- process record
   END LOOP;
   CLOSE emp_cv;
END;
/

Example 7-4 Dynamic SQL for Object Types and Collections

The next example illustrates the use of objects and collections. Suppose you define object type Person and VARRAY type Hobbies, as follows:

CREATE TYPE Person AS OBJECT (name VARCHAR2(25), age NUMBER);
CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);

Using dynamic SQL, you can write a package that uses these types:

CREATE OR REPLACE PACKAGE teams AS
   PROCEDURE create_table (tab_name VARCHAR2);
   PROCEDURE insert_row (tab_name VARCHAR2, p Person, h Hobbies);
   PROCEDURE print_table (tab_name VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY teams AS
   PROCEDURE create_table (tab_name VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||
         ' (pers Person, hobbs Hobbies)';
   END;

   PROCEDURE insert_row (
      tab_name VARCHAR2,
      p Person,
      h Hobbies) IS
   BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||
         ' VALUES (:1, :2)' USING p, h;
   END;

   PROCEDURE print_table (tab_name VARCHAR2) IS
      TYPE RefCurTyp IS REF CURSOR;
      cv RefCurTyp;
      p  Person;
      h  Hobbies;
   BEGIN
      OPEN cv FOR 'SELECT pers, hobbs FROM ' || tab_name;
      LOOP
         FETCH cv INTO p, h;
         EXIT WHEN cv%NOTFOUND;
         -- print attributes of 'p' and elements of 'h'
      END LOOP;
      CLOSE cv;
   END;
END;
/

From an anonymous block, you might call the procedures in package TEAMS:

DECLARE
   team_name VARCHAR2(15);
BEGIN
   team_name := 'Notables';
   teams.create_table(team_name);
   teams.insert_row(team_name, Person('John', 31),
      Hobbies('skiing', 'coin collecting', 'tennis'));
   teams.insert_row(team_name, Person('Mary', 28),
      Hobbies('golf', 'quilting', 'rock climbing'));
   teams.print_table(team_name);
END;
/

http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/11_dynam.htm#sthref935

2010년 4월 8일 목요일

PCC-F-02104, Unable to connect to Oracle

참 골치 아픈 에러다 ...

Unable to connect to Oracle 은 여러가지의 의미를 가진다.

1. ORACLE 이 shutdown 되어 있는상태.

2. PL/SQL 구문검사시 해당 USER/PASSWD 가 일치하지 않는 경우.

3. LIstener 가 활성화 되지 않은 경우.

4. 기타 서버의 상태가 접속 가능 하지 않는 상황.

특히 PL/SQL 문장이 포함된 파일인 경우 위의 에러는 해당 USER 의 상태를 확인 하여야한다.

ID / USER 대소문자만 틀려도 위의 에러가 발생한다.

pcscfg.cfg 내에 있는 USERID=PROC5/PROC5

- PL/SQL 문법 검사시 사용된다.

자신의 코드 안에 있는 접속 USERID/PASSWD

- *.pc 를 c 로 변환하여, 실행파일을 수행시 해당 유저의 세션을 open 할때 사용한다.

- 실행파일을 수행하여 세션을 open 할때 유저정보가 틀리다면 위의 에러가 아닌 다른 에러를 출력한다.

ORA-01017 : invalid username/password: logon denied

PL/SQL 이 포함되지 않는 경우라면..

SQLCHECK=SYNTAX

으로 변경한다.

drop_caches

Writing to this will cause the kernel to drop clean caches, dentries and inodes from memory,

causing that memory to become free.

To free pagecache:

echo 1 > /proc/sys/vm/drop_caches

To free dentries and inodes:

echo 2 > /proc/sys/vm/drop_caches

To free pagecache, dentries and inodes:

echo 3 > /proc/sys/vm/drop_caches

As this is a non-destructive operation, and dirty objects are not freeable, the user should

run "sync" first in order to make sure all cached objects are freed.

http://www.linuxinsight.com/proc_sys_vm_drop_caches.html

2010년 4월 7일 수요일

ORA-28000: the account is locked

SQL> conn proc/proc
ERROR:
ORA-28000: the account is locked

SQL> alter user proc account unlock;

User altered.

SQL> conn proc/proc
Connected.