Home » RDBMS Server » Performance Tuning » SQL Tuning
SQL Tuning [message #65626] Wed, 17 November 2004 08:53 Go to next message
Sounder S. Raja
Messages: 4
Registered: November 2003
Junior Member
Hi,

I did an explain plan on the following query. The plan is appended below. I am wondering if there is anyway to tune this query. I tried essentially moving things around (order of the tables and the WHERE and AND clauses) but nothing changed. I am a novice to performance tuning so please bear with me.

Many thanks in advance!

Sankar.

Query:
=====

EXPLAIN PLAN
SET STATEMENT_ID = 'SMTEST02'
FOR SELECT a.deal_id,
a.customer_company_nm,
a.deal_nm,
a.deal_revenue,
a.deal_status_val,
a.create_dt deal_action_date,
b.sun_id sales_rep_id,
c.first_nm sales_rep_first_nm,
c.last_nm sales_rep_last_nm
FROM nsda_user c,
deal_account_team b,
deal a
WHERE b.title_nm = 'PRIMARY SALES REP'
AND a.deal_id = b.deal_id
AND b.sun_id = c.sun_id (+);

===========================================
Table Counts : FYI
===========================================

SQL> select count(*) from deal_account_team;

COUNT(*)
----------
10000

SQL> select count(*) from nsda_user;

COUNT(*)
----------
34

SQL> select count(*) from deal;

COUNT(*)
----------
30000

========================================

Plan Output:
===========

SELECT
lpad(' ',level-1)||operation||' '||options||' '||object_name "Query Plan",
cardinality "Rows",
cost "Cost"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = 'SMTEST02'
START WITH id = 0
ORDER BY id;

Query Plan Rows Cost
---------- ---- ----

SELECT STATEMENT 10000 47

HASH JOIN 10000 47

HASH JOIN OUTER 10000 11

INDEX FAST FULL SCAN SYS_C00156567 10000 5

TABLE ACCESS FULL NSDA_USER 34 2

TABLE ACCESS FULL DEAL 30000 30
Re: SQL Tuning [message #65628 is a reply to message #65626] Wed, 17 November 2004 10:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please post the plan in the following format.
You can use AUTOTTRACE FEATURE.
execute the sql.
NO OUTPUT of the sql is returned ...just plan and stats are returned.

mag@mutation_mutation > set autotrace traceonly exp stat
mag@mutation_mutation > select * from emp;

14 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'

Statistics
----------------------------------------------------------
        190  recursive calls
          0  db block gets
         33  consistent gets
          7  physical reads
          0  redo size
       1495  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed


anyhow based on the plan posted, see whether deal_account_team and deal tables have proper indexes on the columns used in the where clause.
Re: SQL Tuning [message #65644 is a reply to message #65628] Sat, 20 November 2004 06:24 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
For a convenient way to use Explain Plan in SQL*Plus, try xplan.sql. This reports the plan output for the current SQL statement.

On this forum you can post formatted code by placing it within
</b>...<b>
tags; for example:

SQL> prompt &_O_VERSION
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Developer's Release
With the Partitioning and Oracle Data Mining options

SQL> SELECT * FROM small_table WHERE object_id = 1
  2  
SQL> 
SQL> @xplan

----------------------------------------------------------------------------
| Id  | Operation                   |  Name        | Rows  | Bytes | Cost  
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    72 |     1 
|   1 |  TABLE ACCESS BY INDEX ROWID| SMALL_TABLE  |     1 |    72 |     1 
&#124*  2 |   INDEX UNIQUE SCAN         | SMALL_PK     |   203 |       |       
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SMALL_TABLE"."OBJECT_ID"=1)

Note: cpu costing is off
Re: SQL Tuning [message #65647 is a reply to message #65644] Mon, 22 November 2004 03:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The utility is much better~!.
Is there anyway to to add the statistics? I am more intrested in stats (like consistent gets) than the cost!.
Many Thanks!
Statistics
----------------------------------------------------------        
190  recursive calls          
0  db block gets         
33  consistent gets          
7  physical reads

Re: SQL Tuning [message #65654 is a reply to message #65644] Tue, 23 November 2004 12:55 Go to previous messageGo to next message
Sounder S. Raja
Messages: 4
Registered: November 2003
Junior Member
Thank you Mahesh and William, for your suggestions. The queries you provided indeed gave more information that I had before. The problem seems to be resolved now. I will open a new entry if it pops up again. Many thanks.
Re: SQL Tuning [message #65673 is a reply to message #65626] Mon, 29 November 2004 19:07 Go to previous message
Venkat
Messages: 110
Registered: February 2001
Senior Member
what IS THE significance OF HASH JOIN IN execution PLAN

iam using the function in a sql query.
iam comparing the date column of a table with some function (logical date function ).

which type of the index will help to optiize this query
Previous Topic: creating user / roles with maximum CPU percentage
Next Topic: Using Index
Goto Forum:
  


Current Time: Tue Sep 29 10:27:53 CDT 2020