L v $ db_cache_advice: Turn DB_Cache_Size:
ColumnDatatypeDescriptionIDNUMBERBuffer pool identifier (ranges from 1 to 8) NAMEVARCHAR2 (20) Buffer pool nameBLOCK_SIZENUMBERBlock 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_STATUSVARCHAR2 (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_ESTIMATENUMBERCache size for prediction (in megabytes) BUFFERS_FOR_ESTIMATENUMBERCache size for prediction (in terms of buffers) ESTD_PHYSICAL_READ_FACTORNUMBERPhysical 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.ested_physical_readsnumberestimated Number of Physical Reads for this cache size
Related parameters:
DB_CACHE_SIZE, DB_CACHE_ADVICE, STATISTICS_LEVEL
Alter System Set DB_Cache_Advice = {ON | Ready | Off}
Default: If statistics_level is Typical or ALL, it is ON; if statistics_level is Basic, OFF
L v $ mttr_target_advice (fast_start_mttr_target)
Note: The Mttr is from the middle of failure to recovery, of course, the shorter, the better the MTBF is the separation time between the two faults, then the longer it is, the longer the better.
ColumnDatatypeDescriptionMTTR_TARGET_FOR_ESTIMATENUMBERMTTR setting being simulated. Equal to the current MTTR setting if this is the first row of the view.ADVICE_STATUSVARCHAR2 (5) Current status of MTTR simulation (ON, READY, or OFF) DIRTY_LIMITNUMBERDirty buffer limit derived from the MTTR being simulatedESTD_CACHE_WRITESNUMBEREstimated number of cache physical writes under this MTTRESTD_CACHE_WRITE_FACTORNUMBEREstimated 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_WRITESNUMBEREstimated total number of physical writes under this MTTRESTD_TOTAL_WRITE_FACTORNUMBEREstimated 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_IOSNUMBEREstimated total number of I / Os under this MTTRESTD_TOTAL_IO_FACTORNUMBEREstimated total I / O rati O 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. Related parameters:
Fast_start_mttr_target, statistics_level
L v $ PGA_TARGET_ADVICE_HISTOGRAM (PGA_AGGREGATE_TARGET)
ColumnDatatypeDescriptionPGA_TARGET_FOR_ESTIMATENUMBERValue of PGA_AGGREGATE_TARGET for this prediction (in bytes) PGA_TARGET_FACTORNUMBERPGA_TARGET_FOR_ESTIMATE / the current value of the PGA_AGGREGATE_TARGET parameterADVICE_STATUSVARCHAR2 (3) Indicates whether the advice is enabled (ON) or disabled (OFF) depending on the value of the STATISTICS_LEVEL parameterLOW_OPTIMAL_SIZENUMBERLower bound for the optimal memory requirement of work areas included in this row (in bytes) HIGH_OPTIMAL_SIZENUMBERUpper bound for the optimal memory requirement of work areas included in this row (in bytes) ESTD_OPTIMAL_EXECUTIONSNUMBERNumber 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_ESTIMATEESTD_ONEPASS_EXECUTIONSNUMBERNumber of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which are predicted t o run one-pass given a value of PGA_AGGREGATE_TARGET equal to PGA_TARGET_FOR_ESTIMATEESTD_MULTIPASSES_EXECUTIONSNUMBERNumber 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_ESTIMATEESTD_TOTAL_EXECUTIONSNUMBERSum of ESTD_OPTIMAL_EXECUTIONS, ESTD_ONEPASS_EXECUTIONS, and ESTD_MULTIPASSES_EXECUTIONSIGNORED_WORKAREAS_COUNTNUMBERNumber 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 Related parameters:
PGA_AGGREGATE_TARGET, STATISTICS_LEVEL
L v $ PGA_TARGET_ADVICE (PGA_AGGREGATE_TARGET)
ColumnDatatypeDescriptionPGA_TARGET_FOR_ESTIMATENUMBERValue of PGA_AGGREGATE_TARGET for this prediction (in bytes) PGA_TARGET_FACTORNUMBERPGA_TARGET_FOR_ESTIMATE / the current value of the PGA_AGGREGATE_TARGET parameterADVICE_STATUSVARCHAR2 (3) Indicates whether the advice is enabled (ON) or disabled (OFF) depending on the value of the STATISTICS_LEVEL parameterBYTES_PROCESSEDNUMBERTotal bytes processed by all the work areas considered by this advice (in bytes) ESTD_EXTRA_BYTES_RWNUMBEREstimated 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_PERCENTAGENUMBEREstimated 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 t o BYTES_PROCESSED / (BYTES_PROCESSED ESTD_EXTRA_BYTES_RW) ESTD_OVERALLOC_COUNTNUMBEREstimated 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. Related parameters:
PGA_AGGREGATE_TARGET, STATISTICS_LEVEL
L v $ shared_pool_advice (share_pool_size)
ColumnDatatypeDescriptionSHARED_POOL_SIZE_FOR_ESTIMATENUMBERShared pool size for the estimate (in megabytes) SHARED_POOL_SIZE_FACTORNUMBERSize factor with respect to the current shared pool sizeESTD_LC_SIZENUMBEREstimated memory in use by the library cache (in megabytes) ESTD_LC_MEMORY_OBJECTSNUMBEREstimated number of library cache memory objects in the shared pool of the specified sizeESTD_LC_TIME_SAVEDNUMBEREstimated 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_FACTORNUMBEREstimated parse time saved factor with respect to the current shared pool sizeESTD_LC_MEMORY_OBJECT_HITSNUMBEREstimated number of times a library cache memory object was found in a shared pool of the specified size parameters:
Share_pool_size, statistics_level
