Thursday, August 21, 2008

V$SESSION_WAIT_HISTORY

Oracle 11gR1

V$SESSION_WAIT_HISTORY displays the last 10 wait events for each active session.

Column

Datatype

Description

SID

NUMBER

Session identifier

SEQ#

NUMBER

Sequence of wait events; 1 is the most recent

EVENT#

NUMBER

Event number

EVENT

VARCHAR2(64)

Resource or event for which the session is waiting

P1TEXT

VARCHAR2(64)

Description of the first wait event parameter

P1

NUMBER

First wait event parameter (in decimal)

P2TEXT

VARCHAR2(64)

Description of the second wait event parameter

P2

NUMBER

Second wait event parameter (in decimal)

P3TEXT

VARCHAR2(64)

Description of the third wait event parameter

P3

NUMBER

Third wait event parameter (in decimal)

WAIT_TIME

NUMBER

Amount of time waited (in hundredths of a second)

WAIT_TIME_MICRO

NUMBER

Amount of time waited (in microseconds)

TIME_SINCE_LAST_WAIT_MICRO

NUMBER

Time elapsed (in microseconds) since the end of the previous wait in the wait history

Note:

1.

select * from V$SESSION_WAIT_HISTORY where sid = 1486;

SID

SEQ#

EVENT#

EVENT

P1TEXT

P1

P2TEXT

P2

P3TEXT

P3

WAIT_

TIME

WAIT_

COUNT

1486

1

252

SQL*Net message to client

driver id

1952673792

#bytes

1

0

0

1

1486

2

114

log file sync

buffer#

5134

0

0

2

1

1486

3

256

SQL*Net message from client

driver id

1952673792

#bytes

1

0

0

1

1486

4

252

SQL*Net message to client

driver id

1952673792

#bytes

1

0

0

1

1486

5

115

db file sequential read

file#

67

block#

48251

blocks

1

2

1

1486

6

115

db file sequential read

file#

67

block#

106309

blocks

1

1

1

1486

7

115

db file sequential read

file#

67

block#

51472

blocks

1

1

1

1486

8

256

SQL*Net message from client

driver id

1952673792

#bytes

1

0

0

1

1486

9

252

SQL*Net message to client

driver id

1952673792

#bytes

1

0

0

1

1486

10

256

SQL*Net message from client

driver id

1952673792

#bytes

1

0

0

1

Oracle dynamic performance views

Posted by Admin at 8/21/2008 02:08:00 PM 0 comments

Labels: ,

V$SQL_OPTIMIZER_ENV

Oracle 11gR1

V$SQL_OPTIMIZER_ENV displays the contents of the optimizer environment used to build the execution plan of a SQL cursor. This view can be joined with V$SQLAREA on (HASH_VALUE, ADDRESS) and with V$SQL on (HASH_VALUE, CHILD_ADDRESS).

Column

Datatype

Description

ADDRESS

RAW(4 | 8)

Address of the parent cursor

HASH_VALUE

NUMBER

Hash value of the parent cursor in the library cache. The hash value is the fixed index for the view and should be used when querying V$SQL_OPTIMIZER_ENV to avoid scanning the entire library cache.

SQL_ID

VARCHAR2(13)

SQL identifier

CHILD_ADDRESS

RAW(4 | 8)

Address of the child cursor

CHILD_NUMBER

NUMBER

Child cursor number

ID

NUMBER

Unique identifier of the parameter in the optimizer environment

NAME

VARCHAR2(40)

Name of the parameter

ISDEFAULT

VARCHAR2(3)

Indicates whether the parameter is set to the default value (YES) or not (NO)

VALUE

VARCHAR2(25)

Value of the parameter

Note:

1.

select * from V$SQL_OPTIMIZER_ENV where sql_id = 'g6vymwpvdn00a';

ADDRESS

HASH_

VALUE

SQL_ID

CHILD_ADDRESS

CHILD_

NUMBER

ID

NAME

ISDEFAULT

VALUE

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

2

parallel_execution_enabled

YES

true

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

9

optimizer_features_enable

NO

10.1.0

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

11

cpu_count

YES

16

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

12

active_instance_count

YES

1

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

13

parallel_threads_per_cpu

YES

2

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

14

hash_area_size

YES

131072

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

15

bitmap_merge_area_size

YES

1048576

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

16

sort_area_size

YES

65536

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

17

sort_area_retained_size

YES

0

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

24

pga_aggregate_target

YES

3145728 KB

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

25

_pga_max_size

NO

629140 KB

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

35

parallel_query_mode

YES

enabled

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

36

parallel_dml_mode

YES

disabled

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

37

parallel_ddl_mode

YES

enabled

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

38

optimizer_mode

YES

all_rows

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

48

cursor_sharing

YES

exact

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

50

star_transformation_enabled

YES

false

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

66

optimizer_index_cost_adj

YES

100

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

67

optimizer_index_caching

YES

0

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

70

query_rewrite_enabled

YES

true

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

71

query_rewrite_integrity

NO

stale_tolerated

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

101

workarea_size_policy

YES

auto

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

105

optimizer_dynamic_sampling

YES

2

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

112

statistics_level

YES

typical

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

114

skip_unusable_indexes

YES

true

0000000742A9BDB0

1993998346

g6vymwpvdn00a

00000007428D5430

0

165

optimizer_secure_view_merging

NO

false

2. In Oracle 10.1 and above, optimizer environment variables are externalized at:

instance level: V$SYS_OPTIMIZER_ENV

session level: V$SES_OPTIMIZER_ENV

statement level: V$SQL_OPTIMIZER_ENV

Use the values in these views when determining why execution plans differ.

3. Optimizer Environment Variable values reported by the dynamic performance views include:

active_instance_count parallel_dml_mode

bitmap_merge_area_size parallel_execution_enabled

cpu_count parallel_query_mode

cursor_sharing parallel_threads_per_cpu

db_file_multiblock_read_count pga_aggregate_target

hash_area_size query_rewrite_enabled

optimizer_dynamic_sampling query_rewrite_integrity

optimizer_features_enable skip_unusable_indexes

optimizer_index_caching sort_area_retained_size

optimizer_index_cost_adj sort_area_size

optimizer_mode star_transformation_enabled

optimizer_mode_hinted statistics_level

parallel_ddl_mode workarea_size_policy

Oracle dynamic performance views

Posted by Admin at 8/21/2008 02:08:00 PM 0 comments

Labels: ,

V$SES_OPTIMIZER_ENV

Oracle 11gR1

V$SES_OPTIMIZER_ENV displays the contents of the optimizer environment used by each session. When a new session is first created, it automatically inherits its optimizer environment from the optimizer environment defined at the instance level by V$SYS_OPTIMIZER_ENV. The value of certain parameters can be dynamically modified by issuing an ALTER SESSION statement.

Column

Datatype

Description

SID

NUMBER

Session identifier. This column can be used to join with V$SESSION on the SID column.

ID

NUMBER

Unique identifier of the parameter in the optimizer environment

NAME

VARCHAR2(40)

Name of the parameter

SQL_FEATURE

VARCHAR2(64)

Associated feature control ID

ISDEFAULT

VARCHAR2(3)

Indicates whether the parameter is set to the default value (YES) or not (NO)

VALUE

VARCHAR2(25)

Value of the parameter for the session

Note:

1.

select * from V$SES_OPTIMIZER_ENV where sid = 1487;

SID

ID

NAME

ISDEFAULT

VALUE

1487

2

parallel_execution_enabled

YES

true

1487

9

optimizer_features_enable

NO

10.1.0

1487

11

cpu_count

YES

16

1487

12

active_instance_count

YES

1

1487

13

parallel_threads_per_cpu

YES

2

1487

14

hash_area_size

YES

131072

1487

15

bitmap_merge_area_size

YES

1048576

1487

16

sort_area_size

YES

65536

1487

17

sort_area_retained_size

YES

0

1487

24

pga_aggregate_target

YES

3145728 KB

1487

25

_pga_max_size

NO

629140 KB

1487

35

parallel_query_mode

YES

enabled

1487

36

parallel_dml_mode

YES

disabled

1487

37

parallel_ddl_mode

YES

enabled

1487

38

optimizer_mode

YES

all_rows

1487

48

cursor_sharing

YES

exact

1487

50

star_transformation_enabled

YES

false

1487

66

optimizer_index_cost_adj

YES

100

1487

67

optimizer_index_caching

YES

0

1487

70

query_rewrite_enabled

YES

true

1487

71

query_rewrite_integrity

NO

stale_tolerated

1487

101

workarea_size_policy

YES

auto

1487

105

optimizer_dynamic_sampling

YES

2

1487

112

statistics_level

YES

typical

1487

114

skip_unusable_indexes

YES

true

1487

165

optimizer_secure_view_merging

NO

false

2. In Oracle 10.1 and above, optimizer environment variables are externalized at:

instance level: V$SYS_OPTIMIZER_ENV

session level: V$SES_OPTIMIZER_ENV

statement level: V$SQL_OPTIMIZER_ENV

Use the values in these views when determining why execution plans differ.

3. Optimizer Environment Variable values reported by the dynamic performance views include:

active_instance_count parallel_dml_mode

bitmap_merge_area_size parallel_execution_enabled

cpu_count parallel_query_mode

cursor_sharing parallel_threads_per_cpu

db_file_multiblock_read_count pga_aggregate_target

hash_area_size query_rewrite_enabled

optimizer_dynamic_sampling query_rewrite_integrity

optimizer_features_enable skip_unusable_indexes

optimizer_index_caching sort_area_retained_size

optimizer_index_cost_adj sort_area_size

optimizer_mode star_transformation_enabled

optimizer_mode_hinted statistics_level

parallel_ddl_mode workarea_size_policy

Oracle dynamic performance views

Posted by Admin at 8/21/2008 02:07:00 PM 0 comments

Labels: ,

Subscribe to: Posts (Atom)