Purging SQL Plan Management SPACE BUDGET PERCET TABLESPACE SYSAUX

CONFIGURACION RECOMENDADA

select parameter_name, parameter_value from dba_sql_management_config;
 
PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        30
PLAN_RETENTION_WEEKS                       105

Disk Space Usage

Disk space used by the SQL management base is regularly checked against a limit based on the size of the SYSAUX tablespace. By default, the limit for the SMB is no more than 10% of the size of the SYSAUX tablespace. The allowable range for this limit is between 1% and 50%. A weekly background process measures the total space occupied by the SMB, and when the defined limit is exceeded, the process will generate a warning that is written to the alert log. The alerts are generated weekly until either the SMB space limit is increased, the size of the SYSAUXtablespace is increased, or the disk space used by the SMB is decreased by purging SQL management objects (SQL plan baselines or SQL profiles).

To change the percentage limit, use the CONFIGURE procedure of the DBMS_SPM package:

By default, the limit for the SMB is no more than 10% 

BEGIN
  DBMS_SPM.CONFIGURE(
    'space_budget_percent',30);
END;
/

Purging Policy

A weekly scheduled purging task manages the disk space used by SQL plan management. The task runs as an automated task in the maintenance window. Any plan that has not been used for more than 53 weeks are purged, as identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. The 53-week time frame ensures plan information will be available during any yearly SQL processing activity. The unused plan retention period can range between 5 weeks and 523 weeks (a little more than 10 years).

To configure the retention period, use the CONFIGURE procedure of the DBMS_SPM PL/SQL package:
1-2 AÑOS 
BEGIN
  DBMS_SPM.CONFIGURE(
    'plan_retention_weeks',105);
END;
/

ESTADISTICAS TABLAS & INDICES ORACLE

SQL> execute dbms_stats.gather_table_stats('HR','EMPLOYEES');

PL/SQL procedure successfully completed.

SQL>  execute dbms_stats.gather_index_stats('HR','EMP_EMAIL_UK');

PL/SQL procedure successfully completed.

SQL>execute dbms_stats.gather_schema_stats('HR');

VALIDAR SQL FECHA EJECUCION ESTADISTICAS TABLAS

select table_name,num_rows,avg_row_len,last_analyzed from dba_tables where owner='HR';

select index_name,num_rows,avg_row_len,last_analyzed from dba_indexes where owner='HR';

SCRIPT DINAMICO ESTADISTICAS INDICES

select 'execute dbms_stats.gather_index_stats(' || '''' || table_owner || '''' || ',' ||  '''' || index_name || '''' || ');' from dba_indexes 

where table_name='FOR_DOCS_TAREADOCS'

Reverse Key Indexes

Reverse Key Indexes

A reverse key index is created by including the REVERSE keyword in the index creation.

CREATE INDEX t1_id_idx ON t1 (id) REVERSE;

The Problem

A sequence is a database object used to produce unique integers, which are commonly used to populate a synthetic primary key column in a table. Since the sequence number always increases, typically by 1, each new entry is placed on the right-most leaf block of the index, making it a hot block. By itself this can cause contention, but things can get worse when dealing with a RAC database, where the contention on the right-most leaf block can cause cluster waits, with the RAC instances fighting over the block. Reverse key indexes were introduced in Oracle 8 to help reduce this contention.

ESTADISTICAS BASE DE DATOS

execute dbms_stats.gather_system_stats('NOWORKLOAD');
execute dbms_stats.gather_system_stats('START');
execute dbms_stats.gather_system_stats('STOP');

ALTER SESSION SCHEMA

By default, it is the user you used to log in, and you can query it as below:

select sys_context('USERENV', 'CURRENT_SCHEMA') from dual;

You can change your current schema in your session however:

alter session set current_schema=HR;

SQL CONSULTAS ROW LIMIT

https://www.w3schools.com/
https://www.w3schools.com/sql/sql_top.asp