问：同事告诉我下面我写的这个脚本如果只用一条SQL来处理会更快一些，在date_ended列上用MIN会有问题(可能会存在重复记录和空的情况)。这是真的吗?为什么?为什么我的代码性能如此糟糕？
spool &&filename
-- set serveroutput on format wrapped execute dbms_output.new_line; execute dbms_output.put_line('In UPDATE_VLE_RESP.sql...'); execute dbms_output.new_line;
DECLARE
-- ===============================================================
-- Constants
-- ===============================================================
-- VLE AUTHOR and COAUTHOR responsibilities constants sql_c_usage_type
constant varchar2(1) := 'P';
sql_c_vle_auth constant varchar2(10) := 'VLE_AUTHOR';
sql_c_vle_coauth constant varchar2(12) := 'VLE_COAUTHOR';
sql_c_ps_role_object constant varchar2(3) := 'PPI';
sql_c_rv_domain constant varchar2(15) := 'PS_ROLE_OBJECTS';
-- new VLE PROG_ORG responsibilities constants
sql_c_prog_org constant varchar2(8) := 'PROG_ORG';
-- ===============================================================
-- Type and Subtype Declarations
-- ===============================================================
-- ===============================================================
-- Variable Declarations
-- ===============================================================
sql_commit_level NUMBER := 0;
sql_future_vle_resp_cnt NUMBER := 0;
sql_vle_upd_total NUMBER := 0;
sql_msg_text VARCHAR2(1000);
sql_hdr VARCHAR2(1);
sql_vle_rpt_total NUMBER := 0;
--
-- =====================================================
-- Cursors
-- =====================================================
-- get all current PPI responsibility types of VLE_AUTHOR and VLE_COAUTHOR.
-- This will be used to close off these responsibilities and report those
-- responsibilities that have been closed off in the future before they are updated.
CURSOR c_psr_vle (
p_vle_usage_type IN MISREG_PROG_STRUCT_ROLES.usage_type%TYPE,
p_vle_auth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
p_vle_coauth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
p_vle_ps_role_object IN MISREG_PROG_STRUCT_ROLES.rv_ps_role_object%TYPE,
p_vle_rv_domain IN MISREG_PROG_STRUCT_ROLES.rv_domain%TYPE) IS
SELECT rowid,
psr.role_code,
psr.hple_person_code,
psr.opr_type_code, -- VLE_AUTHOR/VLE_COAUTHOR
psr.rv_ps_role_object, -- 'PPI'
psr.rv_domain, -- 'PS_ROLE_OBJECTS'
psr.ppi_programme_code,
psr.ppi_consecutive_version_no,
psr.ppi_concurrent_version_no,
psr.ppi_programme_syllabus_period,
psr.ppi_domain_psp,
psr.ppi_location_code,
psr.ppi_calendar_type_code,
psr.ppi_moa_code,
psr.date_started,
psr.date_ended
FROM misreg_prog_struct_roles psr
WHERE psr.usage_type = p_vle_usage_type
AND psr.opr_type_code IN (p_vle_auth, p_vle_coauth)
AND psr.rv_ps_role_object = p_vle_ps_role_object
AND psr.rv_domain = p_vle_rv_domain
AND SYSDATE BETWEEN psr.date_started AND NVL(psr.date_ended,SYSDATE);
--
-- =====================================================
-- Procedures and Functions
-- =====================================================
PROCEDURE show_error(
p_err_code number,
p_err_text varchar2)
IS
BEGIN
raise_application_error(p_err_code, p_err_text);
END show_error;
------------------------------------------------------------------------------
PROCEDURE show_sys_error(
p_err_code number,
p_err_text varchar2) IS
BEGIN
show_error(p_err_code => p_err_code,
p_err_text => p_err_text||' - '||
dbms_utility.format_error_stack);
END show_sys_error;
------------------------------------------------------------------------------
PROCEDURE show_msg(
p_text VARCHAR2,
p_new_line VARCHAR2) IS
sql_buffer NUMBER := 1;
BEGIN
WHILE sql_buffer <= LENGTH(p_text) LOOP
dbms_output.put_line(substr(p_text,sql_buffer,200));
sql_buffer := sql_buffer + 200;
END LOOP;
IF p_new_line = 'Y' THEN
dbms_output.new_line;
END IF;
END show_msg;
------------------------------------------------------------------------------
-- Insert PPI responsibility type of PROG_ORG based on existing current
-- PPI responsibility type of VLE_AUTHOR/VLE_COAUTHOR. If person has both
-- responsibilities then only 1 PROG_ORG record is created for the one with
-- the earliest end date. NB. if no end date has been recorded then PROG_ORG
-- record will be created with a NULL end date.
PROCEDURE ins_prog_org_resps
(p_vle_usage_type IN MISREG_PROG_STRUCT_ROLES.usage_type%TYPE,
p_vle_auth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
p_vle_coauth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
p_prog_org IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
p_vle_ps_role_object IN MISREG_PROG_STRUCT_ROLES.rv_ps_role_object%TYPE,
p_vle_rv_domain IN MISREG_PROG_STRUCT_ROLES.rv_domain%TYPE)
IS
sql_role_code MISREG_PROG_STRUCT_ROLES.role_code%TYPE;
BEGIN
--
sql_msg_text := 'Copying VLE responsibilities to new PROG_ORG responsibilities...';
show_msg(p_text => sql_msg_text, p_new_line => 'N');
--
INSERT INTO misreg_prog_struct_roles (role_code,
usage_type,
created_by,
created_date,
hple_person_code,
opr_type_code,
rv_ps_role_object,
rv_domain,
ppi_programme_code,
ppi_consecutive_version_no,
ppi_concurrent_version_no,
ppi_programme_syllabus_period,
ppi_domain_psp,
ppi_location_code,
ppi_calendar_type_code,
ppi_moa_code,
date_started,
date_ended)
SELECT misreg_prog_struct_role_seqs.nextval, inner_view.*
FROM (SELECT DISTINCT p_vle_usage_type in_usage_type, -- 'P'
USER,
SYSDATE in_cr_date,
psr.hple_person_code,
p_prog_org in_prog_org, -- 'PROG_ORG'
p_vle_ps_role_object in_ps_role_object, -- 'PPI'
p_vle_rv_domain in_vle_rv_domain, -- 'PS_ROLE_OBJECTS'
psr.ppi_programme_code,
psr.ppi_consecutive_version_no,
psr.ppi_concurrent_version_no,
psr.ppi_programme_syllabus_period,
psr.ppi_domain_psp,
psr.ppi_location_code,
psr.ppi_calendar_type_code,
psr.ppi_moa_code,
SYSDATE in_date_started,
psr.date_ended
FROM misreg_prog_struct_roles psr
WHERE psr.usage_type = p_vle_usage_type -- 'P'
AND psr.opr_type_code IN (p_vle_auth,p_vle_coauth)
AND psr.rv_ps_role_object = p_vle_ps_role_object
AND psr.rv_domain = p_vle_rv_domain
AND SYSDATE BETWEEN psr.date_started AND NVL(psr.date_ended,SYSDATE)
AND NVL(psr.date_ended,SYSDATE) =
(SELECT MIN(NVL(psr2.date_ended,SYSDATE)) earliest_date
FROM misreg_prog_struct_roles psr2
WHERE psr.usage_type = psr2.usage_type
AND psr.hple_person_code = psr2.hple_person_code
AND psr.rv_domain = psr2.rv_domain
AND psr.rv_ps_role_object = psr2.rv_ps_role_object
AND psr.ppi_programme_code = psr2.ppi_programme_code
AND psr.ppi_consecutive_version_no = psr2.ppi_consecutive_version_no
AND psr.ppi_concurrent_version_no = psr2.ppi_concurrent_version_no
AND psr.ppi_programme_syllabus_period = psr2.ppi_programme_syllabus_period
AND psr.ppi_domain_psp = psr2.ppi_domain_psp
AND psr.ppi_location_code = psr2.ppi_location_code
AND psr.ppi_calendar_type_code = psr2.ppi_calendar_type_code
AND psr.ppi_moa_code = psr2.ppi_moa_code
AND SYSDATE BETWEEN psr2.date_started AND NVL(psr2.date_ended,SYSDATE))) inner_view;
--
sql_msg_text := '---- There were '||SQL%rowcount||' VLE responsibilities copied to new PROG_ORG responsibilities...';
show_msg(p_text => sql_msg_text, p_new_line => 'Y');
-- dbms_output.put_line(chr(10));
-- dbms_output.put_line('VLE responsibilities records copied: '||SQL%rowcount);
EXCEPTION
WHEN OTHERS THEN
rollback;
show_sys_error(-20120,
'Error: Inserting Prog Struct Roles for '||
'Usage Type: '|| p_vle_usage_type|| ' and ' ||
'Opr Type Code: '|| p_prog_org || ' and ' ||
'Prog Struct Role object: '|| p_vle_ps_role_object);
END;
------------------------------------------------------------------------------
-- Check if there are any existing future PPI responsibility types of
-- VLE_AUTHOR/VLE_COAUTHOR. If there are then no further processing is done.
FUNCTION future_vle_resp (p_vle_usage_type IN MISREG_PROG_STRUCT_ROLES.usage_type%TYPE,
p_vle_auth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
p_vle_coauth IN MISREG_PROG_STRUCT_ROLES.opr_type_code%TYPE,
p_vle_ps_role_object IN MISREG_PROG_STRUCT_ROLES.rv_ps_role_object%TYPE,
p_vle_rv_domain IN MISREG_PROG_STRUCT_ROLES.rv_domain%TYPE)
RETURN NUMBER
IS
v_return NUMBER :=0;
BEGIN
SELECT COUNT(psr.role_code)
INTO v_return
FROM misreg_prog_struct_roles psr
WHERE psr.usage_type = p_vle_usage_type -- 'P'
AND psr.opr_type_code IN (p_vle_auth,p_vle_coauth)
AND psr.rv_ps_role_object = p_vle_ps_role_object
AND psr.rv_domain = p_vle_rv_domain
AND SYSDATE
--
RETURN v_return;
--
EXCEPTION
WHEN OTHERS THEN
rollback;
show_sys_error(-20120,
'Error: Checking future Prog Struct Roles for '||
'Usage Type: '|| p_vle_usage_type|| ' and ' ||
'Opr Type Codes: '|| p_vle_auth || ', ' || p_vle_coauth || ' and ' ||
'Prog Struct Role object: '|| p_vle_ps_role_object);
END;
--
--=====================================================
-- Main Processing
--=====================================================
BEGIN
dbms_output.enable('1000000');
-- =========================================================
-- PROCESS VLE RESPONSIBILITIES UPDATE
-- =========================================================
--
-- Check if there are any future responsibilities
sql_future_vle_resp_cnt := future_vle_resp (sql_c_usage_type,
sql_c_vle_auth,
sql_c_vle_coauth,
sql_c_ps_role_object,
sql_c_rv_domain);
--
IF sql_future_vle_resp_cnt = 0 THEN
-- Copy existing VLE responsibilities
ins_prog_org_resps (sql_c_usage_type,
sql_c_vle_auth,
sql_c_vle_coauth,
sql_c_prog_org,
sql_c_ps_role_object,
sql_c_rv_domain);
--
-- Get existing VLE responsibilities to be closed off
sql_msg_text := 'Closing off existing current VLE responsibilities...';
show_msg(p_text => sql_msg_text, p_new_line => 'Y');
-- dbms_output.put_line(chr(10));
-- dbms_output.put_line('Closing off existing VLE responsibilities...');
--
sql_hdr := 'Y';
FOR c_upd_vle in c_psr_vle(sql_c_usage_type,
sql_c_vle_auth,
sql_c_vle_coauth,
sql_c_ps_role_object,
sql_c_rv_domain) LOOP
--
sql_vle_upd_total := sql_vle_upd_total + 1;
--
IF c_upd_vle.date_ended IS NOT NULL THEN
-- current VLE responsibility has been closed off in the future so report it
IF sql_hdr = 'Y' THEN
sql_msg_text := 'Current VLE Responsibilities closed off in the future';
show_msg(p_text => sql_msg_text, p_new_line => 'N');
sql_msg_text := '-----------------------------------------------------';
show_msg(p_text => sql_msg_text, p_new_line => 'Y');
sql_hdr := 'N';
END IF;
-- keep a running total of reported recs to use in output
sql_vle_rpt_total := sql_vle_rpt_total + 1;
sql_msg_text := 'Rec Num: '|| sql_vle_rpt_total;
show_msg(p_text => sql_msg_text, p_new_line => 'N');
sql_msg_text := '----------------------';
show_msg(p_text => sql_msg_text, p_new_line => 'N');
sql_msg_text := 'Role Code: '||c_upd_vle.role_code||'. '||
'Person Code: '||c_upd_vle.hple_person_code||'. '||
'Opr Type Code: '||c_upd_vle.opr_type_code||'. '||
'PS Role Obj: '||c_upd_vle.rv_ps_role_object||'. '||
'RV Domain: '||c_upd_vle.rv_domain||'. '||
'PPI Prog Code: '||c_upd_vle.ppi_programme_code||'. '||
'PPI Cons: '||c_upd_vle.ppi_consecutive_version_no||'. '||
'PPI Conc: '||c_upd_vle.ppi_concurrent_version_no||'. '||
'PPI Period: '||c_upd_vle.ppi_programme_syllabus_period||'. '||
'PPI Domain PSP: '||c_upd_vle.ppi_domain_psp||'. '||
'PPI Location: '||c_upd_vle.ppi_location_code||'. '||
'PPI Cal Type: '||c_upd_vle.ppi_calendar_type_code||'. '||
'PPI MoA: '||c_upd_vle.ppi_moa_code||'. '||
'Date Started: '||c_upd_vle.date_started||'. '||
'Date Ended: '||c_upd_vle.date_ended||'.';
--
show_msg(p_text => sql_msg_text, p_new_line => 'Y');
-- VLE responsibilities record has been closed off in the future
END IF;
--
-- Update VLE responsibilities and close off
UPDATE misreg_prog_struct_roles
SET date_ended = (SYSDATE - 1)
WHERE rowid = c_upd_vle.rowid;
--
sql_commit_level := sql_commit_level + 1; -- Commit after every X records
IF sql_commit_level > 1000 THEN
COMMIT;
sql_commit_level := 0;
END IF;
--
END LOOP;
--
sql_msg_text := '---- There were '||sql_vle_upd_total||' current VLE Responsibilities closed off (date_ended set to '||(sysdate-1)||')...';
show_msg(p_text => sql_msg_text, p_new_line => 'Y');
--
COMMIT;
--
ELSE
dbms_output.new_line;
sql_msg_text := 'Error: Cannot process VLE responsibilities updates.'||chr(10)||
'There are future Prog Struct Roles for...'||chr(10)||
'Usage Type: '|| sql_c_usage_type|| ' and '||
'Opr Type Codes: '|| sql_c_vle_auth || ', ' || sql_c_vle_coauth || ' and ' ||chr(10)||
'Prog Struct Role object: '|| sql_c_ps_role_object;
show_msg(p_text => sql_msg_text, p_new_line => 'Y');
--
END IF;
--
sql_msg_text := '...UPDATE_VLE_RESP.sql finished.';
show_msg(p_text => sql_msg_text, p_new_line => 'N');
END;
/
答：你同事给你建议最可能的原因是你在采用逐行处理数据的方式。如Tom Kyte喜欢说的：“逐行处理数据是越来越慢”。做任何事情一次处理一行而不是一次处理多行，原理上都会更慢一些。
可以这样理解：如果你要到杂货店买10件商品，你来到杂货店，取第一件商品，付款，拿回家，放起来，然后检查一下列表，看看你需要的下一个商品，回到商店，拿那个商品，如此直到取完了10件商品。傻子才会这么做不是吗?你最好一趟就拿起所有的商品。对整个数据集一次处理一条记录就象去杂货店10趟拿10个商品，就是太慢了。听起来很有趣，但是与实际生活中的场景类比：就象用一条SQL语句处理一批数据和用PL/SQL循环一次处理一行的道理是一样的。
你的代码中的一个线索就是，可以肯定你需要在PL/SQL循环中处理的一个或两个SQL语句基本上是一样的。他们都有基本相同的解释(Where从句)。对于要更新的行，真正要做的就是设置date_ended为不同的值，除此之外没有别的。所以，为什么要一行一行地处理呢?为什么不用带Where的select语句选出需要Update的数据，然后直接更新，而不是一次只更新一行。
基本上你要做的就是如下几步：
1、执行一个查询看是否有date_started日期早于今天的的行。
2、给一个表插入新记录(这个逻辑让我有些晕，但如果说它能象期望的一样工作，那么OK)。Min子句的用法很可能可以用分析函数实现(我比较倾向于这样做)，那是我很愿意谈的整个其他主题，如果你想深入了解，并能一直跟踪的话。
3、接着，读取所有的行，就是原有的行加上新插入的行，更新每行的date_ended。
即便是最坏的情况，应该也是执行两个SQL语句就可以搞定：1)执行SQL插入你需要增加的行;2)用一条Update语句修改date_ended 。这种方式比一次处理一行的方式要节约时间和资源，节约时间和资源的多少视这个过程影响的行数多少有很大不同。
另外就是你自己多琢磨琢磨，这有助于理解逐行处理引起了额外的负担，叫做上下文交换。无论何时你从PL/SQL循环中执行SQL，Oracle都不得不在PL/SQL执行引擎和SQL执行引擎之间进行换入换出的上下文交换。在你的例子中是从Update执行语句和PL/SQL引擎间来回交换。这些交换带来了负担。即便只是1/1000秒，如果是处理几千条记录，单单是上下文交换就可以吃掉大量不必要的时间。如果使用单条SQL语句代替，就完全可以避免这些交换。当然，你还是能在PL/SQL块中执行两个或多个SQL语句，并进行例外处理等，但是真的不必用那些额外的代码来处理一次更新一行。再想想杂货店的例子，如果有100，000行记录要更新，你可以执行100，000次单独的Update语句(象你正在做的)，或者一次更新那100，000行记录。
最后，我要告诉你的是，试试看，就能很容易比较每种处理方式所用的时间。如果只是处理几百行记录，可能不会感觉到太明显的区别，但如果处理几千条或几万条记录就很明显。但是从性能优化的观点看，无论何时都应该总是要避免一行一行地处理数据。
337
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
