{"id":469,"date":"2010-08-30T16:21:16","date_gmt":"2010-08-30T13:21:16","guid":{"rendered":"http:\/\/blog.yavor.info\/?p=469"},"modified":"2012-02-05T10:57:42","modified_gmt":"2012-02-05T10:57:42","slug":"update-global-indexes-testing-suite","status":"publish","type":"post","link":"http:\/\/blog.yavor.info\/?p=469","title":{"rendered":"UPDATE GLOBAL INDEXES &#8211; testing suite"},"content":{"rendered":"<p>\u0417\u0430 \u0434\u0430 \u0432\u044a\u0437\u043f\u0440\u043e\u0438\u0437\u0432\u0435\u0434\u0430 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u0438\u0442\u0435, \u043e\u043f\u0438\u0441\u0430\u043d\u0438 \u0432 <a href=\"http:\/\/blog.yavor.info\/?p=468\">\u0441\u0442\u0430\u0442\u0438\u044f\u0442\u0430<\/a>; \u043a\u0430\u043a\u0442\u043e \u0438 \u0437\u0430 \u0434\u0430 \u0442\u0435\u0441\u0442\u0432\u0430\u043c \u0435\u0432\u0435\u043d\u0442\u0443\u0430\u043b\u043d\u0438\u0442\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u044f, \u0434\u0440\u0430\u0441\u043d\u0430\u0445 \u043d\u0430\u0431\u044a\u0440\u0437\u043e \u0435\u0434\u0438\u043d \u0442\u0435\u0441\u0442\u043e\u0432\u0430 \u043f\u0430\u043a\u0435\u0442. \u0418\u043c\u0430 \u0441\u0438 \u043a\u0443\u0441\u0443\u0440\u0438 &#8211; \u043f\u0440\u0438\u043c\u0435\u0440\u043d\u043e \u043d\u0435 \u0441\u0435 \u0434\u044a\u0440\u0436\u0438 \u043f\u0440\u0430\u0432\u0438\u043b\u043d\u043e \u0430\u043a\u043e config \u0442\u0430\u0431\u043b\u0438\u0446\u0430\u0442\u0430 \u0435 \u043f\u0440\u0430\u0437\u043d\u0430 &#8211; \u043d\u043e \u0437\u0430 \u0440\u0435\u0448\u0435\u043d\u0438\u0435 &#8222;\u043d\u0430\u0431\u044a\u0440\u0437\u043e&#8220; \u0441\u0435 \u043f\u043e\u043b\u0443\u0447\u0438 \u0434\u043e\u0431\u0440\u0435. \u0412\u0441\u044a\u0449\u043d\u043e\u0441\u0442 \u0442\u043e\u043b\u043a\u043e\u0432\u0430 \u0434\u043e\u0431\u0440\u0435, \u0447\u0435 \u0440\u0435\u0448\u0438\u0445 \u0434\u0430 \u0433\u043e \u0441\u043b\u043e\u0436\u0430 \u0442\u0443\u043a, \u0434\u0430 \u043d\u0435 \u0433\u043e \u0437\u0430\u0433\u0443\u0431\u044f \ud83d\ude42<\/p>\n<p>\u0418\u0434\u0435\u044f\u0442\u0430 \u0435 \u0441\u043b\u0435\u0434\u043d\u0430\u0442\u0430: \u0441 \u0435\u0434\u043d\u043e \u0435\u043b\u0435\u043c\u0435\u043d\u0442\u0430\u0440\u043d\u043e \u0441\u043a\u0440\u0438\u043f\u0442\u0447\u0435 \u0441\u0435 \u043f\u0443\u0441\u043a\u0430\u0442 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u043b\u0435\u043d \u0431\u0440\u043e\u0439 sql*plus \u0441\u0435\u0441\u0438\u0438 (\u0430\u0437 \u043f\u0440\u043e\u0431\u0432\u0430\u0445 \u0441 20 \u0438 40). <\/p>\n<pre lang=\"bash\">\r\nfor i in `seq 1 20`;\r\ndo\r\n  sqlplus \/nolog @loader.sql &\r\ndone;\r\n<\/pre>\n<p>\u0412\u0441\u044f\u043a\u0430 \u043e\u0442 \u0442\u044f\u0445 \u0438\u0437\u0432\u0438\u043a\u0432\u0430 \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u0430\u0442\u0430 <code>start_single_process<\/code> \u0438 \u0442\u043e\u043b\u043a\u043e\u0432\u0430.<\/p>\n<p>\u0417\u0430\u0440\u0435\u0436\u0434\u0430\u043d\u0435\u0442\u043e \u0437\u0430\u043f\u043e\u0447\u0432\u0430 \u0441 \u0438\u0437\u0432\u0438\u043a\u0432\u0430\u043d\u0435 (\u043e\u0442 \u0434\u0440\u0443\u0433\u0430 &#8211; \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u043d\u0430 &#8211; \u0441\u0435\u0441\u0438\u044f) \u043d\u0430 <code>start_load<\/code>. \u0414\u043e\u0431\u0440\u0435 \u0435 \u0434\u0430 \u0441\u0435 \u0437\u0430\u0434\u0430\u0434\u0435 \u043d\u044f\u043a\u0430\u043a\u044a\u0432 wait, \u043f\u044a\u043a \u0431\u0438\u043b \u0442\u043e\u0439 \u0438 0.01 \u0441\u0435\u043a. \u0418\u043d\u0430\u0447\u0435 \u0441\u0442\u0430\u0432\u0430 \u0442\u0435\u0433\u0430\u0432\u043e.<\/p>\n<p>\u041f\u043e \u0432\u0441\u044f\u043a\u043e \u0432\u0440\u0435\u043c\u0435 \u0437\u0430\u0440\u0435\u0436\u0434\u0430\u043d\u0435\u0442\u043e \u043c\u043e\u0436\u0435 \u0434\u0430 \u0431\u044a\u0434\u0435 \u043f\u0440\u0435\u043a\u044a\u0441\u043d\u0430\u0442\u043e \u0441 <code>pause_load<\/code>. \u0418\u0437\u0432\u0438\u043a\u0432\u0430\u043d\u0435\u0442\u043e \u043d\u0430 <code>stop_load <\/code>\u043a\u0430\u0440\u0430 sql*plus \u0441\u0435\u0441\u0438\u0438\u0442\u0435 \u0434\u0430 \u043f\u0440\u0438\u043a\u043b\u044e\u0447\u0430\u0442.<\/p>\n<pre class=\"brush:sql\">\r\ncreate or replace package multi_ses_load is\r\n\r\n  c_status_start constant number := 1; -- the LOADER sessions should run\r\n  c_status_pause constant number := 0; -- the LOADER sessions should stop\r\n  c_status_exit  constant number := 2; -- the LOADER sessions should exit\r\n  -- every LOADER process should call this procedure. The procedure will wait for the START signal\r\n  procedure start_single_process;\r\n\r\n  -- issies the START signal. Parameters:\r\n  --    part_no: in which partition should we load data\r\n  --    wait: how many seconds to wait between 2 inserts\r\n  --    refresh_iterations: how often to refresh the data from the config table\r\n  procedure start_load(in_part_no            in number,\r\n                       in_wait_sec           in number default 0,\r\n                       in_refresh_iterations in number default 10);\r\n\r\n  -- changes in which partition should we load data\r\n  procedure change_part(in_part_no in number);\r\n\r\n  -- changes how many seconds to wait between 2 inserts\r\n  procedure change_wait(in_wait_sec in number);\r\n\r\n  -- changes how often to refresh the data flrom the config table\r\n  procedure change_refresh(in_refresh_iterations in number);\r\n\r\n  -- stops the load process until another START signal\r\n  procedure pause_load;\r\n\r\n  -- restarts the load without changing any parameters\r\n  procedure restart_load;\r\n\r\n  -- makes the loading processes to exit\r\n  procedure stop_load;\r\n\r\nend multi_ses_load;\r\n\r\n<\/pre>\n<pre class=\"brush:sql\">\r\ncreate or replace package body multi_ses_load is\r\n\r\n  c_alert_name constant varchar2(20) := 'SESS_ALERT';\r\n\r\n  -- every LOADER process should call this procedure. The procedure will wait for the START signal\r\n  procedure start_single_process is\r\n    v_alert_msg          VARCHAR2(1800);\r\n    v_alert_status       PLS_INTEGER;\r\n    v_part_no            number;\r\n    v_wait_sec           number;\r\n    v_refresh_iterations number;\r\n    v_curr_stat          number;\r\n    v_ses_clob           clob;\r\n    v_my_sid             number;\r\n    v_seed               number;\r\n    procedure read_config_vars is\r\n    begin\r\n      -- reed the config variables form teh config table\r\n      select curr_stat, part_no, wait_sec, refresh_iterations\r\n        into v_curr_stat, v_part_no, v_wait_sec, v_refresh_iterations\r\n        from ses_load_parames;\r\n    end;\r\n  \r\n  begin\r\n    -- init the random generator. we will use the current SID and number of seconds since mignight for seed\r\n    Select Sid\r\n      into v_my_sid\r\n      from v$mystat\r\n     where rownum = 1;\r\n    v_seed := (sysdate - trunc(sysdate)) * 1000 * (24 * 60 * 60) + v_my_sid;\r\n    dbms_random.initialize(v_seed);\r\n    -- read the config vars\r\n    read_config_vars;\r\n    while v_curr_stat in (c_status_start, c_status_pause) -- if the current status is START or PAUSE, we proceed; \r\n     loop\r\n      if v_curr_stat = c_status_pause then\r\n        -- if the status us PAUSE, wait for the signal\r\n        -- we should register the current session as a consumer for the alert\r\n        dbms_alert.register(c_alert_name);\r\n        -- now we can wait for the alert\r\n        dbms_alert.waitone(c_alert_name, v_alert_msg, v_alert_status);\r\n        -- refresh the config vars - they may be changed\r\n        read_config_vars;\r\n        if v_curr_stat = c_status_exit then\r\n          exit;\r\n        end if;\r\n      end if;\r\n      -- do 'v_refresh_iterations' inserts\r\n      for i in 1 .. v_refresh_iterations loop\r\n        -- insert a row into 'v_part_no' partition\r\n        insert into ...\r\n        values (dbms_random.string('P', 32), ...); -- here we should take care of the partition placement\r\n        commit;\r\n        dbms_lock.sleep(v_wait_sec); -- wiat for 'v_wait_sec' seconds\r\n      end loop;\r\n      -- refresh the config vars after whe have done enough iterations \r\n      read_config_vars;\r\n    end loop;\r\n  end;\r\n\r\n  -- issues the START signal. Parameters:\r\n  --    part_no: in which partition should we load data\r\n  --    wait: how many seconds to wait between 2 inserts\r\n  --    refresh_iterations: how often to refresh the data flrom the config table\r\n  procedure start_load(in_part_no            in number,\r\n                       in_wait_sec           in number default 0,\r\n                       in_refresh_iterations in number default 10) is\r\n  begin\r\n    -- we set the parameters in the config table\r\n    update ses_load_parames\r\n       set curr_stat = c_status_start, part_no = in_part_no, wait_sec = in_wait_sec,\r\n           refresh_iterations = in_refresh_iterations;\r\n    commit;\r\n    -- set teh LOADER processes free :)\r\n    dbms_alert.signal(c_alert_name, '\u0413\u0430\u0437!');\r\n    commit;\r\n  end;\r\n\r\n  -- changes in which partition should we load data\r\n  procedure change_part(in_part_no in number) is\r\n  begin\r\n    update ses_load_parames\r\n       set part_no = in_part_no;\r\n    commit;\r\n  end;\r\n\r\n  -- changes how many seconds to wait between 2 inserts\r\n  procedure change_wait(in_wait_sec in number) is\r\n  begin\r\n    update ses_load_parames\r\n       set wait_sec = in_wait_sec;\r\n    commit;\r\n  end;\r\n\r\n  -- changes how often to refresh the data flrom the config table\r\n  procedure change_refresh(in_refresh_iterations in number) is\r\n  begin\r\n    update ses_load_parames\r\n       set refresh_iterations = in_refresh_iterations;\r\n    commit;\r\n  end;\r\n\r\n  -- stops the load process until another START signal\r\n  procedure pause_load is\r\n  begin\r\n    update ses_load_parames\r\n       set curr_stat = c_status_pause;\r\n    commit;\r\n  end;\r\n\r\n  -- restarts the load without changing any parameters\r\n  procedure restart_load is\r\n  begin\r\n    -- we set the parameters in the config table\r\n    update ses_load_parames\r\n       set curr_stat = c_status_start;\r\n    commit;\r\n    -- set the LOADER processes free :)\r\n    dbms_alert.signal(c_alert_name, '\u0410\u0440\u0435 \u043f\u0430\u043a \u043d\u0430 \u043f\u0430\u043d\u0433\u0430\u0440\u0430!');\r\n    commit;\r\n  end;\r\n\r\n  -- makes the loading processes to exit\r\n  procedure stop_load is\r\n  begin\r\n    -- put the exit flag in teh table\r\n    update ses_load_parames\r\n       set curr_stat = c_status_exit;\r\n    commit;\r\n    -- is some process is in PAUSE, we should alert him to reread the config vars\r\n    dbms_alert.signal(c_alert_name, '\u0410\u0440\u0435 \u0434\u0430 \u0441\u0438 \u043e\u0434\u0438\u043c\u0435, \u043c\u043e\u043c\u0447\u0435\u0442\u0430!');\r\n    commit;\r\n  end;\r\n\r\nend multi_ses_load;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u0417\u0430 \u0434\u0430 \u0432\u044a\u0437\u043f\u0440\u043e\u0438\u0437\u0432\u0435\u0434\u0430 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u0438\u0442\u0435, \u043e\u043f\u0438\u0441\u0430\u043d\u0438 \u0432 <a href=\"http:\/\/blog.yavor.info\/?p=468\">\u0441\u0442\u0430\u0442\u0438\u044f\u0442\u0430<\/a>; \u043a\u0430\u043a\u0442\u043e \u0438 \u0437\u0430 \u0434\u0430 \u0442\u0435\u0441\u0442\u0432\u0430\u043c \u0435\u0432\u0435\u043d\u0442\u0443\u0430\u043b\u043d\u0438\u0442\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u044f, \u0434\u0440\u0430\u0441\u043d\u0430\u0445 \u043d\u0430\u0431\u044a\u0440\u0437\u043e \u0435\u0434\u0438\u043d \u0442\u0435\u0441\u0442\u043e\u0432\u0430 \u043f\u0430\u043a\u0435\u0442. \u0418\u043c\u0430 \u0441\u0438 \u043a\u0443\u0441\u0443\u0440\u0438 &#8211; \u043f\u0440\u0438\u043c\u0435\u0440\u043d\u043e \u043d\u0435 \u0441\u0435 \u0434\u044a\u0440\u0436\u0438 \u043f\u0440\u0430\u0432\u0438\u043b\u043d\u043e \u0430\u043a\u043e config \u0442\u0430\u0431\u043b\u0438\u0446\u0430\u0442\u0430 \u0435 \u043f\u0440\u0430\u0437\u043d\u0430 &#8211; \u043d\u043e \u0437\u0430 \u0440\u0435\u0448\u0435\u043d\u0438\u0435 &#8222;\u043d\u0430\u0431\u044a\u0440\u0437\u043e&#8220; \u0441\u0435 \u043f\u043e\u043b\u0443\u0447\u0438 \u0434\u043e\u0431\u0440\u0435. \u0412\u0441\u044a\u0449\u043d\u043e\u0441\u0442 \u0442\u043e\u043b\u043a\u043e\u0432\u0430 \u0434\u043e\u0431\u0440\u0435, \u0447\u0435 \u0440\u0435\u0448\u0438\u0445 \u0434\u0430 \u0433\u043e \u0441\u043b\u043e\u0436\u0430 \u0442\u0443\u043a, \u0434\u0430 \u043d\u0435 \u0433\u043e \u0437\u0430\u0433\u0443\u0431\u044f \ud83d\ude42<\/p>\n<p>(<a href=\"http:\/\/blog.yavor.info\/?p=469\">A\u043a\u043e \u0442\u0438 \u0435 \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u043e, \u0447\u0435\u0442\u0438 \u043d\u0430\u0442\u0430\u0442\u044a\u043a&#8230;<\/a>)<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/posts\/469"}],"collection":[{"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=469"}],"version-history":[{"count":1,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/posts\/469\/revisions"}],"predecessor-version":[{"id":721,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/posts\/469\/revisions\/721"}],"wp:attachment":[{"href":"http:\/\/blog.yavor.info\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=469"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=469"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=469"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}