Wednesday, May 11, 2022

find out a SQL how many times its running in a min and avg response time/cpu time/cluster activity/io time

SET SERVEROUTPUT ON
DECLARE a INT := 0; a1 FLOAT := 0; a2 FLOAT := 0; a3 FLOAT := 0; b INT := 0; b1 FLOAT := 0; b2 FLOAT := 0; b3 FLOAT := 0; c INT := 0; d INT := 1; BEGIN SELECT SUM (executions), SUM (elapsed_time) / 1000, SUM (CONCURRENCY_WAIT_TIME) / 1000, SUM (USER_IO_WAIT_TIME) / 1000 INTO a, a1, a2, a3 FROM gv$sql WHERE sql_id = 'gnf46h863mwh3'; DBMS_LOCK.Sleep (60); SELECT SUM (executions), SUM (elapsed_time) / 1000, SUM (CONCURRENCY_WAIT_TIME) / 1000, SUM (USER_IO_WAIT_TIME) / 1000 INTO b, b1, b2, b3 FROM gv$sql WHERE sql_id = 'gnf46h863mwh3'; DBMS_OUTPUT.put_line ( 'gnf46h863mwh3 .. .. count=' || (b - a) || '..........elasped_time_ms=' || (b1 - a1) / (b - a) || '.......conc_time_ms' || (b2 - a2) / (b - a) || '....userwait_time_ms' || (b3 - a3) / (b - a)); end;

No comments:

user level export and import

expdp parfile=PLCT170.par oracle@uslp123sd7dfcvxsza > more PLCT050.par userid= "/ as sysdba" dumpfile=T050.dmp logfile=expdpT0...