롯데홈쇼핑

STT 고도화 2024-04 오픈

유상 유지보수 (월 방문)

　특정 콜 청취 시도 시 DB서버 CPU가 100%로 올라가는 현상 발생

　해당하는 DB 쿼리 조건 수정하여 개발계 테스트 완료

변경 작업 일정 : 09/25 점심시간 적용 (SSL 인증서 교체 작업과 병행)

　-- 변경 전 쿼리 --(rec_search.xml)

<!-- 연관 녹취이력 조회 selectRelationListBackup -->

   <select id="selectRelationListBackup" parameterType="map" resultType="lowerMap">

      /* 연관 녹취이력 조회 selectRelationListBackup */

      select

      rec_seq

      ,to_char(rec_datm, 'YYYY-MM-DD HH24:MI:SS') as rec_datm

      ,rec_date

      ,to_char(rec_start_time, 'HH24:MI:SS') as rec_start_time

      ,to_char(rec_call_time, 'HH24:MI:SS') as rec_call_time

      ,system_code

      ,rec_filename

      ,CASE NVL(custom_fld_08,' ') WHEN ' ' THEN rec_keycode else custom_fld_08 END as rec_keycode

      ,local_no

      ,user_id

      ,user_name

      ,nvl(rec_store_code, '0') as rec_store_code

      ,web_url

      ,cust_tel

      from tbl_record

      where 1=1

      and rec_datm between to_date(#{rec_date} || ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_date(#{rec_date} || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

      and (

      rec_keycode in (

      select distinct CASE nvl(REC_CONF_CODE,' ') WHEN ' ' THEN to_char(rec_keycode) ELSE to_char(REC_CONF_CODE) END from tbl_record a where rec_datm BETWEEN to_date(#{rec_date} || ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS')      AND to_date(#{rec_date} || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS') AND (a.rec_keycode = #{rec_keycode} OR a.custom_fld_08 = #{rec_keycode} OR a.rec_conf_code = #{rec_keycode})

      and a.rec_mode = '1'

      )

      OR

      rec_conf_code in (

      select distinct CASE nvl(REC_CONF_CODE,' ') WHEN ' ' THEN to_char(rec_keycode) ELSE to_char(REC_CONF_CODE) END from tbl_record a where rec_datm BETWEEN to_date(#{rec_date} || ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS')      AND to_date(#{rec_date} || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS') AND (a.rec_keycode = #{rec_keycode} OR a.custom_fld_08 = #{rec_keycode} OR a.rec_conf_code = #{rec_keycode})

      and a.rec_mode = '1'

      )

      )

      and rec_mode = '1'

      <if test='_user_level!=null and _user_level!="" and _user_level>"A"'>

         <choose>

            <when test='_user_level=="E"'>

               and user_id = #{_user_id}

            </when>

         </choose>

      </if>

      order by rec_datm asc, rec_start_time asc

   </select>

　-- 변경 후 쿼리 --(rec_search.xml)

<!-- 연관 녹취이력 조회 selectRelationList -->

   <select id="selectRelationList" parameterType="map" resultType="lowerMap">

      /* 연관 녹취이력 조회 selectRelationList */

      select

      rec_seq

      ,to_char(rec_datm, 'YYYY-MM-DD HH24:MI:SS') as rec_datm

      ,rec_date

      ,to_char(rec_start_time, 'HH24:MI:SS') as rec_start_time

      ,to_char(rec_call_time, 'HH24:MI:SS') as rec_call_time

      ,system_code

      ,rec_filename

      ,CASE NVL(custom_fld_08,' ') WHEN ' ' THEN rec_keycode else custom_fld_08 END as rec_keycode

      ,local_no

      ,user_id

      ,user_name

      ,nvl(rec_store_code, '0') as rec_store_code

      ,web_url

      ,cust_tel

      from tbl_record

      where 1=1

      and rec_datm between to_date(#{rec_date} || ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_date(#{rec_date} || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

      and ( rec_keycode = #{rec_keycode} or custom_fld_08 = #{rec_keycode} or rec_conf_code = #{rec_keycode} ) and rec_mode = #{rec_mode}

      <if test='_user_level!=null and _user_level!="" and _user_level>"A"'>

         <choose>

            <when test='_user_level=="E"'>

               and user_id = #{_user_id}

            </when>

         </choose>

      </if>

      order by rec_datm asc, rec_start_time asc

   </select>

기존 player.jsp에서 selectRelationList를 호출 후 없으면 selectRelationListBackup을 실행하였는데,

두 쿼리가 다른점은 rec_mode가 각각 0/1로 고정하여 호출

player.jsp에서 rec_mode를 0으로 담아서 selectRelationList를 호출 후 없으면 rec_mode를 1로 바꿔담아서 selectRelationList를 한번더 호출하는것으로 수정