Hello All,
I received a request from a user to schedule a script for rebuilding some of their application indexes every time in a while.
This is the code I wrote for testing how the DBMS_SQL.parse and execute procedure should rebuild the application indexes. It only inteded to have the senteces printed to the screen/spool file using the DBMS_OUTPUT package.
The code:
set serveroutput on
--set echo on
set timing on
DEFINE sh_filename=&1
DEFINE sh_blevel=&2
DEFINE sh_rebuild_scope='&3'
spool &sh_filename
DECLARE
CURSOR idx_list (p_blevel IN dba_indexes.blevel%TYPE)
IS
SELECT i.owner, i.index_name, i.table_owner, i.table_name, i.blevel,
SUM (s.BYTES) / 1024 / 1024 AS mb
FROM dba_users u, dba_indexes i, dba_segments s
WHERE ( ( UPPER ('&sh_rebuild_scope') = 'S'
AND ( u.username LIKE '%PROGRA%'
OR u.username LIKE '%MENU%'
OR u.username LIKE '%ADSALES%'
)
)
OR (UPPER ('&sh_rebuild_scope') = 'A')
)
AND u.username NOT IN
('ANONYMOUS', 'APEX_PUBLIC_USER', 'APEX_030200',
'APPQOSSYS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS',
'EXFSYS', 'HR', 'IX', 'LBACSYS', 'MDDATA', 'MDSYS',
'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPSYS',
'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'OUTLN',
'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'SYS',
'SYSMAN', 'SYSTEM', 'TRACESRV', 'MTSSYS', 'OASPUBLIC',
'OLAPSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'WEBSYS', 'WK_PROXY',
'WKSYS', 'WK_TEST', 'WMSYS', 'XDB', 'OSE$HTTP$ADMIN',
'AURORA$JIS$UTILITY$', 'TSMSYS',
'AURORA$ORB$UNAUTHENTICATED')
AND u.username NOT LIKE 'FLOWS_%'
AND i.owner = u.username
AND i.owner = s.owner
AND i.index_name = s.segment_name
AND i.blevel > p_blevel
GROUP BY i.owner, i.index_name, i.table_owner, i.table_name, i.blevel
ORDER BY SUM (s.BYTES) / 1024 / 1024 DESC;
cursor_name INTEGER;
g_ddl_stmt VARCHAR (2000);
g_rows_processed INTEGER;
BEGIN
cursor_name := DBMS_SQL.open_cursor;
FOR anidx IN idx_list (&sh_blevel)
--p_blevel is set to the sh_blevel variable value.
LOOP
g_ddl_stmt :=
'ALTER INDEX ' || anidx.owner || '.' || anidx.index_name
|| ' REBUILD';
DBMS_OUTPUT.put_line (g_ddl_stmt);
DBMS_SQL.parse (cursor_name, g_ddl_stmt, DBMS_SQL.native);
g_rows_processed := 1; --DBMS_SQL.EXECUTE(cursor_name);
--DBMS_OUTPUT.put_line (g_rows_processed);
END LOOP;
DBMS_SQL.close_cursor (cursor_name);
END;
/
The problem is that during it's first execution it took about 20 minutes to complete for only 18 indexes (according to query results obtained from the CURSOR declaration). So, I traced the session and found this are the timings...
The CURSOR's query takes less than 3 seconds to be resolved.
SQL ID: g048assbunqcu Plan Hash: 1978315153
SELECT I.OWNER, I.INDEX_NAME, I.TABLE_OWNER, I.TABLE_NAME, I.BLEVEL, SUM
(S.BYTES) / 1024 / 1024 AS MB
FROM
DBA_USERS U, DBA_INDEXES I, DBA_SEGMENTS S WHERE ( ( UPPER ('A') = 'S' AND (
U.USERNAME LIKE '%PROGRA%' OR U.USERNAME LIKE '%MENU%' OR U.USERNAME LIKE
'%ADSALES%' ) ) OR (UPPER ('A') = 'A') ) AND U.USERNAME NOT IN ('ANONYMOUS',
'APEX_PUBLIC_USER', 'APEX_030200', 'APPQOSSYS', 'BI', 'CTXSYS', 'DBSNMP',
'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'LBACSYS', 'MDDATA', 'MDSYS',
'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPSYS', 'ORACLE_OCM', 'ORDDATA',
'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'SYS', 'SYSMAN', 'SYSTEM',
'TRACESRV', 'MTSSYS', 'OASPUBLIC', 'OLAPSYS', 'OWBSYS', 'OWBSYS_AUDIT',
'WEBSYS', 'WK_PROXY', 'WKSYS', 'WK_TEST', 'WMSYS', 'XDB', 'OSE$HTTP$ADMIN',
'AURORA$JIS$UTILITY$', 'TSMSYS', 'AURORA$ORB$UNAUTHENTICATED') AND
U.USERNAME NOT LIKE 'FLOWS_%' AND I.OWNER = U.USERNAME AND I.OWNER =
S.OWNER AND I.INDEX_NAME = S.SEGMENT_NAME AND I.BLEVEL > :B1 GROUP BY
I.OWNER, I.INDEX_NAME, I.TABLE_OWNER, I.TABLE_NAME, I.BLEVEL ORDER BY SUM
(S.BYTES) / 1024 / 1024 DESC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.76 1.77 0 0 0 0
Fetch 1 0.56 0.59 3 60746 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.33 2.36 3 60746 0 8
But down below it seems the REBUILD index sentences are being executed, and those are the responsibles for much of the 18 min total time the entire query takes to complete.
SQL ID: 1auwfw58aarc3 Plan Hash: 4281638712
ALTER INDEX PROGRA.PDD_EMP_SOP_I REBUILD
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 1 0
Execute 1 106.71 184.33 369002 27882050 169565 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 106.71 184.33 369002 27882054 169566 0
... (bunch of text) ...
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
24 user SQL statements in trace file.
96 internal SQL statements in trace file.
120 SQL statements in trace file.
48 unique SQL statements in trace file.
11915 lines in trace file.
1225 elapsed seconds in trace file.
Similar timings are shown for each one of the 8 indexes which according to the query will be rebuilt.
It seems they're being executed... could you please help me to understand what I am doing wrong?
Thanks in advance.