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


select
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
from
v$system_event a,
v$system_event b
where
a.event = 'db file scattered read'
and
b.event = 'db file sequential read'
;

 

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

https://www.techrepublic.com/html/tr/sidebars/5138236-0.html

 

PGA_AGGREGATE_TARGET vs PGA_AGGREGATE_LIMIT

WITH

pgastat_denorm_1 AS (

SELECT /*+ MATERIALIZE NO_MERGE */

       snap_id,

       dbid,

       instance_number,

       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)

 GROUP BY

       snap_id,

       dbid,

       instance_number

),

pgastat_denorm_2 AS (

SELECT /*+ MATERIALIZE NO_MERGE */

       h.dbid,

       h.instance_number,

       s.startup_time,

       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

 GROUP BY

       h.dbid,

       h.instance_number,

       s.startup_time

),

pgastat_delta AS (

SELECT /*+ MATERIALIZE NO_MERGE */

       h1.snap_id,

       h1.dbid,

       h1.instance_number,

       s1.begin_interval_time,

       s1.end_interval_time,

       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.aggr_pga_auto_target,

       h1.aggr_pga_target_param,

       (h1.bytes_processed - h0.bytes_processed) bytes_processed,

       (h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,

       h1.global_memory_bound,

       h1.max_pga_allocated,

       h1.max_pga_used_aut_wa,

       h1.max_pga_used_man_wa,

       h1.tot_pga_allocated,

       h1.tot_pga_inuse,

       h1.tot_pga_used_aut_wa,

       h1.tot_pga_used_man_wa,

       h1.tot_freeable_pga_mem

  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

 GROUP BY

       snap_id

 ORDER BY

       snap_id;

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.

Source :https://weidongzhou.wordpress.com/2016/02/25/pga_aggregate_target-vs-pga_aggregate_limit/

Paramètre obsolète

https://mikedietrichde.com/2016/11/22/optimizer_adaptive_features-obsolete-in-oracle-12-2/

 

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:

• OPTIMIZER_ADAPTIVE_PLANS=TRUE by default
• OPTIMIZER_ADAPTIVE_STATISTICS=FALSE by default

https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2

Adjusting the optimizer_index_cost_adj Parameter

https://xcoolwinds.wordpress.com/2013/08/23/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

 

SQL>

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) */. 

PLug in sqlserver pour OEM 13c

https://docs.oracle.com/cd/E63000_01/SQLPG/overview.htm#SQLPG108

 

https://docs.oracle.com/cd/E24628_01/doc.121/e24473/plugin_mngr.htm#EMADM13102

OEM Agent 12c: Common issues and resolutions

https://developer.rackspace.com/blog/oemagent12c/

 

CPU too heavy

Constraint NOT NULL missing

 

Data Pump Import With EXCLUDE=CONSTRAINT Or INCLUDE=CONSTRAINT Is Excluding And Respectively Not Including NOT NULL Constraints (Doc ID 1930631.1)

After doing a Data Pump import (impdp) in 12.1.0.2 database release with parameter EXCLUDE=CONSTRAINT the NOT NULL constraints from source tables are not in target tables. Sequence of steps leading to the problem:
– Data Pump export of tables with NOT NULL and referential integrity constraints
– Data Pump import with EXCLUDE=CONSTRAINT

 

http://blog.umairmansoob.com/data-dump-import-missing-not-null-constraints/

Problème avant upgrade en 12.2

Pending dst session 

 

datapump (x) 

list des FREE Pps restant dans un VG AIX

 lsvg | egrep -w "backupvg|datavg" | lsvg -i | grep FREE

Taille des fichiers unix

du -ms expdp_$ORACLE_SID_20190* | awk '{ total += $1 }; END { print total }'