Ok

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.

Oracle12C,19C - Page 3

  • ORA-609: opiodr aborting process unknown ospid

    The Solution

    To resolve this error, you must increase the values for INBOUND_ CONNECT_ TIMEOUT on both the listener side and the server side. If you are getting ORA-609 because of a connection timeout issue, increasing the values should fix the problem. You should change INBOUND_ CONNECT_ TIMEOUT to read as the following:

    Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180

    Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

    Above, the timeout seconds are being set to 120 in place of the default 60. While this simple change should fix most ORA-609 errors, if you continue to see this error then you should try to locate which client is causing the error. Set the sqlnet.ora file parameter to

    sqlnet.ora file: DIAG_ADR_ENABLED = OFF

    Putain je me suis pris la tête avec mon client pour cette erreur à la con!!!

    https://www.tekstream.com/resource-center/ora-609-opiodr-aborting-process-unknown-ospid/

  • Tuning SQL ID

    http://www.runningoracle.com/product_info.php?products_id=435

     

    Summary 
    You can create an SQL TUNING TASK manually ad hoc with the following simple steps. 

    ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; 

    0. Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids. 
    In case this is a current sql running use the v$session. 

    select sql_id from v$session where sid = :x 

    1. Login as SYSTEM (or any other user) at sqlplus and create the tuning task:

    SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '5tru8vxmktswq');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/
task_id: TASK_69287

    2. Run the SQL TUNING TASK

    begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_69287');
end;
/

    3. You can monitor the processing of the tuning task with the statement 

    SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_69287'; 

    4. When the task has a status=COMPLETED, then run:

    SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_69287') AS recommendations FROM dual;

    5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile.

    begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_69287', task_owner => 'SYSTEM', replace => TRUE);
end;
/

6. You can check the database sql profiles with the statement:

select * from dba_sql_profiles;

In case you want to disable an sql profile use the statement:

begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED');
end;
/

     

     

    https://dbaclass.com/article/how-to-run-sql-tuning-advisor-for-a-sql_id/

     


    declare
    l_sql_tune_task_id varchar2(100);
    begin
    l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
    begin_snap => 4883,
    end_snap => 4888,
    sql_id => '1pfbzr2wbnwsy',
    scope => dbms_sqltune.scope_comprehensive,
    time_limit => 10800,
    task_name => 'tune_sql_1pfbzr2wbnwsy',
    description => 'tuning_du_sql_1pfbzr2wbnwsy');
    dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
    end;
    /

    EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'tune_sql_1pfbzr2wbnwsy');

    set long 65536
    set longchunksize 65536
    set linesize 100
    select dbms_sqltune.report_tuning_task('tune_sql_1pfbzr2wbnwsy') from dual;

  • Stats missing

    Pour afficher les stats sur les tables :

    set pages 200

    col index_owner form a10
    col TABLE_NAME for a40
    col table_owner form a10
    col owner form a10

    spool checkstat.lst

    PROMPT Regular Tables

    select owner,table_name,last_analyzed, global_stats
    from dba_tables
    where owner not in ('SYS','SYSTEM')
    order by owner,table_name
    /

     

    Pour les tables avec partitions :

    set pages 200

    col index_owner form a10
    col TABLE_NAME for a40
    col table_owner form a10
    col owner form a10

    spool checkstat.lst

    PROMPT Partitioned Tables

    select table_owner, table_name, partition_name, last_analyzed, global_stats
    from dba_tab_partitions
    where table_owner not in ('SYS','SYSTEM')
    order by table_owner,table_name, partition_name
    /

    Pour afficher les stats sur les indexes ;

    set pages 500
    set lines 200
    col index_owner form a10
    col index_name for a40
    col table_owner form a10
    col owner form a10

    select owner, index_name, last_analyzed, global_stats
    from dba_indexes
    where owner not in ('SYS','SYSTEM')
    order by 3
    /

    Pour afficher les stats manquant avec un taux de % de 10 :

    select m.TABLE_OWNER,
    m.TABLE_NAME,
    m.INSERTS,
    m.UPDATES,
    m.DELETES,
    m.TRUNCATED,
    m.TIMESTAMP as LAST_MODIFIED,
    round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
    t.num_rows as last_known_rows_number,
    t.last_analyzed
    From dba_tab_modifications m,
    dba_tables t
    where m.table_owner=t.owner
    and m.table_name=t.table_name
    and table_owner not in ('SYS','SYSTEM')
    and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
    order by timestamp desc;

     

  • Script for check all the enabled auditing on Database

    --Check the parameter is enabled or disable for Audit
    select name || '=' || value PARAMETER from sys.v_$parameter where name like '%audit%';
    --Statement Audits Enabled on this Database
    column user_name format a10
    column audit_option format a40
    select * from sys.dba_stmt_audit_opts;
    --Privilege Audits Enabled on this Database
    select * from dba_priv_audit_opts;
    -- Object Audits Enabled on this Database
    select (owner ||'.'|| object_name) object_name,
    alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, ref, exe
    from dba_obj_audit_opts
    where alt != '-/-' or aud != '-/-'
    or com != '-/-' or del != '-/-'
    or gra != '-/-' or ind != '-/-'
    or ins != '-/-' or loc != '-/-'
    or ren != '-/-' or sel != '-/-'
    or upd != '-/-' or ref != '-/-'
    or exe != '-/-';

    --Default Audits Enabled on this Database
    select * from all_def_audit_opts;

  • Stats Oracle SYSTEME et schema

    Voir si les stats Oracle sont calculées sur mon schéma : 

     

    set lines 150
    set pages 3000
    col TABLE_NAME for a30
    col INDEX_NAME for a30
    col COLUMN_NAME for a30

    define schema=&schema

    spool stats_&schema

    -- Voir les stats sur mes tables
    select table_name, num_rows, BLOCKS, STALE_STATS from DBA_TAB_STATISTICS where owner = '&schema' order by table_name;

    --Voir les stats sur mes indexes
    select INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, LAST_ANALYZED from DBA_IND_STATISTICS where OWNER = '&schema' order by TABLE_NAME, INDEX_NAME;

    --Voir les stats sur mes colonnes
    select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, DENSITY, LAST_ANALYZED, AVG_COL_LEN from DBA_TAB_COL_STATISTICS where owner = '&schema' order by TABLE_NAME, COLUMN_NAME;

    spool off
    exit

    -- Mise en place des statistiques systèmes et os (sans mode 'concurrent true' !)
    execute sys.dbms_stats.gather_system_stats('Start');
    execute sys.dbms_stats.gather_system_stats('Stop');
    execute sys.dbms_stats.gather_system_stats('interval',interval => 15); # durée de 60 mins
    execute sys.dbms_stats.delete_system_stats();

    -- Vérifier si les statistiques sont calculées
    select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN' order by 1;

     

    Autre source d'infos : 

    https://fadace.developpez.com/oracle/statistiques-systeme/

    http://dbaoraclesql.canalblog.com/archives/2019/03/30/37218417.html