Mustafa Bektaş Tepe

Veritabanında overlap(çakışan) indexleri bulmak için gerekli script.

SELECT miv1.table_owner,
       miv1.table_name,
       miv1.index_name,
       miv2.index_name,
       miv1.index_columns,
       miv2.index_columns
  FROM (  SELECT table_owner, table_name, index_name,
                 listagg (c.column_name, ',') WITHIN GROUP (ORDER BY c.column_position) index_columns
            FROM dba_ind_columns c
WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'XDB', 'OLAPSYS')
        GROUP BY table_owner, table_name, index_name) miv1,
       (  SELECT table_owner, table_name, index_name,
                 listagg (c.column_name, ',') WITHIN GROUP (ORDER BY c.column_position) index_columns
            FROM dba_ind_columns c
WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'XDB', 'OLAPSYS')
        GROUP BY table_owner, table_name, index_name) miv2
 WHERE     miv1.table_owner = miv2.table_owner
       AND miv1.table_name = miv2.table_name
       AND miv1.index_columns LIKE miv2.index_columns || '_%';

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

1,526 total views, 6 views today

Foreign key ilişkisi olupda index atılmamış sütünlar.

OLTP sistemlerde Foreign Key constraintleri veri bütünlüğünü sağlamak için sıkça kullanmaktayız. Foreign Key kullanımı veri bütünlüğünü sağlayabilmek açısından kullanılaması gereken mekanizmaların başında geliyor ancak foreign key constraint kullanırken yapmamız gereken bazı önemli ayrıntılar mevcut bunu atlamamamız gerekiyor.

Nedir bu önemli ayrıntı dediğimizde, yapmamız gereken şeyin foreign key olarak belirlediğimiz kolonu indexlemek olduğunu görmekteyiz. Indexin önemi ise;

• K kolonu indekslenmemiş bir tablo için Parent tabloda yapılacak bir değişiklik Child tablonun tamamının locklanmasına neden olur.
• Eğer F.K yaratırken ON DELETE CASCADE opsiyonu kullandık isek, F.K’yi indexlemediğimiz durumda bir sorunda burada yaşayacağız. Burada yaşayacağımız problem yapacağımız DELETE operasyonunun FULL TABLE SCAN operasyonuna neden olacağıdır. Parent tablodan sileceğimiz her kayıt için, Child tabloya erişim FULL TABLE SCAN ile yapılacak ve delete operasyonumuz oldukça yavaş gerçekleşecektir. Bu durumda yine gün sonunda sistemin eş zamanlılık seviyesini negatif yönde etkileyecektir.
• Parent ve child tablomuzu sık sık joinliyorsak yine F.K üzerinde bir indeksin olmaması sorgunun yavaş çalışmasına neden olacaktır.

Script 1

SELECT a.owner,
       CASE WHEN b.table_name IS NULL THEN 'unindexed' ELSE 'indexed' END
          AS status,
       a.table_name AS table_name,
       a.constraint_name AS fk_name,
       a.fk_columns AS fk_columns,
       b.index_name AS index_name,
       b.index_columns AS index_columns
  FROM (  SELECT a.owner,
                 a.table_name,
                 a.constraint_name,
                 listagg (a.column_name, ',')
                    WITHIN GROUP (ORDER BY a.position)
                    fk_columns
            FROM dba_cons_columns a, dba_constraints b
           WHERE     a.constraint_name = b.constraint_name
                 AND b.constraint_type = 'R'
                 AND a.owner = b.owner
               --  AND a.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN')
        GROUP BY a.owner, a.table_name, a.constraint_name) a,
       (  SELECT table_owner,
                 table_name,
                 index_name,
                 listagg (c.column_name, ',')
                    WITHIN GROUP (ORDER BY c.column_position)
                    index_columns
            FROM dba_ind_columns c
           --WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN')
        GROUP BY table_owner, table_name, index_name) b
 WHERE     a.table_name = b.table_name(+)
       AND b.index_columns(+) LIKE a.fk_columns || '%'
       AND a.owner = b.table_owner(+)
       order by 2 desc,1;

Script 2

SELECT    acc.owner
         || '-> '
         || acc.constraint_name
         || '('
         || acc.column_name
         || '['
         || acc.position
         || '])'
            "Owner, Name, Column, Position"
    FROM all_cons_columns acc, all_constraints ac
   WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R'
         AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN
                (SELECT acc.owner,
                        acc.table_name,
                        acc.column_name,
                        acc.position
                   FROM all_cons_columns acc, all_constraints ac
                  WHERE ac.constraint_name = acc.constraint_name
                        AND ac.constraint_type = 'R'
                 MINUS
                 SELECT table_owner,
                        table_name,
                        column_name,
                        column_position
                   FROM all_ind_columns)
ORDER BY ACC.owner,
         ACC.constraint_name,
         ACC.column_name,
         ACC.position;

Referanslar
https://emrahmete.wordpress.com/2016/01/10/oracle-foreign-key-indexing/

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

1,683 total views, 6 views today

SELECT UC.OWNER child_owner,
       UC.TABLE_NAME child_table,
       UCC.COLUMN_NAME c_column_name,
       UC.R_CONSTRAINT_NAME constaint_name,
       uc.constraint_type     constraint_type,
       UCC.TABLE_NAME parent_table,
       UCC.COLUMN_NAME p_column_name
FROM DBA_CONSTRAINTS  UC,
     DBA_CONS_COLUMNS UCC
WHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
      AND UC.CONSTRAINT_TYPE = 'R';

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

3,120 total views, 6 views today

Script 1 : Günlük çıkan archivelog ile ilgili istatistikler.

SELECT A.*, ROUND (A.Count# * B.AVG# / 1024 / 1024 / 1024) Daily_Avg_Gb
  FROM (  SELECT TO_CHAR (First_Time, 'YYYY-MM-DD') DAY,
                 COUNT (1) Count#,
                 MIN (RECID) Min#,
                 MAX (RECID) Max#
            FROM v$log_history
        GROUP BY TO_CHAR (First_Time, 'YYYY-MM-DD')
        ORDER BY 1) A,
       (SELECT AVG (BYTES) AVG#,
               COUNT (1) Count#,
               MAX (BYTES) Max_Bytes,
               MIN (BYTES) Min_Bytes
          FROM v$log) B;

Script 2 : Saatlik olarak çıkan archivelog sayısı.

set lines 130;
set pages 1050;

  SELECT TO_CHAR (first_time, 'YYYY-MM-DD') day,
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '00', 1, 0)),'999')"00",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '01', 1, 0)),'999')"01",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '02', 1, 0)),'999')"02",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '03', 1, 0)),'999')"03",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '04', 1, 0)),'999')"04",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '05', 1, 0)),'999')"05",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '06', 1, 0)),'999')"06",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '07', 1, 0)),'999')"07",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '08', 1, 0)),'999')"08",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '09', 1, 0)),'999')"09",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '10', 1, 0)),'999')"10",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '11', 1, 0)),'999')"11",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '12', 1, 0)),'999')"12",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '13', 1, 0)),'999')"13",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '14', 1, 0)),'999')"14",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '15', 1, 0)),'999')"15",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '16', 1, 0)),'999')"16",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '17', 1, 0)),'999')"17",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '18', 1, 0)),'999')"18",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '19', 1, 0)),'999')"19",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '20', 1, 0)),'999')"20",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '21', 1, 0)),'999')"21",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '22', 1, 0)),'999')"22",
         TO_CHAR (SUM (DECODE (TO_CHAR (first_time, 'HH24'), '23', 1, 0)),'999')"23"
    FROM v$log_history
GROUP BY TO_CHAR (first_time, 'YYYY-MM-DD')
ORDER BY 1;

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

1,092 total views, no views today

Merhaba Script ile ASM instance’ı kullanan uygulamaları listeleriz.

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN disk_group_name        FORMAT a25           HEAD 'Disk Group Name'
COLUMN instance_name          FORMAT a20           HEAD 'Instance Name'
COLUMN db_name                FORMAT a9            HEAD 'Database Name'
COLUMN status                 FORMAT a12           HEAD 'Status'

SELECT
    a.name              disk_group_name
  , c.instance_name     instance_name
  , c.db_name           db_name
  , c.status            status
FROM
    v$asm_diskgroup a JOIN v$asm_client c USING (group_number)
ORDER BY
    a.name
/

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

3,727 total views, no views today

ASM disk gruplarında ki disklerin listesi,disk alanlarının doluluk oranı vs. veren script.

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN disk_group_name        FORMAT a25           HEAD 'Disk Group Name'
COLUMN disk_file_path         FORMAT a30           HEAD 'Path'
COLUMN disk_file_name         FORMAT a20           HEAD 'File Name'
COLUMN disk_file_fail_group   FORMAT a20           HEAD 'Fail Group'
COLUMN total_gb               FORMAT 999,999,999   HEAD 'File Size (GB)'
COLUMN used_gb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

BREAK ON report ON disk_group_name SKIP 1

COMPUTE sum LABEL ""              OF total_gb used_gb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_gb used_gb ON report

SELECT
    NVL(a.name, '[CANDIDATE]')                       disk_group_name
  , b.path                                           disk_file_path
  , b.name                                           disk_file_name
  , b.failgroup                                      disk_file_fail_group
  , b.total_mb/1024                                       total_gb
  , (b.total_mb - b.free_mb)/1024                         used_gb
  , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used
FROM
    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
    a.name
/

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

3,252 total views, no views today