调整系统参数的几个重要视图

l   V$DB_CACHE_ADVICE(调整db_cache_size):

Column

Datatype

Description

ID

NUMBER

Buffer pool identifier (ranges from 1 to 8)

NAME

VARCHAR2(20)

Buffer pool name

BLOCK_SIZE

NUMBER

Block size in bytes for buffers in this pool. Possible values: the standard block size, the power of 2 non-standard block sizes, 2048, 4096, 8192, 16384, 32768.

ADVICE_STATUS

VARCHAR2(3)

Status of the advisory. ON indicates it is currently running; OFF indicates it is disabled (in this case the estimates are historical and calculated when last enabled).

SIZE_FOR_ESTIMATE

NUMBER

Cache size for prediction (in megabytes)

BUFFERS_FOR_ESTIMATE

NUMBER

Cache size for prediction (in terms of buffers)

ESTD_PHYSICAL_READ_FACTOR

NUMBER

Physical read factor for this cache size, which is the ratio of the number of estimated physical reads to the number of reads in the real cache. If there are no physical reads in the real cache, the value of this column is null.

ESTD_PHYSICAL_READS

NUMBER

Estimated number of physical reads for this cache size

相关参数：

db_cache_size、DB_CACHE_ADVICE、STATISTICS_LEVEL

alter system set DB_CACHE_ADVICE = {ON | READY | OFF}

默认值：如果STATISTICS_LEVEL是TYPICAL或ALL，则为ON ；如果 STATISTICS_LEVEL是BASIC，则为OFF

l   V$MTTR_TARGET_ADVICE(fast_start_mttr_target)

注：MTTR就是从出现故障到恢复中间的这段时间，当然是越短越好
MTBF就是两次故障之间的相隔时间，那自然是在越长越好

Column

Datatype

Description

MTTR_TARGET_FOR_ESTIMATE

NUMBER

MTTR setting being simulated. Equal to the current MTTR setting if this is the first row of the view.

ADVICE_STATUS

VARCHAR2(5)

Current status of MTTR simulation (ON, READY, or OFF)

DIRTY_LIMIT

NUMBER

Dirty buffer limit derived from the MTTR being simulated

ESTD_CACHE_WRITES

NUMBER

Estimated number of cache physical writes under this MTTR

ESTD_CACHE_WRITE_FACTOR

NUMBER

Estimated cache physical write ratio under this MTTR. It is the ratio of the estimated number of cache writes to the number of cache writes under the current MTTR setting.

ESTD_TOTAL_WRITES

NUMBER

Estimated total number of physical writes under this MTTR

ESTD_TOTAL_WRITE_FACTOR

NUMBER

Estimated total physical write ratio under this MTTR. It is the ratio of the estimated total number of physical writes to the total number of physical writes under the current MTTR setting.

ESTD_TOTAL_IOS

NUMBER

Estimated total number of I/Os under this MTTR

ESTD_TOTAL_IO_FACTOR

NUMBER

Estimated total I/O ratio under this MTTR. It is the ratio of the estimated total number of I/Os to the total number of I/Os under the current MTTR setting.

相关参数：

fast_start_mttr_target、STATISTICS_LEVEL

l   V$PGA_TARGET_ADVICE_HISTOGRAM (PGA_AGGREGATE_TARGET)

Column

Datatype

Description

PGA_TARGET_FOR_ESTIMATE

NUMBER

Value of PGA_AGGREGATE_TARGET for this prediction (in bytes)

PGA_TARGET_FACTOR

NUMBER

PGA_TARGET_FOR_ESTIMATE / the current value of the PGA_AGGREGATE_TARGET parameter

ADVICE_STATUS

VARCHAR2(3)

Indicates whether the advice is enabled (ON) or disabled (OFF) depending on the value of the STATISTICS_LEVEL parameter

LOW_OPTIMAL_SIZE

NUMBER

Lower bound for the optimal memory requirement of work areas included in this row (in bytes)

HIGH_OPTIMAL_SIZE

NUMBER

Upper bound for the optimal memory requirement of work areas included in this row (in bytes)

ESTD_OPTIMAL_EXECUTIONS

NUMBER

Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which are predicted to run optimal given a value of PGA_AGGREGATE_TARGET equal to PGA_TARGET_FOR_ESTIMATE

ESTD_ONEPASS_EXECUTIONS

NUMBER

Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which are predicted to run one-pass given a value of PGA_AGGREGATE_TARGET equal to PGA_TARGET_FOR_ESTIMATE

ESTD_MULTIPASSES_EXECUTIONS

NUMBER

Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which are predicted to run multi-pass given a value of PGA_AGGREGATE_TARGET equal to PGA_TARGET_FOR_ESTIMATE

ESTD_TOTAL_EXECUTIONS

NUMBER

Sum of ESTD_OPTIMAL_EXECUTIONS, ESTD_ONEPASS_EXECUTIONS, and ESTD_MULTIPASSES_EXECUTIONS

IGNORED_WORKAREAS_COUNT

NUMBER

Number of work areas with optimal memory requirement between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE ignored in the advice generation due to memory and CPU constraints

相关参数：

PGA_AGGREGATE_TARGET、STATISTICS_LEVEL

l   V$PGA_TARGET_ADVICE(PGA_AGGREGATE_TARGET)

Column

Datatype

Description

PGA_TARGET_FOR_ESTIMATE

NUMBER

Value of PGA_AGGREGATE_TARGET for this prediction (in bytes)

PGA_TARGET_FACTOR

NUMBER

PGA_TARGET_FOR_ESTIMATE / the current value of the PGA_AGGREGATE_TARGET parameter

ADVICE_STATUS

VARCHAR2(3)

Indicates whether the advice is enabled (ON) or disabled (OFF) depending on the value of the STATISTICS_LEVEL parameter

BYTES_PROCESSED

NUMBER

Total bytes processed by all the work areas considered by this advice (in bytes)

ESTD_EXTRA_BYTES_RW

NUMBER

Estimated number of extra bytes which would be read or written if PGA_AGGREGATE_TARGET was set to the value of the PGA_TARGET_FOR_ESTIMATE column. This number is derived from the estimated number and size of work areas which would run in one-pass (or multi-pass) for that value of PGA_AGGREGATE_TARGET.

ESTD_PGA_CACHE_HIT_PERCENTAGE

NUMBER

Estimated value of the cache hit percentage statistic when PGA_AGGREGATE_TARGET equals PGA_TARGET_FOR_ESTIMATE. This column is derived from the above two columns and is equal to BYTES_PROCESSED / (BYTES_PROCESSED + ESTD_EXTRA_BYTES_RW)

ESTD_OVERALLOC_COUNT

NUMBER

Estimated number of PGA memory over-allocations if the value of PGA_AGGREGATE_TARGET is set to PGA_TARGET_FOR_ESTIMATE. A nonzero value means that PGA_TARGET_FOR_ESTIMATE is not large enough to run the work area workload. Hence, the DBA should not set PGA_AGGREGATE_TARGET to PGA_TARGET_FOR_ESTIMATE since Oracle will not be able to honor that target.

相关参数：

PGA_AGGREGATE_TARGET、STATISTICS_LEVEL

l   V$SHARED_POOL_ADVICE(SHARE_POOL_SIZE)

Column

Datatype

Description

SHARED_POOL_SIZE_FOR_ESTIMATE

NUMBER

Shared pool size for the estimate (in megabytes)

SHARED_POOL_SIZE_FACTOR

NUMBER

Size factor with respect to the current shared pool size

ESTD_LC_SIZE

NUMBER

Estimated memory in use by the library cache (in megabytes)

ESTD_LC_MEMORY_OBJECTS

NUMBER

Estimated number of library cache memory objects in the shared pool of the specified size

ESTD_LC_TIME_SAVED

NUMBER

Estimated elapsed parse time saved (in seconds), owing to library cache memory objects being found in a shared pool of the specified size. This is the time that would have been spent in reloading the required objects in the shared pool had they been aged out due to insufficient amount of available free memory.

ESTD_LC_TIME_SAVED_FACTOR

NUMBER

Estimated parse time saved factor with respect to the current shared pool size

ESTD_LC_MEMORY_OBJECT_HITS

NUMBER

Estimated number of times a library cache memory object was found in a shared pool of the specified size

相关参数：

SHARE_POOL_SIZE、STATISTICS_LEVEL