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.

Professionnel - Page 21

  • 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;

  • Cursor environment mismatch V$SQL_SHARED_CURSOR

    SELECT
    SS1.SQL_TEXT,
    SS1.SQL_ID,
    SS1.CHILD_NUMBER,
    SS2.CHILD_NUMBER,
    SS1.SQLTYPE,
    SS2.SQLTYPE,
    SS1.PARSING_SCHEMA_NAME,
    SS2.PARSING_SCHEMA_NAME
    FROM
    V$SQL SS1,
    V$SQL SS2
    WHERE
    SS1.SQL_ID=SS2.SQL_ID
    AND SS1.SQLTYPE < SS2.SQLTYPE
    ORDER BY
    SS1.SQL_TEXT,
    SS1.CHILD_NUMBER,
    SS2.CHILD_NUMBER;

    SELECT
    SSC.*
    FROM
    V$SQL S,
    V$SQL_SHARED_CURSOR SSC
    WHERE
    S.SQL_ID='xxxxxxxxxxxxx'
    AND S.SQL_ID=SSC.SQL_ID
    AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS
    ORDER BY
    SSC.CHILD_NUMBER;


    SELECT
  S.CHILD_NUMBER,
  SBM.POSITION,
  SBM.DATATYPE,
  SBM.MAX_LENGTH,
  SBM.BIND_NAME
FROM
  V$SQL S,
  V$SQL_BIND_METADATA SBM
WHERE
  S.SQL_ID='xxxxxx'
  AND S.CHILD_ADDRESS=SBM.ADDRESS
ORDER BY
  S.CHILD_NUMBER,
  SBM.POSITION;

     

    source : https://hoopercharles.wordpress.com/2010/07/23/sql_type_mismatch-in-vsql_shared_cursor/

  • 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

  • Migrate database from One RMAN catalog to another RMAN catalog

    Migrate database from One RMAN catalog to another RMAN catalog

    http://www.nazmulhuda.info/migrate-database-from-one-rman-catalog-to-another-rman-catalog

     

    Migrate database from One RMAN catalog to another RMAN catalog

    In that scenario we will move catalog from stagecat(source catalog) to target database CLOUD.

     

    [oracle@rmancatalog2 ~]$ rman target / catalog=rman/kjh7Rjsr1

    Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 21 09:28:47 2016

    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

    connected to target database: CLOUD (DBID=1801493836)

    connected to recovery catalog database

    RMAN> list db_unique_name all;

    List of Databases
    DB Key  DB Name  DB ID            Database Role    Db_unique_name
    ------- ------- ----------------- ---------------  ------------------
    49382        DB2   3896599226       PRIMARY          DB2
    49382        DB2   3896599226       STANDBY          DB1
    1                DB1   4139331099       PRIMARY          DB1
    1                DB1   4139331099       STANDBY          DB2

    SQL> select * from rman.rc_database;

    DB_KEY

    DBINC_KEY

    DBID

    NAME

    RESELOGS_CHANGE#

    RESETLOGS_TIME

    1

    247199

    4139331099

    DB1

    116371882675

    06/07/2015 18:23

    49382

    49383

    3896599226

    DB2

    90051108188

    09/02/2012 16:18

     

    RMAN> import catalog rman/U98wsjdw2#@stagecat;

    Starting import catalog at 2016-12-21 09:36:33

    connected to source recovery catalog database

    import validation complete

    Finished import catalog at 2016-12-21 09:36:40


    RMAN> list db_unique_name all;

    List of Databases
    DB Key  DB Name  DB ID            Database Role    Db_unique_name
    ------- ------- ----------------- ---------------  ------------------
    548781  DB3      829073653        PRIMARY          DB3
    514674  DB4      1753676295       PRIMARY          DB4
    523618  DB5      3779067219       PRIMARY          DB5
    551022  DB6      4018492764       PRIMARY          DB6
    545408  DB7      4110887114       PRIMARY          DB7

    SQL> select * from rman.rc_database;

    DB_KEY

    DBINC_KEY

    DBID

    NAME

    RESELOGS_CHANGE#

    RESETLOGS_TIME

    1

    247199

    4139331099

    DB1

    116371882675

    06/07/2015 18:23:36

    49382

    49383

    3896599226

    DB2

    90051108188

    09/02/2012 16:18:57

    548781

    548782

    829073653

    DB3

    1

    08/09/2015 09:48:37

    514674

    514675

    1753676295

    DB4

    1

    29/06/2015 09:19:03

    523618

    523619

    3779067219

    DB5

    1

    27/08/2015 12:55:47

    551022

    551023

    4018492764

    DB6

    1

    02/09/2015 09:18:20

    545408

    545409

    4110887114

    DB7

    1

    22/06/2015 13:41:30



    On the Source Catalog, the database will be automatically unregistered after the IMPORT CATALOG. If you need to retain the catalog on the source side even after the import then use keyword NO UNREGISTER.

    Example:

    RMAN> import catalog rman/U98wsjdw2#@stagecat no unregister;

     

     

  • Checking Firewall Ports on remote Host with SSH auth

    #!/bin/bash
 
 input file format is:  hostname
 USAGE: ./scan_oem_ports_remote_hosts.sh hostfile
 if test 'X'"$1" = 'X'; then
         echo -n "ENter full path and name of file containing host names: ";read file
 else
         file="$1"
 fi
 grep -v "^#" $file|while read host 
 do
         if test 'X'"$host" = 'X'; then
                 echo "Malformed line in input file, skipping, should be name $host"
         else
                 RESULTS=$(ssh -n -q $host "timeout 5 bash -c 'oemap01.mydomain.com:4903  NOT open"}'

           if test "$RESULTS" != "OPEN"; then
                         /usr/bin/dig +short  $host | awk -v ip=$host '{print ip,"   " $NF "  -->oemap01.mydomain.com    4903  NOT open"}'  
           fi  
         fi
 done


    Other Tools you can use for the same instead of /dev/tcp 
    nc -vz -w5 hostname port (TCP) 
    nc -vzu -w5 hostname port (UDP) 

    Reference: https://superuser.com/questions/621870/test-if-a-port-on-a-remote-system-is-reachable-without-telnet


    https://arunbavera.wordpress.com/category/em13c/

  • 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