SQL optimization: A very simple article to improve SQL performance!

In SQL query in order to improve query efficiency, we often take some measures to SQL optimization of query statements, the following summarizes some of the methods, there is a need to reference. In the optimization experience of a carrier has encountered a more interesting SQL, as follows:

1 The initial SQL execution 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 and    t.end_dt >=        sysdate    One and (t.offer_spec_id = 109910000618    OR EXISTS)      (SELECT a.offer_spec_grp_id from      offer_spec_grp_ RELA A,      WHERE a.sub_offer_spec_id = 109910000618 and      t.offer_spec_grp_id   = a.offer_spec_grp_id 17< c29/>)) 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 SE NT 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)  Plsqlend_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-time analysis
There should be one of the following places to note
1) The SQL executes thousands of times per day, averaging less than 10 rows of data per execution, but with an average logical read of 1.2W, there may be performance issues.
2) There are two full-table scans in the execution plan path with ID 4,5, and we can see here that there may be no suitable index leading to a full table scan to perform inefficiently.
3) The execution plan path with ID 2 appears as filter, and 3, and 6 is its sub-path, if the filter has two and more than two sub-paths, then his execution principle will resemble nested loops, the ID number of the smallest sub-path if the number of rows returned, may result in multiple execution of the ID number smaller sub-path, Cause poor performance. This occurs when there is a general "OR EXISTS", which can be avoided as appropriate.

RELATED links:

PHP-FPM for performance optimization and PHP-FPM performance optimization

"SQL" MySQL performance optimization _mysql

MySQL Optimization video tutorial