In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
-- -- Print a list of all the jobs related to the selected one -- in this case 'MSTR#JS1.PLAN' -- CONNECT TO DB; -- If you're running this several times in one session you may need to -- DROP TABLE session.heirarchy; -- DECLARE GLOBAL TEMPORARY TABLE SESSION.hierarchy ( pkey VARCHAR(74) NOT NULL, ckey VARCHAR(74)) ON COMMIT PRESERVE ROWS; COMMIT; INSERT INTO SESSION.hierarchy SELECT JS_WRKST_NAME || '#' || JS_NAME || '.' || JB_NAME, DEP_JS_WRKST_NAME || '#' || DEP_JS_NAME || '.' || DEP_JB_NAME FROM JB_DPS_V; -- You could simply use the temporary table -- to print a list of job dependencies -- with a simple -- SELECT * FROM SESSION.hierarchy; -- But now we can also see, for a particular job -- what relationships it has to any other job -- in the database WITH children (kkey, lv1) AS ( SELECT ckey, 1 FROM SESSION.hierarchy WHERE pkey = 'MSTR#JS1.PLAN' UNION ALL SELECT H.ckey, C.lv1 + 1 FROM SESSION.hierarchy H, children C WHERE H.pkey = C.kkey ), parents (kkey, lv1) AS ( SELECT pkey, -1 FROM SESSION.hierarchy WHERE ckey = 'MSTR#JS1.PLAN' UNION ALL SELECT H.pkey, P.lv1 - 1 FROM SESSION.hierarchy H, parents P WHERE H.ckey = P.kkey ) SELECT kkey, lv1 FROM children UNION ALL SELECT kkey, lv1 FROM parents;
ORA-06550: line 1, column 26: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; not null range default character The symbol ":= was inserted before "TABLE" to continue. ORA-06550: line 2, column 12: PLS-00103: Encountered the symbol "VARCHAR" when expecting one of the following: . ( ) , * @ % & | = - + < / > at in is mod remainder not range rem => .. <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKE ORA-06550: line 2, column 28
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.