Tracnac

alter system set "_disable_streams_pool_auto_tuning"=TRUE;

Vérification :

select * from v$sgastat where pool ='streams pool';

col "Hidden Parameter" form a30

Tracnac

CONN / AS SYSDBA

SET SERVEROUTPUT ON
DECLARE
  l_latency  PLS_INTEGER;
  l_iops     PLS_INTEGER;
  l_mbps     PLS_INTEGER;
BEGIN
 DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 1, 

Tracnac

The Big Five

At the time of writing, a query against the view v$sql_hint on Oracle 12.1.0.2 reports 332 hints – but there are very few which we should really consider as safe for production code, and it’s best to view even those as nothing more than a medium-term tool to stabilise performance until the optimizer is able to do a better job with our SQL.

The handful of hints that I tend to rely on for solving problems is basically a set of what I call “structural” queries though in recent years it has become appropriate to label them as “query block” hints. These are hints that give the optimizer some idea of the shape of the best plan without trying to enforce every detail of how it should finalize the plan. The hints (with their negatives where appropriate) are:

• Unnest / no_unnest — Whether or not to unnest subqueries

Tracnac

 alter diskgroup DATA_10FA mount restricted;

Diskgroup altered.

 alter diskgroup DATA_10FA set attribute 'compatible.asm'='11.2.0.2.0';

Diskgroup altered.

Tracnac

SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE"
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'

Tracnac

select dhsnap.begin_interval_time, dhsnap.end_interval_time, dhstat.cpu_time_total/1000000 time_exec_seconds, dhstat.sql_id, replace(to_char(substr(dhsql.sql_text,1,128)),chr(10),'') sql_text
from DBA_HIST_SQLSTAT dhstat, DBA_HIST_SNAPSHOT dhsnap, DBA_HIST_SQLTEXT dhsql
where dhstat.parsing_schema_name = '<SHEMANAME>'
and dhstat.snap_id = dhsnap.snap_id
and dhstat.sql_id = dhsql.sql_id
order by time_exec_seconds desc

Tracnac

BEGIN
dbms_auto_task_admin.disable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

Tracnac

alter system set "_optimizer_adaptive_cursor_sharing"=FALSE scope=both sid='*';
alter system set "_optimizer_extended_cursor_sharing_rel"='NONE' scope=both sid='*';
alter system set "_optimizer_extended_cursor_sharing"='NONE' scope=both sid='*';
alter system set "_OPTIMIZER_USE_FEEDBACK"=FALSE scope=both sid='*';

Tracnac

From : https://oracle-base.com/articles/11g/sql-performance-analyzer-11gr1

EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'SPA_STS');
DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
     SELECT VALUE(a)
     FROM   TABLE(

Tracnac

shutdown immediate
startup upgrade
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
purge dba_recyclebin;
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;