Oracle 问题谁能帮我.docx

Jobs调用了一个存储过程，要5分钟才能执行完成，单独执行那个存储过程第一次要很久，以后都很快，要怎么才能让每次执行只要1分钟，数据库里一共大约100W数据左右，下面是那个存储过程：CREATE OR REPLACE PROCEDURE QBET.FMS_SCHE_JOBISl_table_name VARCHAR(500);l_sql VARCHAR(4000);l_count NUMBER;l_runnint_status NUMBER;l_start_exec_min NUMBER;l_exec_time DATE;l_start TIMESTAMP;l_end TIMESTAMP;TYPE odds_table_name IS TABLE OF VARCHAR2(50);odds_tables odds_table_name:=odds_table_name(T_FMS_TOTALGOAL,T_FMS_HALFFULLTIME,T_FMS_FIRST_LAST,T_FMS_CORRECTSCORE,T_FMS_OUTRIGHT); -,T_FMS_OUTRIGHTBEGIN FOR rec in(select * from v_setting_sche) LOOP - dbms_output.put_line(rec.section:|rec.section|=rec.section+Id:|rec.section_id|=rec.effect_time:|rec.effect_time); FOR rec2 in (select * from t_fms_master_table where section_id = rec.section_id) LOOP - dbms_output.put_line(rec2.GAME_TYPE_KEY:|rec2.GAME_TYPE_KEY); IF(rec.section=EA)THEN IF(UPPER(rec2.game_type_key)=OTHER) THEN FOR i IN odds_tables.FIRST . odds_tables.LAST LOOP dbms_output.put_line(EA=|odds_tables(i); -主盘口 l_sql := UPDATE | odds_tables (i)| SET auto_first = :1, auto_second = :2, auto_third = :3, auto_fourth = :4, auto_first_percent = :5, auto_second_percent = :6, auto_third_percent = :7, auto_fourth_percent = :8, company_min_bet = :9, company_highest_bet = :10, min_bet = :11, auto_shift_mode = :12, auto_pause = :13, update_date = sysdate, auto_setting = 1, update_by = sche, SETTING_SECTION_ID = :14, last_setting_update_time = sysdate WHERE match_id IN ( SELECT match_id FROM t_fms_match WHERE setting_table_id = :15 and HAS_DIVIDENT = 0 and RBALL_STATUS = 0 AND find_acct_day (start_time) today_acct_day () and nvl(last_setting_update_time,trunc(find_acct_day(sysdate),DDD) today_acct_day () and nvl(last_setting_update_time,trunc(find_acct_day(sysdate),DDD) today_acct_day () and nvl(last_setting_update_time,trunc(find_acct_day(sysdate), DDD) today_acct_day () and nvl(last_setting_update_time,trunc(find_acct_day(sysdate), DDD) = :17 ); BEGIN EXECUTE IMMEDIATE l_sql USING rec2.auto_first, rec2.auto_second, rec2.auto_third, rec2.auto_fourth, rec2.auto_first_percent, rec2.auto_second_percent, rec2.auto_third_percent, rec2.auto_fourth_percent, rec2.each_jump, pany_min_bet, pany_highest_bet, rec2.min_bet, rec2.auto_shift_mode, rec2.auto_pause, rec2.section_id, rec