Menu
I have a SQL request defined like that :
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";
If I set a fixed date in the request :
...
+ "INNER JOIN offer_storage.t_simulationeventtrack_set ON set_sim_id = sim_id AND set_date >= TO_DATE('20180726000000','yyyymmddhh24miss') "
...
And play the request with : simulationsSt = connection.prepareStatement(fetchOfferQuery); ResultSet simulationsRs = simulationsSt.executeQuery();
The request take 1min30 to end.
If I use the bind parameter :
...
+ "INNER JOIN offer_storage.t_simulationeventtrack_set ON set_sim_id = sim_id AND set_date >= ? "
...
With set method before executeQuery (dateLastExtract = same Date of 20180726000000) :
simulationsSt.setTimestamp(1, new Timestamp(this.dateLastExtract.getTime()));
The request take 10min !
I also have the problem with all requests I need to set any param (int, date, string...etc). So it's not specific to date, it's just when I use binding and when I'm not.
For information, the number of data rows are pretty huge :
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
The CREATE TABLE t_simulationeventtrack_set script is :
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" ;
What is wrong ?
I've tried to add an index on "T_SIMULATIONEVENTTRACK_SET" ("SET_SIM_ID", "SET_DATE") But it didn't change anything.
EDIT :
I've find a solution for the date param here : https://blog.jooq.org/2014/12/22/are-you-binding-your-oracle-dates-correctly-i-bet-you-arent/
If I used "CAST(? AS DATE)" in replacement of "?", it works fast !
But now I have the same problem with an integer param. I've a clause ">= CAST(? AS DATE) OR 1 = ?" The second param is 1 or 0, and if I put 0, it gonna take every line even the old one.
When I put this simple int param, it is slow again...
EDIT 2 :
Here is the execution plan with binding :
--------------------------------------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------------------------------------
And the execution plan without binding (more faster) :
-----------------------------------------------------------------------------------------------------------------------------
| 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 |
-----------------------------------------------------------------------------------------------------------------------------
Thanks
218
When two copies of essentially the same query have very different performance characteristics, the cause is usually some quirk in how the execution plan is calculated by the optimizer.
For the above, I will note that your query has a Cartesian join. As per the Oracle documentation concerning
Cartesian Joins :
"In some cases, the optimizer could pick up a common filter condition between the two tables as a possible join condition."
One possible cause of the apparent choice of a not optimal execution plan may be the presence of the bind variables - see the Oracle documentation on Why Execution Plans Change :
"Some factors that affect the costs include the following:
...
Bind variable types and values
..."
One interpretation of the statements from the Oracle documentation is 'if you specify a Cartesian join, you may require some luck to have it perform well - and using bind variables does not improve your chances'.
If you update the query by replacing "FROM t_user_usr, t_simulationeventtrack_set "
with "FROM t_user_usr INNER JOIN t_simulationeventtrack_set ON xxx=yyy "
(xxx=yyy should probably be one of the conditions in the WHERE, but it is not possible to tell which one without the table description) would likely make the optimizer behave better.
The first question had the statement 'The request is launched, but it never end, no error is displayed.' How long was 'never' in this instance? I ask because, when something should take a fraction of a second anything approaching the 30 seconds mark may well find itself killed on my machine.
The latest problem with '... OR 1 = ?' will force the execution plan for the query to not use the SIM_ID + SET_DATE index (which, given the data size, will produce a performance issue).
Given what the condition does, I would simply have 2 prepared statements in my Java code (one with and one without the date condition) - that should allow the optimizer to pick the correct index to use for each of these.
151
It seems that the SQL engine eliminates constant expressions like X OR 1=0 to X.
And for a dynamic prepared expression not so, failing to use some index on set_date.
The condition has the purpose to disable the filtering on set_date, going for all set_date values.
If feasible you could do that by replacing
+ "AND (set_date >= TO_DATE('2018-07-19', 'YYYY-MM-DD') "
+ "OR 1 = ?) "
with
+ "AND set_date >= ? "
and
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
