Receive a SQL for development submission, slow down in high concurrency, and see if you can do some optimizations:
The SQL is 1 tables to another table doing a left join, first performing a look below,
Sql> Select L.vcno,opttype,add_points,optdate,remark,memid,id,familyid,create_username,billno,billtype, Billsubcase,reduce_points, L.addnum,addpresentum,reducenum,reducepresentnum
2 from Hq_07310066.m_mempoint_logs l to join hq_07310066.m_memdetail m on l.vcno = M.VCNO where 1=1 and L.memid = ' WX 000000361 ' ORDER by optdate Desc;
208 rows have been selected.
Execution plan
----------------------------------------------------------
Plan Hash value:2095947206
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 464 | 172 (2) | 00:00:03 |
| 1 | SORT ORDER by | | 4 | 464 | 172 (2) | 00:00:03 |
|* 2 | TABLE ACCESS full| M_mempoint_logs | 4 | 464 | 171 (1) | 00:00:03 |
--------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-filter ("L". " Memid "= ' WX000000361 ')
Statistical information
----------------------------------------------------------
0 Recursive calls
0 db Block gets
626 Consistent gets
0 physical Reads
0 Redo Size
7125 Bytes sent via sql*net to client
663 bytes received via sql*net from client
Sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
208 Rows processed
May be the cause of the primary foreign KEY constraint, the optimizer automatically hq_07310066.m_memdetail the table. So if you are sure that there is a strict primary foreign key constraint between the two tables, you can remove the unwanted table directly in SQL, as follows:
Sql> Select L.vcno,opttype,add_points,optdate,remark,memid,id,familyid,create_username,billno,billtype, Billsubcase,reduce_points, L.addnum,addpresentum,reducenum,
2 Reducepresentnum
3 from Hq_07310066.m_mempoint_logs L
4 Where L.memid = ' WX000000361 '
5 ORDER BY optdate Desc;
208 rows have been selected.
Time used: 00:00:00.02
Execution plan
----------------------------------------------------------
Plan Hash value:2095947206
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 464 | 172 (2) | 00:00:03 |
| 1 | SORT ORDER by | | 4 | 464 | 172 (2) | 00:00:03 |
|* 2 | TABLE ACCESS full| M_mempoint_logs | 4 | 464 | 171 (1) | 00:00:03 |
--------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-filter ("L". " Memid "= ' WX000000361 ')
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
626 Consistent gets
0 physical Reads
0 Redo Size
11245 Bytes sent via sql*net to client
663 bytes received via sql*net from client
Sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
208 Rows processed
Recreate index: CREATE index hq_07310066.i_m_mempoint_logs_optdate on hq_07310066.m_mempoint_logs (optdate);
Sql> Select L.vcno,opttype,add_points,optdate,remark,memid,id,familyid,create_username,billno,billtype, Billsubcase,reduce_points, L.addnum,addpresentum,reducenum,
2 Reducepresentnum
3 from Hq_07310066.m_mempoint_logs L
4 Where L.memid = ' WX000000361 '
5 ORDER BY optdate Desc;
208 rows have been selected.
Time used: 00:00:00.02
Execution plan
----------------------------------------------------------
Plan Hash value:3466595853
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 464 | 6 (17) | 00:00:01 |
| 1 | SORT ORDER by | | 4 | 464 | 6 (17) | 00:00:01 |
| 2 | TABLE ACCESS by INDEX rowid| M_mempoint_logs | 4 | 464 | 5 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_m_mempoint_logs_optdate | 4 | | 1 (0) | 00:00:01 |
----------------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
3-access ("L". " Memid "= ' WX000000361 ')
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
Consistent gets
2 physical Reads
0 Redo Size
11245 Bytes sent via sql*net to client
663 bytes received via sql*net from client
Sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
208 Rows processed
Optimization completed.
Oracle's SQL Optimization II
