'오라클' 카테고리의 글 목록 (26 Page)

오라클

Child Cursor가 많은 경우 2014.02.12

Child Cursor가 많은 경우

2014. 2. 12. 14:40

728x90

동일한 SQL임에도 불구 하고 V$SQLAREA를 보면 version_count가 높은 경우가 존재 한다.

동일 SQL에 대하여 Child cursor가 많을 경우, 라이브러리 캐시 경합이 발생할 수 있다.

Child Cursor가 생기는 이유는 매우 다양하다.

어떤 이유로 해당 SQL에 Child Cursor가 생겼는지 확인 할 필요가 있다.

이럴때 V$SQL_SHARED_CURSOR 뷰를 이용하면 어떤 이유로 Version Count가 증가 하게 되었는지 이유를 확인 할 수 있다.

V$SQL_SHARED_CURSOR 테이블의 컬럼 값이 Y인 것이 Version Count가 증가하게 된 이유가 된다.

- Version Count가 큰 SQL 확인

SQL>SELECT SQL_ID , VERSION_COUNT , EXECUTIONS , INVALIDATIONS

FROM v$sqlarea

ORDER BY version_count desc

SQL_ID VERSION_COUNT EXECUTIONS INVALIDATIONS

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

gjm43un5cy843 4 4 3

9p6bq1v54k13j 4 11 2

1gfaj4z5hn1kf 3 36 0

dcstr36r0vz0d 3 28 0

08bqjmf8490s2 3 820 0

69subccxd9b03 3 129 0

47y3mqvyhpkvs 3 128 0

c6awqs517jpj0 3 224 0

ga9j9xk5cy9s0 3 224 0

3ktacv9r56b51 3 365 1

20vv6ttajyjzq 3 36 0

4yyb4104skrwj 3 26 0

8swypbbr0m372 3 365 1

39m4sx9k63ba2 3 224 0

cb21bacyh3c7d 3 160 0

2h0gb24h6zpnu 3 2 0

cvn54b7yz0s8u 3 224 0

6aq34nj2zb2n7 2 304 1

f6xf2v1pw72ww 2 5 0

cubcqrdcjb3mz 2 3 0

bsa0wjtftg3uw 2 112 1

0kugqg48477gf 2 26 0

1a8n1zgb7m90w 2 73 0

- 무슨 이유로 Version Count가 증가 하였는지 확인

SQL>SELECT * FROM v$sql_shared_cursor WHERE sql_id = '08bqjmf8490s2'

SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER BIND_MISMATCH BIND_LENGTH_UPGRADEABLE

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

08bqjmf8490s2 BE8E06AC B8E87670 0 N N

08bqjmf8490s2 BE8E06AC B8DEB10C 1 N Y

08bqjmf8490s2 BE8E06AC B8D3C814 2 Y N

3 rows

- Bind Mismatch 인데 Varchar 컬럼의 변수 크기로 인한 Version count 증가 인지,

데이터 타입 불 일치 인지 확인 -> varchar2 컬럼에 들어오는 변수값 크기 때문에 증가

SQL>SELECT NAME , POSITION , DATATYPE_STRING , VALUE_STRING

FROM DBA_HIST_SQLBIND

WHERE SQL_ID = '08bqjmf8490s2'

ORDER BY snap_id ASC

NAME POSITION DATATYPE_STRING VALUE_STRING

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

:B1 1 VARCHAR2(32) string_metrics_batchsize

:B1 1 VARCHAR2(32) NOTIFY_DEV_PING_STATE_TIMEOUT

:B1 1 VARCHAR2(32) string_metrics_batchsize

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(32) string_metrics_batchsize

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(32) NOTIFY_DEV_PING_STATE_TIMEOUT

:B1 1 VARCHAR2(32) string_metrics_batchsize

:B1 1 VARCHAR2(32) NOTIFY_DEV_PING_STATE_TIMEOUT

:B1 1 VARCHAR2(32) string_metrics_batchsize

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(32) string_metrics_batchsize

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(32) string_metrics_batchsize

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(32) string_metrics_batchsize

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(32) string_metrics_batchsize

:B1 1 VARCHAR2(32) NOTIFY_DEV_PING_STATE_TIMEOUT

:B1 1 VARCHAR2(32) string_metrics_batchsize

:B1 1 VARCHAR2(32) NOTIFY_DEV_PING_STATE_TIMEOUT

:B1 1 VARCHAR2(32) string_metrics_batchsize

:B1 1 VARCHAR2(32) NOTIFY_DEV_PING_STATE_TIMEOUT

:B1 1 VARCHAR2(128) EM_SCHEMA_OWNER

:B1 1 VARCHAR2(128) REPO_SEV_ENABLE_OPT

207 rows

- 각 각의 Plan이 다른 지 확인

SELECT *

FROM table(dbms_xplan.display_cursor('08bqjmf8490s2',NULL,'allstats last rows cost bytes +alias +outline +predicate'))

PLAN_TABLE_OUTPUT

SQL_ID 08bqjmf8490s2, child number 0

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

SELECT PARAMETER_VALUE FROM MGMT_PARAMETERS WHERE PARAMETER_NAME = :B1

Plan hash value: 2890534904

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

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

| 0 | SELECT STATEMENT | | | | 1 (100)|

|* 1 | INDEX RANGE SCAN| MGMT_PARAMETERS_IDX_01 | 1 | 38 | 1 (0)|

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1 / MGMT_PARAMETERS@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

OPT_PARAM('_optimizer_cost_model' 'cpu')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "MGMT_PARAMETERS"@"SEL$1"

("MGMT_PARAMETERS"."PARAMETER_NAME" "MGMT_PARAMETERS"."PARAMETER_VALUE"))

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

1 - access("PARAMETER_NAME"=:B1)

Note

-----

- SQL plan baseline SQL_PLAN_9992q9b0m30902f8b24ae used for this statement

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

SQL_ID 08bqjmf8490s2, child number 1

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

SELECT PARAMETER_VALUE FROM MGMT_PARAMETERS WHERE PARAMETER_NAME = :B1

Plan hash value: 2890534904

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

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

| 0 | SELECT STATEMENT | | | | 1 (100)|

|* 1 | INDEX RANGE SCAN| MGMT_PARAMETERS_IDX_01 | 1 | 38 | 1 (0)|

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1 / MGMT_PARAMETERS@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

OPT_PARAM('_optimizer_cost_model' 'cpu')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "MGMT_PARAMETERS"@"SEL$1"

("MGMT_PARAMETERS"."PARAMETER_NAME" "MGMT_PARAMETERS"."PARAMETER_VALUE"))

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

1 - access("PARAMETER_NAME"=:B1)

Note

-----

- SQL plan baseline SQL_PLAN_9992q9b0m30902f8b24ae used for this statement

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

SQL_ID 08bqjmf8490s2, child number 2

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

SELECT PARAMETER_VALUE FROM MGMT_PARAMETERS WHERE PARAMETER_NAME = :B1

Plan hash value: 2890534904

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

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

| 0 | SELECT STATEMENT | | | | 1 (100)|

|* 1 | INDEX RANGE SCAN| MGMT_PARAMETERS_IDX_01 | 1 | 38 | 1 (0)|

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1 / MGMT_PARAMETERS@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

DB_VERSION('11.2.0.1')

OPT_PARAM('_optimizer_cost_model' 'cpu')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "MGMT_PARAMETERS"@"SEL$1"

("MGMT_PARAMETERS"."PARAMETER_NAME" "MGMT_PARAMETERS"."PARAMETER_VALUE"))

END_OUTLINE_DATA

Predicate Information (identified by operation id):

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

1 - access("PARAMETER_NAME"=:B1)

Note

-----

- SQL plan baseline SQL_PLAN_9992q9b0m30902f8b24ae used for this statement

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

저작자표시

PREV 1 ···23 24 25 26 27 28 29 ···43 NEXT

멋지게 놀아라

오라클

Child Cursor가 많은 경우

+ Recent posts

티스토리툴바