SQL Tuning [message #65626] |
Wed, 17 November 2004 08:53 |
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 #65644 is a reply to message #65628] |
Sat, 20 November 2004 06:24 |
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 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
|* 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 #65654 is a reply to message #65644] |
Tue, 23 November 2004 12:55 |
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 |
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
|
|
|