Home » RDBMS Server » Performance Tuning » Need help - Tuning the SQL
Need help - Tuning the SQL [message #255070] Mon, 30 July 2007 10:00 Go to next message
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 1574 times)
Re: Need help - Tuning the SQL [message #255075 is a reply to message #255070] Mon, 30 July 2007 10:09 Go to previous messageGo to next message
ThomasG
Messages: 3212
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>
Re: Need help - Tuning the SQL [message #255092 is a reply to message #255070] Mon, 30 July 2007 10:54 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read the "sticky" in Performance tuning forum.

Regards
Michel
Previous Topic: how to increase a performance of a table in oracle 10g (merged)
Next Topic: View doesn't use Index
Goto Forum:
  


Current Time: Sat Nov 23 08:43:39 CST 2024