CHERIF BEN HENDA

19c:Hint Usage Reporting feature.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_cursor('474wtnf3p7973', null,FORMAT=>'HINT_REPORT'));

Protégé : Display the contents of the execution plan of any loaded cursor for a SQL in all RAC nodes

DBMS_UTILITY.expand_sql_text

DECLARE
  l_clob CLOB;
  i_clob CLOB := q'[   
                   SELECT /*+ optimizer_adaptive */
                   count(CHB_t1.pad), count(CHB_t2.pad)
                   FROM CHB_t1, CHB_t2
                  WHERE CHB_t1.id = CHB_t2.id
                    AND CHB_t1.n = 666
                    AND burn_cpu(CHB_t1.id / CHB_t1.id) = 1 ]';

BEGIN
  DBMS_UTILITY.expand_sql_text(input_sql_text  => i_clob,
                               output_sql_text => l_clob);
  DBMS_OUTPUT.put_line(i_clob);
  DBMS_OUTPUT.put_line(l_clob);
END;

SQL Server Architecture Explained: Named Pipes, Optimizer, Buffer Manage

view https://www.guru99.com/sql-server-architecture.html

Oracle Developer Live—MySQL

Watch technical sessions, hands-on-labs, demos, panels, and Q&A with experts on demand.

Take a Class (oracle.com)

GitHub – oracle/db-sample-schemas: Oracle Database Sample Schemas

Announcing: Updates to the Customer Orders Sample Schema | Oracle All Things SQL Blog

Oracle Blogs | Oracle Optimizer Blog

Sessions Catalogue | Oracle OpenWorld Europe: London 2020 | Oracle United Kingdom (rainfocus.com)

Session Catalog | Oracle OpenWorld 2019 (rainfocus.com)

All the OpenWorld 2019 downloads! – Learning is not a spectator sport (connor-mcdonald.com)

Session Catalog | Oracle OpenWorld 2018 (rainfocus.com)

Protégé : Merging Multiple Partitions

ORA-13511: Error Code and Solution Suggestion

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    interval  => 5);          -- Minutes. Current value retained if NULL.
END;
-------- -------------------------------- --------------- ---------------
ORA-13511: invalid INTERVAL 5, must be in the range (10, 52560000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 198
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 246
ORA-06512: at line 2
-------- -------------------------------- --------------- ---------------
ORA-13511: invalid INTERVAL string, must be in the range (string, string)
Cause: The user has specified a INTERVAL setting that is not in the supported range of (MIN, MAX).
Action: Choose a valid INTERVAL setting and retry the operation.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
  job_name        => 'DBMS_WORKLOAD_REPOSITORY_CHB',
  job_type        => 'PLSQL_BLOCK',
  job_action      => 'BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END;',
  start_date      => SYSDATE,
  repeat_interval => 'FREQ=MINUTELY;INTERVAL=3',
 
  end_date        => SYSDATE + 1 / 24,
  enabled         => TRUE,
  comments        => 'DBMS_WORKLOAD_REPOSITORY_CHB');
END;

DBMS_SCHEDULER.get_scheduler_attribute

set serveroutput on 
DECLARE
  PROCEDURE display(p_param IN VARCHAR2) AS
    l_result VARCHAR2(50);
  BEGIN
    DBMS_SCHEDULER.get_scheduler_attribute(
      attribute => p_param,
      value     => l_result);
    DBMS_OUTPUT.put_line(RPAD(p_param, 30, ' ') || ' : ' || l_result);
  END;
BEGIN
  display('current_open_window');
  display('default_timezone');
  display('email_sender');
  display('email_server');
  display('event_expiry_time');
  display('log_history');
  display('max_job_slave_processes');
  display('scheduler_disabled');
END;

Procédure PL/SQL terminée.

current_open_window : SATURDAY_WINDOW
default_timezone : PST8PDT
email_sender :
email_server :
event_expiry_time :
log_history : 30
max_job_slave_processes :
scheduler_disabled :

Procédure PL/SQL terminée.

Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later

This feature is controlled by _partition_large_extents for tables and _index_partition_large_extents for indexes

set linesize 210
col "Parameter" format a30
col "Session Value" format a20
col "Instance Value" format a20
col "Desc" format a70
select a.ksppinm  "Parameter", c.ksppstvl "Instance Value", ksppdesc "Desc"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and a.ksppinm like ('%_partition_large_extents');

Parameter                      Instance Value   Desc
------------------------------ ---------------- -------------------------------------------------------
_partition_large_extents       TRUE             Enables large extent allocation for partitioned tables 
_index_partition_large_extents TRUE              Enables large extent allocation forpartitioned indices

The details of the behavior change are available in the below metalink.

Doc ID 1295484.1 — Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later.

Archives de l’auteur : CHERIF BEN HENDA