Oracle SQL optimization 1

Oracle SQL optimization 1
The data size of the two schemas is almost the same as that of the development. However, for the same SQL statement, schema a runs for 6 minutes and Schema B only comes out in 5 seconds. The SQL statement is as follows:

SELECT *FROM (SELECT A.*, ROWNUM RNFROM (select t3.check_show,t1.*,t2.storesid,to_char(rdate, 'yyyy-mm-dd') as to_rdate,to_char(submitdate, 'yyyy-mm-dd') as to_submitdate,to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate,to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate2,to_char(billingdate, 'yyyy-mm-dd hh24:mi:ss') as to_billingdate,to_char(dealtotal, 'fm9999999990.00') as to_dealtotal,(nvl(cashreceiptssum, 0) + nvl(received, 0)) as to_cashreceiptssum,to_char((nvl(debtsum, 0) - nvl(received, 0)),'fm9999999990.00') to_debtsum,nvl(amounttotal, 0) as to_amounttotal,(select a.reduce_pointsfrom SchemaB.m_mempoint_logs awhere a.billno = t1.billnoand a.billtype = 4) as reduce_pointsfrom SchemaB.v0bill t1,SchemaB.tbl_stores t2,SchemaB.TBL_BILL_CHECKSTATE_SHOW t3where ((posbillno is not null and BCOMPLETE = 1) orposbillno is null)and t1.StoreRoomID = t2.storesidand t1.billsubcase = t3.billsubcaseand t1.check_status = t3.check_statusand (instoreroomid in(select storesidfrom SchemaB.tbl_user_storeswhere employeeid = 4352) oroutstoreroomid in(select storesidfrom SchemaB.tbl_user_storeswhere employeeid = 4352))and servicStatus = 1and rdate >= to_date('2015-06-04', 'yyyy-mm-dd')and rdate <=to_date('2015-06-11 23:59:59', 'yyyy-mm-dd hh24:mi:ss')and (t1.billsubcase = '75' or t1.billsubcase = '711' or(instoreroomid in(select storesidfrom SchemaB.tbl_storeswhere areacode like '001%') oroutstoreroomid in(select storesidfrom SchemaB.tbl_storeswhere areacode like '001%')))ORDER by rdate desc, billingdate desc, billno desc) AWHERE ROWNUM <= (1 * 20))WHERE RN > ((1 - 1) * 20);

B Schema execution plan:

-------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 20 | 152K| | 16718 (1)| 00:03:21 | | || 1 | TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_BILL | 1 | 25 | | 3 (0)| 00:00:01 | ROWID | ROWID ||* 2 | INDEX UNIQUE SCAN | XPK_BILL_P | 1 | | | 2 (0)| 00:00:01 | | ||* 3 | TABLE ACCESS BY INDEX ROWID | M_MEMPOINT_LOGS | 1 | 24 | | 4 (0)| 00:00:01 | | ||* 4 | INDEX RANGE SCAN | IDX_MEMPOINT_LOGS_BILLNO | 1 | | | 3 (0)| 00:00:01 | | ||* 5 | VIEW | | 20 | 152K| | 16718 (1)| 00:03:21 | | ||* 6 | COUNT STOPKEY | | | | | | | | || 7 | VIEW | | 917 | 6989K| | 16718 (1)| 00:03:21 | | ||* 8 | SORT ORDER BY STOPKEY | | 917 | 522K| 57M| 16718 (1)| 00:03:21 | | ||* 9 | FILTER | | | | | | | | ||* 10 | HASH JOIN | | 96250 | 53M| | 4844 (1)| 00:00:59 | | || 11 | TABLE ACCESS FULL | TBL_BILL_CHECKSTATE_SHOW | 200 | 9600 | | 3 (0)| 00:00:01 | | ||* 12 | HASH JOIN | | 57750 | 29M| | 4840 (1)| 00:00:59 | | || 13 | INDEX FULL SCAN | XPK_TBL_STORES | 85 | 340 | | 1 (0)| 00:00:01 | | ||* 14 | HASH JOIN RIGHT OUTER | | 57750 | 29M| | 4838 (1)| 00:00:59 | | || 15 | TABLE ACCESS FULL | TBL_STORES | 85 | 2550 | | 4 (0)| 00:00:01 | | ||* 16 | HASH JOIN RIGHT OUTER | | 57750 | 27M| | 4834 (1)| 00:00:59 | | || 17 | TABLE ACCESS FULL | TBL_STORES | 85 | 2550 | | 4 (0)| 00:00:01 | | ||* 18 | HASH JOIN RIGHT OUTER | | 57750 | 25M| 4200K| 4829 (1)| 00:00:58 | | || 19 | TABLE ACCESS FULL | M_MEMDETAIL | 89469 | 3145K| | 380 (1)| 00:00:05 | | ||* 20 | HASH JOIN RIGHT OUTER | | 57750 | 24M| | 3020 (1)| 00:00:37 | | || 21 | TABLE ACCESS FULL | TBL_BILLCASE | 40 | 840 | | 4 (0)| 00:00:01 | | ||* 22 | HASH JOIN RIGHT OUTER | | 57750 | 22M| 3176K| 3015 (1)| 00:00:37 | | || 23 | TABLE ACCESS FULL | M_MEMDETAILCARD | 90213 | 2114K| | 310 (1)| 00:00:04 | | ||* 24 | HASH JOIN RIGHT OUTER | | 57750 | 21M| | 1450 (1)| 00:00:18 | | || 25 | TABLE ACCESS FULL | TBL_CUSTOM | 537 | 17184 | | 6 (0)| 00:00:01 | | ||* 26 | HASH JOIN RIGHT OUTER | | 57750 | 19M| | 1443 (1)| 00:00:18 | | || 27 | TABLE ACCESS FULL | TBL_BUSINESS_TYPE | 38 | 760 | | 3 (0)| 00:00:01 | | || 28 | PARTITION RANGE ITERATOR| | 57750 | 18M| | 1439 (1)| 00:00:18 | 181 | 180 ||* 29 | TABLE ACCESS FULL | TBL_BILL | 57750 | 18M| | 1439 (1)| 00:00:18 | 181 | 180 ||* 30 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 8 | | 7 (0)| 00:00:01 | | ||* 31 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 8 | | 7 (0)| 00:00:01 | | ||* 32 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 12 | | 1 (0)| 00:00:01 | | ||* 33 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 0 (0)| 00:00:01 | | ||* 34 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 12 | | 1 (0)| 00:00:01 | | ||* 35 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 0 (0)| 00:00:01 | | |-------------------------------------------------------------------------------------------------------------------------------------------

Executing the SQL statement in schema B consumes more than 10 thousand logical reads.

Execution Plan of Schema:

----------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 20 | 152K| | 50624 (1)| 00:10:08 | | || 1 | TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_BILL | 1 | 31 | | 3 (0)| 00:00:01 | ROWID | ROWID ||* 2 | INDEX UNIQUE SCAN | XPK_BILL_P | 1 | | | 2 (0)| 00:00:01 | | ||* 3 | TABLE ACCESS BY INDEX ROWID | M_MEMPOINT_LOGS | 1 | 47 | | 1 (0)| 00:00:01 | | ||* 4 | INDEX RANGE SCAN | IDX_MEMPOINT_LOGS_BILLNO | 1 | | | 1 (0)| 00:00:01 | | ||* 5 | VIEW | | 20 | 152K| | 50624 (1)| 00:10:08 | | ||* 6 | COUNT STOPKEY | | | | | | | | || 7 | VIEW | | 3600 | 26M| | 50624 (1)| 00:10:08 | | ||* 8 | SORT ORDER BY STOPKEY | | 3600 | 2000K| 210M| 50624 (1)| 00:10:08 | | ||* 9 | FILTER | | | | | | | | ||* 10 | HASH JOIN | | 378K| 205M| | 5203 (1)| 00:01:03 | | || 11 | TABLE ACCESS FULL | TBL_BILL_CHECKSTATE_SHOW | 200 | 9600 | | 3 (0)| 00:00:01 | | ||* 12 | HASH JOIN | | 151K| 75M| | 5199 (1)| 00:01:03 | | || 13 | INDEX FAST FULL SCAN | XPK_TBL_STORES | 616 | 3080 | | 2 (0)| 00:00:01 | | ||* 14 | HASH JOIN RIGHT OUTER | | 151K| 74M| | 5196 (1)| 00:01:03 | | || 15 | TABLE ACCESS FULL | TBL_STORES | 616 | 20944 | | 8 (0)| 00:00:01 | | ||* 16 | HASH JOIN RIGHT OUTER | | 151K| 69M| | 5187 (1)| 00:01:03 | | || 17 | TABLE ACCESS FULL | TBL_STORES | 616 | 20944 | | 8 (0)| 00:00:01 | | ||* 18 | HASH JOIN RIGHT OUTER | | 151K| 64M| | 5178 (1)| 00:01:03 | | || 19 | TABLE ACCESS FULL | TBL_CUSTOM | 93 | 2139 | | 4 (0)| 00:00:01 | | ||* 20 | HASH JOIN RIGHT OUTER | | 151K| 61M| | 5173 (1)| 00:01:03 | | || 21 | TABLE ACCESS FULL | TBL_BILLCASE | 40 | 800 | | 4 (0)| 00:00:01 | | ||* 22 | HASH JOIN RIGHT OUTER | | 151K| 58M| | 5168 (1)| 00:01:03 | | || 23 | TABLE ACCESS FULL | TBL_BUSINESS_TYPE | 33 | 660 | | 3 (0)| 00:00:01 | | ||* 24 | HASH JOIN RIGHT OUTER | | 151K| 55M| | 5164 (1)| 00:01:02 | | || 25 | TABLE ACCESS FULL | M_MEMDETAIL | 5 | 120 | | 4 (0)| 00:00:01 | | || 26 | NESTED LOOPS OUTER | | 151K| 52M| | 5159 (1)| 00:01:02 | | || 27 | PARTITION RANGE ITERATOR | | 151K| 50M| | 5158 (1)| 00:01:02 | 181 | 180 ||* 28 | TABLE ACCESS FULL | TBL_BILL | 151K| 50M| | 5158 (1)| 00:01:02 | 181 | 180 || 29 | TABLE ACCESS BY INDEX ROWID| M_MEMDETAILCARD | 1 | 14 | | 1 (0)| 00:00:01 | | ||* 30 | INDEX UNIQUE SCAN | XPK_TBL_MEMDETAIL | 1 | | | 0 (0)| 00:00:01 | | ||* 31 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 10 | | 175 (1)| 00:00:03 | | ||* 32 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 10 | | 175 (1)| 00:00:03 | | ||* 33 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 18 | | 2 (0)| 00:00:01 | | ||* 34 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 1 (0)| 00:00:01 | | ||* 35 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 18 | | 2 (0)| 00:00:01 | | ||* 36 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 1 (0)| 00:00:01 | | |----------------------------------------------------------------------------------------------------------------------------------------------Statistics----------------------------------------------------------14 recursive calls0 db block gets27716859 consistent gets1 physical reads0 redo size16407 bytes sent via SQL*Net to client531 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client3 sorts (memory)0 sorts (disk)20 rows processed

27.71 million logical reads.
In all the tables involved in this SQL statement, TBL_BILL is the main large table. In this case, partition pruning is performed on the access to TBL_BILL, the main difference between the two execution plans is that schema a uses the TBL_BILL as the driving table for nested loops outer,
B Schema uses the TBL_BUSINESS_TYPE table as the driving table to perform HASH JOIN on TBL_BILL. It is suspected that A has gone through the wrong execution plan, so the hint is used to make A HASH JOIN on TBL_BILL, no improvement was found,
So let's take a look at the specific time consumption:

SELECT /*+gather_plan_statistics */ *FROM (SELECT A.*, ROWNUM RNFROM (selectt3.check_show,t1.*,t2.storesid,to_char(rdate, 'yyyy-mm-dd') as to_rdate,to_char(submitdate, 'yyyy-mm-dd') as to_submitdate,to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate,to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate2,to_char(billingdate, 'yyyy-mm-dd hh24:mi:ss') as to_billingdate,to_char(dealtotal, 'fm9999999990.00') as to_dealtotal,(nvl(cashreceiptssum, 0) + nvl(received, 0)) as to_cashreceiptssum,to_char((nvl(debtsum, 0) - nvl(received, 0)),'fm9999999990.00') to_debtsum,nvl(amounttotal, 0) as to_amounttotal,t4.reduce_pointsfrom SchemaA.v0bill t1,SchemaA.tbl_stores t2,SchemaA.TBL_BILL_CHECKSTATE_SHOW t3,SchemaA.m_mempoint_logs t4where ((posbillno is not null and BCOMPLETE = 1) orposbillno is null)and t1.StoreRoomID = t2.storesidand t1.billsubcase = t3.billsubcaseand t1.check_status = t3.check_statusand (instoreroomid in(select storesidfrom SchemaA.tbl_user_storeswhere employeeid = 3945) oroutstoreroomid in(select storesidfrom SchemaA.tbl_user_storeswhere employeeid = 3945))and servicStatus = 1and rdate >= to_date('2015-06-04', 'yyyy-mm-dd')and rdate <=to_date('2015-06-11 23:59:59', 'yyyy-mm-dd hh24:mi:ss')and (t1.billsubcase in ('75' ,'711') or(instoreroomid in(select storesidfrom SchemaA.tbl_storeswhere areacode like '001022%') oroutstoreroomid in(select storesidfrom SchemaA.tbl_storeswhere areacode like '001022%')))and t1.billno=t4.billno(+)and T4.billtype (+)= 4ORDER by rdate desc, billingdate desc, t1.billno desc) AWHERE ROWNUM <= (1 * 20))WHERE RN > ((1 - 1)* 20);

It is found that the main time is spent on TBL_USER_STORES (two filter operations are performed on the table (full table scan), accounting for 99% of the total time consumption), such:

Let's take a look at the two companies. The data volume in this table (TBL_USER_STORES) is not an order of magnitude, as shown below:

A Schema record count: 136057

Number of B Schema records: 7439

This is the reason, so we optimized the access path of the table TBL_USER_STORES and created a composite index:

Create index schepaa. I _TBL_USER_STORESon schepaa. TBL_USER_STORES (employeeid, storesid );

After an index is created, run the SQL statement on schepaa within 3 seconds: