Offer项目SQL能报告-左小龙.docx

Offer项目SQL性能报告左小龙本报告分为三个部分，前两部分分别从代码分析和压力测试分析，第三部分总结发现的问题和建议。一、PL/SQL包和视图代码技术分析本报告依据svn版本号12679的代码，2012年8月20日下午8030数据库，进行分析，包括包CUX_HCM_OFFER_UTIL和视图AD_PER_OFFER_TEMPLATE_V。首先对视图进行分析，该项目只有一个视图，该视图对FND_LOOKUP_VALUES进行查询，没有关联其他表，执行计划使用了LOOKUP_TYPE字段上的复合索引，而且该视图的结果只有几条数据，不存在性能问题。CUX_HCM_OFFER_UTIL包中有10个存储过程和11个函数，对代码中的SQL代码逐一分析如下：1、function get_offer_lookup_meaning(p_lookup_type in varchar2, p_lookup_code in varchar2)return varchar2该函数中对offer_lookup进行条件查询，8030上的执行计划显示使用了其item_type上的索引。2、function get_user_name_by_person_name(p_person_name in varchar2)return varchar2该函数中对per_all_people_f papf, fnd_user fu进行了关联查询，关联条件均有索引，不存在索引被抑制的情况，执行计划显示使用索引扫描，没有问题。3、procedure add_user_apply_to_nitification(p_apply_id in number)该过程对offer_user_apply 使用了条件查询和更新，均使用了ID主键索引，不存在性能问题。4、procedure update_user_apply_notification(p_apply_id in number, p_grant_role in varchar2)该过程中对cux_all_notifications的查询使用了全表扫描，该表其实可以加索引和主键，目前看该表未加任何索引和主键，不过该表不属于Offer项目建的表，所以这里根据情况处理。5、FUNCTION find_code_from_hr_by_meaning(v_meaning in varchar2, v_lookup_type in varchar2)RETURN VARCHAR2该函数对HR_LOOKUPS视图进行查询，使用了FND_LOOKUP_VALUES上的索引。6、function get_location_name_by_id(p_location_id in number) return varchar2该函数对cux_hr_locations_v进行查询，使用了HR_LOCATIONS_ALL上的主键索引。7、function get_building_name_by_id(p_building_id in number) return varchar2该函数同上。8、function get_organization_name_by_id(p_organization_id in number)return varchar2SQL如下：select anization_name /*, p.full_name*/ into l_organization_name from (select organization_id, name organization_name, attribute6 from apps.cux_hr_change_departments_v where trunc(sysdate) = nvl(date_to, to_date(2047-12-31, YYYY-MM-DD) and business_group_id = 0) v, (select anization_id, p.full_name from HR_ORGANIZATION_UNITS_V o, (select p.person_id, p.full_name from per_all_people_f p where trunc(sysdate) between p.effective_start_date and p.effective_end_date) p where trunc(sysdate) = nvl(o.date_to, to_date(2047-12-31, YYYY-MM-DD) and o.attribute13 = p.person_id(+) p where anization_id = anization_id(+) and anization_id = p_organization_id;该函数中对两个子查询进行了左外关联，不过从结果看这个关联似乎没用了，查询结果在该查询中只能是一个字符串，而且结果字段也不取决外关联的条件，这里不需要p.full_name字段的话建议把后一个子查询屏蔽掉，虽然从执行计划看都是使用了索引的。9、function get_user_id_by_person_id(p_person_id in number) return number该函数对fnd_user进行单表查询，使用了索引，没有性能问题。10、procedure insert_submit_entry_log(p_offer_m_id in number, p_recruiter_id in number, p_organization_id in number, p_supervisor_id in number, p_erp_position_id in number, p_location_id in number, p_building_id in number, p_entry_form_type in integer, p_plan_entry_date in date, p_re_entry_flag in varchar2 default N, p_hc_budget_desc in varchar2, p_entry_form_desc in varchar2)该过程使用了自治事务，向offer_entry_log中插入一条数据，没有性能问题。11、procedure offersys_submit_entry_form(p_offer_m_id in number, p_recruiter_id in number, p_organization_id in number, p_supervisor_id in number, p_erp_position_id in number, p_location_id in number, p_building_id in number, p_entry_form_type in integer, p_plan_entry_date in date, p_re_entry_flag in varchar2 default N, p_hc_budget_desc in varchar2, p_entry_form_desc in varchar2, x_entry_form_id out number)该过程中的SQL经过逐个分析执行计划，均使用了索引，不存在全表扫描。12、FUNCTION GET_OFFERCOLLEAGE(USER_ID NUMBER, POSITION_NAME VARCHAR2, P_SUPERVISOR_NAME VARCHAR2, JOB_DESC VARCHAR2, OFFER_CONFIRM_DATE DATE, OFFER_DELIVERY_DATE DATE) RETURN VARCHAR2该函数对offer_user_m进行了查询，并且调用了同名重载函数，SQL查询使用了主键扫描，没有问题。13、PROCEDURE GET_HC_FROM_JOB(p_plan_year IN NUMBER, P_POSTION_NAME IN VARCHAR2, R_HC_USED OUT NUMBER, R_HC_TOTAL OUT NUMBER)该过程中SQL：select hc, nvl(select count(distinct h.user_id) from offer_create_his h where h.position_name = p.position_name), 0) hc_used /* into R_HC_TOTAL, R_HC_USED */ from offer_plan_position p where p.position_name = :P_POSTION_NAME and p.plan_year = :p_plan_year and p.plan_id = (select max(id) from offer_plan where year = :p_plan_year and status = 1)对OFFER_CREATE_HIS、OFFER_CREATE_POSITION、OFFER_PLAN均使用了全表扫描，而且OFFER_PLAN使用了聚合函数，而且(select count(distinct h.user_id) from offer_create_his h where h.position_name = p.position_name) 这个子查询的结果非0即1，不需要进行非空处理，可以去掉外层的nvl函数，这里建议对offer_create_his表position_name，offer_plan_position表的position_name和plan_year以及offer_plan表的year和status字段添加索引。14、PROCEDURE GET_EMAIL_OF_USER(USER_ID IN NUMBER, EMAIL_ADDR OUT VARCHAR2)该过程对PER_ALL_PEOPLE_F进行简单查询，使用了索引，没有问题。15、FUNCTION writexml(p_name IN VARCHAR2, p_value IN VARCHAR2) RETURN VARCHAR2不含SQL。16、FUNCTION GET_OFFERCOLLEAGE(USER_ID NUMBER, POSITION_NAME VARCHAR2, P_SUPERVISOR_NAME VARCHAR2, JOB_DESC VARCHAR2, P_PAYMENT_AMOUNT NUMBER, P_BONUS_TYPE VARCHAR2, P_BONUS_AMOUNT NUMBER, P_YEAR_BONUS NUMBER, OFFER_CONFIRM_DATE DATE, OFFER_DELIVERY_DATE DATE) RETURN VARCHAR2使用静态游标，游标查询使用索引，非批量处理的话没有问题。17、FUNCTION GET_HC_USED(P_PLAN_YEAR IN NUMBER, P_POSITION_NAME IN VARCHAR2) RETURN NUMBERoffer_create_his表的position_name字段需要加索引。同13。18、PROCEDURE GET_OFFER_EMAIL_BODY(EMPLOYEE_ID IN NUMBER, EMAIL_BODY_OUT OUT VARCHAR2)这个问题比较多：SELECT TO_CHAR(DBMS_LOB.SUBSTR(T.TEMPLATE_CONTENT, 2000, 1) /* INTO EMAIL_BODY */ FROM WEBLOGIC.OFFER_EMAIL_TEMPLATE T WHERE TEMPLATE_TYPE = 6首先是OFFER_EMAIL_TEMPLATE表的TEMPLATE_TYPE字段可以建索引，避免全表扫描，然后是TEMPLATE_CONTENT字段的问题，之前我提到过这里可以不使用CLOB，现在从这个函数来看，既然已经取了2000个字节，完全可以使用varchar2，这里感觉有点多次一举了，我们如果使用CLOB的话，就是假设这个字段超过了4000个字节，但该函数中我们又只处理前2000个字节，那么这个CLOB好像是没用的。19、PROCEDURE READ_BLOB(TABLE_NAME IN VARCHAR2, COLUMN_NAME IN VARCHAR2, CONTENT OUT BLOB, KEY_NAME IN VARCHAR2, KEY_VALUE IN NUMBER) SQL为：STR_SQL := SELECT | COLUMN_NAME | FROM | TABLE_NAME | WHERE | KEY_NAME | = | KEY_VALUE; execute immediate STR_SQL INTO CONTENT; 使用了动态语句，获取结果为BLOB字段，可以绑定变量KEY_VALUE，可以使用execute immediate STR_SQL using x这种语法进行邦定，防止产生过多的硬解析，当然也可以考虑使用静态语句。20、PROCEDURE UPDATE_BLOB(TABLE_NAME IN VARCHAR2, COLUMN_NAME IN VARCHAR2, CONTENT IN BLOB, KEY_NAME IN VARCHAR2, KEY_VALUE IN NUMBER)这里是对BLOB字段进行更新，代码中邦定了变量，由于使用动态语句，无法得知执行计划。21、PROCEDURE IS_POSITION_EDITABLE(POSITION_ID IN NUMBER, EDITABLE OUT VARCHAR2)建议对OFFER_USER_M表的OFFER_STATUS字段和PLAN_POSITION_ID字段创建索引，以避免使用全表扫描。二、DB压力测试结果分析经过2012/8/29压力测试，发现页面上提供了根据各种指标进行的查询，但很多查询条件未创建索引，所以建议对所有可能出现的查询条件创建索引，已经让RD进行了修改。另外，测试中出现了死锁的情况，主要是不同用户同时生成同一个用户的offer文件，存在同时更新同一条数据的情况，发生了死锁，导致时间增长严重，后经验证实际操作中出现这种情况的概率比较低，本身生成offer的语句执行速度并不慢，而且根据管理员用户数和同时操作的可能性，认为这个问题可以暂时不考虑，有必要的话可以在代码中加锁。三、SQL性能分析结果汇总根据一的分析结果：1、 缺少索引：a) OFFER_USER_M表：OFFER_STATUS字段和PLAN_POSITION_ID字段b) OFFER_EMAIL_TEMPLATE表：TEMPLATE_TYPE字段c) cux_all_notifications表：cux_notif_id字段（不属于Offer项目）d) o