Shulou(Shulou.com)06/01 Report--

aux_stats$base table (system statistics)

View operating system statistics col sname for a20col pname for a20col pval2 for a40select * from sys.aux_stats$; SNAME PNAME PVAL1 PVAL2-------------------- -------------------- ---------- ----------------------------------------SYSSTATS_INFO STATUS COMPLETEDSYSSTATS_INFO DSTART 08-24-2013 12:04SYSSTATS_INFO DSTOP 08-24-2013 12:04SYSSTATS_INFO FLAGS 1SYSSTATS_MAIN CPUSPEEDNW 3074.07407SYSSTATS_MAIN IOSEEKTIM 10SYSSTATS_MAIN IOTFRSPEED 4096SYSSTATS_MAIN SREADTIMSYSSTATS_MAIN MREADTIMSYSSTATS_MAIN CPUSPEEDSYSSTATS_MAIN MBRCSYSSTATS_MAIN MAXTHRSYSSTATS_MAIN SLAVETHaux_stats$is a base table with suffix $under sys administrator user. Schema must be written to query it. The so-called base table is the original table that provides data for dynamic performance view. Since the base table is very important, oracle stipulates that it is not allowed to directly access and modify the base table. If you know these, then say something else. This table records "operating system statistics." Oracle uses operating system statistics to correct the cost of executing the plan, which means that this information is one of the factors that affect the cost calculation. Note: If oracle collects OS statistics, CBO calculates costs using workload statistics mode. If oracle does not collect OS statistics, CBO calculates costs using non-workload statistics mode. If MBRC does not have parameter values, it means that OS statistics have not been collected. The formulas for calculating costs for these two models are different. Column Meaning: SQL> desc sys.aux_stats$Name Null? Type------------------------ -------- ----------------------------------------------------------------------------SNAME NOT NULL VARCHAR2(30)PNAME NOT NULL VARCHAR2(30)PVAL1 NUMBERPVAL2 VARCHAR2(255)SNAME： OS statistics: parameter name: parameter name: PVAL1: parameter value PVAL2: parameter value parameter interpretation FLAGS: flag CPUSPEEDNW: CPU frequency in non-workload statistics mode, directly from hardware IOSEEKTIM: IO addressing time (milliseconds), directly from hardware IOTFRSPEED: IO transfer rate (bytes/millisecond) SREADTIM: average time to read a single data block MREADTIM: average time to read multiple data blocks CPUSPEED: In workload statistics mode, CPU frequency evaluates a reasonable value according to the current workload MBRC: Oracle evaluates how many data blocks can be read in one multiblock read after collecting statistical information db_file_multiblock_read_count MAXTHR: Maximum IO throughput (bytes/second) SLAVETHR: Average IO throughput (bytes/second) These 6 parameters can be obtained only after oracle collects statistical information. What is the use? CBO calculates the cost of SQL statements by using database object statistics such as table indexes and operating system statistics such as CPU cycle IO speed data block read time to select the execution plan that takes the least time as the best execution plan. Oracle uses dbms_stats.gather_system_stats stored procedure to collect operating system statistics. The collected data is stored in sys.aux_stats$table. If we do the collection operation, there will be statistics. If we do not do it, there will be no statistics. These two methods of calculating the cost are different. Collect statistics for the system: dbms_stats.gather_system_stats Syntax execute dbms_stats.gather_system_stats ( gathering_mode varchar2 default 'noworkload' interval integer default null, stattab varchar2 default null, statid varchar2 default null, statown varchar2 default null); explain gathering_mode parameter, default value "novorkload", can also be set to "workload" meaning novorkload: non-workload statistics mode, collected data are from hardware workload: workload statistics mode, collected data needs to be counted within a specific database load interval, such data can truly reflect the operating system parameters of the database interval: You can specify the time interval for collecting statistics, for example, 5. Collect statistics for 5 minutes Command: execute dbms_stats.gather_system_stats ('novorkload ', 5); START and STOP keywords decide when to start and when to end collecting statistics Command: execute dbms_stats.gather_system_stats ('start'); Execute dbms_stats.gather_system_stats ('stop '); MBRC parameter, which is the abbreviation of initialization parameter db_file_multiblock_read_count. CBO will use MBRC to calculate the cost if statistical information is collected. CBO will use this initialization parameter db_file_multiblock_read_count to calculate the cost if statistical information is not collected.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.