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 4

  • Connexions aux db du cloud

    #!/bin/sh
    #-------------------------------------------------------------------------------
    #
    # Script to connect to IBM Cloud DB, version 2.0
    # Author : JJY
    #
    #-------------------------------------------------------------------------------
    #set -vx
    ##############
    #INSTANCIATION
    ##############
    DOMAIN="<domain>"
    DATABASE="postgres"
    USERNAME="<user_dba>"

    while true
    do
    DATABASE="postgres"
     clear
     echo "CONNEXION AUX BASES DU CLOUD "
     echo " "
     echo "CONNEXION A l ECOSYSTEM : <ecosystem>"
     echo " "
     echo "1 - DEV(dbname) - serveur : <instance> connexion DBA"
     echo " "
     echo "2 - DEV(dbname) - serveur : <instance> connexion dynamique applicative"
     echo " "
     echo "3 - QUAL(dbname) - serveur : <instance> connexion DBA"
     echo " "
     echo "4 - QUAL(dbname) - serveur : <instance> connexion dynamique applicative"
     echo " "
     echo "0 - TOOLBOX VAULT "
     echo " "
     echo " Faites votre choix (0 a 4) :"
     echo " Taper Q pour quitter."
     echo " "
     read var
     case $var in
            1)
            echo "Connexion a la base, veuillez patienter..."
            export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;
            vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME
            secret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM"  database/postgres/$SERVER/static-creds/$USERNAME | grep password |  awk '{print $2}')
            export PGPASSWORD=$secret;psql -h $SERVER$DOMAIN -p $PORT -U $USERNAME -d $DATABASE
            ;;
            2)
            export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;export DATABASE=<dbname>
            echo "Liste des roles:"
            vault list -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles
            echo " "
            echo "A quel role souhaitez-vous vous connecter:"
            read ROLE
            clear
            echo "Connexion a la base en mode dynamique, veuillez patienter..."
            var=$(vault read -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/creds/$ROLE)
            username=$(echo "$var" | grep username |  awk '{print $2}');
            password=$(echo "$var" | grep password |  awk '{print $2}');
            echo "User is : $username"
            echo "Password is : $password"
            echo " Tapez entrée pour continuer."
            read bidon
            export PGPASSWORD=$password;psql -h $SERVER$DOMAIN -p $PORT -U $username -d $DATABASE
            ;;
            3)
            echo "Connexion a la base, veuillez patienter..."
            export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;
            vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME
            secret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM"  database/postgres/$SERVER/static-creds/$USERNAME | grep password |  awk '{print $2}')
            export PGPASSWORD=$secret;psql -h $SERVER$DOMAIN -p $PORT -U $USERNAME -d $DATABASE
            ;;
            4)
            export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;export DATABASE=<dbname>
            echo "Liste des roles:"
            vault list -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles
            echo " "
            echo "A quel role souhaitez-vous vous connecter:"
            read ROLE
            clear
            echo "Connexion a la base en mode dynamique, veuillez patienter..."
            var=$(vault read -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/creds/$ROLE)
            username=$(echo "$var" | grep username |  awk '{print $2}');
            password=$(echo "$var" | grep password |  awk '{print $2}');
            echo "User is : $username"
            echo "Password is : $password"
            echo " Tapez entrée pour continuer."
            read bidon
            export PGPASSWORD=$password;psql -h $SERVER$DOMAIN -p $PORT -U $username -d $DATABASE
            ;;
            0)
            ./toolbox.sh
            echo " Tapez entrée pour continuer."
            read bidon
            ;;
            Q)
            echo "Merci d'avoir utiliser ce programme."
            exit
            ;;
            *)
            echo "Merci de renseignez un chiffre entre 0 et 4."
            echo " Tapez entrée pour continuer."
            read bidon
            ;;
     esac
    done
    exit

  • [Postgres] Spot a missing postgreSQL index

    --tuto tuning psql
    --Spot a missing PostgreSQL index

    SELECT
      relname                                               AS TableName,
      to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
      to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
      to_char(n_live_tup, '999,999,999,999')                AS TableRows,
      pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
    FROM pg_stat_all_tables
    WHERE schemaname = 'public'
          AND 50 * seq_scan > idx_scan -- more than 2%
          AND n_live_tup > 10000
          AND pg_relation_size(relname :: regclass) > 5000000
    ORDER BY relname ASC;

    --To find unused index in a db

    SELECT
      relname AS table,
      indexrelname AS index,
      pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
      idx_scan as index_scans
    FROM pg_stat_user_indexes ui
    JOIN pg_index i ON ui.indexrelid = i.indexrelid
    WHERE NOT indisunique AND idx_scan =0 AND pg_relation_size(relid) > 5 * 8192
    ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
    pg_relation_size(i.indexrelid) DESC;


    --returns the tables which have been hit by sequential scans the most and tells us how many rows a sequential scan has hit on average.
    SELECT schemaname, 
                  relname, 
                  seq_scan, 
                  seq_tup_read, 
                  idx_scan, 
                  seq_tup_read / seq_scan AS avg
     FROM         pg_stat_user_tables 
     WHERE        seq_scan > 0 
     ORDER BY     seq_tup_read DESC;
    --Finding slow queries to be executed on pgadmin
    SELECT query, 
                  total_exec_time, 
                  calls, 
                  mean_exec_time 
           FROM   pg_stat_statements 
           ORDER BY total_exec_time DESC;

  • Résoudre l'ORA-600 doc id 2523249.1

    Optimizer expression statistics monitoring is introduced in 12.2 database version.

     For specific applications, this might end up in growing repository segments. Monitoring expression statistics is introduced for a new optimizer feature in 12.2 to collect expression usage statistics in SQL queries.

    Monitoring feature is controlled by parameter "_column_tracking_level". High growth have been noticed when monitoring expression statistics is active, enabled by default.

     

     

    Solution:

    Disable future monitoring:

    alter system set "_column_tracking_level"=17 scope=both;

    --wait 10 minutes

     

    To purge data from tables:

    exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

    truncate table sys.exp_head$ drop storage;

    truncate table sys.exp_obj$ drop storage;

    truncate table sys.exp_stat$ drop storage;

    alter index SYS.I_EXP_HEAD$ rebuild tablespace sysaux online;

    alter index SYS.I_EXP_STAT$ rebuild tablespace sysaux online;

    alter index SYS.I_EXP_OBJ$ rebuild tablespace sysaux online;

     

    To re-enable expression statistics monitoring later :

    alter system set "_column_tracking_level"=21 scope=both;