Home » RDBMS Server » Performance Tuning » sql performance
sql performance [message #284293] |
Thu, 29 November 2007 05:51 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: sql performance [message #284303 is a reply to message #284293] |
Thu, 29 November 2007 06:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Mohammad Taj
Messages: 2412 Registered: September 2006 Location: Dubai, UAE
|
Senior Member |
![star_taj%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: sql performance [message #284915 is a reply to message #284855] |
Sun, 02 December 2007 20:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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
|
|
|
Goto Forum:
Current Time: Wed Sep 11 12:17:05 CDT 2024
|