October 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);

Oracle-PL/SQL