In order to improve the query efficiency in SQL queries, we often take some measures to optimize the query statements. Some of the methods summarized below can be referred to if necessary. In the optimization experience of a certain operator, I once encountered a relatively interesting SQL, the details are as follows:
1 The execution of the initial SQL is as follows
SQL> SELECT 2 NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId" 3 FROM OFFER_SPEC_RELA T 4 LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS 5 ON T.RELA_GRP_ID = SUBOS.OFFER_SPEC_GRP_ID 6 AND subos.start_dt <= SYSDATE 7 AND subos.end_dt >= SYSDATE 8 WHERE T.RELA_TYPE_CD = 2 9 AND t.start_dt <= SYSDATE 10 AND t.end_dt >= SYSDATE 11 AND (T.OFFER_SPEC_ID = 109910000618 12 OR EXISTS 13 (SELECT A.OFFER_SPEC_GRP_ID 14 FROM OFFER_SPEC_GRP_RELA A 15 WHERE A.SUB_OFFER_SPEC_ID = 109910000618 16 AND T.OFFER_SPEC_GRP_ID = A.OFFER_SPEC_GRP_ID 17 )) 18 AND rownum<500; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1350156609
Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<500) 2 - filter("T"."OFFER_SPEC_ID"=109910000618 OR EXISTS (SELECT 0 FROM "SPEC"."OFFER_SPEC_GRP_RELA" "A" WHERE "A"."OFFER_SPEC_GRP_ID"=:B1 AND "A"."SUB_OFFER_SPEC_ID"=109910000618)) 3 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+)) 4 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@!) 5 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!) 6 - access("A"."SUB_OFFER_SPEC_ID"=109910000618 AND "A"."OFFER_SPEC_GRP_ID"=:B1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 12444 consistent gets 0 physical reads 0 redo size 339 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed PLAN GET DISK WRITE ROWS ROWS USER_IO(MS) ELA(MS) CPU(MS) CLUSTER(MS) PLSQL END_TI I HASH VALUE EXEC PRE EXEC PRE EXEC PER EXEC ROW_P PRE EXEC PRE FETCH PER EXEC PRE EXEC PRE EXEC PER EXEC PER EXEC
2 First analysis
There should be the following points worth noting at this time
1) The sql is executed every day Thousands of times, the average execution returns less than 10 rows of data, but the average logical read reaches 1.2W, which may cause performance problems.
2) Two full table scans appear in the execution plan path with IDs 4 and 5. Seeing this, we can think that there may be no suitable indexes, resulting in a full table scan and low execution efficiency.
3) FILTER appears in the execution plan path with ID 2, and 3, and 6 are its sub-paths. If FILTER has two or more sub-paths, its execution principle will be similar to a nested loop. , if the subpath with the smallest ID number returns a large number of rows, it may cause the subpath with the smaller ID number to be executed multiple times, resulting in low performance. This situation generally occurs when "OR EXISTS" exists and can be avoided according to the situation.
Related links:
PHP-FPM achieves performance optimization, php-fpm performance optimization
[SQL]MySQL performance Optimization_MySQL
MySQL Optimization Video Tutorial
The above is the detailed content of SQL Optimization: A very simple article to improve SQL performance!. For more information, please follow other related articles on the PHP Chinese website!