En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

  • optimizer_index_cost_adj.sql

    col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999
    col c2 heading 'Average Waits for|Index Read I/O' format 9999.999
    col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99
    col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99
    col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999

    a.average_wait c1,
    b.average_wait c2,
    a.total_waits /(a.total_waits + b.total_waits) c3,
    b.total_waits /(a.total_waits + b.total_waits) c4,
    (b.average_wait / a.average_wait)*100 c5
    v$system_event a,
    v$system_event b
    a.event = 'db file scattered read'
    b.event = 'db file sequential read'


    source : https://www.techrepublic.com/article/database-optimization-tweaking-oracle-sql-performance-parameters/#




    pgastat_denorm_1 AS (
           SUM(CASE name WHEN 'PGA memory freed back to OS'           THEN value ELSE 0 END) pga_mem_freed_to_os,
           SUM(CASE name WHEN 'aggregate PGA auto target'             THEN value ELSE 0 END) aggr_pga_auto_target,
           SUM(CASE name WHEN 'aggregate PGA target parameter'        THEN value ELSE 0 END) aggr_pga_target_param,
           SUM(CASE name WHEN 'bytes processed'                       THEN value ELSE 0 END) bytes_processed,
           SUM(CASE name WHEN 'extra bytes read/written'              THEN value ELSE 0 END) extra_bytes_rw,
           SUM(CASE name WHEN 'global memory bound'                   THEN value ELSE 0 END) global_memory_bound,
           SUM(CASE name WHEN 'maximum PGA allocated'                 THEN value ELSE 0 END) max_pga_allocated,
           SUM(CASE name WHEN 'maximum PGA used for auto workareas'   THEN value ELSE 0 END) max_pga_used_aut_wa,
           SUM(CASE name WHEN 'maximum PGA used for manual workareas' THEN value ELSE 0 END) max_pga_used_man_wa,
           SUM(CASE name WHEN 'total PGA allocated'                   THEN value ELSE 0 END) tot_pga_allocated,
           SUM(CASE name WHEN 'total PGA inuse'                       THEN value ELSE 0 END) tot_pga_inuse,
           SUM(CASE name WHEN 'total PGA used for auto workareas'     THEN value ELSE 0 END) tot_pga_used_aut_wa,
           SUM(CASE name WHEN 'total PGA used for manual workareas'   THEN value ELSE 0 END) tot_pga_used_man_wa,
           SUM(CASE name WHEN 'total freeable PGA memory'             THEN value ELSE 0 END) tot_freeable_pga_mem
      FROM dba_hist_pgastat
     WHERE name IN
    ('PGA memory freed back to OS'
    ,'aggregate PGA auto target'
    ,'aggregate PGA target parameter'
    ,'bytes processed'
    ,'extra bytes read/written'
    ,'global memory bound'
    ,'maximum PGA allocated'
    ,'maximum PGA used for auto workareas'
    ,'maximum PGA used for manual workareas'
    ,'total PGA allocated'
    ,'total PGA inuse'
    ,'total PGA used for auto workareas'
    ,'total PGA used for manual workareas'
    ,'total freeable PGA memory'
       AND snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time > sysdate -60)
    pgastat_denorm_2 AS (
           MIN(h.pga_mem_freed_to_os) pga_mem_freed_to_os,
           MIN(h.bytes_processed) bytes_processed,
           MIN(h.extra_bytes_rw) extra_bytes_rw
      FROM pgastat_denorm_1 h,
           dba_hist_snapshot s
     WHERE s.snap_id = h.snap_id
       AND s.dbid = h.dbid
       AND s.instance_number = h.instance_number
    pgastat_delta AS (
           ROUND((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 24 * 60 * 60) interval_secs,
           (h1.pga_mem_freed_to_os - h0.pga_mem_freed_to_os) pga_mem_freed_to_os,
           (h1.bytes_processed - h0.bytes_processed) bytes_processed,
           (h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,
      FROM pgastat_denorm_1 h0,
           pgastat_denorm_1 h1,
           dba_hist_snapshot s0,
           dba_hist_snapshot s1,
           pgastat_denorm_2 min /* to see cumulative use (replace h0 with min on select list above) */
     WHERE h1.snap_id = h0.snap_id + 1
       AND h1.dbid = h0.dbid
       AND h1.instance_number = h0.instance_number
       AND s0.snap_id = h0.snap_id
       AND s0.dbid = h0.dbid
       AND s0.instance_number = h0.instance_number
       AND s1.snap_id = h1.snap_id
       AND s1.dbid = h1.dbid
       AND s1.instance_number = h1.instance_number
       AND s1.snap_id = s0.snap_id + 1
       AND s1.startup_time = s0.startup_time
       AND s1.begin_interval_time > (s0.begin_interval_time + (1 / (24 * 60))) /* filter out snaps apart < 1 min */
       AND min.dbid = s1.dbid
       AND min.instance_number = s1.instance_number
       AND min.startup_time = s1.startup_time
    SELECT snap_id,
           TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI') begin_time,
           TO_CHAR(MIN(end_interval_time), 'YYYY-MM-DD HH24:MI') end_time,
           ROUND(SUM(pga_mem_freed_to_os) / POWER(2, 30), 3) pga_mem_freed_to_os,
           ROUND(SUM(aggr_pga_auto_target) / POWER(2, 30), 3) aggr_pga_auto_target,
           ROUND(SUM(aggr_pga_target_param) / POWER(2, 30), 3) aggr_pga_target_param,
           ROUND(SUM(bytes_processed) / POWER(2, 30), 3) bytes_processed,
           ROUND(SUM(extra_bytes_rw) / POWER(2, 30), 3) extra_bytes_rw,
           ROUND(SUM(global_memory_bound) / POWER(2, 30), 3) global_memory_bound,
           ROUND(SUM(max_pga_allocated) / POWER(2, 30), 3) max_pga_allocated,
           ROUND(SUM(max_pga_used_aut_wa) / POWER(2, 30), 3) max_pga_used_aut_wa,
           ROUND(SUM(max_pga_used_man_wa) / POWER(2, 30), 3) max_pga_used_man_wa,
           ROUND(SUM(tot_pga_allocated) / POWER(2, 30), 3) tot_pga_allocated,
           ROUND(SUM(tot_pga_inuse) / POWER(2, 30), 3) tot_pga_inuse,
           ROUND(SUM(tot_pga_used_aut_wa) / POWER(2, 30), 3) tot_pga_used_aut_wa,
           ROUND(SUM(tot_pga_used_man_wa) / POWER(2, 30), 3) tot_pga_used_man_wa,
           ROUND(SUM(tot_freeable_pga_mem) / POWER(2, 30), 3) tot_freeable_pga_mem,
           0 dummy_15
      FROM pgastat_delta
    Captured and imported the result into Excel spreadsheet. Then I created the following two charts showing PGA allocation and usage for the past 60 days and 7 days.

  • Paramètre obsolète



    The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2.

    It gets replaced with two parameters of whom one is enabled, the other one is disabled by default:



  • Adjusting the optimizer_index_cost_adj Parameter



    Issue the following query to view the average wait times for both of the wait events:

    SQL> select event, average_wait from v$system_event
           where event like 'db file s%read';
    EVENT                                                            AVERAGE_WAIT
    ---------------------------------                            -------------------------------
    db file sequential read                                                   .91
    db file scattered read                                                   1.41



    Based on the output of this query, single block sequential reads take roughly 75% of the time it takes to perform a multiblock (scattered) read. This indicates that the optimizer_index_cost_adj parameter should be set to somewhere around 75. However, as mentioned earlier, setting the parameter at the database level isn't recommended—instead, use this parameter sparingly for specific statements where you want to force the use of an index. Note that setting the parameter at the session level requires the DBA to grant the user privileges to issue the alter session command (or you must do it through some other procedural mechanism). A good alternative to having to issue the alter session statement is to specify the OPT_PARAM hint to change the parameter for a specific query, such as /*+ opt_param('optimizer_index_cost_adj',50) */