Thứ Tư, 9 tháng 3, 2022

Bí quyết kiểm tra cấu hình, hiệu năng Oracle Database 1 cách nhanh chóng

Mục đích: Các câu lệnh  kiểm tra tổng thể DB 1 cách nhanh chóng, cùng các câu lệnh cao tải (bình thường lấy trong AWR)

-- db
SELECT DBID,NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,
    PLATFORM_NAME,CURRENT_SCN,FLASHBACK_ON,DB_UNIQUE_NAME, (select version from v$instance) version FROM v$database;

-- ctl
SELECT name FROM v$controlfile;  

-- backup
    select start_time start_time1, to_char(start_time,'DAY dd/mm/yyyy hh24:mi:ss') start_time, to_char(end_time,'DAY dd/mm/yyyy hh24:mi:ss') end_time, output_device_type, status, input_type, round(compression_ratio,2) compression_ratio, 
        input_bytes_display, output_bytes_display, time_taken_display
    from v$rman_backup_job_details 
    where trunc(end_time)>=trunc(sysdate-7)
    order by start_time1 desc;     

-- redo
SELECT l.GROUP# group_no,l.thread# thread_no,l.sequence# sequence_no,l.BYTES/1024/1024 size_in_mb,l.MEMBERS logfile_member,l.status logfile_status,lf.member Log_member,NVL(lf.STATUS,'File in use') log_status
FROM v$logfile lf, v$log l WHERE l.GROUP#=lf.GROUP# ORDER BY l.GROUP#;

-- archive_log
select trunc(completion_time) completion_time, round(sum(blocks*block_size)/1024/1024/1024,2) archive_log_in_gb from V$ARCHIVED_LOG
    where trunc(completion_time) >= sysdate - 7
    --and trunc(completion_time)>= to_date(trunc(sysdate),'dd/mm/yyyy')
    and dest_id=1
    group by trunc(completion_time)
    order by trunc(completion_time) desc;

-- asm
select name, sector_size, block_size, state, type, round(total_mb/1024,2) total_gb , round(free_mb/1024,2) free_gb, 
    round(hot_used_mb/1024,2) hot_used_gb, round(cold_used_mb/1024,2) cold_used_gb, round(required_mirror_free_mb/1024,2) required_mirror_free_gb, 
    round(usable_file_mb/1024,2) usable_file_gb, voting_files
from gv$asm_diskgroup where inst_id = 1 and state not in ('CLOSED','DISMOUNTED') order by name;

-- tbs
select 
    a.tablespace_name,
   round( a.bytes_alloc/(1024*1024*1024)) Total_in_GB,
   round(a.physical_bytes/(1024*1024*1024)) Max_total_in_GB,
    round(nvl(b.tot_used,0)/(1024*1024*1024)) Used_in_GB,
    round((nvl(b.tot_used,0)/a.bytes_alloc)*100,2) used_percentage,
    (select status from dba_tablespaces where tablespace_name = a.tablespace_name) status
 from 
    (select 
       tablespace_name,
       sum(bytes) physical_bytes,
       sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
     from dba_data_files
     group by tablespace_name ) a,
    (select  tablespace_name, sum(bytes) tot_used
     from dba_segments
     group by tablespace_name ) b
 where  a.tablespace_name = b.tablespace_name (+)
 and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
 and a.tablespace_name not like 'UNDO%'
 order by 5 desc;

-- schm
SELECT owner, size_in_gb FROM
    (SELECT owner, ROUND(SUM(bytes)/1024/1024/1024) size_in_gb FROM dba_segments
    WHERE owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 
                'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 
                'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB')
    GROUP BY owner
    ORDER BY 2 DESC)
    WHERE size_in_gb > 50;

-- sgm
SELECT segment_type,  ROUND(SUM(bytes)/1024/1024/1024) Size_GB
    FROM dba_segments
    GROUP BY segment_type
    ORDER BY 2 DESC;

-- table
SELECT owner, table_name,table_type, num_part, num_rows,  table_size, tablespace_name,last_analyzed FROM
    (SELECT owner, table_name,table_type, num_part, num_rows, table_size, tablespace_name,last_analyzed FROM
        (
            SELECT owner,table_name,'NON PARTITIONED' table_type, 1 num_part, nvl(num_rows,0) num_rows, 
                    nvl(ROUND(NVL(blocks,0)*(select to_number(block_size) from dba_tablespaces where tablespace_name = a.tablespace_name)/1024/1024/1024),0) table_Size, 
                    tablespace_name, last_analyzed 
                FROM dba_tables a
                WHERE partitioned = 'NO' and 
                    owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 
                        'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB')
            union
            select table_owner, table_name, 'PARTITIONED' table_type, count(1) num_part, sum(num_rows) num_rows, round(sum(part_size)/1024/1024/1024,2) table_size, '' tablespace_name, 
                    max(last_analyzed) last_analyzed 
                from
                    (select table_owner, table_name, nvl(num_rows,0) num_Rows, 
                            nvl(NVL(blocks,0)*(select to_number(block_size) from dba_tablespaces where tablespace_name = b.tablespace_name),0) part_size, last_analyzed
                        from dba_tab_Partitions b where table_owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 
                            'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB'))
                group by table_owner, table_name
        )
    ORDER BY table_Size DESC)
WHERE rownum <=20;

-- index
SELECT index_size,Ind_name,Tab_name,tablespace_name, Owner,Status,Analyzed FROM
    (SELECT ROUND(sum(s.bytes)/1024/1024/1024,2) index_size, i.owner Owner, i.index_name Ind_name, i.tablespace_name, i.table_name Tab_name, i.status Status, max(i.last_analyzed) Analyzed
        FROM dba_indexes i, dba_segments s
        WHERE 
            i.owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB')
            AND
            i.last_analyzed NOT NULL
            AND
            i.index_name=s.segment_name
            AND
            i.owner=s.owner
            group by i.owner, i.index_name, i.tablespace_name, i.table_name, status
        ORDER BY 1 DESC)
WHERE rownum<=20;

-- invalid_obj
select owner, object_name, owner || '.' ||object_name full_name, object_type, to_char(last_ddl_time,'dd/mm/yyyy hh24:mi:ss') last_ddl_time,status 
    From dba_objects 
    where status <> 'VALID' and owner in ('TEST_OWNER')
    order by owner, object_name;

-- hit_ratio
SELECT a.inst_id, ROUND((1-(a.physical_reads)/((a.db_block_gets+a.consistent_gets)))*100,3) p_pool_hit_ratio,
        ROUND((1-(sum(b.getmisses)/(sum(b.gets)+sum(b.getmisses)))) * 100,3) p_Dictionary_cache_hit_ratio,
        ROUND((SUM(c.pins)/(SUM(c.pins)+SUM(c.reloads)))*100,3) p_Lib_Cache_Hit_Ratio , ROUND((sum(c.pinhits)/sum(c.pins))*100,3) p_Lib_Cache_Pin_Hit_Ratio
    FROM gv$buffer_pool_statistics a, gv$rowcache b, gv$librarycache c
    WHERE a.db_block_gets + a.consistent_gets > 0 and b.gets + b.getmisses <> 0 and a.inst_id = b.inst_id and a.inst_id = c.inst_id 
    group by a.inst_id,ROUND((1-(a.physical_reads)/((a.db_block_gets+a.consistent_gets)))*100,3) order by 1;

-- top_cpu
SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text
    FROM
        (SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id, dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea
        ORDER BY cpu_time DESC)
    WHERE rownum <=20;

-- top_buffer
SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text
FROM
    (SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id, dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea
ORDER BY buffer_gets DESC)
WHERE rownum <=20;

-- top_disk
SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text
FROM
    (SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id,  dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE rownum <=20;

-- top_exec
SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text
FROM
    (SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id,  dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea
ORDER BY executions DESC)
WHERE rownum <=20;

Hy vọng hữu ích cho bạn.
=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, middleware, hoc solaris, hoc linux, hoc aix, unix, securecrt, xshell, mobaxterm, putty

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master