OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » SQL Tuning

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: SQL Tuning [message #65628 is a reply to message #65626]

Wed, 17 November 2004 10:17

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

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 </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

Report message to a moderator

Re: SQL Tuning [message #65647 is a reply to message #65644]

Mon, 22 November 2004 03:05

Mahesh Rajendran
Messages: 10708
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

Report message to a moderator

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.

Report message to a moderator

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

Report message to a moderator

Previous Topic:	creating user / roles with maximum CPU percentage
Next Topic:	Using Index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Oct 31 02:40:45 CDT 2025