Hi,
I'll like to gather some statistics on long running statements on a standard edition database. Can you please suggest the best way to gather stats on this statement?
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SET SERVEROUTPUT ON SIZE 200000
DECLARE
table_own VARCHAR2 (30);
table_name VARCHAR2 (30);
sqlstmt VARCHAR2 (1000);
CURSOR table_cur
IS
SELECT owner, table_name
FROM dba_tables
WHERE owner NOT IN ('ANONYMOUS',
'APEX_030200',
'APEX_PUBLIC_USER',
'APPQOSSYS',
'CTXSYS',
'DBSNMP',
'DIP',
'EXFSYS',
'FLOWS_FILES',
'MDSYS',
'ORACLE_OCM',
'ORDDATA',
'ORDPLUGINS',
'ORDSYS',
'OUTLN',
'MDDATA',
'MGMT_VIEW',
'OLAPSYS',
'OWBSYS',
'OWBSYS_AUDIT',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'SI_INFORMTN_SCHEMA',
'SYS',
'SYSTEM',
'WMSYS',
'XDB',
'XS$NULL',
'SYSMAN',
'OLAPSYS')
AND (owner, table_name) NOT IN
(SELECT DISTINCT a.OWNER, a.TABLE_NAME
FROM dba_tab_columns a, dba_objects b
WHERE a.table_name = b.object_name
AND LOWER (COLUMN_NAME) = 'last_dml_time')
ORDER BY 1;
BEGIN
OPEN table_cur;
LOOP
FETCH table_cur INTO table_own, table_name;
EXIT WHEN table_cur%NOTFOUND;
sqlstmt :=
'alter table '
|| table_own
|| '.'
|| table_name
|| ' add (last_dml_time timestamp default systimestamp NOT NULL)';
EXECUTE IMMEDIATE sqlstmt;
END LOOP;
CLOSE table_cur;
END;
/
Sorry, I'm not sure what you mean by "gather stats" ? Do you mean get some performance metrics ?
If you just want timings, I'd do:
LOOP
s := systimestamp;
FETCH table_cur INTO table_own, table_name;
EXIT WHEN table_cur%NOTFOUND;
sqlstmt :=
'alter table '
|| table_own
|| '.'
|| table_name
|| ' add (last_dml_time timestamp default systimestamp NOT NULL)';
EXECUTE IMMEDIATE sqlstmt;
dbms_output.put_line(table_own||'.'||table_name||' took '||(systimestamp-s));
END LOOP;
If you want really detailed performance analysis you can enable a trace, ie:
SQL> alter session set events = '10046 trace next context forever, level 12';
SQL> [your block of code]
SQL> alter session set sql_trace = false;