sql tuning

[Roshan]

Oracle Database 12.1.0.2
RHEL 7.4

Hi Erman,

Can you please guide me in tuning the query attached?

I see this 'HASH UNIQUE' (line 117) is taking time

| 117 |   HASH UNIQUE                            |                             |   621K|   140M|   149M|    22M  (1)| 00:29:10

explain_plan.txt

Thanks,

Roshan

[ErmanArslansOracleBlog]

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 |

[Roshan]

The 'Time' values does not seems to be high for these tables.

What metric should I check to see they have high costs?

[ErmanArslansOracleBlog]

there is a cost column there.

[Roshan]

Thanks for your usual support Erman :)

[ErmanArslansOracleBlog]

What do you mean by 'Usual support'? 

31 Oca 2019 Per 09:26 tarihinde Roshan [via Erman Arslan's Oracle Forum] <[hidden email]> şunu yazdı:

Thanks for your usual support Erman :)

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/sql-tuning-tp7161p7186.html

To start a new topic under Database, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML