/u01/app/admin/VMDEV01/udump/vmdev01_ora_29341_162910_vconovalov_128_957.trc *** TRACE DUMP CONTINUES IN FILE /u01/app/admin/VMDEV01/udump/vmdev01_ora_29341_162910_vconovalov_128_957.trc *** /u01/app/admin/VMDEV01/udump/vmdev01_ora_29341_162910_vconovalov_128_957.trc *** TRACE DUMP CONTINUED FROM FILE /u01/app/admin/VMDEV01/udump/vmdev01_ora_29341_162910_vconovalov_128_957.trc *** Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 System name: Linux Node name: mdch-vm-qadb Release: 2.6.18-92.el5PAE Version: #1 SMP Tue Jun 10 19:22:41 EDT 2008 Machine: i686 Instance name: VMDEV01 Redo thread mounted by this instance: 1 Oracle process number: 35 Unix process pid: 29341, image: oracle@mdch-vm-qadb *** 2009-10-26 16:29:48.397 *** ACTION NAME:() 2009-10-26 16:29:48.397 *** MODULE NAME:(nunit.exe) 2009-10-26 16:29:48.397 *** SERVICE NAME:(VMDEV01) 2009-10-26 16:29:48.397 *** SESSION ID:(128.957) 2009-10-26 16:29:48.397 Registered qb: SEL$1 0xb7f50308 (PARSER) signature (): qb_name=SEL$1 nbfros=2 flg=0 fro(0): flg=4 objn=18341 hint_alias="COUNTRY2_"@"SEL$1" fro(1): flg=4 objn=18384 hint_alias="THIS_"@"SEL$1" Registered qb: SEL$2 0xb7fab470 (PARSER) signature (): qb_name=SEL$2 nbfros=2 flg=0 fro(0): flg=4 objn=18344 hint_alias="ACQUIRER1_"@"SEL$2" fro(1): flg=5 objn=0 hint_alias="from$_subquery$_003"@"SEL$2" Registered qb: SEL$3 0xb7f56808 (PARSER) signature (): qb_name=SEL$3 nbfros=1 flg=0 fro(0): flg=5 objn=0 hint_alias="from$_subquery$_005"@"SEL$3" ************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in SEL$3 (#0). PM: Checking validity of predicate move-around in SEL$3 (#0). JPPD: JPPD bypassed: View not on right-side of outer join JPPD: JPPD bypassed: View not on right-side of outer join CBQT: Validity checks failed for 58zx8sr96rmpf. Query block (0xb7f50308) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT "THIS_"."MLE_ID" "MLE_ID","THIS_"."MLE_CREATIONDATE" "MLE_CREATIONDATE","THIS_"."MLE_DELETIONDATE" "MLE_DELETIONDATE","THIS_"."MLE_MODIFICATIONDATE" "MLE_MODIFICATIONDATE","THIS_"."MLE_STATUS" "MLE_STATUS","THIS_"."MLE_ACQ_ACQUIRERBID" "MLE_ACQ_ACQUIRERBID","THIS_"."MLE_TRADINGNAME" "MLE_TRADINGNAME","THIS_"."MLE_LEGALNAME" "MLE_LEGALNAME","THIS_"."MLE_PRIMARYPRINCIPALNAME" "MLE_PRIMARYPRINCIPALNAME","THIS_"."MLE_2NDPRINCIPALNAME" "MLE_2NDPRINCIPALNAME","THIS_"."MLE_3RDPRINCIPALNAME" "MLE_3RDPRINCIPALNAME","THIS_"."MLE_PRIMARYPHONENUMBER" "MLE_PRIMARYPHONENUMBER","THIS_"."MLE_2NDPHONENUMBER" "MLE_2NDPHONENUMBER","THIS_"."MLE_3RDPHONENUMBER" "MLE_3RDPHONENUMBER","THIS_"."MLE_ADDRESSSTREET" "MLE_ADDRESSSTREET","THIS_"."MLE_ADDRESSPOSTCODE" "MLE_ADDRESSPOSTCODE","THIS_"."MLE_ADDRESSCITY" "MLE_ADDRESSCITY","THIS_"."MLE_ADDRESSCOUNTYREGION" "MLE_ADDRESSCOUNTYREGION","THIS_"."MLE_ADDRESSLOCALITYREGION" "MLE_ADDRESSLOCALITYREGION","THIS_"."MLE_CNY_ADDRESSCOUNTRY" "MLE_CNY_ADDRESSCOUNTRY","THIS_"."MLE_MCC_CODE" "MLE_MCC_CODE","THIS_"."MLE_BRQ_BATCHID" "MLE_BRQ_BATCHID","THIS_"."MLE_ECOMMERCE" "MLE_ECOMMERCE","THIS_"."MLE_WEBADDRESS" "MLE_WEBADDRESS","THIS_"."MLE_INTERNATIONAL" "MLE_INTERNATIONAL","THIS_"."MLE_FINANCIALINSTITUTIONID" "MLE_FINANCIALINSTITUTIONID","THIS_"."MLE_BANKACCOUNTNUMBER" "MLE_BANKACCOUNTNUMBER","THIS_"."MLE_TAXIDENTIFICATION" "MLE_TAXIDENTIFICATION","THIS_"."MLE_COMPANYIDENTIFICATION" "MLE_COMPANYIDENTIFICATION","THIS_"."MLE_IBAN" "MLE_IBAN","THIS_"."MLE_CREATOR_NAME" "MLE_CREATOR_NAME","THIS_"."MLE_CREATOR_LOGIN" "MLE_CREATOR_LOGIN","COUNTRY2_"."CNY_ALPHA2CODE" "CNY_ALPHA2CODE","COUNTRY2_"."CNY_NAME" "CNY_NAME","COUNTRY2_"."CNY_ISOCODE" "CNY_ISOCODE" FROM "VMAS_TT"."ENQUIRY" "THIS_","VMAS_TT"."COUNTRY" "COUNTRY2_" WHERE "THIS_"."MLE_CNY_ADDRESSCOUNTRY"="COUNTRY2_"."CNY_ALPHA2CODE" kkqvIsPrimaryForeignKey cfro:ENQUIRY objn:18341 col#:21 dfro:COUNTRY dcol#:21 prp objn:18370 num:1 ref:22 prp objn:18341 num:1 ref:21 Query block (0xb7f50308) unchanged Query block (0xb7fab470) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT "from$_subquery$_003"."MLE_ID_0" "MLE_ID","from$_subquery$_003"."MLE_CREATIONDATE_1" "MLE_CREATIONDATE","from$_subquery$_003"."MLE_DELETIONDATE_2" "MLE_DELETIONDATE","from$_subquery$_003"."MLE_MODIFICATIONDATE_3" "MLE_MODIFICATIONDATE","from$_subquery$_003"."MLE_STATUS_4" "MLE_STATUS","from$_subquery$_003"."MLE_ACQ_ACQUIRERBID_5" "MLE_ACQ_ACQUIRERBID","from$_subquery$_003"."MLE_TRADINGNAME_6" "MLE_TRADINGNAME","from$_subquery$_003"."MLE_LEGALNAME_7" "MLE_LEGALNAME","from$_subquery$_003"."MLE_PRIMARYPRINCIPALNAME_8" "MLE_PRIMARYPRINCIPALNAME","from$_subquery$_003"."MLE_2NDPRINCIPALNAME_9" "MLE_2NDPRINCIPALNAME","from$_subquery$_003"."MLE_3RDPRINCIPALNAME_10" "MLE_3RDPRINCIPALNAME","from$_subquery$_003"."MLE_PRIMARYPHONENUMBER_11" "MLE_PRIMARYPHONENUMBER","from$_subquery$_003"."MLE_2NDPHONENUMBER_12" "MLE_2NDPHONENUMBER","from$_subquery$_003"."MLE_3RDPHONENUMBER_13" "MLE_3RDPHONENUMBER","from$_subquery$_003"."MLE_ADDRESSSTREET_14" "MLE_ADDRESSSTREET","from$_subquery$_003"."MLE_ADDRESSPOSTCODE_15" "MLE_ADDRESSPOSTCODE","from$_subquery$_003"."MLE_ADDRESSCITY_16" "MLE_ADDRESSCITY","from$_subquery$_003"."MLE_ADDRESSCOUNTYREGION_17" "MLE_ADDRESSCOUNTYREGION","from$_subquery$_003"."MLE_ADDRESSLOCALITYREGION_18" "MLE_ADDRESSLOCALITYREGION","from$_subquery$_003"."MLE_CNY_ADDRESSCOUNTRY_19" "MLE_CNY_ADDRESSCOUNTRY","from$_subquery$_003"."MLE_MCC_CODE_20" "MLE_MCC_CODE","from$_subquery$_003"."MLE_BRQ_BATCHID_21" "MLE_BRQ_BATCHID","from$_subquery$_003"."MLE_ECOMMERCE_22" "MLE_ECOMMERCE","from$_subquery$_003"."MLE_WEBADDRESS_23" "MLE_WEBADDRESS","from$_subquery$_003"."MLE_INTERNATIONAL_24" "MLE_INTERNATIONAL","from$_subquery$_003"."MLE_FINANCIALINSTITUTIONID_25" "MLE_FINANCIALINSTITUTIONID","from$_subquery$_003"."MLE_BANKACCOUNTNUMBER_26" "MLE_BANKACCOUNTNUMBER","from$_subquery$_003"."MLE_TAXIDENTIFICATION_27" "MLE_TAXIDENTIFICATION","from$_subquery$_003"."MLE_COMPANYIDENTIFICATION_28" "MLE_COMPANYIDENTIFICATION","from$_subquery$_003"."MLE_IBAN_29" "MLE_IBAN","from$_subquery$_003"."MLE_CREATOR_NAME_30" "MLE_CREATOR_NAME","from$_subquery$_003"."MLE_CREATOR_LOGIN_31" "MLE_CREATOR_LOGIN","from$_subquery$_003"."CNY_ALPHA2CODE_32" "CNY_ALPHA2CODE","from$_subquery$_003"."CNY_NAME_33" "CNY_NAME","from$_subquery$_003"."CNY_ISOCODE_34" "CNY_ISOCODE","ACQUIRER1_"."ACQ_BID" "ACQ_BID","ACQUIRER1_"."ACQ_NAME" "ACQ_NAME","ACQUIRER1_"."ACQ_ORIGINALCOUNTRY" "ACQ_ORIGINALCOUNTRY","ACQUIRER1_"."ACQ_DEFAULTCOUNTRY" "ACQ_DEFAULTCOUNTRY","ACQUIRER1_"."ACQ_SUSPENDENQUIRYFEE" "ACQ_SUSPENDENQUIRYFEE" FROM (SELECT "THIS_"."MLE_ID" "MLE_ID_0","THIS_"."MLE_CREATIONDATE" "MLE_CREATIONDATE_1","THIS_"."MLE_DELETIONDATE" "MLE_DELETIONDATE_2","THIS_"."MLE_MODIFICATIONDATE" "MLE_MODIFICATIONDATE_3","THIS_"."MLE_STATUS" "MLE_STATUS_4","THIS_"."MLE_ACQ_ACQUIRERBID" "MLE_ACQ_ACQUIRERBID_5","THIS_"."MLE_TRADINGNAME" "MLE_TRADINGNAME_6","THIS_"."MLE_LEGALNAME" "MLE_LEGALNAME_7","THIS_"."MLE_PRIMARYPRINCIPALNAME" "MLE_PRIMARYPRINCIPALNAME_8","THIS_"."MLE_2NDPRINCIPALNAME" "MLE_2NDPRINCIPALNAME_9","THIS_"."MLE_3RDPRINCIPALNAME" "MLE_3RDPRINCIPALNAME_10","THIS_"."MLE_PRIMARYPHONENUMBER" "MLE_PRIMARYPHONENUMBER_11","THIS_"."MLE_2NDPHONENUMBER" "MLE_2NDPHONENUMBER_12","THIS_"."MLE_3RDPHONENUMBER" "MLE_3RDPHONENUMBER_13","THIS_"."MLE_ADDRESSSTREET" "MLE_ADDRESSSTREET_14","THIS_"."MLE_ADDRESSPOSTCODE" "MLE_ADDRESSPOSTCODE_15","THIS_"."MLE_ADDRESSCITY" "MLE_ADDRESSCITY_16","THIS_"."MLE_ADDRESSCOUNTYREGION" "MLE_ADDRESSCOUNTYREGION_17","THIS_"."MLE_ADDRESSLOCALITYREGION" "MLE_ADDRESSLOCALITYREGION_18","THIS_"."MLE_CNY_ADDRESSCOUNTRY" "MLE_CNY_ADDRESSCOUNTRY_19","THIS_"."MLE_MCC_CODE" "MLE_MCC_CODE_20","THIS_"."MLE_BRQ_BATCHID" "MLE_BRQ_BATCHID_21","THIS_"."MLE_ECOMMERCE" "MLE_ECOMMERCE_22","THIS_"."MLE_WEBADDRESS" "MLE_WEBADDRESS_23","THIS_"."MLE_INTERNATIONAL" "MLE_INTERNATIONAL_24","THIS_"."MLE_FINANCIALINSTITUTIONID" "MLE_FINANCIALINSTITUTIONID_25","THIS_"."MLE_BANKACCOUNTNUMBER" "MLE_BANKACCOUNTNUMBER_26","THIS_"."MLE_TAXIDENTIFICATION" "MLE_TAXIDENTIFICATION_27","THIS_"."MLE_COMPANYIDENTIFICATION" "MLE_COMPANYIDENTIFICATION_28","THIS_"."MLE_IBAN" "MLE_IBAN_29","THIS_"."MLE_CREATOR_NAME" "MLE_CREATOR_NAME_30","THIS_"."MLE_CREATOR_LOGIN" "MLE_CREATOR_LOGIN_31","COUNTRY2_"."CNY_ALPHA2CODE" "CNY_ALPHA2CODE_32","COUNTRY2_"."CNY_NAME" "CNY_NAME_33","COUNTRY2_"."CNY_ISOCODE" "CNY_ISOCODE_34" FROM "VMAS_TT"."ENQUIRY" "THIS_","VMAS_TT"."COUNTRY" "COUNTRY2_" WHERE "THIS_"."MLE_CNY_ADDRESSCOUNTRY"="COUNTRY2_"."CNY_ALPHA2CODE") "from$_subquery$_003","VMAS_TT"."ACQUIRER" "ACQUIRER1_" WHERE "from$_subquery$_003"."MLE_ACQ_ACQUIRERBID_5"="ACQUIRER1_"."ACQ_BID" Query block (0xb7fab470) unchanged CVM: Considering view merge in query block SEL$3 (#0) CVM: Checking validity of merging SEL$2 (#0) CVM: Considering view merge in query block SEL$2 (#0) CVM: Checking validity of merging SEL$1 (#0) CVM: Considering view merge in query block SEL$1 (#0) CVM: Merging SPJ view SEL$1 (#0) into SEL$2 (#0) Registered qb: SEL$58A6D7F6 0xb7fab470 (VIEW MERGE SEL$2; SEL$1) signature (): qb_name=SEL$58A6D7F6 nbfros=3 flg=0 fro(0): flg=0 objn=18341 hint_alias="COUNTRY2_"@"SEL$1" fro(1): flg=0 objn=18384 hint_alias="THIS_"@"SEL$1" fro(2): flg=0 objn=18344 hint_alias="ACQUIRER1_"@"SEL$2" CVM: Merging SPJ view SEL$58A6D7F6 (#0) into SEL$3 (#0) Registered qb: SEL$9E43CB6E 0xb7f56808 (VIEW MERGE SEL$3; SEL$58A6D7F6) signature (): qb_name=SEL$9E43CB6E nbfros=3 flg=0 fro(0): flg=0 objn=18341 hint_alias="COUNTRY2_"@"SEL$1" fro(1): flg=0 objn=18384 hint_alias="THIS_"@"SEL$1" fro(2): flg=0 objn=18344 hint_alias="ACQUIRER1_"@"SEL$2" Query block (0xb7f56808) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT "THIS_"."MLE_ID" "MLE1_1_2_","THIS_"."MLE_PRIMARYPHONENUMBER" "MLE2_1_2_","THIS_"."MLE_PRIMARYPRINCIPALNAME" "MLE3_1_2_","THIS_"."MLE_2NDPHONENUMBER" "MLE4_1_2_","THIS_"."MLE_2NDPRINCIPALNAME" "MLE5_1_2_","THIS_"."MLE_3RDPHONENUMBER" "MLE6_1_2_","THIS_"."MLE_3RDPRINCIPALNAME" "MLE7_1_2_","THIS_"."MLE_BANKACCOUNTNUMBER" "MLE8_1_2_","THIS_"."MLE_ADDRESSCITY" "MLE9_1_2_","THIS_"."MLE_ADDRESSCOUNTYREGION" "MLE10_1_2_","THIS_"."MLE_ADDRESSLOCALITYREGION" "MLE11_1_2_","THIS_"."MLE_ADDRESSPOSTCODE" "MLE12_1_2_","THIS_"."MLE_ADDRESSSTREET" "MLE13_1_2_","THIS_"."MLE_COMPANYIDENTIFICATION" "MLE14_1_2_","THIS_"."MLE_FINANCIALINSTITUTIONID" "MLE15_1_2_","THIS_"."MLE_IBAN" "MLE16_1_2_","THIS_"."MLE_LEGALNAME" "MLE17_1_2_","THIS_"."MLE_TRADINGNAME" "MLE18_1_2_","THIS_"."MLE_TAXIDENTIFICATION" "MLE19_1_2_","THIS_"."MLE_WEBADDRESS" "MLE20_1_2_","THIS_"."MLE_ECOMMERCE" "MLE21_1_2_","THIS_"."MLE_INTERNATIONAL" "MLE22_1_2_","THIS_"."MLE_CREATOR_LOGIN" "MLE23_1_2_","THIS_"."MLE_CREATOR_NAME" "MLE24_1_2_","THIS_"."MLE_STATUS" "MLE25_1_2_","THIS_"."MLE_CREATIONDATE" "MLE26_1_2_","THIS_"."MLE_DELETIONDATE" "MLE27_1_2_","THIS_"."MLE_MODIFICATIONDATE" "MLE28_1_2_","THIS_"."MLE_BRQ_BATCHID" "MLE29_1_2_","THIS_"."MLE_CNY_ADDRESSCOUNTRY" "MLE30_1_2_","THIS_"."MLE_ACQ_ACQUIRERBID" "MLE31_1_2_","THIS_"."MLE_MCC_CODE" "MLE32_1_2_","COUNTRY2_"."CNY_ALPHA2CODE" "CNY1_27_0_","COUNTRY2_"."CNY_NAME" "CNY2_27_0_","COUNTRY2_"."CNY_ISOCODE" "CNY3_27_0_","ACQUIRER1_"."ACQ_BID" "ACQ1_4_1_","ACQUIRER1_"."ACQ_NAME" "ACQ2_4_1_","ACQUIRER1_"."ACQ_SUSPENDENQUIRYFEE" "ACQ3_4_1_","ACQUIRER1_"."ACQ_DEFAULTCOUNTRY" "ACQ4_4_1_","ACQUIRER1_"."ACQ_ORIGINALCOUNTRY" "ACQ5_4_1_" FROM "VMAS_TT"."ENQUIRY" "THIS_","VMAS_TT"."COUNTRY" "COUNTRY2_","VMAS_TT"."ACQUIRER" "ACQUIRER1_" WHERE "THIS_"."MLE_CREATIONDATE">=:B1 AND "THIS_"."MLE_CREATIONDATE"<=:B2 AND "ACQUIRER1_"."ACQ_BID"=:B3 AND "COUNTRY2_"."CNY_ALPHA2CODE"=:B4 AND "THIS_"."MLE_LEGALNAME"=:B5 AND "THIS_"."MLE_ACQ_ACQUIRERBID"="ACQUIRER1_"."ACQ_BID" AND "THIS_"."MLE_CNY_ADDRESSCOUNTRY"="COUNTRY2_"."CNY_ALPHA2CODE" ORDER BY "THIS_"."MLE_ID" DESC NULLS LAST Query block (0xb7f56808) unchanged CBQT: Validity checks failed for 58zx8sr96rmpf. *************** Subquery Unnest *************** SU: Considering subquery unnesting in query block SEL$9E43CB6E (#0) ************************* Set-Join Conversion (SJC) ************************* SJC: Considering set-join conversion in SEL$9E43CB6E (#0). ************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in SEL$9E43CB6E (#0). PM: Checking validity of predicate move-around in SEL$9E43CB6E (#0). PM: PM bypassed: Outer query contains no views. FPD: Considering simple filter push in SEL$9E43CB6E (#0) FPD: Current where clause predicates in SEL$9E43CB6E (#0) : "THIS_"."MLE_CREATIONDATE">=:B1 AND "THIS_"."MLE_CREATIONDATE"<=:B2 AND "ACQUIRER1_"."ACQ_BID"=:B3 AND NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''')=NLSSORT(:B4,'nls_sort=''BINARY_CI''') AND NLSSORT("THIS_"."MLE_LEGALNAME",'nls_sort=''BINARY_CI''')=NLSSORT(:B5,'nls_sort=''BINARY_CI''') AND "THIS_"."MLE_ACQ_ACQUIRERBID"="ACQUIRER1_"."ACQ_BID" AND NLSSORT("THIS_"."MLE_CNY_ADDRESSCOUNTRY",'nls_sort=''BINARY_CI''')=NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''') kkogcp: try to generate transitive predicate from check constraints for SEL$9E43CB6E (#0) constraint: "THIS_"."MLE_INTERNATIONAL"=1 OR "THIS_"."MLE_INTERNATIONAL"=0 constraint: "THIS_"."MLE_ECOMMERCE"=1 OR "THIS_"."MLE_ECOMMERCE"=0 constraint: "THIS_"."MLE_STATUS"=1 OR "THIS_"."MLE_STATUS"=0 predicates with check contraints: "THIS_"."MLE_CREATIONDATE">=:B1 AND "THIS_"."MLE_CREATIONDATE"<=:B2 AND "ACQUIRER1_"."ACQ_BID"=:B3 AND NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''')=NLSSORT(:B4,'nls_sort=''BINARY_CI''') AND NLSSORT("THIS_"."MLE_LEGALNAME",'nls_sort=''BINARY_CI''')=NLSSORT(:B5,'nls_sort=''BINARY_CI''') AND "THIS_"."MLE_ACQ_ACQUIRERBID"=:B6 AND NLSSORT("THIS_"."MLE_CNY_ADDRESSCOUNTRY",'nls_sort=''BINARY_CI''')=NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''') AND :B7>=:B8 AND ("THIS_"."MLE_INTERNATIONAL"=1 OR "THIS_"."MLE_INTERNATIONAL"=0) AND ("THIS_"."MLE_ECOMMERCE"=1 OR "THIS_"."MLE_ECOMMERCE"=0) AND ("THIS_"."MLE_STATUS"=1 OR "THIS_"."MLE_STATUS"=0) after transitive predicate generation: "THIS_"."MLE_CREATIONDATE">=:B1 AND "THIS_"."MLE_CREATIONDATE"<=:B2 AND "ACQUIRER1_"."ACQ_BID"=:B3 AND NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''')=NLSSORT(:B4,'nls_sort=''BINARY_CI''') AND NLSSORT("THIS_"."MLE_LEGALNAME",'nls_sort=''BINARY_CI''')=NLSSORT(:B5,'nls_sort=''BINARY_CI''') AND "THIS_"."MLE_ACQ_ACQUIRERBID"=:B6 AND NLSSORT("THIS_"."MLE_CNY_ADDRESSCOUNTRY",'nls_sort=''BINARY_CI''')=NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''') AND :B7<=:B8 AND ("THIS_"."MLE_INTERNATIONAL"=1 OR "THIS_"."MLE_INTERNATIONAL"=0) AND ("THIS_"."MLE_ECOMMERCE"=1 OR "THIS_"."MLE_ECOMMERCE"=0) AND ("THIS_"."MLE_STATUS"=1 OR "THIS_"."MLE_STATUS"=0) finally: "THIS_"."MLE_CREATIONDATE">=:B1 AND "THIS_"."MLE_CREATIONDATE"<=:B2 AND "ACQUIRER1_"."ACQ_BID"=:B3 AND NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''')=NLSSORT(:B4,'nls_sort=''BINARY_CI''') AND NLSSORT("THIS_"."MLE_LEGALNAME",'nls_sort=''BINARY_CI''')=NLSSORT(:B5,'nls_sort=''BINARY_CI''') AND "THIS_"."MLE_ACQ_ACQUIRERBID"=:B6 AND NLSSORT("THIS_"."MLE_CNY_ADDRESSCOUNTRY",'nls_sort=''BINARY_CI''')=NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''') AND :B7<=:B8 FPD: Following transitive predicates are generated in SEL$9E43CB6E (#0) : :B1<=:B2 apadrv-start: call(in-use=24928, alloc=32736), compile(in-use=134516, alloc=138004) kkoqbc-start : call(in-use=24936, alloc=32736), compile(in-use=135468, alloc=138004) kkoqbc-subheap (create addr=0xb7fa3ca4) ****************************************** Current SQL statement for this session: SELECT this_.MLE_ID as MLE1_1_2_, this_.MLE_PRIMARYPHONENUMBER as MLE2_1_2_, this_.MLE_PRIMARYPRINCIPALNAME as MLE3_1_2_, this_.MLE_2NDPHONENUMBER as MLE4_1_2_, this_.MLE_2NDPRINCIPALNAME as MLE5_1_2_, this_.MLE_3RDPHONENUMBER as MLE6_1_2_, this_.MLE_3RDPRINCIPALNAME as MLE7_1_2_, this_.MLE_BANKACCOUNTNUMBER as MLE8_1_2_, this_.MLE_ADDRESSCITY as MLE9_1_2_, this_.MLE_ADDRESSCOUNTYREGION as MLE10_1_2_, this_.MLE_ADDRESSLOCALITYREGION as MLE11_1_2_, this_.MLE_ADDRESSPOSTCODE as MLE12_1_2_, this_.MLE_ADDRESSSTREET as MLE13_1_2_, this_.MLE_COMPANYIDENTIFICATION as MLE14_1_2_, this_.MLE_FINANCIALINSTITUTIONID as MLE15_1_2_, this_.MLE_Iban as MLE16_1_2_, this_.MLE_LEGALNAME as MLE17_1_2_, this_.MLE_TRADINGNAME as MLE18_1_2_, this_.MLE_TaxIDENTIFICATION as MLE19_1_2_, this_.MLE_WEBADDRESS as MLE20_1_2_, this_.MLE_ECOMMERCE as MLE21_1_2_, this_.MLE_INTERNATIONAL as MLE22_1_2_, this_.MLE_CREATOR_LOGIN as MLE23_1_2_, this_.MLE_CREATOR_NAME as MLE24_1_2_, this_.MLE_STATUS as MLE25_1_2_, this_.MLE_CREATIONDATE as MLE26_1_2_, this_.MLE_DELETIONDATE as MLE27_1_2_, this_.MLE_MODIFICATIONDATE as MLE28_1_2_, this_.MLE_BRQ_BATCHID as MLE29_1_2_, this_.MLE_CNY_ADDRESSCOUNTRY as MLE30_1_2_, this_.MLE_ACQ_ACQUIRERBid as MLE31_1_2_, this_.MLE_Mcc_CODE as MLE32_1_2_, country2_.CNY_ALPHA2CODE as CNY1_27_0_, country2_.CNY_NAME as CNY2_27_0_, country2_.CNY_ISOCODE as CNY3_27_0_, acquirer1_.ACQ_Bid as ACQ1_4_1_, acquirer1_.ACQ_NAME as ACQ2_4_1_, acquirer1_.ACQ_SUSPENDENQUIRYFEE as ACQ3_4_1_, acquirer1_.ACQ_DEFAULTCOUNTRY as ACQ4_4_1_, acquirer1_.ACQ_ORIGINALCOUNTRY as ACQ5_4_1_ FROM ENQUIRY this_ inner join COUNTRY country2_ on this_.MLE_CNY_ADDRESSCOUNTRY=country2_.CNY_ALPHA2CODE inner join ACQUIRER acquirer1_ on this_.MLE_ACQ_ACQUIRERBid=acquirer1_.ACQ_Bid WHERE this_.MLE_CREATIONDATE between :p0 and :p1 and acquirer1_.ACQ_Bid = :p2 and country2_.CNY_ALPHA2CODE = :p3 and this_.MLE_LEGALNAME = :p4 ORDER BY this_.MLE_ID desc nulls last ******************************************* Legend The following abbreviations are used by optimizer trace. CBQT - cost-based query transformation JPPD - join predicate push-down FPD - filter push-down PM - predicate move-around CVM - complex view merging SPJ - select-project-join SJC - set join conversion SU - subquery unnesting OBYE - order by elimination ST - star transformation qb - query block LB - leaf blocks DK - distinct keys LB/K - average number of leaf blocks per key DB/K - average number of data blocks per key CLUF - clustering factor NDV - number of distinct values Resp - response cost Card - cardinality Resc - resource cost NL - nested loops (join) SM - sort merge (join) HA - hash (join) CPUCSPEED - CPU Speed IOTFRSPEED - I/O transfer speed IOSEEKTIM - I/O seek time SREADTIM - average single block read time MREADTIM - average multiblock read time MBRC - average multiblock read count MAXTHR - maximum I/O system throughput SLAVETHR - average slave I/O throughput dmeth - distribution method 1: no partitioning required 2: value partitioned 4: right is random (round-robin) 512: left is random (round-robin) 8: broadcast right and partition left 16: broadcast left and partition right 32: partition left using partitioning of right 64: partition right using partitioning of left 128: use hash partitioning dimension 256: use range partitioning dimension 2048: use list partitioning dimension 1024: run the join in serial 0: invalid distribution method sel - selectivity ptn - partition ******************************************* Peeked values of the binds in SQL statement ******************************************* kkscoacd Bind#0 oacdty=180 mxl=11(28) mxlc=00 mal=00 scl=09 pre=00 oacflg=01 fl2=1000000 frm=00 csi=00 siz=112 off=0 kxsbbbfp=b7f8c8d8 bln=11 avl=07 flg=05 value= Dump of memory from 0xB7F8C8D8 to 0xB7F8C8DF B7F8C8D0 1A0A6D78 0E010101 [xm......] Bind#1 oacdty=180 mxl=11(28) mxlc=00 mal=00 scl=09 pre=00 oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=12 kxsbbbfp=b7f8c8e4 bln=11 avl=07 flg=01 value= Dump of memory from 0xB7F8C8E4 to 0xB7F8C8EB B7F8C8E0 1B0A6D78 003C3C18 [xm...<<.] Bind#2 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24 kxsbbbfp=b7f8c8f0 bln=22 avl=05 flg=01 value=10020253 Bind#3 oacdty=01 mxl=32(08) mxlc=02 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=48 kxsbbbfp=b7f8c908 bln=32 avl=02 flg=01 value="GB" Bind#4 oacdty=01 mxl=32(16) mxlc=04 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=80 kxsbbbfp=b7f8c928 bln=32 avl=04 flg=01 value="Kolo" *************************************** PARAMETERS USED BY THE OPTIMIZER ******************************** ************************************* PARAMETERS WITH ALTERED VALUES ****************************** ********************************* Bug Fix Control Environment *************************** fix 4611850 = enabled fix 4663804 = enabled fix 4663698 = enabled fix 4545833 = enabled fix 3499674 = disabled fix 4584065 = enabled fix 4602374 = enabled fix 4569940 = enabled fix 4631959 = enabled fix 4519340 = enabled fix 4550003 = enabled fix 4488689 = enabled fix 3118776 = enabled fix 4519016 = enabled fix 4487253 = enabled fix 4556762 = 15 fix 4728348 = enabled fix 4723244 = enabled fix 4554846 = enabled fix 4175830 = enabled fix 4722900 = enabled fix 5094217 = enabled fix 4904890 = enabled fix 4483286 = disabled fix 4969880 = disabled fix 4711525 = enabled fix 4717546 = enabled fix 4904838 = enabled fix 5005866 = enabled fix 4600710 = enabled fix 5129233 = enabled fix 5195882 = enabled fix 5084239 = enabled fix 4595987 = enabled fix 4134994 = enabled fix 5104624 = enabled fix 4908162 = enabled fix 5015557 = enabled fix 5263572 = enabled fix 4483240 = enabled fix 5099909 = enabled fix 5650477 = enabled fix 4273361 = enabled fix 5694984 = enabled fix 5449488 = enabled fix 5236908 = enabled fix 5618040 = enabled fix 5634346 = enabled fix 5220356 = enabled fix 5611962 = enabled fix 5741121 = enabled fix 5547058 = enabled fix 5762598 = enabled fix 5509293 = enabled fix 5396162 = enabled fix 5891471 = enabled fix 4872602 = disabled fix 5882954 = enabled fix 5884780 = enabled fix 5680702 = enabled fix 5240607 = enabled fix 4924149 = enabled fix 4752814 = enabled fix 4583239 = enabled fix 5949981 = enabled fix 5096560 = enabled fix 5838613 = enabled fix 5482831 = enabled fix 5624216 = enabled fix 5976822 = enabled fix 5741044 = enabled fix 5385629 = enabled fix 5705630 = disabled fix 5483301 = enabled fix 6122894 = enabled fix 5842686 = disabled fix 6006300 = disabled fix 6070954 = enabled fix 2492766 = enabled fix 6042205 = enabled fix 5302124 = enabled fix 6051211 = enabled fix 5620485 = enabled fix 4545802 = enabled fix 4716096 = enabled fix 5259048 = enabled fix 6163564 = enabled fix 6082745 = enabled fix 5944076 = enabled fix 4878299 = enabled fix 5288623 = enabled fix 5570494 = enabled fix 5387148 = enabled fix 4605810 = enabled fix 4704779 = enabled fix 5547895 = enabled fix 6188881 = enabled fix 5872956 = enabled fix 4708389 = enabled fix 3151991 = enabled fix 3426050 = enabled fix 599680 = enabled fix 5505157 = enabled fix 5996801 = enabled fix 5765456 = 0 fix 6494943 = enabled fix 6251917 = enabled fix 6087237 = enabled fix 6239971 = enabled fix 6062266 = enabled fix 5520732 = 0 fix 6151963 = enabled fix 4567767 = enabled fix 6007259 = enabled fix 6694548 = enabled ************************************* PARAMETERS WITH DEFAULT VALUES ****************************** optimizer_mode_hinted = false optimizer_features_hinted = 0.0.0 parallel_execution_enabled = true parallel_query_forced_dop = 0 parallel_dml_forced_dop = 0 parallel_ddl_forced_degree = 0 parallel_ddl_forced_instances = 0 _query_rewrite_fudge = 90 optimizer_features_enable = 10.2.0.4 _optimizer_search_limit = 5 cpu_count = 8 active_instance_count = 1 parallel_threads_per_cpu = 2 hash_area_size = 131072 bitmap_merge_area_size = 1048576 sort_area_size = 65536 sort_area_retained_size = 0 _sort_elimination_cost_ratio = 0 _optimizer_block_size = 8192 _sort_multiblock_read_count = 2 _hash_multiblock_io_count = 0 _db_file_optimizer_read_count = 16 _optimizer_max_permutations = 2000 pga_aggregate_target = 403456 KB _pga_max_size = 204800 KB _query_rewrite_maxdisjunct = 257 _smm_auto_min_io_size = 56 KB _smm_auto_max_io_size = 248 KB _smm_min_size = 403 KB _smm_max_size = 80691 KB _smm_px_max_size = 201728 KB _cpu_to_io = 0 _optimizer_undo_cost_change = 10.2.0.4 parallel_query_mode = enabled parallel_dml_mode = disabled parallel_ddl_mode = enabled optimizer_mode = all_rows sqlstat_enabled = false _optimizer_percent_parallel = 101 _always_anti_join = choose _always_semi_join = choose _optimizer_mode_force = true _partition_view_enabled = true _always_star_transformation = false _query_rewrite_or_error = false _hash_join_enabled = true cursor_sharing = exact _b_tree_bitmap_plans = true star_transformation_enabled = false _optimizer_cost_model = choose _new_sort_cost_estimate = true _complex_view_merging = true _unnest_subquery = true _eliminate_common_subexpr = true _pred_move_around = true _convert_set_to_join = false _push_join_predicate = true _push_join_union_view = true _fast_full_scan_enabled = true _optim_enhance_nnull_detection = true _parallel_broadcast_enabled = true _px_broadcast_fudge_factor = 100 _ordered_nested_loop = true _no_or_expansion = false optimizer_index_cost_adj = 100 optimizer_index_caching = 0 _system_index_caching = 0 _disable_datalayer_sampling = false query_rewrite_enabled = true query_rewrite_integrity = enforced _query_cost_rewrite = true _query_rewrite_2 = true _query_rewrite_1 = true _query_rewrite_expression = true _query_rewrite_jgmigrate = true _query_rewrite_fpc = true _query_rewrite_drj = true _full_pwise_join_enabled = true _partial_pwise_join_enabled = true _left_nested_loops_random = true _improved_row_length_enabled = true _index_join_enabled = true _enable_type_dep_selectivity = true _improved_outerjoin_card = true _optimizer_adjust_for_nulls = true _optimizer_degree = 0 _use_column_stats_for_function = true _subquery_pruning_enabled = true _subquery_pruning_mv_enabled = false _or_expand_nvl_predicate = true _like_with_bind_as_equality = false _table_scan_cost_plus_one = true _cost_equality_semi_join = true _default_non_equality_sel_check = true _new_initial_join_orders = true _oneside_colstat_for_equijoins = true _optim_peek_user_binds = true _minimal_stats_aggregation = true _force_temptables_for_gsets = false workarea_size_policy = auto _smm_auto_cost_enabled = true _gs_anti_semi_join_allowed = true _optim_new_default_join_sel = true optimizer_dynamic_sampling = 2 _pre_rewrite_push_pred = true _optimizer_new_join_card_computation = true _union_rewrite_for_gs = yes_gset_mvs _generalized_pruning_enabled = true _optim_adjust_for_part_skews = true _force_datefold_trunc = false statistics_level = typical _optimizer_system_stats_usage = true skip_unusable_indexes = true _remove_aggr_subquery = true _optimizer_push_down_distinct = 0 _dml_monitoring_enabled = true _optimizer_undo_changes = false _predicate_elimination_enabled = true _nested_loop_fudge = 100 _project_view_columns = true _local_communication_costing_enabled = true _local_communication_ratio = 50 _query_rewrite_vop_cleanup = true _slave_mapping_enabled = true _optimizer_cost_based_transformation = linear _optimizer_mjc_enabled = true _right_outer_hash_enable = true _spr_push_pred_refspr = true _optimizer_cache_stats = false _optimizer_cbqt_factor = 50 _optimizer_squ_bottomup = true _fic_area_size = 131072 _optimizer_skip_scan_enabled = true _optimizer_cost_filter_pred = false _optimizer_sortmerge_join_enabled = true _optimizer_join_sel_sanity_check = true _mmv_query_rewrite_enabled = true _bt_mmv_query_rewrite_enabled = true _add_stale_mv_to_dependency_list = true _distinct_view_unnesting = false _optimizer_dim_subq_join_sel = true _optimizer_disable_strans_sanity_checks = 0 _optimizer_compute_index_stats = true _push_join_union_view2 = true _optimizer_ignore_hints = false _optimizer_random_plan = 0 _query_rewrite_setopgrw_enable = true _optimizer_correct_sq_selectivity = true _disable_function_based_index = false _optimizer_join_order_control = 3 _optimizer_cartesian_enabled = true _optimizer_starplan_enabled = true _extended_pruning_enabled = true _optimizer_push_pred_cost_based = true _sql_model_unfold_forloops = run_time _enable_dml_lock_escalation = false _bloom_filter_enabled = true _update_bji_ipdml_enabled = 0 _optimizer_extended_cursor_sharing = udo _dm_max_shared_pool_pct = 1 _optimizer_cost_hjsmj_multimatch = true _optimizer_transitivity_retain = true _px_pwg_enabled = true optimizer_secure_view_merging = true _optimizer_join_elimination_enabled = true flashback_table_rpi = non_fbt _optimizer_cbqt_no_size_restriction = true _optimizer_enhanced_filter_push = true _optimizer_filter_pred_pullup = true _rowsrc_trace_level = 0 _simple_view_merging = true _optimizer_rownum_pred_based_fkr = true _optimizer_better_inlist_costing = all _optimizer_self_induced_cache_cost = false _optimizer_min_cache_blocks = 10 _optimizer_or_expansion = depth _optimizer_order_by_elimination_enabled = true _optimizer_outer_to_anti_enabled = true _selfjoin_mv_duplicates = true _dimension_skip_null = true _force_rewrite_enable = false _optimizer_star_tran_in_with_clause = true _optimizer_complex_pred_selectivity = true _optimizer_connect_by_cost_based = true _gby_hash_aggregation_enabled = true _globalindex_pnum_filter_enabled = true _fix_control_key = 0 _optimizer_skip_scan_guess = false _enable_row_shipping = false _row_shipping_threshold = 80 _row_shipping_explain = false _optimizer_rownum_bind_default = 10 _first_k_rows_dynamic_proration = true _px_ual_serial_input = true _optimizer_native_full_outer_join = off _optimizer_star_trans_min_cost = 0 _optimizer_star_trans_min_ratio = 0 _optimizer_fkr_index_cost_bias = 10 _optimizer_connect_by_combine_sw = true _optimizer_use_subheap = true _optimizer_or_expansion_subheap = true _optimizer_sortmerge_join_inequality = true _optimizer_use_histograms = true _optimizer_enable_density_improvements = false ********************************* Bug Fix Control Environment *************************** fix 4611850 = enabled fix 4663804 = enabled fix 4663698 = enabled fix 4545833 = enabled fix 3499674 = disabled fix 4584065 = enabled fix 4602374 = enabled fix 4569940 = enabled fix 4631959 = enabled fix 4519340 = enabled fix 4550003 = enabled fix 4488689 = enabled fix 3118776 = enabled fix 4519016 = enabled fix 4487253 = enabled fix 4556762 = 15 fix 4728348 = enabled fix 4723244 = enabled fix 4554846 = enabled fix 4175830 = enabled fix 4722900 = enabled fix 5094217 = enabled fix 4904890 = enabled fix 4483286 = disabled fix 4969880 = disabled fix 4711525 = enabled fix 4717546 = enabled fix 4904838 = enabled fix 5005866 = enabled fix 4600710 = enabled fix 5129233 = enabled fix 5195882 = enabled fix 5084239 = enabled fix 4595987 = enabled fix 4134994 = enabled fix 5104624 = enabled fix 4908162 = enabled fix 5015557 = enabled fix 5263572 = enabled fix 4483240 = enabled fix 5099909 = enabled fix 5650477 = enabled fix 4273361 = enabled fix 5694984 = enabled fix 5449488 = enabled fix 5236908 = enabled fix 5618040 = enabled fix 5634346 = enabled fix 5220356 = enabled fix 5611962 = enabled fix 5741121 = enabled fix 5547058 = enabled fix 5762598 = enabled fix 5509293 = enabled fix 5396162 = enabled fix 5891471 = enabled fix 4872602 = disabled fix 5882954 = enabled fix 5884780 = enabled fix 5680702 = enabled fix 5240607 = enabled fix 4924149 = enabled fix 4752814 = enabled fix 4583239 = enabled fix 5949981 = enabled fix 5096560 = enabled fix 5838613 = enabled fix 5482831 = enabled fix 5624216 = enabled fix 5976822 = enabled fix 5741044 = enabled fix 5385629 = enabled fix 5705630 = disabled fix 5483301 = enabled fix 6122894 = enabled fix 5842686 = disabled fix 6006300 = disabled fix 6070954 = enabled fix 2492766 = enabled fix 6042205 = enabled fix 5302124 = enabled fix 6051211 = enabled fix 5620485 = enabled fix 4545802 = enabled fix 4716096 = enabled fix 5259048 = enabled fix 6163564 = enabled fix 6082745 = enabled fix 5944076 = enabled fix 4878299 = enabled fix 5288623 = enabled fix 5570494 = enabled fix 5387148 = enabled fix 4605810 = enabled fix 4704779 = enabled fix 5547895 = enabled fix 6188881 = enabled fix 5872956 = enabled fix 4708389 = enabled fix 3151991 = enabled fix 3426050 = enabled fix 599680 = enabled fix 5505157 = enabled fix 5996801 = enabled fix 5765456 = 0 fix 6494943 = enabled fix 6251917 = enabled fix 6087237 = enabled fix 6239971 = enabled fix 6062266 = enabled fix 5520732 = 0 fix 6151963 = enabled fix 4567767 = enabled fix 6007259 = enabled fix 6694548 = enabled *************************************** PARAMETERS IN OPT_PARAM HINT **************************** *************************************** Column Usage Monitoring is ON: tracking level = 1 *************************************** **************** QUERY BLOCK TEXT **************** SELECT this_.MLE_ID as MLE1_1_2_, this_.MLE_PRIMARYPHONENUMBER as MLE2_1_2_, this_.MLE_PRIMARYPRINCIPALNAME as MLE3_1_2_, this_.MLE_2NDPHONENUMBER as MLE4_1_2_, this_.MLE_2NDPRINCIPALNAME as MLE5_1_2_, this_.MLE_3RDPHONENUMBER as MLE6_1_2_, this_.MLE_3RDPRINCIPALNAME as MLE7_1_2_, this_.MLE_BANKACCOUNTNUMBER as MLE8_1_2_, this_.MLE_ADDRESSCITY as MLE9_1_2_, this_.MLE_ADDRESSCOUNTYREGION as MLE10_1_2_, this_.MLE_ADDRESSLOCALITYREGION as MLE11_1_2_, this_.MLE_ADDRESSPOSTCODE as MLE12_1_2_, this_.MLE_ADDRESSSTREET as MLE13_1_2_, this_.MLE_COMPANYIDENTIFICATION as MLE14_1_2_, this_.MLE_FINANCIALINSTITUTIONID as MLE15_1_2_, this_.MLE_Iban as MLE16_1_2_, this_.MLE_LEGALNAME as MLE17_1_2_, this_.MLE_TRADINGNAME as MLE18_1_2_, this_.MLE_TaxIDENTIFICATION as MLE19_1_2_, this_.MLE_WEBADDRESS as MLE20_1_2_, this_.MLE_ECOMMERCE as MLE21_1_2_, this_.MLE_INTERNATIONAL as MLE22_1_2_, this_.MLE_CREATOR_LOGIN as MLE23_1_2_, this_.MLE_CREATOR_NAME as MLE24_1_2_, this_.MLE_STATUS as MLE25_1_2_, this_.MLE_CREATIONDATE as MLE26_1_2_, this_.MLE_DELETIONDATE as MLE27_1_2_, this_.MLE_MODIFICATIONDATE as MLE28_1_2_, this_.MLE_BRQ_BATCHID as MLE29_1_2_, this_.MLE_CNY_ADDRESSCOUNTRY as MLE30_1_2_, this_.MLE_ACQ_ACQUIRERBid as MLE31_1_2_, this_.MLE_Mcc_CODE as MLE32_1_2_, country2_.CNY_ALPHA2CODE as CNY1_27_0_, country2_.CNY_NAME as CNY2_27_0_, country2_.CNY_ISOCODE as CNY3_27_0_, acquirer1_.ACQ_Bid as ACQ1_4_1_, acquirer1_.ACQ_NAME as ACQ2_4_1_, acquirer1_.ACQ_SUSPENDENQUIRYFEE as ACQ3_4_1_, acquirer1_.ACQ_DEFAULTCOUNTRY as ACQ4_4_1_, acquirer1_.ACQ_ORIGINALCOUNTRY as ACQ5_4_1_ FROM ENQUIRY this_ inner join COUNTRY country2_ on this_.MLE_CNY_ADDRESSCOUNTRY=country2_.CNY_ALPHA2CODE inner join ACQUIRER acquirer1_ on this_.MLE_ACQ_ACQUIRERBid=acquirer1_.ACQ_Bid WHERE this_.MLE_CREATIONDATE between :p0 and :p1 and acquirer1_.ACQ_Bid = :p2 and country2_.CNY_ALPHA2CODE = :p3 and this_.MLE_LEGALNAME = :p4 ********************* QUERY BLOCK SIGNATURE ********************* qb name was generated signature (optimizer): qb_name=SEL$9E43CB6E nbfros=3 flg=0 fro(0): flg=0 objn=18341 hint_alias="COUNTRY2_"@"SEL$1" fro(1): flg=0 objn=18384 hint_alias="THIS_"@"SEL$1" fro(2): flg=0 objn=18344 hint_alias="ACQUIRER1_"@"SEL$2" ***************************** SYSTEM STATISTICS INFORMATION ***************************** Using NOWORKLOAD Stats CPUSPEED: 665 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) *************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: ACQUIRER Alias: ACQUIRER1_ #Rows: 141 #Blks: 5 AvgRowLen: 37.00 Index Stats:: Index: INDX_DEFAULTCOUNTRY Col#: 4 LVLS: 0 #LB: 1 #DK: 33 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00 Index: INDX_ORIGINALCOUNTRY Col#: 3 LVLS: 0 #LB: 1 #DK: 33 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00 Index: PK_ACQUIRER Col#: 1 LVLS: 0 #LB: 1 #DK: 141 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00 *********************** Table Stats:: Table: COUNTRY Alias: COUNTRY2_ #Rows: 43 #Blks: 5 AvgRowLen: 19.00 Index Stats:: Index: PK_COUNTRY Col#: 1 LVLS: 0 #LB: 1 #DK: 43 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00 Index: UQ_COUNTRY_CNY_ISOCODE Col#: 4 LVLS: 0 #LB: 1 #DK: 43 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00 *********************** Table Stats:: Table: ENQUIRY Alias: THIS_ #Rows: 284035 #Blks: 10097 AvgRowLen: 224.00 Index Stats:: Index: INDX_ENQUIRY_ACQBID Col#: 7 LVLS: 2 #LB: 987 #DK: 76 LB/K: 12.00 DB/K: 634.00 CLUF: 48196.00 Index: INDX_ENQUIRY_BATCHID Col#: 25 LVLS: 1 #LB: 2 #DK: 33 LB/K: 1.00 DB/K: 1.00 CLUF: 62.00 Index: INDX_ENQUIRY_COUNTRY Col#: 21 LVLS: 2 #LB: 788 #DK: 34 LB/K: 23.00 DB/K: 1259.00 CLUF: 42832.00 Index: INDX_ENQUIRY_CRDATE Col#: 3 LVLS: 2 #LB: 1014 #DK: 83724 LB/K: 1.00 DB/K: 1.00 CLUF: 43120.00 Index: INDX_ENQUIRY_LEGALNAME Col#: 9 LVLS: 2 #LB: 882 #DK: 148978 LB/K: 1.00 DB/K: 1.00 CLUF: 179229.00 Index: INDX_ENQUIRY_LR1 Col#: 23 LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00 Index: INDX_ENQUIRY_LR2 Col#: 24 LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00 Index: INDX_ENQUIRY_MCC Col#: 22 LVLS: 2 #LB: 933 #DK: 360 LB/K: 2.00 DB/K: 503.00 CLUF: 181384.00 Index: INDX_ENQUIRY_MLETYPE Col#: 2 LVLS: 2 #LB: 673 #DK: 1 LB/K: 673.00 DB/K: 9186.00 CLUF: 9186.00 Index: INDX_ENQUIRY_NOM Col#: 41 LVLS: 2 #LB: 673 #DK: 1 LB/K: 673.00 DB/K: 9186.00 CLUF: 9186.00 Index: PK_ENQUIRY Col#: 1 LVLS: 2 #LB: 765 #DK: 284035 LB/K: 1.00 DB/K: 1.00 CLUF: 24513.00 *************************************** 1-ROW TABLES: ACQUIRER[ACQUIRER1_]#0 *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- Column (#1): ACQ_BID(NUMBER) AvgLen: 6.00 NDV: 141 Nulls: 0 Density: 0.0070922 Min: 1 Max: 10064945 Table: ACQUIRER Alias: ACQUIRER1_ Card: Original: 141 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 3.01 Resp: 3.01 Degree: 0 Cost_io: 3.00 Cost_cpu: 63887 Resp_io: 3.00 Resp_cpu: 63887 Access Path: index (UniqueScan) Index: PK_ACQUIRER resc_io: 1.00 resc_cpu: 8401 ix_sel: 0.0070922 ix_sel_with_filters: 0.0070922 Cost: 1.00 Resp: 1.00 Degree: 1 Access Path: index (AllEqUnique) Index: PK_ACQUIRER resc_io: 1.00 resc_cpu: 8401 ix_sel: 0.0070922 ix_sel_with_filters: 0.0070922 Cost: 1.00 Resp: 1.00 Degree: 1 One row Card: 1.00 Best:: AccessPath: IndexUnique Index: PK_ACQUIRER Cost: 1.00 Degree: 1 Resp: 1.00 Card: 1.00 Bytes: 0 *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- Column (#9): MLE_LEGALNAME(VARCHAR2) AvgLen: 15.00 NDV: 148978 Nulls: 75917 Density: 6.7124e-06 Column (#7): MLE_ACQ_ACQUIRERBID(NUMBER) AvgLen: 6.00 NDV: 55 Nulls: 0 Density: 9.4769e-05 Min: 1 Max: 10064865 Histogram: Freq #Bkts: 55 UncompBkts: 5276 EndPtVals: 55 Column (#3): MLE_CREATIONDATE(TIMESTAMP) AvgLen: 11.00 NDV: 3162 Nulls: 0 Density: 2.8987e-04 Min: 2454753 Max: 2455125 Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 244 Using prorated density: 2.8481e-04 of col #3 as selectivity of out-of-range value pred Table: ENQUIRY Alias: THIS_ Card: Original: 284035 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 2240.35 Resp: 2240.35 Degree: 0 Cost_io: 2210.00 Cost_cpu: 242340735 Resp_io: 2210.00 Resp_cpu: 242340735 Access Path: index (AllEqRange) Index: INDX_ENQUIRY_ACQBID resc_io: 11245.00 resc_cpu: 154106371 ix_sel: 0.22858 ix_sel_with_filters: 0.22858 Cost: 11264.30 Resp: 11264.30 Degree: 1 Using prorated density: 2.8481e-04 of col #3 as selectivity of out-of-range value pred Access Path: index (RangeScan) Index: INDX_ENQUIRY_CRDATE resc_io: 16.00 resc_cpu: 216448 ix_sel: 2.8481e-04 ix_sel_with_filters: 2.8481e-04 Cost: 16.03 Resp: 16.03 Degree: 1 ****** trying bitmap/domain indexes ****** Access Path: index (AllEqRange) Index: INDX_ENQUIRY_ACQBID resc_io: 228.00 resc_cpu: 14608888 ix_sel: 0.22858 ix_sel_with_filters: 0.22858 Cost: 229.83 Resp: 229.83 Degree: 0 Using prorated density: 2.8481e-04 of col #3 as selectivity of out-of-range value pred Using prorated density: 2.8481e-04 of col #3 as selectivity of out-of-range value pred Access Path: index (IndexOnly) Index: INDX_ENQUIRY_CRDATE resc_io: 3.00 resc_cpu: 37564 ix_sel: 2.8481e-04 ix_sel_with_filters: 2.8481e-04 Cost: 3.00 Resp: 3.00 Degree: 0 SORT resource Sort statistics Sort width: 471 Area size: 412672 Max Area size: 82627584 Degree: 1 Blocks to Sort: 1 Row size: 21 Total Rows: 82 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 8007456 Total Temp space used: 0 Access path: Bitmap index - rejected Cost: 240.20 Cost_io: 236.26 Cost_cpu: 31486853 Sel: 6.6260e-05 Not believed to be index-only ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: INDX_ENQUIRY_CRDATE Cost: 16.03 Degree: 1 Resp: 16.03 Card: 0.00 Bytes: 0 *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- Column (#1): CNY_ALPHA2CODE(CHARACTER) AvgLen: 3.00 NDV: 43 Nulls: 0 Density: 0.023256 Table: COUNTRY Alias: COUNTRY2_ Card: Original: 43 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 3.01 Resp: 3.01 Degree: 0 Cost_io: 3.00 Cost_cpu: 65767 Resp_io: 3.00 Resp_cpu: 65767 Best:: AccessPath: TableScan Cost: 3.01 Degree: 1 Resp: 3.01 Card: 1.00 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: ACQUIRER[ACQUIRER1_]#0 ENQUIRY[THIS_]#1 COUNTRY[COUNTRY2_]#2 *************** Now joining: ENQUIRY[THIS_]#1 *************** NL Join Outer table: Card: 1.00 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 37 Inner table: ENQUIRY Alias: THIS_ Access Path: TableScan NL Join: Cost: 2259.14 Resp: 2259.14 Degree: 1 Cost_io: 2211.00 Cost_cpu: 384352538 Resp_io: 2211.00 Resp_cpu: 384352538 Access Path: index (AllEqJoin) Index: INDX_ENQUIRY_ACQBID resc_io: 11244.00 resc_cpu: 186552632 ix_sel: 0.22858 ix_sel_with_filters: 0.22858 NL Join: Cost: 11268.37 Resp: 11268.37 Degree: 1 Cost_io: 11245.00 Cost_cpu: 186561034 Resp_io: 11245.00 Resp_cpu: 186561034 Using prorated density: 2.8481e-04 of col #3 as selectivity of out-of-range value pred Access Path: index (RangeScan) Index: INDX_ENQUIRY_CRDATE resc_io: 15.00 resc_cpu: 240454 ix_sel: 2.8481e-04 ix_sel_with_filters: 2.8481e-04 NL Join: Cost: 16.03 Resp: 16.03 Degree: 1 Cost_io: 16.00 Cost_cpu: 248855 Resp_io: 16.00 Resp_cpu: 248855 ****** trying bitmap/domain indexes ****** Access Path: index (AllEqJoin) Index: INDX_ENQUIRY_ACQBID resc_io: 227.00 resc_cpu: 14602617 ix_sel: 0.22858 ix_sel_with_filters: 0.22858 NL Join: Cost: 229.83 Resp: 229.83 Degree: 1 Cost_io: 228.00 Cost_cpu: 14611018 Resp_io: 228.00 Resp_cpu: 14611018 Using prorated density: 2.8481e-04 of col #3 as selectivity of out-of-range value pred Using prorated density: 2.8481e-04 of col #3 as selectivity of out-of-range value pred Access Path: index (IndexOnly) Index: INDX_ENQUIRY_CRDATE resc_io: 2.00 resc_cpu: 31293 ix_sel: 2.8481e-04 ix_sel_with_filters: 2.8481e-04 NL Join: Cost: 3.00 Resp: 3.00 Degree: 1 Cost_io: 3.00 Cost_cpu: 39694 Resp_io: 3.00 Resp_cpu: 39694 SORT resource Sort statistics Sort width: 471 Area size: 412672 Max Area size: 82627584 Degree: 1 Blocks to Sort: 1 Row size: 21 Total Rows: 82 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 8007456 Total Temp space used: 0 Access path: Bitmap index - rejected Cost: 241.20 Cost_io: 237.26 Cost_cpu: 31499515 Sel: 6.6260e-05 Not believed to be index-only ****** finished trying bitmap/domain indexes ****** Best NL cost: 16.03 resc: 16.03 resc_io: 16.00 resc_cpu: 248855 resp: 16.03 resp_io: 16.00 resp_cpu: 248855 Join Card: 0.00 = outer (1.00) * inner (0.00) * sel (1) Join Card - Rounded: 1 Computed: 0.00 Best:: JoinMethod: NestedLoop Cost: 16.03 Degree: 1 Resp: 16.03 Card: 0.00 Bytes: 261 *************** Now joining: COUNTRY[COUNTRY2_]#2 *************** NL Join Outer table: Card: 0.00 Cost: 16.03 Resp: 16.03 Degree: 1 Bytes: 261 Inner table: COUNTRY Alias: COUNTRY2_ Access Path: TableScan NL Join: Cost: 19.04 Resp: 19.04 Degree: 1 Cost_io: 19.00 Cost_cpu: 314623 Resp_io: 19.00 Resp_cpu: 314623 Best NL cost: 19.04 resc: 19.04 resc_io: 19.00 resc_cpu: 314623 resp: 19.04 resp_io: 19.00 resp_cpu: 314623 Join Card: 0.00 = outer (0.00) * inner (1.00) * sel (0.01) Join Card - Rounded: 1 Computed: 0.00 SM Join Outer table: resc: 16.03 card 0.00 bytes: 261 deg: 1 resp: 16.03 Inner table: COUNTRY Alias: COUNTRY2_ resc: 3.01 card: 1.00 bytes: 18 deg: 1 resp: 3.01 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 471 Area size: 412672 Max Area size: 82627584 Degree: 1 Blocks to Sort: 1 Row size: 298 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 7983968 Total Temp space used: 0 SORT resource Sort statistics Sort width: 471 Area size: 412672 Max Area size: 82627584 Degree: 1 Blocks to Sort: 1 Row size: 30 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 7983968 Total Temp space used: 0 SM join: Resc: 21.04 Resp: 21.04 [multiMatchCost=0.00] SM cost: 21.04 resc: 21.04 resc_io: 19.00 resc_cpu: 16282558 resp: 21.04 resp_io: 19.00 resp_cpu: 16282558 HA Join Outer table: resc: 16.03 card 0.00 bytes: 261 deg: 1 resp: 16.03 Inner table: COUNTRY Alias: COUNTRY2_ resc: 3.01 card: 1.00 bytes: 18 deg: 1 resp: 3.01 using dmeth: 2 #groups: 1 Cost per ptn: 0.50 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 19.54 Resp: 19.54 [multiMatchCost=0.00] HA cost: 19.54 resc: 19.54 resc_io: 19.00 resc_cpu: 4306857 resp: 19.54 resp_io: 19.00 resp_cpu: 4306857 ORDER BY sort SORT resource Sort statistics Sort width: 471 Area size: 412672 Max Area size: 82627584 Degree: 1 Blocks to Sort: 1 Row size: 317 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 7983968 Total Temp space used: 0 Best:: JoinMethod: Hash Cost: 20.54 Degree: 1 Resp: 20.54 Card: 0.00 Bytes: 279 *********************** Best so far: Table#: 0 cost: 1.0011 card: 1.0000 bytes: 37 Table#: 1 cost: 16.0312 card: 0.0001 bytes: 261 Table#: 2 cost: 20.5394 card: 0.0000 bytes: 279 ****** Recost for ORDER BY (using join row order) ******* *********************** Join order[1]: ACQUIRER[ACQUIRER1_]#0 ENQUIRY[THIS_]#1 COUNTRY[COUNTRY2_]#2 Join order aborted: ORDER BY elimination not possible *********************** *********************** Join order[2]: ACQUIRER[ACQUIRER1_]#0 COUNTRY[COUNTRY2_]#2 ENQUIRY[THIS_]#1 *************** Now joining: COUNTRY[COUNTRY2_]#2 *************** NL Join Outer table: Card: 1.00 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 37 Inner table: COUNTRY Alias: COUNTRY2_ Access Path: TableScan NL Join: Cost: 4.01 Resp: 4.01 Degree: 1 Cost_io: 4.00 Cost_cpu: 74169 Resp_io: 4.00 Resp_cpu: 74169 Best NL cost: 4.01 resc: 4.01 resc_io: 4.00 resc_cpu: 74169 resp: 4.01 resp_io: 4.00 resp_cpu: 74169 Join Card: 1.00 = outer (1.00) * inner (1.00) * sel (1) Join Card - Rounded: 1 Computed: 1.00 Best:: JoinMethod: NestedLoop Cost: 4.01 Degree: 1 Resp: 4.01 Card: 1.00 Bytes: 55 *************** Now joining: ENQUIRY[THIS_]#1 *************** NL Join Outer table: Card: 1.00 Cost: 4.01 Resp: 4.01 Degree: 1 Bytes: 55 Inner table: ENQUIRY Alias: THIS_ Access Path: TableScan NL Join: Cost: 2262.15 Resp: 2262.15 Degree: 1 Cost_io: 2214.00 Cost_cpu: 384418305 Resp_io: 2214.00 Resp_cpu: 384418305 Access Path: index (AllEqJoin) Index: INDX_ENQUIRY_ACQBID resc_io: 11244.00 resc_cpu: 186552632 ix_sel: 0.22858 ix_sel_with_filters: 0.22858 NL Join: Cost: 11271.38 Resp: 11271.38 Degree: 1 Cost_io: 11248.00 Cost_cpu: 186626801 Resp_io: 11248.00 Resp_cpu: 186626801 Using prorated density: 2.8481e-04 of col #3 as selectivity of out-of-range value pred Access Path: index (RangeScan) Index: INDX_ENQUIRY_CRDATE resc_io: 15.00 resc_cpu: 240454 ix_sel: 2.8481e-04 ix_sel_with_filters: 2.8481e-04 NL Join: Cost: 19.04 Resp: 19.04 Degree: 1 Cost_io: 19.00 Cost_cpu: 314623 Resp_io: 19.00 Resp_cpu: 314623 ****** trying bitmap/domain indexes ****** Access Path: index (AllEqJoin) Index: INDX_ENQUIRY_ACQBID resc_io: 227.00 resc_cpu: 14602617 ix_sel: 0.22858 ix_sel_with_filters: 0.22858 NL Join: Cost: 232.84 Resp: 232.84 Degree: 1 Cost_io: 231.00 Cost_cpu: 14676786 Resp_io: 231.00 Resp_cpu: 14676786 Using prorated density: 2.8481e-04 of col #3 as selectivity of out-of-range value pred Using prorated density: 2.8481e-04 of col #3 as selectivity of out-of-range value pred Access Path: index (IndexOnly) Index: INDX_ENQUIRY_CRDATE resc_io: 2.00 resc_cpu: 31293 ix_sel: 2.8481e-04 ix_sel_with_filters: 2.8481e-04 NL Join: Cost: 6.01 Resp: 6.01 Degree: 1 Cost_io: 6.00 Cost_cpu: 105462 Resp_io: 6.00 Resp_cpu: 105462 SORT resource Sort statistics Sort width: 471 Area size: 412672 Max Area size: 82627584 Degree: 1 Blocks to Sort: 1 Row size: 21 Total Rows: 82 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 8007456 Total Temp space used: 0 Access path: Bitmap index - rejected Cost: 250.23 Cost_io: 246.26 Cost_cpu: 31696816 Sel: 6.6260e-05 Not believed to be index-only ****** finished trying bitmap/domain indexes ****** Best NL cost: 19.04 resc: 19.04 resc_io: 19.00 resc_cpu: 314623 resp: 19.04 resp_io: 19.00 resp_cpu: 314623 Join Card: 0.00 = outer (1.00) * inner (0.00) * sel (0.01) Join Card - Rounded: 1 Computed: 0.00 SM Join Outer table: resc: 4.01 card 1.00 bytes: 55 deg: 1 resp: 4.01 Inner table: ENQUIRY Alias: THIS_ resc: 16.03 card: 0.00 bytes: 224 deg: 1 resp: 16.03 using dmeth: 2 #groups: 1 SORT resource Sort statistics Sort width: 471 Area size: 412672 Max Area size: 82627584 Degree: 1 Blocks to Sort: 1 Row size: 71 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 7983968 Total Temp space used: 0 SORT resource Sort statistics Sort width: 471 Area size: 412672 Max Area size: 82627584 Degree: 1 Blocks to Sort: 1 Row size: 257 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 7983968 Total Temp space used: 0 SM join: Resc: 22.04 Resp: 22.04 [multiMatchCost=0.00] SM cost: 22.04 resc: 22.04 resc_io: 20.00 resc_cpu: 16258552 resp: 22.04 resp_io: 20.00 resp_cpu: 16258552 HA Join Outer table: resc: 4.01 card 1.00 bytes: 55 deg: 1 resp: 4.01 Inner table: ENQUIRY Alias: THIS_ resc: 16.03 card: 0.00 bytes: 224 deg: 1 resp: 16.03 using dmeth: 2 #groups: 1 Cost per ptn: 0.50 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 20.54 Resp: 20.54 [multiMatchCost=0.00] HA cost: 20.54 resc: 20.54 resc_io: 20.00 resc_cpu: 4282851 resp: 20.54 resp_io: 20.00 resp_cpu: 4282851 ORDER BY sort SORT resource Sort statistics Sort width: 471 Area size: 412672 Max Area size: 82627584 Degree: 1 Blocks to Sort: 1 Row size: 317 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 7983968 Total Temp space used: 0 Best:: JoinMethod: NestedLoop Cost: 20.04 Degree: 1 Resp: 20.04 Card: 0.00 Bytes: 279 *********************** Best so far: Table#: 0 cost: 1.0011 card: 1.0000 bytes: 37 Table#: 2 cost: 4.0093 card: 1.0000 bytes: 55 Table#: 1 cost: 20.0394 card: 0.0000 bytes: 279 ****** Recost for ORDER BY (using join row order) ******* *********************** Join order[2]: ACQUIRER[ACQUIRER1_]#0 COUNTRY[COUNTRY2_]#2 ENQUIRY[THIS_]#1 Join order aborted: ORDER BY elimination not possible *********************** (newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000 ********************************* Number of join permutations tried: 2 ********************************* (newjo-save) [0 1 2 ] SORT resource Sort statistics Sort width: 471 Area size: 412672 Max Area size: 82627584 Degree: 1 Blocks to Sort: 1 Row size: 317 Total Rows: 1 Initial runs: 1 Merge passes: 0 IO Cost / pass: 0 Total IO sort cost: 0 Total CPU sort cost: 7983968 Total Temp space used: 0 Final - All Rows Plan: Best join order: 2 Cost: 20.0394 Degree: 1 Card: 1.0000 Bytes: 279 Resc: 20.0394 Resc_io: 19.0000 Resc_cpu: 8298591 Resp: 20.0394 Resp_io: 19.0000 Resc_cpu: 8298591 kkoipt: Query block SEL$9E43CB6E (#0) ******* UNPARSED QUERY IS ******* SELECT "THIS_"."MLE_ID" "MLE1_1_2_","THIS_"."MLE_PRIMARYPHONENUMBER" "MLE2_1_2_","THIS_"."MLE_PRIMARYPRINCIPALNAME" "MLE3_1_2_","THIS_"."MLE_2NDPHONENUMBER" "MLE4_1_2_","THIS_"."MLE_2NDPRINCIPALNAME" "MLE5_1_2_","THIS_"."MLE_3RDPHONENUMBER" "MLE6_1_2_","THIS_"."MLE_3RDPRINCIPALNAME" "MLE7_1_2_","THIS_"."MLE_BANKACCOUNTNUMBER" "MLE8_1_2_","THIS_"."MLE_ADDRESSCITY" "MLE9_1_2_","THIS_"."MLE_ADDRESSCOUNTYREGION" "MLE10_1_2_","THIS_"."MLE_ADDRESSLOCALITYREGION" "MLE11_1_2_","THIS_"."MLE_ADDRESSPOSTCODE" "MLE12_1_2_","THIS_"."MLE_ADDRESSSTREET" "MLE13_1_2_","THIS_"."MLE_COMPANYIDENTIFICATION" "MLE14_1_2_","THIS_"."MLE_FINANCIALINSTITUTIONID" "MLE15_1_2_","THIS_"."MLE_IBAN" "MLE16_1_2_","THIS_"."MLE_LEGALNAME" "MLE17_1_2_","THIS_"."MLE_TRADINGNAME" "MLE18_1_2_","THIS_"."MLE_TAXIDENTIFICATION" "MLE19_1_2_","THIS_"."MLE_WEBADDRESS" "MLE20_1_2_","THIS_"."MLE_ECOMMERCE" "MLE21_1_2_","THIS_"."MLE_INTERNATIONAL" "MLE22_1_2_","THIS_"."MLE_CREATOR_LOGIN" "MLE23_1_2_","THIS_"."MLE_CREATOR_NAME" "MLE24_1_2_","THIS_"."MLE_STATUS" "MLE25_1_2_","THIS_"."MLE_CREATIONDATE" "MLE26_1_2_","THIS_"."MLE_DELETIONDATE" "MLE27_1_2_","THIS_"."MLE_MODIFICATIONDATE" "MLE28_1_2_","THIS_"."MLE_BRQ_BATCHID" "MLE29_1_2_","THIS_"."MLE_CNY_ADDRESSCOUNTRY" "MLE30_1_2_","THIS_"."MLE_ACQ_ACQUIRERBID" "MLE31_1_2_","THIS_"."MLE_MCC_CODE" "MLE32_1_2_","COUNTRY2_"."CNY_ALPHA2CODE" "CNY1_27_0_","COUNTRY2_"."CNY_NAME" "CNY2_27_0_","COUNTRY2_"."CNY_ISOCODE" "CNY3_27_0_","ACQUIRER1_"."ACQ_BID" "ACQ1_4_1_","ACQUIRER1_"."ACQ_NAME" "ACQ2_4_1_","ACQUIRER1_"."ACQ_SUSPENDENQUIRYFEE" "ACQ3_4_1_","ACQUIRER1_"."ACQ_DEFAULTCOUNTRY" "ACQ4_4_1_","ACQUIRER1_"."ACQ_ORIGINALCOUNTRY" "ACQ5_4_1_" FROM "VMAS_TT"."ENQUIRY" "THIS_","VMAS_TT"."COUNTRY" "COUNTRY2_","VMAS_TT"."ACQUIRER" "ACQUIRER1_" WHERE :B1<=:B2 AND "THIS_"."MLE_CREATIONDATE"<=:B3 AND "THIS_"."MLE_CREATIONDATE">=:B4 AND "THIS_"."MLE_ACQ_ACQUIRERBID"=:B5 AND NLSSORT("THIS_"."MLE_LEGALNAME",'nls_sort=''BINARY_CI''')=NLSSORT(:B6,'nls_sort=''BINARY_CI''') AND NLSSORT("THIS_"."MLE_CNY_ADDRESSCOUNTRY",'nls_sort=''BINARY_CI''')=NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''') AND NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''')=NLSSORT(:B7,'nls_sort=''BINARY_CI''') AND "ACQUIRER1_"."ACQ_BID"=:B8 ORDER BY "THIS_"."MLE_ID" DESC NULLS LAST kkoqbc-subheap (delete addr=0xb7fa3ca4, in-use=56032, alloc=59172) kkoqbc-end : call(in-use=128900, alloc=199356), compile(in-use=143712, alloc=145412) apadrv-end: call(in-use=128900, alloc=199356), compile(in-use=144312, alloc=145412) sql_id=58zx8sr96rmpf. Current SQL statement for this session: SELECT this_.MLE_ID as MLE1_1_2_, this_.MLE_PRIMARYPHONENUMBER as MLE2_1_2_, this_.MLE_PRIMARYPRINCIPALNAME as MLE3_1_2_, this_.MLE_2NDPHONENUMBER as MLE4_1_2_, this_.MLE_2NDPRINCIPALNAME as MLE5_1_2_, this_.MLE_3RDPHONENUMBER as MLE6_1_2_, this_.MLE_3RDPRINCIPALNAME as MLE7_1_2_, this_.MLE_BANKACCOUNTNUMBER as MLE8_1_2_, this_.MLE_ADDRESSCITY as MLE9_1_2_, this_.MLE_ADDRESSCOUNTYREGION as MLE10_1_2_, this_.MLE_ADDRESSLOCALITYREGION as MLE11_1_2_, this_.MLE_ADDRESSPOSTCODE as MLE12_1_2_, this_.MLE_ADDRESSSTREET as MLE13_1_2_, this_.MLE_COMPANYIDENTIFICATION as MLE14_1_2_, this_.MLE_FINANCIALINSTITUTIONID as MLE15_1_2_, this_.MLE_Iban as MLE16_1_2_, this_.MLE_LEGALNAME as MLE17_1_2_, this_.MLE_TRADINGNAME as MLE18_1_2_, this_.MLE_TaxIDENTIFICATION as MLE19_1_2_, this_.MLE_WEBADDRESS as MLE20_1_2_, this_.MLE_ECOMMERCE as MLE21_1_2_, this_.MLE_INTERNATIONAL as MLE22_1_2_, this_.MLE_CREATOR_LOGIN as MLE23_1_2_, this_.MLE_CREATOR_NAME as MLE24_1_2_, this_.MLE_STATUS as MLE25_1_2_, this_.MLE_CREATIONDATE as MLE26_1_2_, this_.MLE_DELETIONDATE as MLE27_1_2_, this_.MLE_MODIFICATIONDATE as MLE28_1_2_, this_.MLE_BRQ_BATCHID as MLE29_1_2_, this_.MLE_CNY_ADDRESSCOUNTRY as MLE30_1_2_, this_.MLE_ACQ_ACQUIRERBid as MLE31_1_2_, this_.MLE_Mcc_CODE as MLE32_1_2_, country2_.CNY_ALPHA2CODE as CNY1_27_0_, country2_.CNY_NAME as CNY2_27_0_, country2_.CNY_ISOCODE as CNY3_27_0_, acquirer1_.ACQ_Bid as ACQ1_4_1_, acquirer1_.ACQ_NAME as ACQ2_4_1_, acquirer1_.ACQ_SUSPENDENQUIRYFEE as ACQ3_4_1_, acquirer1_.ACQ_DEFAULTCOUNTRY as ACQ4_4_1_, acquirer1_.ACQ_ORIGINALCOUNTRY as ACQ5_4_1_ FROM ENQUIRY this_ inner join COUNTRY country2_ on this_.MLE_CNY_ADDRESSCOUNTRY=country2_.CNY_ALPHA2CODE inner join ACQUIRER acquirer1_ on this_.MLE_ACQ_ACQUIRERBid=acquirer1_.ACQ_Bid WHERE this_.MLE_CREATIONDATE between :p0 and :p1 and acquirer1_.ACQ_Bid = :p2 and country2_.CNY_ALPHA2CODE = :p3 and this_.MLE_LEGALNAME = :p4 ORDER BY this_.MLE_ID desc nulls last ============ Plan Table ============ ---------------------------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 20 | | | 1 | FILTER | | | | | | | 2 | SORT ORDER BY | | 1 | 279 | 20 | 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID | ENQUIRY | 1 | 224 | 15 | 00:00:01 | | 4 | NESTED LOOPS | | 1 | 279 | 19 | 00:00:01 | | 5 | NESTED LOOPS | | 1 | 55 | 4 | 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID | ACQUIRER | 1 | 37 | 1 | 00:00:01 | | 7 | INDEX UNIQUE SCAN | PK_ACQUIRER | 1 | | 0 | | | 8 | TABLE ACCESS FULL | COUNTRY | 1 | 18 | 3 | 00:00:01 | | 9 | INDEX RANGE SCAN | INDX_ENQUIRY_CRDATE| 81 | | 2 | 00:00:01 | ---------------------------------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 1 - filter(:P0<=:P1) 3 - filter(("THIS_"."MLE_ACQ_ACQUIRERBID"=:P2 AND NLSSORT("THIS_"."MLE_LEGALNAME",'nls_sort=''BINARY_CI''')=NLSSORT(:P4,'nls_sort=''BINARY_CI''') AND NLSSORT(INTERNAL_FUNCTION("THIS_"."MLE_CNY_ADDRESSCOUNTRY"),'nls_sort=''BINARY_CI''')=NLSSORT(INTERNAL_FUNCTION("COUNTRY2_"."CNY_ALPHA2CODE"),'nls_sort=''BINARY_CI'''))) 7 - access("ACQUIRER1_"."ACQ_BID"=:P2) 8 - filter(NLSSORT("COUNTRY2_"."CNY_ALPHA2CODE",'nls_sort=''BINARY_CI''')=NLSSORT(:P3,'nls_sort=''BINARY_CI''')) 9 - access("THIS_"."MLE_CREATIONDATE">=:P0 AND "THIS_"."MLE_CREATIONDATE"<=:P1) Content of other_xml column =========================== db_version : 10.2.0.4 parse_schema : VMAS_APP_TT plan_hash : 4088330551 Peeked Binds ============ Bind variable information position=1 datatype(code)=180 datatype(string)=TIMESTAMP max length=11 Bind variable information position=2 datatype(code)=180 datatype(string)=TIMESTAMP max length=11 Bind variable information position=3 datatype(code)=2 datatype(string)=NUMBER precision=0 scale=0 max length=22 value=10020253 Bind variable information position=4 datatype(code)=1 datatype(string)=VARCHAR2(32) char set id=873 char format=1 max length=32 value=GB Bind variable information position=5 datatype(code)=1 datatype(string)=VARCHAR2(32) char set id=873 char format=1 max length=32 value=Kolo Outline Data: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$9E43CB6E") MERGE(@"SEL$58A6D7F6") OUTLINE(@"SEL$3") OUTLINE(@"SEL$58A6D7F6") MERGE(@"SEL$1") OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") INDEX_RS_ASC(@"SEL$9E43CB6E" "ACQUIRER1_"@"SEL$2" ("ACQUIRER"."ACQ_BID")) FULL(@"SEL$9E43CB6E" "COUNTRY2_"@"SEL$1") INDEX(@"SEL$9E43CB6E" "THIS_"@"SEL$1" ("ENQUIRY"."MLE_CREATIONDATE")) LEADING(@"SEL$9E43CB6E" "ACQUIRER1_"@"SEL$2" "COUNTRY2_"@"SEL$1" "THIS_"@"SEL$1") USE_NL(@"SEL$9E43CB6E" "COUNTRY2_"@"SEL$1") USE_NL(@"SEL$9E43CB6E" "THIS_"@"SEL$1") END_OUTLINE_DATA */ Optimizer environment: optimizer_mode_hinted = false optimizer_features_hinted = 0.0.0 parallel_execution_enabled = true parallel_query_forced_dop = 0 parallel_dml_forced_dop = 0 parallel_ddl_forced_degree = 0 parallel_ddl_forced_instances = 0 _query_rewrite_fudge = 90 optimizer_features_enable = 10.2.0.4 _optimizer_search_limit = 5 cpu_count = 8 active_instance_count = 1 parallel_threads_per_cpu = 2 hash_area_size = 131072 bitmap_merge_area_size = 1048576 sort_area_size = 65536 sort_area_retained_size = 0 _sort_elimination_cost_ratio = 0 _optimizer_block_size = 8192 _sort_multiblock_read_count = 2 _hash_multiblock_io_count = 0 _db_file_optimizer_read_count = 16 _optimizer_max_permutations = 2000 pga_aggregate_target = 403456 KB _pga_max_size = 204800 KB _query_rewrite_maxdisjunct = 257 _smm_auto_min_io_size = 56 KB _smm_auto_max_io_size = 248 KB _smm_min_size = 403 KB _smm_max_size = 80691 KB _smm_px_max_size = 201728 KB _cpu_to_io = 0 _optimizer_undo_cost_change = 10.2.0.4 parallel_query_mode = enabled parallel_dml_mode = disabled parallel_ddl_mode = enabled optimizer_mode = all_rows sqlstat_enabled = false _optimizer_percent_parallel = 101 _always_anti_join = choose _always_semi_join = choose _optimizer_mode_force = true _partition_view_enabled = true _always_star_transformation = false _query_rewrite_or_error = false _hash_join_enabled = true cursor_sharing = exact _b_tree_bitmap_plans = true star_transformation_enabled = false _optimizer_cost_model = choose _new_sort_cost_estimate = true _complex_view_merging = true _unnest_subquery = true _eliminate_common_subexpr = true _pred_move_around = true _convert_set_to_join = false _push_join_predicate = true _push_join_union_view = true _fast_full_scan_enabled = true _optim_enhance_nnull_detection = true _parallel_broadcast_enabled = true _px_broadcast_fudge_factor = 100 _ordered_nested_loop = true _no_or_expansion = false optimizer_index_cost_adj = 100 optimizer_index_caching = 0 _system_index_caching = 0 _disable_datalayer_sampling = false query_rewrite_enabled = true query_rewrite_integrity = enforced _query_cost_rewrite = true _query_rewrite_2 = true _query_rewrite_1 = true _query_rewrite_expression = true _query_rewrite_jgmigrate = true _query_rewrite_fpc = true _query_rewrite_drj = true _full_pwise_join_enabled = true _partial_pwise_join_enabled = true _left_nested_loops_random = true _improved_row_length_enabled = true _index_join_enabled = true _enable_type_dep_selectivity = true _improved_outerjoin_card = true _optimizer_adjust_for_nulls = true _optimizer_degree = 0 _use_column_stats_for_function = true _subquery_pruning_enabled = true _subquery_pruning_mv_enabled = false _or_expand_nvl_predicate = true _like_with_bind_as_equality = false _table_scan_cost_plus_one = true _cost_equality_semi_join = true _default_non_equality_sel_check = true _new_initial_join_orders = true _oneside_colstat_for_equijoins = true _optim_peek_user_binds = true _minimal_stats_aggregation = true _force_temptables_for_gsets = false workarea_size_policy = auto _smm_auto_cost_enabled = true _gs_anti_semi_join_allowed = true _optim_new_default_join_sel = true optimizer_dynamic_sampling = 2 _pre_rewrite_push_pred = true _optimizer_new_join_card_computation = true _union_rewrite_for_gs = yes_gset_mvs _generalized_pruning_enabled = true _optim_adjust_for_part_skews = true _force_datefold_trunc = false statistics_level = typical _optimizer_system_stats_usage = true skip_unusable_indexes = true _remove_aggr_subquery = true _optimizer_push_down_distinct = 0 _dml_monitoring_enabled = true _optimizer_undo_changes = false _predicate_elimination_enabled = true _nested_loop_fudge = 100 _project_view_columns = true _local_communication_costing_enabled = true _local_communication_ratio = 50 _query_rewrite_vop_cleanup = true _slave_mapping_enabled = true _optimizer_cost_based_transformation = linear _optimizer_mjc_enabled = true _right_outer_hash_enable = true _spr_push_pred_refspr = true _optimizer_cache_stats = false _optimizer_cbqt_factor = 50 _optimizer_squ_bottomup = true _fic_area_size = 131072 _optimizer_skip_scan_enabled = true _optimizer_cost_filter_pred = false _optimizer_sortmerge_join_enabled = true _optimizer_join_sel_sanity_check = true _mmv_query_rewrite_enabled = true _bt_mmv_query_rewrite_enabled = true _add_stale_mv_to_dependency_list = true _distinct_view_unnesting = false _optimizer_dim_subq_join_sel = true _optimizer_disable_strans_sanity_checks = 0 _optimizer_compute_index_stats = true _push_join_union_view2 = true _optimizer_ignore_hints = false _optimizer_random_plan = 0 _query_rewrite_setopgrw_enable = true _optimizer_correct_sq_selectivity = true _disable_function_based_index = false _optimizer_join_order_control = 3 _optimizer_cartesian_enabled = true _optimizer_starplan_enabled = true _extended_pruning_enabled = true _optimizer_push_pred_cost_based = true _sql_model_unfold_forloops = run_time _enable_dml_lock_escalation = false _bloom_filter_enabled = true _update_bji_ipdml_enabled = 0 _optimizer_extended_cursor_sharing = udo _dm_max_shared_pool_pct = 1 _optimizer_cost_hjsmj_multimatch = true _optimizer_transitivity_retain = true _px_pwg_enabled = true optimizer_secure_view_merging = true _optimizer_join_elimination_enabled = true flashback_table_rpi = non_fbt _optimizer_cbqt_no_size_restriction = true _optimizer_enhanced_filter_push = true _optimizer_filter_pred_pullup = true _rowsrc_trace_level = 0 _simple_view_merging = true _optimizer_rownum_pred_based_fkr = true _optimizer_better_inlist_costing = all _optimizer_self_induced_cache_cost = false _optimizer_min_cache_blocks = 10 _optimizer_or_expansion = depth _optimizer_order_by_elimination_enabled = true _optimizer_outer_to_anti_enabled = true _selfjoin_mv_duplicates = true _dimension_skip_null = true _force_rewrite_enable = false _optimizer_star_tran_in_with_clause = true _optimizer_complex_pred_selectivity = true _optimizer_connect_by_cost_based = true _gby_hash_aggregation_enabled = true _globalindex_pnum_filter_enabled = true _fix_control_key = 0 _optimizer_skip_scan_guess = false _enable_row_shipping = false _row_shipping_threshold = 80 _row_shipping_explain = false _optimizer_rownum_bind_default = 10 _first_k_rows_dynamic_proration = true _px_ual_serial_input = true _optimizer_native_full_outer_join = off _optimizer_star_trans_min_cost = 0 _optimizer_star_trans_min_ratio = 0 _optimizer_fkr_index_cost_bias = 10 _optimizer_connect_by_combine_sw = true _optimizer_use_subheap = true _optimizer_or_expansion_subheap = true _optimizer_sortmerge_join_inequality = true _optimizer_use_histograms = true _optimizer_enable_density_improvements = false ********************************* Bug Fix Control Environment *************************** fix 4611850 = enabled fix 4663804 = enabled fix 4663698 = enabled fix 4545833 = enabled fix 3499674 = disabled fix 4584065 = enabled fix 4602374 = enabled fix 4569940 = enabled fix 4631959 = enabled fix 4519340 = enabled fix 4550003 = enabled fix 4488689 = enabled fix 3118776 = enabled fix 4519016 = enabled fix 4487253 = enabled fix 4556762 = 15 fix 4728348 = enabled fix 4723244 = enabled fix 4554846 = enabled fix 4175830 = enabled fix 4722900 = enabled fix 5094217 = enabled fix 4904890 = enabled fix 4483286 = disabled fix 4969880 = disabled fix 4711525 = enabled fix 4717546 = enabled fix 4904838 = enabled fix 5005866 = enabled fix 4600710 = enabled fix 5129233 = enabled fix 5195882 = enabled fix 5084239 = enabled fix 4595987 = enabled fix 4134994 = enabled fix 5104624 = enabled fix 4908162 = enabled fix 5015557 = enabled fix 5263572 = enabled fix 4483240 = enabled fix 5099909 = enabled fix 5650477 = enabled fix 4273361 = enabled fix 5694984 = enabled fix 5449488 = enabled fix 5236908 = enabled fix 5618040 = enabled fix 5634346 = enabled fix 5220356 = enabled fix 5611962 = enabled fix 5741121 = enabled fix 5547058 = enabled fix 5762598 = enabled fix 5509293 = enabled fix 5396162 = enabled fix 5891471 = enabled fix 4872602 = disabled fix 5882954 = enabled fix 5884780 = enabled fix 5680702 = enabled fix 5240607 = enabled fix 4924149 = enabled fix 4752814 = enabled fix 4583239 = enabled fix 5949981 = enabled fix 5096560 = enabled fix 5838613 = enabled fix 5482831 = enabled fix 5624216 = enabled fix 5976822 = enabled fix 5741044 = enabled fix 5385629 = enabled fix 5705630 = disabled fix 5483301 = enabled fix 6122894 = enabled fix 5842686 = disabled fix 6006300 = disabled fix 6070954 = enabled fix 2492766 = enabled fix 6042205 = enabled fix 5302124 = enabled fix 6051211 = enabled fix 5620485 = enabled fix 4545802 = enabled fix 4716096 = enabled fix 5259048 = enabled fix 6163564 = enabled fix 6082745 = enabled fix 5944076 = enabled fix 4878299 = enabled fix 5288623 = enabled fix 5570494 = enabled fix 5387148 = enabled fix 4605810 = enabled fix 4704779 = enabled fix 5547895 = enabled fix 6188881 = enabled fix 5872956 = enabled fix 4708389 = enabled fix 3151991 = enabled fix 3426050 = enabled fix 599680 = enabled fix 5505157 = enabled fix 5996801 = enabled fix 5765456 = 0 fix 6494943 = enabled fix 6251917 = enabled fix 6087237 = enabled fix 6239971 = enabled fix 6062266 = enabled fix 5520732 = 0 fix 6151963 = enabled fix 4567767 = enabled fix 6007259 = enabled fix 6694548 = enabled Query Block Registry: ********************* SEL$3 0xb7f56808 (PARSER) SEL$9E43CB6E 0xb7f56808 (VIEW MERGE SEL$3; SEL$58A6D7F6) [FINAL] SEL$2 0xb7fab470 (PARSER) SEL$58A6D7F6 0xb7fab470 (VIEW MERGE SEL$2; SEL$1) SEL$9E43CB6E 0xb7f56808 (VIEW MERGE SEL$3; SEL$58A6D7F6) [FINAL] SEL$1 0xb7f50308 (PARSER) SEL$58A6D7F6 0xb7fab470 (VIEW MERGE SEL$2; SEL$1) ... Optimizer State Dump: call(in-use=139264, alloc=199356), compile(in-use=190604, alloc=236624)