OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » sql performance

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

sql performance [message #284293]

Thu, 29 November 2007 05:51

sellafrica
Messages: 68
Registered: July 2005
Location: Braynston

Member

Good Day all,

I have the following sqlplus statements that are being run in the order below,and somehow they are slow.i have also put the explain plan results,can you please assisting in what. i can possebly do to make them a bit fast

The sql

// First SQL

SELECT C.ContactPersonNumber,
C.EventStartDate,
C.NoteNumber,
C.LocationName,
E.EmployeeNumber,
E.Name1 as EmployeeName,
DECODE(E.Gender, '-1', '', '0', 'Male', '1', 'Female') as Gender,
DECODE(E.EmployeePopup1, '-1', '', '0', 'A', '1', 'C', '2', 'I', '3', 'W') as EthnicGroup
FROM pwc.ContactHeader C, pwc.Employee E
WHERE C.ContactPersonNumber = E.EmployeeNumber and
C.LocationName >= '0000000' AND C.LocationName <= '9999999' and
C.EventStartDate <= '2007.11.01' AND ( C.EventEndingDate >= '2007.11.01' OR C.EventEndingDate = ' ' )
ORDER BY C.ContactPersonNumber,C.EventStartDate

//Second SQL

SELECT C.ContactPersonNumber as EmployeeNumber,
DECODE(C.ContactPersonPopup1, '-1', '', '0', 'No', '1', 'Yes') as SACitizen,
D.FromDate as EffectiveDate
FROM pwc.ContactPerson C,
pwc.TheOption T,
pwc.DimCombVersionHeader H,
pwc.DimCombVersionLine D
WHERE C.ContactPersonNumber = H.Key1 and
D.DimCombVersionNumber = H.DimCombVersionNumber and
C.ContactPersonNumber = '110001' and
D.SelectedOption = T.Name and
T.OptionListNumber = 'Action reason'
AND T.Description = 'Active'

//Third SQL

SELECT *
FROM NoteLine
WHERE NoteNumber = '^1' and
NoteType = '^2'

Their explain Plans
//1st Query

Access Access Object
Cost ID P_ID Plan Path Name
---- ---- ---- ------------------------------ --------------- ---------------
2780 0 SELECT STATEMENT
2780 1 0 SORT ORDER BY
1876 2 1 HASH JOIN
140 3 2 TABLE ACCESS FULL EMPLOYEE
1735 4 2 TABLE ACCESS FULL CONTACTHEADER

//2nd Query

Access Access Object
Cost ID P_ID Plan Path Name
---- ---- ---- ------------------------------ --------------- ---------------
19 0 SELECT STATEMENT
19 1 0 MERGE JOIN CARTESIAN
1 2 1 TABLE ACCESS BY INDEX ROWID THEOPTION
17 3 2 NESTED LOOPS
16 4 3 NESTED LOOPS
13 5 4 TABLE ACCESS BY INDEX ROWID DIMCOMBVERSIONHEADER

12 6 5 INDEX SKIP SCAN DIMCOMBVERSHEAD02

3 7 4 TABLE ACCESS BY INDEX ROWID DIMCOMBVERSIONLINE

2 8 7 INDEX RANGE SCAN DIMCOMBVERSLINE 01

1 9 3 INDEX RANGE SCAN THEOPTION01
18 10 1 BUFFER SORT
2 11 10 TABLE ACCESS BY INDEX ROWID CONTACTPERSON
1 12 11 INDEX RANGE SCAN CONTACTPERSON01

//3nd Query

Access Access Object
Cost ID P_ID Plan Path Name
---- ---- ---- ------------------------------ --------------- ---------------
4 0 SELECT STATEMENT
4 1 0 TABLE ACCESS BY INDEX ROWID NOTELINE
3 2 1 INDEX RANGE SCAN NOTELINE2

Report message to a moderator

Re: sql performance [message #284303 is a reply to message #284293]

Thu, 29 November 2007 06:01

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

1. Did you read OraFAQ forms guide ?
2. I can say "not Full table scan is bad or not Index scan is good"
3. You provide us insufficient information.
like: how many rows in tables?
table statistics is up-to-date
how many indexes on table and their position.
Oracle Version
Optimizer parameter value

Please read Oracle Documentation at "http://tahiti.oracle.com" and just search "execution" ..you will get your all answer in full details.

[Updated on: Thu, 29 November 2007 06:05]

Report message to a moderator

Re: sql performance [message #284853 is a reply to message #284303]

Sat, 01 December 2007 20:15

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Query 1:
You are joining the entire pwc.ContactHeader and pwc.Employee tables. I guess they are pretty big.

You probably think that

C.EventStartDate <= '2007.11.01' 
AND ( C.EventEndingDate >= '2007.11.01' OR C.EventEndingDate = ' ' )

is pretty helpful to the query. It's not.

Oracle cannot perform range-scans over two columns.

There might be nothing you can do about this one. Tell us the number of rows in each table, and the number of rows in ContractHeader that match the date condition above.

Soory I can't answer the others, I gotta go now.

Ross Leishman

Report message to a moderator

Re: sql performance [message #284855 is a reply to message #284293]

Sat, 01 December 2007 21:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

From SQL*Plus DESCRIBE every table in the 3 SQL statements & post results nicely formated using <code tags> and documented in STICKY post at top of this forum as found in URL below
http://www.orafaq.com/forum/t/88153/0/

[Updated on: Sat, 01 December 2007 21:01] by Moderator

Report message to a moderator

Re: sql performance [message #284915 is a reply to message #284855]

Sun, 02 December 2007 20:12

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Query 2:

Key1 is the second column of index DIMCOMBVERSHEAD02. It would help if it were the leading column.

You are not joining any of the other tables to pwc.ContactPerson. Is that because you are selecting on a unique key (C.ContactPersonNumber = '110001'). If so, then that index should be defined as UNIQUE. If it is not unique, then how many rows are there for 110001?

Which columns are in index THEOPTION01? I gues that NAME is the leading column, but it would be helpful if that index contained (NAME, OPTIONLISTNUMBER, DESCRIPTION).

Query 3:

Which columns are in index NOTELINE2?

It would help if that index contained both (NoteNumber,NoteType) as leading columns.

Ross Leishman

Report message to a moderator

Previous Topic:	Weird client performance problem
Next Topic:	Performance Degrads

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 15 21:12:51 CDT 2024