| Need help - Tuning the SQL [message #255070] |
Mon, 30 July 2007 10:00  |
ritesh15j
Messages: 1
Registered: July 2007
Location: Durham
|
Junior Member |
|
|
Dear Sir/ Madam,
I am attaching the below query which is taking almost more then 1 Mins to retrieve first row from the Database.
Kindly have a look at it and try to fine tune this query.
This Query include following record in the Query and there details are :
1.Record Name - PS_JOB
Key Field - EMPLID, EMPL_RCD, EFFDT, EFFSEQ
2. Record Name - PS_PERSON
Key Field - EMPLID
3. Record Name - PS_ACTN_REASON_TBL
Key Field - ACTION, ACTION_REASON, EFFDT
4. Record Name - XLATTABLE
Key Field - FIELDNAME, LANGUAGE_CD, FIELDVALUE, EFFDT
Query :
Please find it in the Attachment.
Kindly let me know if you need any inputs from my side. Your help is much Appreciated.
Thanks & Regards
Ritesh Jain
-
Attachment: Query.txt
(Size: 1.75KB, Downloaded 1472 times)
|
|
|
|
| Re: Need help - Tuning the SQL [message #255075 is a reply to message #255070] |
Mon, 30 July 2007 10:09  |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Have a look at the execution plan of the query.
Connect with SQLPlus, set autotrace, and execute your query.
Example With a simple "select * from dual" :
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> set autotrace trace;
SQL> select * from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
201 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
