Database : Oacle 11.2.0.4 Standard edition
OS : Windows Server 2008 R2 64x

At this morning, our application got this error message.

"Can not change status:ORA-04029: error ORA-7445 occurred
 when querying Fixed Table/View ORA-06512: at
 "SYS.DBMS_ALERT", line 78 ORA-06512: at
 "SYS.DBMS_ALERT", line 102 ORA-06512: at
 "LIMS.UTILITY_PKG", line 254 ORA-06512: at line 2"

When I got this error message from our BA, I had no idea about the DBMS_ALERT package. But after reviewing the DBMS_ALERT part in Oracle database manual, I guessed that DBMS_ALERT package uses a kind of message queue, and the queue has been pending status in some unknown reason.

So I tried to clear the pending status message with the following command;

SQL> EXEC DBMS_ALERT.REMOVEALL;

or

SQL> EXEC DBMS_ALERT.REMOVE(' <Name of the Alert>');

You can find the name of the alert using this command;

SQL> SELECT * FROM DBMS_ALERT_INFO;

Then, this issue was resolved!

This query shows latch information in an Oracle database.

-- latchinfo.sql
col name for a30
SELECT name, gets, misses,
       (misses/decode(gets,0,1,gets))*100 as md, immediate_gets,
       immediate_misses,
       (immediate_misses/DECODE(immediate_gets,0,1,immediate_gets))*100 as im,
       sleeps
FROM v$latch
WHERE name IN ('library cache',
               'cache buffers chains',
               'shared pool','cache buffer handles',
               'checkpoint queue latch',
               'row cache objects',
               'session allocation',
               'redo writing',
               'cache buffers lru chain',
               'redo allocation',
               'parallel query alloc buffer',
               'process queue reference',
               'undo global data',
               'process allocation',
               'transaction allocation',
               'parallel query stats',
               'user lock',
               'done queue latch',
               'longop free list',
               'enqueues',
               'enqueue hash chains',
               'latch wait list',
               'dml lock allocation')
ORDER BY sleeps DESC;

No. 10699

ROW SIZE 계산방법

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

Row Header의 구성은( cf. " Concepts Manual 5-5 " )

(a) +-Row Overhead(2Byte) +

(b) | Number of columns(1Byte) +

(c) | Cluster key ID(if clustered,1Byte) +

(d) +- ROWID of chained row piece(if any, 6Byte)

1.One table 에는 block 을 위해

57 byte + 23* INITRANS + 4 (table directory) + 2* (1block 의 ROW수) 이 할당.

각 Row 의 overhead 는 3 byte (a+b) 입니다.

각 column 의 overhead 는 1 byte 입니다.

(단 250 byte 이하 인 경우, 또는 이상이면3byte) (e)

단 null 컬럼들이 맨 뒤에 있으면 이 column들의 overhead는 save됩니다. (*)

Tuning 요소

==== ex1)

A varchar2(10), B number, C char(10) ,D char(10), E varchar2(10), F char(10)

이고

('a',null,null,null,null,null)의 data가 들어 있으면

=>

3B(a+b) +

2B(for data 'a',column overhead(1byte)포함) = 5 Byte 입니다.

; 이것이 10개 record 라면 50 byte 입니다.

==== ex2)

A varchar2(10), B number, C char(10) ,D char(10), E varchar2(10), F char(10)

이고

('a',null,null,'d',null,null)의 data가 들어 있으면

=>

3B(a+b) +

2B(for data 'a',column overhead포함) +

2B(for two null columns ) +

11B(for data 'd',column overhead(1byte)포함) = 18 Byte 입니다.

==== ex3)

A varchar2(10), B number 이고

('a',1000000000)의 data가 들어 있으면

=>

3B(a+b) +

2B(for data 'a',column overhead포함) +

3B(for data 1000000000,column overhead(1byte)포함) = 8 Byte 입니다.

; 숫자 1000000000은 내부적으로 1*E9 으로변환 (정수,소수,지수) -> 2byte

; 숫자 1000000001은 내부적으로 (?)

-> 6 byte

data가 많이 들어 있다면 다음 block 으로 의 chain 을 위해 x byte 가 증가되며,

이값을 user_tables의 avg_row_len 으로 구해 보았다면

이 값은 row의 평균 size 이므로 훨씬 많은 byte 일 것입니다.

(사용가능한 space / avg_row_len) 입니다.

* 사용가능한 space 는

(block size - block header) - (block size - block header) * 0.1 입니다.

( 만약 pctfree 가 10% 라면)......

2. One Row가 one Block 에 들어가지 못하는 경우에는 체인이 발생하고

그로 인한 오버헤드는 row header부분에 체인된 블럭을 가리키는 ROWID(6바이트)가 추가됩니다.

체인된 블럭으로 이어진 컬럼은 끊긴 자신의 길이 지정자 ( <= 250이면1B, > 250이면 3B )가 추가됩니다.

==== ex4)

SQL> create table Tbl1 ( c1 varchar2(2000)) pctfree 0;

SQL> Data insert ..

(a) (b) (d) (e)

(e) (Total Size)

; 1800개의 문자 입력(1 block) => 2 + 1 + 3 + 1800 =

1506 Byte

; 1900 " (2 block) => 2 + 1 + 6 + 3 + 약1850 + 1 + 50 =

1913 Byte

; pctfree가 50 이어도 일단 한 레코드가 들어가야 하므로

같은결과

SQL> analyze table Tbl1 compute statistics;

SQL> select table_name, num_rows, blocks, avg_row_len

from user_tables;

SQL> select vsize(c1) from Tbl1;

How to calculate ROW size

This is the Row Header structure ( cf. "Oracle database 11g Concepts Manual 12.Logical Storage Structures " )
(a) +-Row Overhead(2Byte) +
(b) | Number of columns(1Byte) +
(c) | Cluster key ID(if clustered,1Byte) +
(d) +- ROWID of chained row piece(if any, 6Byte)

1. If one block can bs in one Row,

57 byte + 23* INITRANS + 4 (table directory) + 2* (the number of ROWs for one block) is allocated.

The overhead for each ROW is 3 bytes (a+b).
And each column's overhead is 1 byte if it is less than 250 bytes, or it is 3 bytes if it is more than 250 bytes) (e)
But, if null columns are located at the very end, the overhead of these columns is saved. (*)

Factor of Tuning

ex1)

A varchar2(10), B number, C char(10) ,D char(10), E varchar2(10), F char(10)
and
Data : ('a',null,null,null,null,null)

then,

3B(a+b) +
2B(for data 'a', column overhead(1byte) included) = 5 bytes

; if there are 10 rows of this data, the total size will be 50 bytes

ex2)

A varchar2(10), B number, C char(10) ,D char(10), E varchar2(10), F char(10)
and
Data : ('a',null,null,'d',null,null)

then,

3B(a+b) +
2B(for data 'a', column overhead included) +
2B(for two null columns ) +
11B(for data 'd',column overhead(1byte) included) = 18 bytes

ex3)

A varchar2(10), B number
and
Data : ('a',1000000000)

then,

3B(a+b) +
2B(for data 'a',column overhead included) +
3B(for data 1000000000,column overhead(1byte) included) = 8 bytes

; number 1000000000 is converted to 1*E9 internally (integer, decimal, float) -> 2bytes
; number 1000000001 is internally (?) -> 6 bytes

If lots of data is in it, x byte will be increased for the chain of the next block, and if we calculate this with the avg_row_len of user_tables, this size will be much larger bytes than the average row size.
=> (available space / avg_row_len)
* Available space = (block size - block header) - (block size - block header) * 0.1 (if pctfree is 10%)......

2. If one block cannot be in one Row,

Chain will be happened. So ROWID(6 bytes) as the overhead of this will be added in order to point chained block on the row header.
And the length indicator(if <= 250 bytes, then 1 bytes. if > 250 bytes, then 3 bytes) of the column which is linked with the chained block will be added will be added.

ex4)

SQL> create table Tbl1 ( c1 varchar2(2000)) pctfree 0;
SQL> Data insert ..

(a) (b) (d) (e)
(e) (Total Size)

; 1800 characters inserted(1 block) => 2 + 1 + 3 + 1800 = 1506 bytes

; 1900 characters inserted(2 block) => 2 + 1 + 6 + 3 + about 1850 + 1 + 50 = 1913 bytes

; even if pctfree = 50, at least one recorder should be inserted.


So,

SQL> analyze table Tbl1 compute statistics;

SQL> select table_name, num_rows, blocks, avg_row_len 

from user_tables;

SQL> select vsize(c1) from Tbl1;

If you have any question, please leaver your comment.

CREATE OR REPLACE TYPE SALES_CUST_TYPEAS OBJECT
(prod_cnt NUMBER(5),
 channel_cnt NUMBER(2),
 tot_amt NUMBER(15,2));
/

SELECT /*+ GATHER_PLAN_STATISTICS */
       s.cust_id, s.cust_first_name, s.cust_last_name,
       s.sales_cust.prod_cnt,  -- the Alias was used here
       s.sales_cust.channel_cnt,
       s.sales_cust.tot_amt
  FROM (SELECT /*+ INDEX(c IX_CUST_BIRTH_CUST) */
               c.cust_id, c.cust_first_name, c.cust_last_name,
               (SELECT sales_cust_type -- the type name should be used as it is
                          (COUNT (DISTINCT s.prod_id),
                           COUNT (DISTINCT s.channel_id),
                           SUM (s.amount_sold)
                          )
                  FROM sales s
                 WHERE s.cust_id = c.cust_id
) AS sales_cust -- this Alias is being used on the main query block.
          FROM customers c
         WHERE c.cust_year_of_birth= 1987
           AND ROWNUM   ;

The SQL above shows the similar effect when JPPD is being used. So it is same as a lateral view has been created.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

질의 최적화(query optimization)에서 RBO(Rule Base Optimizer)는 정해진 랭킹(ranking)에 의해 플랜을 결정한다.

같은 랭킹이라면 Where 절의 뒤부터, From절 뒤의 객체가 우선 순위를 갖는다. 한 객체(예 : 테이 블)에서 같은

랭킹의 인덱스가 있다면 가장 최근에 만들어진 인덱스를 사 용한다. 이는 CBO(Cost Base Optimizer)에서도 같이

적용되는 사항이다.

다분히RBO는 개발자들이 프로그래밍 단계에서 SQL 문장 구조의 인위적 인 조정 등으로 인덱스를 사용 못하게 하는

등 개발자가 코딩에 신경을 많 이 써야 하는 문제점이 있다. 또한RBO는 해당 질의에 대한 테이블의 인덱스가

존재한다면 전체 90% 이상의 대상이어도 인덱스를 선택한다는 것이 다. 즉, RBO는 무조건 다음과 같은 미리 정해진

룰을 기준으로 플랜을 결 정하게 된다. 1992년 Oracle 7에서 CBO가 지원되면서 CBO는 계속적인 신기능의 적용으로

발전해 온 반면, RBO는 더 이상의 기능 향상은 없으며, 향후는CBO만 지원될 계획이다. 그러므로RBO에 더 이상의

미련을 갖지 말기 바라며, CBO의 훌륭한 기능들을 적극 활용하길 바란다.

다음은RBO의 랭킹을 정리한 것이다.

Path 1 : Single Row by Rowid

Path 2 : Single Row by Cluster Join

Path 3 : Single Row by Hash Cluster Key with Unique or Primary Key

Path 4 : Single Row by Unique or Primary Key

Path 5 : Clustered Join

Path 6 : Hash Cluster Key

Path 7 : Indexed Cluster Key

Path 8 : Composite Index

Path 9 : Single-Column Indexes

Path 10 : Bounded Range Search on Indexed Columns

Path 11 : Unbounded Range Search on Indexed Columns

Path 12 : Sort-Merge Join

Path 13 : MAX or MIN of Indexed Column

Path 14 : ORDER BY on Indexed Column

Path 15 : Full Table Scan

** last_ddl_time 참조

Using the package DBMS_SCHEDULER one can enable/disable jobs.

To disable job: This disables the job from running

SQL> exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

—check job status

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB';

JOB_NAME ENABL

————————- —–

GATHER_STATS_JOB FALSE

To enable job:

SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

—check job status

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB';

JOB_NAME ENABL

————————- —–

GATHER_STATS_JOB TRUE

This script will report on all backups – full, incremental and archivelog backups -

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

This script will report all on full and incremental backups, not archivelog backups -

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

SQL> connect / as sysdba
SQL> @/home/oracle/sqlt/install/sqcreate.sql

PL/SQL procedure successfully completed.

Installing SQLT

1. Uninstall a prior version (optional).

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdrop.sql

2. Execute installation script sqlt/install/sqcreate.sql connected as SYS.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcreate.sql

1. Optional Connect Identifier (mandatory when installing in a Pluggable Database)

2. SQLTXPLAIN password.

3. SQLTXPLAIN Default Tablespace.

4. SQLTXPLAIN Temporary Tablespace.

5. Optional Application User.

6. Licensed Oracle Pack. (T, D or N)

1. In a file.

2. In-line.

3. Internal installation at Oracle.