You can use the following techniques to speed up search in the code:
Packages, functions, and procedures:
SELECT --+ORDERED PARALLEL(A) B.OBJECT_NAME, A.LINE, A.SOURCE FROM SYS.SOURCE$ A, DBA_OBJECTS B WHERE UPPER (A.SOURCE) LIKE '%SEARCH STRING%' AND B.OWNER = 'OBJ_OWNER' AND A.OBJ# = B.OBJECT_ID /
Views:
-- create tmp table CREATE TABLE VIEW_CONVERT ( OWNER VARCHAR2(255), VIEW_NAME VARCHAR2(255), VIEW_TEXT CLOB ) NOLOGGING PARALLEL PCTFREE 0 / -- fill table and convert long column INSERT --+APPEND INTO VIEW_CONVERT NOLOGGING SELECT --+PARALLEL(AA) OWNER , VIEW_NAME , TO_LOB(TEXT) FROM DBA_VIEWS AA WHERE AA.OWNER = 'OBJ_OWNER' / COMMIT / -- search in views SELECT --+PARALLEL(A) * FROM VIEW_CONVERT A WHERE VIEW_TEXT LIKE '%SEARCH STRING%' /
Warning: By using PARALLEL keywords you can easily exhaust all the server resources, which could cause problems on a production system, use with caution and on your own risk.
One Response
olegar
19|Jun|2007 1This is useful, thanks. We have around 60 000 objects in our application schema.
Leave a reply