About DATABASE

SHARED POOL에 대한 점검 사항들

제품 : ORACLE SERVER




SHARED POOL에 대한 점검 사항들
======================


PURPOSE
-------
다음은 shared pool에 관련된 performance 에 대한 점검 사항들이다.


Explanation
-----------

1. literal SQL Statements

SELECT      substr(sql_text,1,40) "SQL", count(*) ,
sum(executions) "TotExecs"
FROM      v$sqlarea
WHERE      executions < 5
     GROUP BY substr(sql_text,1,40)
     HAVING count(*) > 30
ORDER BY 2
/

shared SQL문들 중에서 literal SQL문들을 찾아내어 bind variable을
사용할 수 있는 경우 bind variable로 전환하도록 한다.
ORACLE cost based optimizer는 bind variable 보다 literal value를
사용하는 SQL에 대하여 보다 최적화된 execution plan을 결정하게 된다.
하지만 과도한 literal SQL문들을 사용하게 되면 hard parsing 이
빈번하게 되고 library cache와 dictionary cache의 사용율을 높이게 된다.


2. Library cahe hit ratio

SELECT to_char(trunc(sum(reloads)/sum(pins)*100, 5),99.99999)||
'%(less than 1%)' "Library Cache MISS RATIO"
FROM v$librarycache
/

만일 miss ratio가 1%보다 큰 경우 library cache miss를 줄이는 노력이
필요하다. 예를 들어 적절한 크기의 shared pool을 사용하거나 dynamic SQL
(literal SQL) 사용을 줄이도록 한다.


3. Checking hash chain lengths
SELECT hash_value, count(*)
FROM v$sqlarea
GROUP BY hash_value
HAVING count(*) > 5
/

위 query에 대한 결과가 없어야 한다. 만일 동일한 HASH_VALUE를 갖는
sql 수가 많다면 다음의 query를 이용하여 이 hach chain에 의하여 관리되는
sql 들을 확인하여 본다.
대부분 literal sql문들에 의하여 이런 문제가 발생하는 경우가 많다.

SELECT sql_text FROM v$sqlarea WHERE hash_value= <XXX>;


4. Checking for high version counts

     SELECT      address, hash_value,
     version_count ,
users_opening ,
users_executing
sql_text
FROM v$sqlarea
WHERE version_count > 10
     /

SQL의 version은 문장 상으로 완벽히 일치하지만 참조 object가 틀리는
SQL문들을 의미한다. 만일 이해할 수 없을 정도의 version count를 갖는
row가 있다면 한국 오라클 기술지원팀으로 문의하도록 한다.


5. Finding statement/s which use lots of shared pool memory

SELECT      substr(sql_text,1,40) "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,40)
HAVING sum(sharable_mem) > <MEMSIZE>
/


6. Allocations causing shared pool memory to be 'aged' out

SELECT      *
FROM      sys.x$ksmlru
WHERE      ksmlrnum>0
/

x$ksmlru는 shared pool의 object에 대한 list로 object 할당 시 age out된
object 수에 정보를 담고 있어 age out으로 인한 응답율 저하나 latch
병합들의 원인을 추적하는 데 유용하다.
이 table은 8i부터 sys user로만 조회가 가능하며 한번 조회되면 reset된다.

(x$ksmlru.ksmlrnum : Number of items flushed from the shared pool)


Example
-------


Reference Documents
-------------------

출처 : http://www.commit.co.kr/101

Single_to_RAC(Converting).pdf

Steps to Remove Node from Cluster When the Node Crashes Due to OS/Hardware Failure and cannot boot up (Doc ID 466975.1) To BottomTo Bottom

Modified:16-Nov-2012Type:HOWTO

Rate this document Email link to this document Open document in new window Printable Page

In this Document

Goal

Fix

  Summary

  Example Configuration

  Initial Stage

  Step 1 Remove oifcfg information for the failed node

  Step 2 Remove ONS information

  Step 3 Remove resources

  Step 4 Execute rootdeletenode.sh

  Step 5 Update the Inventory

References

APPLIES TO:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.1.0.6 [Release 10.2 to 11.1]

Oracle Server - Standard Edition - Version 10.2.0.1 to 11.1.0.6 [Release 10.2 to 11.1]

Information in this document applies to any platform.

Oracle Server Enterprise Edition - Version: 10.2.0.1 to 11.1.0.6

Oracle Clusterware

GOAL

This document is intented to provide the steps to be taken to remove a node from the Oracle cluster. The node itself is unavailable due to some OS issue or hardware issue which prevents the node from starting up. This document will provide the steps to remove such a node so that it can be added back after the node is fixed.

The steps to remove a node from a Cluster is already documented in the Oracle documentation at

Version Documentation Link

10gR2 http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/adddelunix.htm#BEIFDCAF

11gR1 http://download.oracle.com/docs/cd/B28359_01/rac.111/b28255/adddelclusterware.htm#BEIFDCAF

This note is different because the documentation covers the scenario where the node is accessible and the removal is a planned procedure. This note covers the scenario where the Node is unable to boot up and therefore it is not possible to run the clusterware commands from this node.

For 11gR2, refer to note 1262925.1

FIX

Summary

Basically all the steps documented in the Oracle Clusterware Administration and Deployment Guide must be followed. The difference here is that we skip the steps that are to be executed on the node which is not available and we run some extra commands on the other node which is going to remain in the cluster to remove the resources from the node that is to be removed.

Example Configuration

 All steps outlined in this document were executed on a cluster with the following configuration:

Item Value

Node Names lc2n1, lc2n2, lc2n3

Operating System Oracle Enterprise Linux 5 Update 4

Oracle Clusterware Release 10.2.0.5.0

ASM & Database Release 10.2.0.5.0

Clusterware Home /u01/app/oracle/product/10.2.0/crs ($CRS_HOME)

ASM Home /u01/app/oracle/product/10.2.0/asm

Database Home /u01/app/oracle/product/10.2.0/db_1

 Cluster Name lc2

 Assume that node lc2n3 is down due to a hardware failure and cannot even boot up. The plan is to remove it from the clusterware, fix the issue and then add it again to the Clusterware. In this document, we will cover the steps to remove the node from the clusterware

Please note that for better readability instead of 'crs_stat -t' the sample script 'crsstat' from 

  Doc ID 259301.1 CRS and 10g/11.1 Real Application Clusters 

was used to query the state of the CRS resources. This script is not part of a standard CRS installation.

Initial Stage

At this stage, the Oracle Clusterware is up and running on nodes lc2n1 & lc2n2 (good nodes) . Node lc2n3 is down and cannot be accessed. Note that the Virtual IP of lc2n3 is running on Node 1. The rest of the lc2n3 resources are OFFLINE:

[oracle@lc2n1 ~]$ crsstat

Name                                     Target     State      Host      

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

ora.LC2DB1.LC2DB11.inst                  ONLINE     ONLINE     lc2n1     

ora.LC2DB1.LC2DB12.inst                  ONLINE     ONLINE     lc2n2     

ora.LC2DB1.LC2DB13.inst                  ONLINE     OFFLINE              

ora.LC2DB1.LC2DB1_SRV1.LC2DB11.srv       ONLINE     ONLINE     lc2n1     

ora.LC2DB1.LC2DB1_SRV1.LC2DB12.srv       ONLINE     ONLINE     lc2n2     

ora.LC2DB1.LC2DB1_SRV1.LC2DB13.srv       ONLINE     OFFLINE              

ora.LC2DB1.LC2DB1_SRV1.cs                ONLINE     ONLINE     lc2n1     

ora.LC2DB1.db                            ONLINE     ONLINE     lc2n2     

ora.lc2n1.ASM1.asm                       ONLINE     ONLINE     lc2n1     

ora.lc2n1.LISTENER_LC2N1.lsnr            ONLINE     ONLINE     lc2n1     

ora.lc2n1.gsd                            ONLINE     ONLINE     lc2n1     

ora.lc2n1.ons                            ONLINE     ONLINE     lc2n1     

ora.lc2n1.vip                            ONLINE     ONLINE     lc2n1     

ora.lc2n2.ASM2.asm                       ONLINE     ONLINE     lc2n2     

ora.lc2n2.LISTENER_LC2N2.lsnr            ONLINE     ONLINE     lc2n2     

ora.lc2n2.gsd                            ONLINE     ONLINE     lc2n2     

ora.lc2n2.ons                            ONLINE     ONLINE     lc2n2     

ora.lc2n2.vip                            ONLINE     ONLINE     lc2n2     

ora.lc2n3.ASM3.asm                       ONLINE     OFFLINE              

ora.lc2n3.LISTENER_LC2N3.lsnr            ONLINE     OFFLINE              

ora.lc2n3.gsd                            ONLINE     OFFLINE              

ora.lc2n3.ons                            ONLINE     OFFLINE              

ora.lc2n3.vip                            ONLINE     ONLINE     lc2n1     

[oracle@lc2n1 ~]$

Step 1 Remove oifcfg information for the failed node

Generally most installations use the global flag of the oifcfg command and therefore they can skip this step. They can confirm this using:

[oracle@lc2n1 bin]$ $CRS_HOME/bin/oifcfg getif

eth0  192.168.56.0  global  public

eth1  192.168.57.0  global  cluster_interconnect

If the output of the command returns global as shown above then you can skip the following step (executing the command below on a global defination will return an error as shown below.

If the output of the oifcfg getif command does not return global then use the following command

[oracle@lc2n1 bin]$ $CRS_HOME/bin/oifcfg delif -node lc2n3 

PROC-4: The cluster registry key to be operated on does not exist.

PRIF-11: cluster registry error

Step 2 Remove ONS information

Execute the following command to find out the remote port number to be used

[oracle@lc2n1 bin]$ cat $CRS_HOME/opmn/conf/ons.config

localport=6113 

remoteport=6200 

loglevel=3

useocr=on

and remove the information pertaining to the node to be deleted using:

[oracle@lc2n1 bin]$ $CRS_HOME/bin/racgons remove_config lc2n3:6200

Step 3 Remove resources

In this step, the resources that were defined on this node have to be removed. These resources include Database Instances, ASm, Listener and Nodeapps resources. A list of these can be acquired by running crsstat (crs_stat -t) command from any node

[oracle@lc2n1 ~]$ crsstat |grep OFFLINE

ora.LC2DB1.LC2DB13.inst                  ONLINE     OFFLINE              

ora.LC2DB1.LC2DB1_SRV1.LC2DB13.srv       ONLINE     OFFLINE              

ora.lc2n3.ASM3.asm                       ONLINE     OFFLINE              

ora.lc2n3.LISTENER_LC2N3.lsnr            ONLINE     OFFLINE              

ora.lc2n3.gsd                            ONLINE     OFFLINE              

ora.lc2n3.ons                            ONLINE     OFFLINE             

 Before removing any resource it is recommended to take a backup of the OCR:

[root@lc2n1 ~]# cd $CRS_HOME/cdata/lc2

[root@lc2n1 lc2]# $CRS_HOME/bin/ocrconfig -export ocr_before_node_removal.exp

[root@lc2n1 lc2]# ls -l ocr_before_node_removal.exp

-rw-r--r-- 1 root root 151946 Nov 15 15:24 ocr_before_node_removal.exp

 Use 'srvctl' from the database home to delete the database instance on node 3:

[oracle@lc2n1 ~]$ . oraenv

ORACLE_SID = [oracle] ? LC2DB1

[oracle@lc2n1 ~]$ $ORACLE_HOME/bin/srvctl remove instance -d LC2DB1 -i LC2DB13

Remove instance LC2DB13 from the database LC2DB1? (y/[n]) y

 Use 'srvctl' from the ASM home to delete the ASM instance on node 3:

[oracle@lc2n1 ~]$ . oraenv

ORACLE_SID = [oracle] ? +ASM1

[oracle@lc2n1 ~]$ $ORACLE_HOME/bin/srvctl remove asm -n lc2n3

Next remove the listener resource.

Please note that there is no 'srvctl remove listener' subcommand prior to 11.1 so this command will not work in 10.2. Using 'netca' to delete the listener from a down node also is not an option as netca needs to remove the listener configuration from the listener.ora.

10.2 only:

The only way to remove the listener resources is to use the command 'crs_unregister', please use this command only in this particular scenario:

[oracle@lc2n1 lc2]$ $CRS_HOME/bin/crs_unregister ora.lc2n3.LISTENER_LC2N3.lsnr

 11.1 only:

 Set the environment to the home from which the listener runs (ASM or database):

[oracle@lc2n1 ~]$ . oraenv

ORACLE_SID = [oracle] ? +ASM1

[oracle@lc2n1 lc2]$ $ORACLE_HOME/bin/srvctl remove listener -n lc2n3 

  As user root stop the nodeapps resources:

[root@lc2n1 oracle]# $CRS_HOME/bin/srvctl stop nodeapps -n lc2n3

[root@lc2n1 oracle]# crsstat |grep OFFLINE

ora.lc2n3.LISTENER_LC2N3.lsnr            OFFLINE    OFFLINE              

ora.lc2n3.gsd                            OFFLINE    OFFLINE              

ora.lc2n3.ons                            OFFLINE    OFFLINE              

ora.lc2n3.vip                            OFFLINE    OFFLINE        

 Now remove them:

[root@lc2n1 oracle]#  $CRS_HOME/bin/srvctl remove nodeapps -n lc2n3

Please confirm that you intend to remove the node-level applications on node lc2n3 (y/[n]) y

 At this point all resources from the bad node should be gone:

[oracle@lc2n1 ~]$ crsstat 

Name                                     Target     State      Host      

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

ora.LC2DB1.LC2DB11.inst                  ONLINE     ONLINE     lc2n1     

ora.LC2DB1.LC2DB12.inst                  ONLINE     ONLINE     lc2n2     

ora.LC2DB1.LC2DB1_SRV1.LC2DB11.srv       ONLINE     ONLINE     lc2n1     

ora.LC2DB1.LC2DB1_SRV1.LC2DB12.srv       ONLINE     ONLINE     lc2n2     

ora.LC2DB1.LC2DB1_SRV1.cs                ONLINE     ONLINE     lc2n1     

ora.LC2DB1.db                            ONLINE     ONLINE     lc2n2     

ora.lc2n1.ASM1.asm                       ONLINE     ONLINE     lc2n1     

ora.lc2n1.LISTENER_LC2N1.lsnr            ONLINE     ONLINE     lc2n1     

ora.lc2n1.gsd                            ONLINE     ONLINE     lc2n1     

ora.lc2n1.ons                            ONLINE     ONLINE     lc2n1     

ora.lc2n1.vip                            ONLINE     ONLINE     lc2n1     

ora.lc2n2.ASM2.asm                       ONLINE     ONLINE     lc2n2     

ora.lc2n2.LISTENER_LC2N2.lsnr            ONLINE     ONLINE     lc2n2     

ora.lc2n2.gsd                            ONLINE     ONLINE     lc2n2     

ora.lc2n2.ons                            ONLINE     ONLINE     lc2n2     

ora.lc2n2.vip                            ONLINE     ONLINE     lc2n2  

Step 4 Execute rootdeletenode.sh

From the node that you are not deleting execute as root the following command which will help find out the node number of the node that you want to delete

[oracle@lc2n1 ~]$ $CRS_HOME//bin/olsnodes -n

lc2n1   1

lc2n2   2

lc2n3   3

this number can be passed to the rootdeletenode.sh command which is to be executed as root from any node which is going to remain in the cluster.

[root@lc2n1 ~]# cd $CRS_HOME/install

[root@lc2n1 install]# ./rootdeletenode.sh lc2n3,3

CRS-0210: Could not find resource 'ora.lc2n3.ons'.

CRS-0210: Could not find resource 'ora.lc2n3.vip'.

CRS-0210: Could not find resource 'ora.lc2n3.gsd'.

CRS-0210: Could not find resource ora.lc2n3.vip.

CRS nodeapps are deleted successfully

clscfg: EXISTING configuration version 3 detected.

clscfg: version 3 is 10G Release 2.

Successfully deleted 14 values from OCR.

Key SYSTEM.css.interfaces.nodelc2n3 marked for deletion is not there. Ignoring.

Successfully deleted 5 keys from OCR.

Node deletion operation successful.

'lc2n3,3' deleted successfully

[root@lc2n1 install]# $CRS_HOME/bin/olsnodes -n

lc2n1   1

lc2n2   2

Step 5 Update the Inventory

From the node which is going to remain in the cluster run the following command as owner of the CRS_HOME. The argument to be passed to the CLUSTER_NODES is a comma seperated list of node names of the cluster which are going to remain in the cluster. This step needs to be performed from once per home (Clusterware, ASM and RDBMS homes).

[oracle@lc2n1 install]$ $CRS_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/product/10.2.0/crs "CLUSTER_NODES={lc2n1,lc2n2}" CRS=TRUE  

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/app/oracle/oraInventory

'UpdateNodeList' was successful.

[oracle@lc2n1 install]$ $CRS_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm "CLUSTER_NODES={lc2n1,lc2n2}"

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/app/oracle/oraInventory

'UpdateNodeList' was successful.

[oracle@lc2n1 install]$ $CRS_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 "CLUSTER_NODES={lc2n1,lc2n2}"

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/app/oracle/oraInventory

'UpdateNodeList' was successful.

11g NEWFEATURE.(upgrade).pdf

4-oracle-db-11g-best-practice-forum.pdf

11g에 추가된 파티션 유형

Reference 파티셔닝

• 상품 테이블을 상품대분류 기준으로 리스트 파티셔닝하고, 일별상품거래 테이블도 부모 테이블인 상품과 똑 같은 방식과 기준으로 파티셔닝
• 이럴 때 10g 까지는 상품에 있는 상품대분류 컬럼을 일별 상품거래 테이블에 반정규화
• 11g에서 부모 테이블 파티션 키를 이용해 자식 테이블을 파티셔닝하는 기능

Create table 상품 {
상품번호 number NOT NULL PRIMARY KEY
, 상품명 varchar2(50) not null
, 현재가격 number not null
, 상품대분류 varchar2(4) not null
, 등록일시 date not null
)
Partition by list(상품대분류) (
 Partition p1 values ('의류')
,partition p2 values ('식품')
,partition p3 values ('가전')
,partition 4 values ('컴퓨터')
);

create table 일별상품거래 (
  상품번호   number  NOT NULL, 거래일자   varchar2(8)
, 판매가격   number
, 판매수량   number
, 판매금액   number
, constraint 일별상품거래_fk foreign key(상품번호) references 상품
)
partition by reference (일별상품거래_fk);

Interval 파티셔닝

• 11g 부터는 Range 파티션을 생성할 대 아래와 같이 interval, 기준을 정의함으로써 정해진 간격으로 파티션이 자동 추가되도록 할 수 있다.
• 테이블을 일단위로 파티셔닝 했을때 유용하다.

create table 주문일자 (주문번호 number, 주문일시 date, ... )
partition by range(주문일시) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(
, partition p200907 values less than(to_date('2009/08/01', 'yyyy/mm/dd'))
, partition p200908 values less than(to_date('2009/09/01', 'yyyy/mm/dd'))
, partition p200909 values less than(to_date('2009/10/01', 'yyyy/mm/dd'))
, partition p200910 values less than(to_date('2009/11/01', 'yyyy/mm/dd'))
);

create table 고객 (고객번호 number, 고객명 varchar2(20), ... )
partition by range(고객번호) INTERVAL (100000)
( partition p_cust1 values less than ( 100001 )
, partition p_cust2 values less than ( 200001 )
, partition p_cust3 values less than ( 300001 )
) ;

오라클

srvctl stop home -o $ORACLE_HOME -s /app/oracle/state_file.state -n rac1

root로
cd /app/11g/grid/crs/install
./rootcrs.pl -unlock

oracle로

/app/11g/grid/OPatch/opatch napply -oh /app/11g/grid/ -local /home/oracle/16459322/

/app/11g/grid/OPatch/opatch apply -oh /app/11g/grid/ -local /home/oracle/16056267

/home/oracle/16459322/custom/server/16459322/custom/scripts/prepatch.sh -dbhome /app/oracle/11g/db

DB패치 oracle로
opatch napply -oh /app/oracle/11g/db -local /home/oracle/16459322/custom/server/16459322

opatch apply -oh /app/oracle/11g/db -local /home/oracle/16056267

/home/oracle/16459322/custom/server/16459322/custom/scripts/postpatch.sh -dbhome /app/oracle/11g/db

root로

cd /app/11g/grid/rdbms/install/

./rootadd_rdbms.sh

cd /app/11g/grid/crs/install

./rootcrs.pl -patch

oracle로

srvctl start home -o $ORACLE_HOME -s /app/oracle/state_file.state -n rac1

###########################################################################################################################

###########################################################################################################################

2번 노드에서

oracle유저로
srvctl stop home -o $ORACLE_HOME -s /app/oracle/state_file.state -n rac2

root로
cd /app/11g/grid/crs/install
./rootcrs.pl -unlock

oracle로

/app/11g/grid/OPatch/opatch napply -oh /app/11g/grid/ -local /home/oracle/16459322/

/app/11g/grid/OPatch/opatch apply -oh /app/11g/grid/ -local /home/oracle/16056267

/home/oracle/16459322/custom/server/16459322/custom/scripts/prepatch.sh -dbhome /app/oracle/11g/db

DB패치 oracle로
opatch napply -oh /app/oracle/11g/db -local /home/oracle/16459322/custom/server/16459322

opatch apply -oh /app/oracle/11g/db -local /home/oracle/16056267

/home/oracle/16459322/custom/server/16459322/custom/scripts/postpatch.sh -dbhome /app/oracle/11g/db

root로

cd /app/11g/grid/rdbms/install/

./rootadd_rdbms.sh

cd /app/11g/grid/crs/install

./rootcrs.pl -patch

oracle로

srvctl start home -o $ORACLE_HOME -s /app/oracle/state_file.state -n rac2

출처 : http://www.bysql.net/index.php?document_srl=18171

1. 10046 Event가 갖지 못한 DBMS_XPLAN.DISPLAY, DBMS_XPLAN.DISPLAY_CURSOR의 장점

2. DBMS_XPLAN

3. DBMS_XPLAN.DISPLAY_CURSOR

• Function Parameter
• FUNCTION DISPLAY_CURSOR(SQL_ID VARCHAR2 DEFAULT NULL,
                          CURSOR_CHILD_NO INTEGER DEFAULT 0,
                          FORMAT VARCHAR2 DEFAULT 'TYPICAL')
• 파라미터 설명
  

파라미터	설명
SQL_ID	실제 수행된 SQL의 SQL_ID 값을 입력하면 해당 SQL의 실행계획을 볼 수 있다.
	SQL_ID 값은 V$SQL, V$SQLAREA에서 확인할 수 있다.
	SQL_ID 값을 명시하지 않으면 해당 세션의 마지막에 실행된 문장을 의미한다.
CHILD_NUMBER	해당 SQL_ID의 CHILD NUMBER 값을 지정한다.
	CURSOR_CHILD_NO 값을 명시하지 않으면 해당 SQL_ID의 첫번째 CURSOR_CHILD_NO 값을 가져온다.
FORMAT	저장된 PLAN을 어떤 Format으로 보여줄 지 결정하는 파라미터

• FORMAT 옵션
  

  구분	FORMAT	내용
  예측 내용	BASIC	가장 기본적인 정보만 보여준다.
  	TYPICAL	FORMAT의 DEFAULT 값인 TYPICAL은 SQL 투닝에 필요한 NORMAL한 정보를 보여 준다. SQL 튜닝에 가장 유용하게 사용되는 PREDICATE INFORMATION이 제공된다.
  	ALL	TYPICAL  FORMAT에 QUERY BLOCK NAME과 COLUMN PROJECTION INFORMATION이 추가로 제공된다.
  	OUTLINE	TYPICAL FORMAT에 추가적으로 HIDDEN HINT인 OUTLINE GLOBAL HINT를 제공한다.
  	ADVANCE	ALL FORMAT에 OUTLINE FORMAT를 합친 정보를 제공한다.
  실측 내용	ALLSTATS	실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
  		수행횟수에 따라 누적된 값을 보여준다.
  	ALLATATS LAST	실제 엑스스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
  		마지막에 수행된 값을 보여준다.
  	ADVANCED ALLSTATS LSAT	DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 FORMAT의 정보를 보여준다.

4. DBMS_XPLAN.DISPLAY_CURSOR 사용방법

• PLAN 정보 기록
• 'GATHER_PLAN_STATISTICS' HINT 사용

  SELECT /*+GATHER_PLAN_STATISTICS*/                  *   FROM (SELECT E.*           FROM EMPLOYEE E          WHERE E.DEPARTMENT_ID = 50          ORDER BY E.EMPLOYEE_ID)  WHERE ROWNUM <= 100

• STSTISTICS_LEVEL를 ALL로 설정
• '_ROWWOURCE_EXECUTION_STATISTICS' 파라미터를 TRUE로 설정\
• PLAN 정보 조회

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

  
  

5. Plan 상의 항목들에 대한 설명

1) Basics 항목

• Id : 각 Operationd의 ID임. *가 달려있는 경우는 Predicate Informatio에 Access 및 Filter에 관한 정보를 표시함
• Operation : 각각 실행되는 JOB
• Name : Operationdl 엑세스 하는 Table 및 Index

2) Query Optimizer Estimations 항목(예상치)

• E-Rows : 각 Operation이 끝났을 때 return 되는 건수.
• E-Bytes : 각 Operation이 Temporany Space를 사용한 양
• Cost(%CPU) : 각 Operation의 Cost. 괄호 안의 내용은 CPU Cost의 백분율임. 이 값은 Child Operation의 Cost를 합친 누적치.
• E-Time : 수행시간

3) Runtime Statistics 항목

• Starts : 각 Operation을 반복 수행한 건수
• A-Rows : 각 Operation이 Return 한 건수
• A-Time : 실제 실행시간. 0.01초까지 나타남(HH:MM:SS.FF).  Child Operation의 A-Time을 합친 누적치

4) I/O Statistics

• Buffers : 각 Operation이 memory에서 읽은 Block 수.
• Reads : 각 Operation이 Disk에서 Read한 Block 수.
• Writes : 각 Operation이 Disk에서 White한 Block 수.

5) Memory Utilization Statistics(hash 작업이나 sort 작업 시 사용한 메모리 통계)

• OMen : Optimal Execution에 필요한 Memory
• SQL 실행 메모리가 최적의 크기를 가졌을때의 메모리. 여기서 메모리가 최적의 크기를 갖는다는 것은 예를 들어, disk에 write하지 않고 sort 작업을 수행하는 경우를 의미한다.
• 1Mem : One-pass Execution에 필요한 Momory
• SQL 실행 메모리가 1 pass의 크기를 가졌을 때의 메모리. 여기서 메모리가 1 pass의 크기를 갖는다는 의미는, 예를 들어 sort의 경우 disk에 임시 결과를 한번은 저장하고 결과를 merge해서 sort 작업을 마치는 경우를 의미한다.
• O/1/M : 각 Operation이 실행한 Optmal/One-pass/Multipass 횟수가 순서대로 표시됨
              O 일 경우 메모리공간(hash_area_size)이 부족하지 않아 temp 영역(disk)을 
              사용하지 않고 처리 되었다는 의미임. 
• multipass 횟수 : SQL 실행 메모리가 2 pass 이상의 크기를 가졌던 횟수.
• Used-mem : 마지막 실행 시 사용한 PGA -Memory
• Used-Tmp : 마지막 실행 시 메모리가 부족하여 Temporary Space를 대신 사용할 때 나타남. 보이는 값에 1024를 곱해야 함.
• Max-Tmp : 메모리가 부족하여 Temporary Space를 사용할 때 최대 Temp 사용량. Used-Tmp와 다른 점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을 경우에 항상 최대값만 보인다는 것. 보이는 값에 1024를 곱해야 함.

6) 쿼리블록 정보 : Plan 상의 Id별로 쿼리블럭 및 Alias 정보를 출력.,

7) Outline Date : 오라클이 내부적으로 사용한 힌트.

8) Predicate Information : Plan 상의 Id 별로 인덱스 액세스, Filter, 조인정보 등을 표출

9) Column Projection Information : Plan 상의 Id 별로 Select 되는 컬럼의 정보.

6. 쿼리 변형이 없는 단순 쿼리 튜닝의 경우는 최대한 단순화.

• Format : 'allstats last -rows +predicate'로 설정
• 예측 Row 수(E-row) 생략. 실행통계와 Predicate Information만 출력

7.쿼리 변형이 발생하거나 복잡한 쿼리 튜닝시 쿼리블럭과 힌트정보를 추가로 출력

• Format : 'allstats last -rows +alias +outline +predicate'로 설정

• Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력
• +alias : 쿼리블록 추가
• +outline : 오라클리 내부적으로 사용한 힌트정보를 출력

출처 :  http://blog.naver.com/bj2118/80069059099

shared pool 의 dump한결과

-Memory :공간 요청시 chunk 단위로 할당

-Freelist : Free chunk의 목록들

-LRU list : 현재 사용중이지 않는 재생성 가능한 chunk목록들

-Reserved Freelist : 최상위 Heap에만 존재, shared pool의 예약 영역에 대한 Freelist

-Freelist에서 원하는 크기의 chunk검색 -> LRU list 검색 -> 특정 크기 이상이면 Reserved list 검색

 ->모두 실패하면 ora-4031에러 발생

아래와같이 dump파일을 텍스트 문서로 하면 처음볼수있는건 시스템에 기본정보들을 볼수 있다.

/u01/app/oracle/admin/orcl/udump/orcl_ora_5836.trc                                                 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production                             
With the Partitioning                                                                              
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1                                                  
System name: Linux                                                                                 
Node name: localhost.localdomain                                                                   
Release: 2.6.9-42.0.0.0.1.EL                                                                       
Version: #1 Sun Oct 15 13:58:55 PDT 2006                                                           
Machine: i686                                                                                      
Instance name: orcl                                                                                
Redo thread mounted by this instance: 1                                                            
Oracle process number: 22                                                                          
Unix process pid: 5836

shared pool의 heap구조의 Top heap에 해당하는부분에 정보이다.

******************************************************                                             
HEAP DUMP heap name="sga heap"  desc=0x2000002c          <-----------  Top heap            

extent sz=0x32c8 alt=108 het=32767 rec=9 flg=-126 opc=0                                           
 parent=(nil) owner=(nil) nex=(nil) xsz=0xfd0                                                      
*****************************************************

HEAP DUMP heap name="sga heap(1                                                                    
 extent sz=0xfc4 alt=108 het=32767 rec=9 flg=-125 opc=0                                            
 parent=(nil) owner=(nil) nex=(nil) xsz=0x400000

EXTENT 상태를 확인할수 있는 자료

EXTENT 0 addr=0x26c00000         <------익스텐드                                                            
  Chunk 26c00038 sz=  4194248    perm      "perm           "  alo=134768   <---청크                        
EXTENT 1 addr=0x29000000                                                                           
  Chunk 29000038 sz=       24  R-freeable  "reserved stoppe"                                       
  Chunk 29000050 sz=   212888  R-free      "               "                                       
  Chunk 29033fe8 sz=       24  R-freeable  "reserved stoppe"                                       
  Chunk 29034000 sz=  3979072    perm      "perm           "  alo=3979072                          
  Chunk 293ff740 sz=     2240    free      "               "                                       
EXTENT 2 addr=0x29400000                                                                           
  Chunk 29400038 sz=       24  R-freeable  "reserved stoppe"                                        
  Chunk 29400050 sz=   212888  R-free      "               "                                       
  Chunk 29433fe8 sz=       24  R-freeable  "reserved stoppe"                                       
  Chunk 29434000 sz=  3214744    perm      "perm           "  alo=3214744                          
  Chunk 29744d98 sz=   765044    perm      "perm           "  alo=765044                           
  Chunk 297ffa0c sz=     1524    free      "               "                                       
EXTENT 3 addr=0x29800000                                                                           
  Chunk 29800038 sz=       24  R-freeable  "reserved stoppe"                                       
  Chunk 29800050 sz=   212888  R-free      "               "                                       
  Chunk 29833fe8 sz=       24  R-freeable  "reserved stoppe"                                       
  Chunk 29834000 sz=  3645256    perm      "perm           "  alo=3645256                          
  Chunk 29badf48 sz=   333196    perm      "perm           "  alo=333196                           
  Chunk 29bff4d4 sz=     2860    free      "               "              

freelist에 목록이 주소값으로 나와있으면 29bff4d4 이주소로 검색하면

FREE LISTS:Chunk 293ff740 sz=     2240    free 목록을 확인할수 있다.

FREE LISTS:                                                                                        
 Bucket 0 size=16           <----  버킷                                                                        
 Bucket 1 size=20                                                                                  
 Bucket 2 size=24                                                                                  
 Bucket 3 size=28                                                                                  
 Bucket 4 size=32                                                                                  
 Bucket 5 size=36                                                                                  
 Bucket 6 size=40                                                                                  
 Bucket 7 size=44                                                                                  
 Bucket 8 size=48                                                                                  
 Bucket 9 size=52                                                                                  
 Bucket 10 size=56                                                                                 
 Bucket 11 size=60                                                                                 
 Bucket 12 size=64                                                                                 
 Bucket 13 size=68                                                                                 
  Chunk 2a7fffbc sz=       68    free      "     <---- 프리 청크                        

..... 생략

Free lists는 프리chunk의 목록들이면 여기에 나오는 chunk 주소값은 heap구조상으로 익스텐드에

위치를 나타내준다.

UNPINNED RECREATABLE CHUNKS (lru first):                                                      
SEPARATOR                                                                                          
Unpinned space     =        0  rcr=0 trn=0                                                         
PERMANENT CHUNKS:                                                                                  
  Chunk 26c00038 sz=  4194248    perm      "perm           "  alo=134768                           
  Chunk 29badf48 sz=   333196    perm      "perm           "  alo=333196                           
  Chunk 29744d98 sz=   765044    perm      "perm           "  alo=765044                           
  Chunk 29034000 sz=  3979072    perm      "perm           "  alo=3979072                          
  Chunk 29434000 sz=  3214744    perm      "perm           "  alo=3214744                          
  Chunk 29834000 sz=  3645256    perm      "perm           "  alo=3645256                          
  Chunk 2b7ece44 sz=    76816    perm      "perm           "  alo=76816                            
  Chunk 2abff884 sz=     1040    perm      "perm           "  alo=1040                             
  Chunk 2afe0a1c sz=   128016    perm      "perm           "  alo=128016                           
  Chunk 2abe3c74 sz=   113680    perm      "perm           "  alo=113680                           
  Chunk 29fffa7c sz=     1040    perm      "perm           "  alo=1040                             
  Chunk 2a3fd14c sz=    11280    perm      "perm           "  alo=11280                            
  Chunk 29c34000 sz=  3979900    perm      "perm           "  alo=3979900                           
Permanent space    = 40015520 

lru list 정보를 확인할수 있으며 여기에 있는 목록들은 현재사용하지않은 재생성 가능한 chunk의 목록들이다.

RESERVED FREE LISTS:   <-----  예약 프리리스트                                     
 Reserved bucket 0 size=16                                                                         
 Reserved bucket 1 size=4400                                                                       
 Reserved bucket 2 size=8204                                                                       
 Reserved bucket 3 size=8460                                                                       
 Reserved bucket 4 size=8464                                                                       
 Reserved bucket 5 size=8468                                                                       
 Reserved bucket 6 size=8472                                                                       
 Reserved bucket 7 size=9296                                                                       
 Reserved bucket 8 size=9300                                                                       
 Reserved bucket 9 size=12320                                                                      
 Reserved bucket 10 size=12324                                                                     
 Reserved bucket 11 size=16396                                                                     
 Reserved bucket 12 size=32780                                                                     
 Reserved bucket 13 size=65548 

이상 대략적으로 재가 직접 Dump를 떠서 나름 공부해보면서 정리해본 자료입니다.

어차피 owi에 나와있는내용 한번 정리한 샘이라 책읽어 보시면 좀더 자세히 아실듯해요 ^_^