Re: sql tuning

Posted by ErmanArslansOracleBlog on Jan 28, 2019; 10:47am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/sql-tuning-tp7161p7162.html

Concantrate on the Full Table Scan activities, which have high costs.
Because those activities increase the cost of the subsequent activities as well.

For example:

FTS on TT_TICKETINFO and FTS on TT_TICKETINCIDENT tables are very expensive operations.
That table has partitions, but you do a range all.. So you are visiting all the partitions..          

| 119 |     TABLE ACCESS FULL                    | CB_USER_GROUP               |    92 |  1840 |       |     4   (0)| 00:00:01 |       |       |
|*120 |     HASH JOIN RIGHT ANTI NA              |                             |   621K|   128M|       |   182K  (1)| 00:00:15 |       |       |
| 121 |      PARTITION RANGE ALL                 |                             | 43773 |   299K|       | 86981   (1)| 00:00:07 |     1 |1048575|
| 122 |       TABLE ACCESS FULL                  | WF_WORKFLOW                 | 43773 |   299K|       | 86981   (1)| 00:00:07 |     1 |1048575|
|*123 |      HASH JOIN                           |                             |   621K|   124M|       | 95738   (1)| 00:00:08 |       |       |
| 124 |       TABLE ACCESS FULL                  | CB_USERS                    |  1511 | 33242 |       |    19   (0)| 00:00:01 |       |       |
|*125 |       HASH JOIN                          |                             |   621K|   111M|    77M| 95717   (1)| 00:00:08 |       |       |
| 126 |        TABLE ACCESS FULL                 | TT_TICKETINCIDENT           |  1327K|    62M|       |  2673   (1)| 00:00:01 |       |       |
| 127 |        PARTITION RANGE ALL               |                             |   621K|    82M|       | 85914   (1)| 00:00:07 |     1 |1048575|
| 128 |         TABLE ACCESS FULL                | TT_TICKETINFO               |   621K|    82M|       | 85914   (1)| 00:00:07 |