What happens when you execute dbms_stats.gather_system_stats() with the ‘Exadata’ option ?
Here’s what my system stats look like (12.2.0.1 test results) after doing so. (The code to generate the two different versions is at the end of the note).
System Stats ============ Status: COMPLETED Timed: 13-Aug-2019 15:00:00 - 13-Aug-2019 15:00:00 -------------------------------------------------- CPUSPEED : CPUSPEEDNW : 918 IOSEEKTIM : 10 IOTFRSPEED : 204,800 MAXTHR : MBRC : 128 MREADTIM : SLAVETHR : SREADTIM : PL/SQL procedure successfully completed. MBRC : 128 MREADTIM : SREADTIM : CPUSPEED : CPUSPEEDNW : 918 IOSEEKTIM : 10 IOTFRSPEED : 204,800 MAXTHR : SLAVETHR : PL/SQL procedure successfully completed.
All the code does is set the MBRC, IOSEEKTIM, and IOTFRSPEED to fixed values and the only real gather is the CPUSPEEDNW. The parameters showing blanks are deliberately set null by the procedure – before I called the gather_system_stats() every parameter had a value. You could also check the SQL trace file (with bind captured enabled) to see the statements that deliberately set those parameters to null if you want more proof.
What are the consequences of this call (assuming you haven’t also done something with the calibrate_io() procedure? Essentially Oracle now has information that says a single (8KB) block read request will take marginally over 10 milliseconds, and a multiblock read request of 1MB will take just over 15 milliseconds: in other words “tablescans are great, don’t use indexes unless they’re really precisely targetted”. To give you a quantitative feel for the numbers: given the choice between doing a tablescan of 1GB to pick 1,500 randomly scattered rows and using a perfect index the optimizer would choose the index.
To explain the time calculations: Oracle has set an I/O seek time of 10 ms, and a transfer rate of 204,800 bytes per ms (200 MB/s), with the guideline that a “typical” multiblock read is going to achieve 128 blocks. So the optimizer believes a single block read will take 10 + 8192/204800 ms = 10.04ms, while a multiblock read request for 1MB will take 10 + 1048576/204800 ms = 15.12 ms.
It’s also important to note that Oracle will use the 128 MBRC value in its calculation of the cost of the tablescan – even if you’ve set the db_file_mulitblock_read_count parameter for the session or system to something smaller; and if you have set the db_file_multiblock_read_count that’s the maximum size of multiblock read that the run-time engine will use.
Here are the two procedures I used to report the values above. You will only need the privilege to execute the dbms_stats package for the second one, but you’ll need the privilege to access the SYS table aux_stats$ to use the first. The benefit of the first one is that it can’t go out of date as versions change.
rem rem Script: get_system_stats.sql rem Author: Jonathan Lewis rem Dated: March 2002 rem rem Last tested rem 18.3.0.0 rem 12.2.0.1 rem 12.1.0.2 rem 11.2.0.4 rem set linesize 180 set trimspool on set pagesize 60 set serveroutput on spool get_system_stats declare m_value number; m_status varchar2(64); m_start date; m_stop date; begin for r1 in ( select rownum rn, pname from sys.aux_stats$ where sname = 'SYSSTATS_MAIN' ) loop dbms_stats.get_system_stats(m_status, m_start, m_stop, r1.pname, m_value); if r1.rn = 1 then dbms_output.put_line('System Stats'); dbms_output.put_line('============'); dbms_output.put_line('Status: ' || m_status); dbms_output.put_line( 'Timed: ' || to_char(m_start,'dd-Mon-yyyy hh24:mi:ss') || ' - ' || to_char(m_stop ,'dd-Mon-yyyy hh24:mi:ss') ); dbms_output.put_line('--------------------------------------------------'); end if; dbms_output.put_line(rpad(r1.pname,15) || ' : ' || to_char(m_value,'999,999,999')); end loop; end; / declare m_value number; m_status varchar2(64); m_start date; m_stop date; begin dbms_stats.get_system_stats(m_status, m_start, m_stop, 'MBRC', m_value); dbms_output.put_line('MBRC : ' || to_char(m_value,'999,999,999')); dbms_stats.get_system_stats(m_status, m_start, m_stop, 'MREADTIM', m_value); dbms_output.put_line('MREADTIM : ' || to_char(m_value,'999,999,999')); dbms_stats.get_system_stats(m_status, m_start, m_stop, 'SREADTIM', m_value); dbms_output.put_line('SREADTIM : ' || to_char(m_value,'999,999,999')); dbms_stats.get_system_stats(m_status, m_start, m_stop, 'CPUSPEED', m_value); dbms_output.put_line('CPUSPEED : ' || to_char(m_value,'999,999,999')); dbms_stats.get_system_stats(m_status, m_start, m_stop, 'CPUSPEEDNW', m_value); dbms_output.put_line('CPUSPEEDNW : ' || to_char(m_value,'999,999,999')); dbms_stats.get_system_stats(m_status, m_start, m_stop, 'IOSEEKTIM', m_value); dbms_output.put_line('IOSEEKTIM : ' || to_char(m_value,'999,999,999')); dbms_stats.get_system_stats(m_status, m_start, m_stop, 'IOTFRSPEED', m_value); dbms_output.put_line('IOTFRSPEED : ' || to_char(m_value,'999,999,999')); dbms_stats.get_system_stats(m_status, m_start, m_stop, 'MAXTHR', m_value); dbms_output.put_line('MAXTHR : ' || to_char(m_value,'999,999,999')); dbms_stats.get_system_stats(m_status, m_start, m_stop, 'SLAVETHR', m_value); dbms_output.put_line('SLAVETHR : ' || to_char(m_value,'999,999,999')); end; / spool off
Recent comments
1 year 45 weeks ago
2 years 5 weeks ago
2 years 9 weeks ago
2 years 10 weeks ago
2 years 14 weeks ago
2 years 36 weeks ago
3 years 4 weeks ago
3 years 33 weeks ago
4 years 18 weeks ago
4 years 18 weeks ago