» Unlabelled » Oracle-PL/SQL

Oracle-PL/SQL

Tuesday, October 29, 2019
Oracle-PL/SQL
1. Check Oracle performance

-- check defragment of table
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables WHERE table_name='SC_QST_MAIN';

-- analyze index
analyze index SCC_I18N.IDX_QST_MAIN validate structure;

--gather statstic
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => '"SCC_I18N"', tabname => '"SC_QST_MAIN"', estimate_percent => 1);

-- check response time of database
SET LINESIZE 200 PAGESIZE 50000
    COL BEGIN_TIME FORMAT A17
    COL END_TIME FORMAT A17
    COL INST_ID FORMAT 999
    COL "Response Time (msecs)" FORMAT 999,999,999,999.99

    SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INST_ID,
         ROUND (VALUE * 10, 2) "Response Time (msecs)"
    FROM GV$SYSMETRIC
    WHERE     1 = 1
         AND METRIC_NAME = 'SQL Service Response Time'
    ORDER BY INST_ID;

-- explain sql statement
explain plan for select count(user_key) from sc_qst_main where  reg_dt >= TO_DATE('20190421000000', 'YYYYMMDDHH24MISS') AND user_key = '2803115653';
select * from table(dbms_xplan.display);
Chia sẻ bài viết
Bài liên quan

Comments[ 0 ]


Post a Comment