SQL Tuning Tools

Free Oracle SQL Tuning Tools

For Oracle SQL Query Tuning you are welcome to use our free SQL Query Tuning Tool.

To rearrange your SQL query statement into the appropriate standard format use SQL Query Formatter Tool for the following databases: Oracle, MySQL, MS Access, DB2 and MSSQL.

Oracle SQL Tuning Tool in SQL-tuning.com

The example below shows how you can tune Oracle SQL query in SQL-tuning.com .

1. First you should click on button “LOAD TEST DB SCRIPT”, if SQL Script input form is empty:

load-db-script
2. Then insert Oracle DB scripts of your table or tables used in a SQL query, like in the image below. Do not use schema names.

load-db-script
3. Insert a SQL query into SQL Query input form.

insert-sql-query

4. Click on a SQL Tuning button:

sql-tuning-button
5. Now you will get SQL PERFORMANCE TUNING recommendations

sqltuningrecommendations

SQL Tuning in Quest Toad for Oracle

The example below shows how you can tune SQL query in TOAD for Oracle database. In TOAD we will use SQL tuning tool “Explain Plan”.

1. First I created two test tables, TABLE_MASTER and TABLE_DETAIL.

2. Note: I did not create any Primary Key or Index!

3. I coded SQL query in Editor as shown bellow, then I marked SQL query using CTRL + A and finaly pressed CTRL + E for explain plan, also shown bellow.

4. In the image above we can see that the overall Cost is 12,969, Bytes:122.610.129 and Cardinality: 53,147. Also TABLE ACCESS FULL is written in red, which is something that we would like to avoid in Explain Plan.

5. Lets do some SQL tuning. Looking at the WHERE clause inside our SQL statement, we can see join m.id = d.master_id and m.served_pdp_address = ‘176.76.6.130’. Now we will create a Primary Key and Index.

Create Primary Key and Index on table TABLE_MASTER

CREATE UNIQUE INDEX TABLE_MASTER_PK ON TABLE_MASTER
(ID)
LOGGING
NOPARALLEL;

ALTER TABLE TABLE_MASTER
  ADD CONSTRAINT TABLE_MASTER_PK
  PRIMARY KEY (ID);
CREATE INDEX TABLE_MASTER_IDX ON TABLE_MASTER
(SERVED_PDP_ADDRESS)
LOGGING
NOPARALLEL;

6. Create Index on table TABLE_DETAIL

CREATE INDEX TABLE_DETAIL_IDX ON TABLE_DETAIL
(MASTER_ID)
LOGGING
NOPARALLEL;

7. Lets run Explain Plan, once again using keyboard shortcut CTRL + E.

8. Do you see the improvement we made in SQL tuning? The cost is now 74, Bytes: 5.096.163 and Cardinality: 2,209.

You will find more tips and tricks for query optimization on page SQL PERFORMANCE TUNING .