Prepared Statement에서 매개 변수 설정이 작동하지 않음
다음과 같이 정의된 SQL 요청이 있습니다.
private static final String fetchOfferQuery = "SELECT DISTINCT "
+ "sim_id, sim_code, sim_label, sim_state, sim_type, sim_customerid, sim_storeid, sim_projectnumber, sim_version, sim_type_user, sim_type_vente, sim_statut, "
+ "(SELECT MAX(set_date) FROM offer_storage.t_simulationeventtrack_set WHERE set_sim_id = sim_id) AS sim_dateevtmax, "
+ "sim_creation_user, sim_modif_user, sim_rayon, sim_hours_lifetime, sim_eligible_reduced_vat, sim_store_linked, sim_canal, "
+ "ofr_id, CAST(ofr_creationdate AS timestamp) AS ofr_creationdate, ofr_label, ofr_state, ofr_transaction, ofr_modif_date, ofr_del_valid, "
+ "ofr_numcdecli, "
+ "ofi_id, ofi_productid, ofi_quantity,ofi_productprice, ofi_top, ofi_c1promo, ofi_codeactivite, ofi_codrem, "
+ "ofi_datejour, ofi_datepose, ofi_dateprevpose, ofi_datfinc1, ofi_datfinprxvtepromo, ofi_delai, ofi_libligdtl, ofi_montantpresta, "
+ "ofi_montrt, ofi_aro_id, ofi_numartisan, ofi_prxvte, ofi_prxvtepromo, ofi_typinitialoff, ofi_typoff, ofi_c1, ofi_numlig, "
+ "deo_id, deo_numligdtl, deo_codligdtl, deo_libligdtl, "
+ "aro_id, aro_type_offer, aro_type_inioff, aro_top_caisse, aro_num_arty, aro_date_prev, aro_mntrt, aro_date_jour, "
+ "aro_delai, aro_mnt_presta, aro_codact, aro_date_pose "
+ "FROM offer_storage.t_simulation_sim "
//with fixed date
+ "INNER JOIN offer_storage.t_simulationeventtrack_set ON set_sim_id = sim_id AND set_date >= TO_DATE('20180726000000','yyyymmddhh24miss') "
//with bind parameter
//+ "INNER JOIN offer_storage.t_simulationeventtrack_set ON set_sim_id = sim_id AND set_date >= ? "
+ "LEFT JOIN offer_storage.t_offer_ofr ON ofr_sim_id = sim_id "
+ "LEFT JOIN offer_storage.t_offeritem_ofi ON ofi_ofr_id = ofr_id "
+ "LEFT JOIN offer_storage.t_details_item_offer_deo ON deo_ofi_id = ofi_id "
+ "LEFT JOIN offer_storage.t_artisan_offer_aro ON aro_id = ofi_aro_id "
+ "ORDER BY sim_id, ofr_id, ofi_id, deo_id, aro_id";
요청에 정해진 날짜를 설정한 경우:
...
+ "INNER JOIN offer_storage.t_simulationeventtrack_set ON set_sim_id = sim_id AND set_date >= TO_DATE('20180726000000','yyyymmddhh24miss') "
...
그리고 다음과 같이 요청을 재생합니다. simulationsSt = connection.prepareStatement(fetchOfferQuery);결과설정 시뮬레이션Rs = 시뮬레이션St.실행쿼리();
요청이 종료되는 데 1분 30초가 걸립니다.
바인딩 매개 변수를 사용하는 경우:
...
+ "INNER JOIN offer_storage.t_simulationeventtrack_set ON set_sim_id = sim_id AND set_date >= ? "
...
실행 전에 설정 방법 사용쿼리(날짜 마지막 추출 = 같은 날짜 20180726000000):
simulationsSt.setTimestamp(1, new Timestamp(this.dateLastExtract.getTime()));
요청에 10분이 걸립니다!
또한 파라미터(int, date, string 등)를 설정해야 하는 모든 요청에 문제가 있습니다.따라서 날짜가 특정한 것이 아니라 바인딩을 사용할 때와 그렇지 않을 때입니다.
참고로 데이터 행의 수는 매우 많습니다.
t_simulationeventtrack_set : 66.097.939 rows
t_details_item_offer_deo : 46.259.704 rows
t_offeritem_ofi : 14.232.150 rows
t_artisan_offer_aro : 2.317.658 rows
t_offer_ofr : 1.801.969 rows
t_simulation_sim : 1.756.235 rows
CREATE TABLE t_simulation eventtrack_set 스크립트는 다음과 같습니다.
CREATE TABLE "OFFER_STORAGE"."T_SIMULATIONEVENTTRACK_SET"
( "SET_ID" NUMBER(*,0) NOT NULL ENABLE,
"SET_DATE" DATE,
"SET_CHANGETYPE" VARCHAR2(254 BYTE),
"SET_CHANGE" VARCHAR2(254 BYTE),
"SET_USR_ID" NUMBER(*,0),
"SET_SIM_ID" NUMBER(*,0),
CONSTRAINT "PK_SIMULATIONEVENTTRACK" PRIMARY KEY ("SET_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_INDX" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_DATA" ;
CREATE INDEX "OFFER_STORAGE"."IDX_SET_SIM_ID" ON "OFFER_STORAGE"."T_SIMULATIONEVENTTRACK_SET" ("SET_SIM_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_INDX" ;
CREATE INDEX "OFFER_STORAGE"."IDX_SIMULATIONEVENTTRACK_DATE" ON "OFFER_STORAGE"."T_SIMULATIONEVENTTRACK_SET" ("SET_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_INDX" ;
CREATE INDEX "OFFER_STORAGE"."IDX_SIMULATIONEVENTTRACK_FDATE" ON "OFFER_STORAGE"."T_SIMULATIONEVENTTRACK_SET" (TO_CHAR("SET_DATE",'YYYY-MM-DD'))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_INDX" ;
CREATE INDEX "OFFER_STORAGE"."IDX_SIM_ID_USER_ID" ON "OFFER_STORAGE"."T_SIMULATIONEVENTTRACK_SET" ("SET_SIM_ID", "SET_USR_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_INDX" ;
뭐가 잘못됐습니까?
"T_SIMULATION EVENT TRACK_SET"("SET_SIM_ID", "SET_DATE")에 인덱스를 추가하려고 했지만 변경되지 않았습니다.
편집:
여기서 날짜 매개변수에 대한 해결책을 찾았습니다. https://blog.jooq.org/2014/12/22/are-you-binding-your-oracle-dates-correctly-i-bet-you-arent/
"?" 대신 "CAST(? AS DATE)"를 사용하면 빠르게 작동합니다!
하지만 지금 저는 정수 매개변수에 대해서도 같은 문제를 겪고 있습니다.">= CAST(? AS DATE) or 1 =?" 절이 있습니다. 두 번째 매개변수는 1 또는 0입니다. 0을 입력하면 이전 매개변수도 모두 사용됩니다.
이 간단한 int 파라미터를 입력하면 다시 느려집니다.
편집 2:
바인딩된 실행 계획은 다음과 같습니다.
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 191M(100)| |
| 1 | SORT AGGREGATE | | 1 | 14 | | | |
| 2 | FIRST ROW | | 1 | 14 | | 4 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN (MIN/MAX)| IDX_SIMULATIONEVENTTRACK_SD | 1 | 14 | | 4 (0)| 00:00:01 |
| 4 | SORT ORDER BY | | 26 | 9698 | 17G| 191M (1)|637:07:31 |
| 5 | FILTER | | | | | | |
| 6 | HASH JOIN RIGHT OUTER | | 46M| 16G| 3351M| 915K (1)| 03:03:10 |
| 7 | TABLE ACCESS FULL | T_DETAILS_ITEM_OFFER_DEO | 46M| 2815M| | 145K (1)| 00:29:01 |
| 8 | HASH JOIN RIGHT OUTER | | 14M| 4263M| 134M| 384K (1)| 01:16:57 |
| 9 | TABLE ACCESS FULL | T_ARTISAN_OFFER_ARO | 2317K| 108M| | 4543 (1)| 00:00:55 |
| 10 | HASH JOIN OUTER | | 14M| 3589M| 325M| 187K (1)| 00:37:28 |
| 11 | HASH JOIN RIGHT OUTER | | 1823K| 304M| 125M| 35194 (1)| 00:07:03 |
| 12 | TABLE ACCESS FULL | T_OFFER_OFR | 1824K| 104M| | 5995 (1)| 00:01:12 |
| 13 | TABLE ACCESS FULL | T_SIMULATION_SIM | 1778K| 195M| | 12293 (1)| 00:02:28 |
| 14 | TABLE ACCESS FULL | T_OFFERITEM_OFI | 14M| 1183M| | 69005 (1)| 00:13:49 |
| 15 | INDEX RANGE SCAN | IDX_SIMULATIONEVENTTRACK_SD | 1 | 14 | | 4 (0)| 00:00:01 |
| 16 | INDEX RANGE SCAN | IDX_SET_SIM_ID | 2 | 12 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
구속력 없는 실행 계획(더 빠른 속도):
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 163K(100)| |
| 1 | SORT AGGREGATE | | 1 | 14 | | | |
| 2 | FIRST ROW | | 1 | 14 | | 4 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN (MIN/MAX) | IDX_SIMULATIONEVENTTRACK_SD | 1 | 14 | | 4 (0)| 00:00:01 |
| 4 | SORT ORDER BY | | 156K| 57M| 60M| 163K (1)| 00:32:41 |
| 5 | NESTED LOOPS OUTER | | 156K| 57M| | 150K (1)| 00:30:07 |
| 6 | NESTED LOOPS OUTER | | 48049 | 14M| | 40080 (1)| 00:08:01 |
| 7 | NESTED LOOPS OUTER | | 48049 | 12M| | 35935 (1)| 00:07:12 |
| 8 | HASH JOIN OUTER | | 6085 | 1123K| | 12654 (1)| 00:02:32 |
| 9 | NESTED LOOPS | | | | | | |
| 10 | NESTED LOOPS | | 5930 | 747K| | 6654 (1)| 00:01:20 |
| 11 | SORT UNIQUE | | 6008 | 84112 | | 643 (0)| 00:00:08 |
| 12 | TABLE ACCESS BY INDEX ROWID| T_SIMULATIONEVENTTRACK_SET | 6008 | 84112 | | 643 (0)| 00:00:08 |
| 13 | INDEX RANGE SCAN | IDX_SIMULATIONEVENTTRACK_DATE | 6008 | | | 20 (0)| 00:00:01 |
| 14 | INDEX UNIQUE SCAN | PK_SIMULATION | 1 | | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | T_SIMULATION_SIM | 1 | 115 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | T_OFFER_OFR | 1779K| 101M| | 5994 (1)| 00:01:12 |
| 17 | TABLE ACCESS BY INDEX ROWID | T_OFFERITEM_OFI | 8 | 688 | | 4 (0)| 00:00:01 |
| 18 | INDEX RANGE SCAN | IDX_OFI_OFR_ID | 9 | | | 2 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | T_ARTISAN_OFFER_ARO | 1 | 49 | | 2 (0)| 00:00:01 |
| 20 | INDEX UNIQUE SCAN | PK_ARTISANOFFER | 1 | | | 1 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | T_DETAILS_ITEM_OFFER_DEO | 3 | 189 | | 4 (0)| 00:00:01 |
| 22 | INDEX RANGE SCAN | IDX_DEO_OFI_ID | 22 | | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------
감사해요.
기본적으로 동일한 쿼리의 두 복사본이 성능 특성이 매우 다른 경우, 일반적으로 최적화 도구에 의해 실행 계획이 계산되는 방식이 약간 특이하기 때문입니다.
위의 경우, 당신의 쿼리에 데카르트 조인이 있다는 점에 유의하겠습니다.데카르트 조인에 관한 오라클 문서에 따르면:
"일부 경우 옵티마이저는 두 테이블 사이의 공통 필터 조건을 가능한 조인 조건으로 선택할 수 있습니다."
최적의 실행 계획이 아닌 실행 계획을 선택한 한 가지 가능한 원인은 바인딩 변수의 존재일 수 있습니다. 실행 계획이 변경되는 이유에 대한 Oracle 문서를 참조하십시오.
몇 가지 과 같습니다
변수 유형 및 값 바인딩..."
오라클 문서의 설명 중 하나는 '데카르트 조인을 지정하면 제대로 수행하기 위해 운이 필요할 수 있으며 바인딩 변수를 사용해도 기회가 개선되지 않습니다.'입니다.
"FROM_user_usr, t_simulation event track_set"을 대체하여 쿼리를 업데이트하는 경우
"FROMt_user_usr INER JOINT_simulation eventtrack_set ON xxx=yyy"를 사용하여
(xxx=yyyy는 아마도 WHERE의 조건 중 하나여야 하지만 표 설명 없이는 어떤 조건을 구분할 수 없습니다.) 최적화 도구가 더 잘 작동할 수 있습니다.
첫 번째 질문에는 '요청이 실행되지만 끝이 없습니다. 오류가 표시되지 않습니다.'라는 문구가 있었습니다.이 경우 '절대 안 함'이 얼마나 길었습니까?왜냐하면, 어떤 것이 몇 분의 1초가 걸릴 때, 30초 마크에 접근하는 것은 내 기계에서 죽임을 당하는 것을 발견할 수 있기 때문입니다.
의 최근 는... 1 ?'는 1 = ?'를 합니다.ID + SET_DATE 인덱스(데이터 크기가 주어지면 성능 문제가 발생함).
조건이 무엇을 하는지를 고려할 때, 나는 단순히 내 Java 코드에 두 개의 문(하나는 날짜 조건이 있고 하나는 날짜 조건이 없는 문)을 준비할 것입니다. 이것은 최적화 도구가 각 문에 사용할 올바른 인덱스를 선택할 수 있게 해줍니다.
과 같은 상수 표현을 제거하는 것 같습니다.X OR 1=0X동적으로 준비된 이 아닌 에는 리고동로준비표된현경의우일, ▁on▁▁and▁some▁failingion못함▁to▁not▁prepared▁express▁use▁dynamic지▁so▁for,하용▁a사를그.set_date.
이 은 이조의목필사터않설것입다니정는에 대한 을 가지고 .set_dateset_date가치.
가능하다면, 당신은 그것을 교체할 수 있습니다.
+ "AND (set_date >= TO_DATE('2018-07-19', 'YYYY-MM-DD') "
+ "OR 1 = ?) "
와 함께
+ "AND set_date >= ? "
그리고.
LocalDate d = seen == 1 ? LocalDate.of(1900, 1, 1) : LocalDate.of(2018, 7, 19);
java.sql.Date sd = new java.sql.Date(d.toEpochDay);
simulationsSt.setDate(1, sd);
요청이 시작되지만 종료되지 않고 오류가 표시되지 않습니다.
긴 실행 문(몇 초 이상)이 있는 경우 v$sg_longops에서 볼 수 있습니다.
select * from v$session_longops v order by v.start_time desc;
찾아 sql_id를 가져가서 dba_hist_sqltext의 문을 찾습니다.
select * from dba_hist_sqltext t where t.sql_id = 'b6usrg82hwsa3';
실행 계획은 dba_hist_sql_plan에 있습니다.
select * from dba_hist_sql_plan p where p.sql_id = 'b6usrg82hwsa3' order by p.plan_hash_value, p.id;
여기서 1개 이상의 값을 얻을 수 있습니다(다른 plan_value).
Joop Eggen이 이미 게시한 바와 같이, 바인딩 변수를 통해 실제로 매개 변수를 설정하면 다른 실행 계획에 부딪힐 수 있습니다.
더 빠른 실행 계획에서 볼 수 있듯이 날짜 열의 인덱스가 사용되고 최적화 도구가 시작되는 위치입니다.더 느린 실행 계획에서 최적화 도구는 이것이 좋은 인덱스라는 것을 알아차리지 못하므로 다른 곳에서 시작합니다.
문제는 java.sql입니다.타임스탬프 데이터 유형이 오라클 DATE와 일치하지 않습니다."set_date"는 DATE 유형이며, 정확도가 초임을 의미합니다.이제 java.sql 유형의 매개 변수를 사용하여 쿼리를 만듭니다.나노초 정밀도의 타임스탬프입니다.테이블의 DATE 값을 쿼리 매개 변수와 비교하기 위해 Oracle은 DATE를 더 높은 정밀도로 변환하는 함수를 사용해야 합니다(Java에서 캐스트로 생각).또한 열 값에 대한 함수를 먼저 사용해야 하는 경우에는 인덱스가 작동하지 않습니다.
수행해야 할 작업:JDBC를 통해 쿼리할 때는 항상 열의 유형보다 낮은 정밀도의 시간 유형을 사용하십시오.당신의 경우:java.sql을 사용합니다.쿼리 날짜 또는 열의 데이터 형식을 나노초 이상의 정밀도로 변경합니다.
이것으로 다른 데이터 유형의 문제가 해결되지 않는다는 것을 알지만, 당신이 게시한 정보와 실행 계획을 통해 제가 알려드릴 수 있는 것은 이것뿐입니다.
언급URL : https://stackoverflow.com/questions/51444951/set-any-param-in-preparedstatement-not-working
'programing' 카테고리의 다른 글
| MariaDB Left join이 예상 결과를 반환하지 않음 (0) | 2023.07.24 |
|---|---|
| 업데이트 쿼리의 SQL 트리거에 오류가 있습니다. (0) | 2023.07.24 |
| 대형 MySQL InnoDB 테이블 변경 (0) | 2023.07.24 |
| fragment의 onCreate(), onCreateView() 및 onActivityCreated()의 차이점 및 사용 (0) | 2023.07.24 |
| 브라우저 창 닫기 이벤트를 캡처하는 방법은 무엇입니까? (0) | 2023.07.24 |