System optimization Experience (I.)

Since receiving the responsible operations project, users will always put forward various optimization issues.

Database server, Application Server management by the basis responsible, their own application to view the database execution log is almost impossible, helpless can only constantly look at the code, analysis code, one line.

Take a look at recent problems

The comparison of the ①oracle standard time format is much more efficient than the time in the string format.

②join...on in the back if there is only one condition, use a single-value index

SELECT * from SCF2. A A
INNER JOIN scf2. b b
On b.sessionid = A.sessionid
INNER JOIN scf2. E E
On substr (E.c_pzh, 3, ten) = B.gr_gi_slip_no
WHERE 1 = 1
and b.pstng_date >= ' 2015.01.11 '
and b.pstng_date <= ' 2015.01.12 '
and b.doc_date >= ' 2015.01.01 '
and b.doc_date <= ' 2015.01.12 '
and a.plant = ' 8812 '
and A.status in (' 3 ')
and A.stge_loc = ' 2103 ';

Actually ran for 5 minutes did not see the results show.

Change into

and a.plant = ' 8812 '
and A.status in (' 3 ')
and A.stge_loc = ' 2103 ';
On substr (E.c_pzh, 3, ten) = B.gr_gi_slip_no
WHERE 1 = 1
and To_date (b.pstng_date, ' yyyy.mm.dd ') >=
To_date (' 2015.01.01 ', ' yyyy.mm.dd ')
and To_date (b.pstng_date, ' yyyy.mm.dd ') <=
To_date (' 2015.02.01 ', ' yyyy.mm.dd ')
and To_date (b.doc_date, ' yyyy.mm.dd ') >=
To_date (' 2015.01.01 ', ' yyyy.mm.dd ')
and To_date (b.doc_date, ' yyyy.mm.dd ') <=
To_date (' 2015.02.01 ', ' yyyy.mm.dd ')
and a.plant = ' 8812 '
and A.status in (' 3 ')
and A.stge_loc = ' 2103 ';

And after adding an index based on the requirements, more than 10 s results come out.

System optimization Experience (i)