Closed

Oracle/SQL Query Optimisations

We have an enterprise solution and have recently lost our primary DBA resource. We have a small number of specific database queries/updates that are causing major performance issues for a number of clients.

In addition, we are not currently using BIND VARIABLES in either SQL or Oracle and would seek input/guidance on adoption thereof.

Our application is an [login to view URL] webforms solution.

We are seeking DBA support to help on this.

This is a typical query (in Oracle format):

SELECT * FROM (SELECT JOB_HEADERS_FW.*, COALESCE(STATUS_CODES_FW.DESCRIPTION_FW,JOB_HEADERS_FW.JOB_STATUS_FW) AS JOB_STATUS, COALESCE(JOB_REASONS_FW.DESCRIPTION_FW,JOB_HEADERS_FW.REASON_CODE_FW) AS REASON_CODE, COALESCE(FITTERS_FW.FITTER_NAME_FW,JOB_HEADERS_FW.FITTER_FW) AS FITTER_NAME, (SELECT SUM(COALESCE(STANDARD_HOURS_FW,HOURS_FW)) FROM JOB_DETAILS_fW WHERE JOB_DETAILS_FW.JOB_NUMBER_FW=JOB_HEADERS_FW.JOB_NUMBER_FW AND JOB_DETAILS_FW.JOB_DETAIL_STATUS_FW='FINISH' AND (JOB_DETAILS_FW.JOB_TYPE_FW = 'L' OR (JOB_DETAILS_FW.JOB_TYPE_FW IS NULL OR LENGTH(JOB_DETAILS_FW.JOB_TYPE_FW) = 0) ) ) AS STANDARD_COMPLETED, (SELECT SUM(COALESCE(STANDARD_HOURS_FW,HOURS_FW)) FROM JOB_DETAILS_fW WHERE JOB_DETAILS_FW.JOB_NUMBER_FW=JOB_HEADERS_FW.JOB_NUMBER_FW AND (JOB_DETAILS_FW.JOB_TYPE_FW = 'L' OR (JOB_DETAILS_FW.JOB_TYPE_FW IS NULL OR LENGTH(JOB_DETAILS_FW.JOB_TYPE_FW) = 0) ) ) AS STANDARD_TOTAL, STATUS_CODES_FW.BACKGROUND_COLOUR_FW FROM JOB_HEADERS_FW LEFT OUTER JOIN JOB_REASONS_FW ON JOB_HEADERS_FW.REASON_CODE_FW=JOB_REASONS_FW.CODE_FW AND JOB_REASONS_FW.ARCHIVE_STATUS_FW='N' LEFT OUTER JOIN FITTERS_FW ON JOB_HEADERS_FW.FITTER_FW=FITTERS_FW.FITTER_FW AND FITTERS_FW.ARCHIVE_STATUS_FW='N' LEFT OUTER JOIN STATUS_CODES_FW ON JOB_HEADERS_FW.JOB_STATUS_FW=STATUS_CODES_FW.STATUS_CODE_FW AND STATUS_CODES_FW.JOB_HEADERS_FW=1 AND STATUS_CODES_FW.ARCHIVE_STATUS_FW='N' WHERE JOB_HEADERS_FW.ARCHIVE_STATUS_FW='N' AND (JOB_HEADERS_FW.DEPOT_ID_FW='MDNK1N' OR (JOB_HEADERS_FW.DEPOT_ID_FW IS NULL OR LENGTH(JOB_HEADERS_FW.DEPOT_ID_FW) = 0)) AND (NOT (STATUS_CODES_FW.CLOSING_FW=1 OR STATUS_CODES_FW.EXCLUDE_WORKSHOP_SCHEDULE_FW =1) OR JOB_HEADERS_FW.JOB_END_DATE_FW=TO_DATE('2018-05-14', 'yyyy-mm-dd')) AND JOB_HEADERS_FW.JOB_STATUS_FW<>'FINISH' AND JOB_HEADERS_FW.JOB_NUMBER_FW IN (SELECT DISTINCT JOB_NUMBER_FW FROM JOB_DETAILS_FW WHERE ARCHIVE_STATUS_FW ='N' AND FITTER_FW ='8226157841' AND (JOB_DETAIL_STATUS_FW IN ('','WND','WIP','AWP','FINISH') OR JOB_DETAIL_STATUS_FW IS NULL ) AND (JOB_DETAILS_FW.JOB_TYPE_FW = 'L' OR (JOB_DETAILS_FW.JOB_TYPE_FW IS NULL OR LENGTH(JOB_DETAILS_FW.JOB_TYPE_FW) = 0) ) UNION ALL (SELECT DISTINCT JOB_NUMBER_FW FROM JOB_DETAILS_FW WHERE ARCHIVE_STATUS_FW ='N' AND FITTER_FW ='8226157841' AND JOB_DETAIL_STATUS_FW IN ('COMPLETED','DFT','CHO') AND (JOB_DETAILS_FW.JOB_TYPE_FW = 'L' OR (JOB_DETAILS_FW.JOB_TYPE_FW IS NULL OR LENGTH(JOB_DETAILS_FW.JOB_TYPE_FW) = 0) ) AND JOB_NUMBER_FW NOT IN ( (SELECT DISTINCT JOB_NUMBER_FW FROM JOB_DETAILS_FW WHERE FITTER_FW = '8226157841' AND ARCHIVE_STATUS_FW = 'N' ) INTERSECT (SELECT DISTINCT JOB_NUMBER_FW FROM JOB_DETAILS_FW WHERE ARCHIVE_STATUS_FW ='N' AND FITTER_FW <>'8226157841' AND (JOB_DETAIL_STATUS_FW IN('','WND','WIP','AWP') OR JOB_DETAIL_STATUS_FW IS NULL ) AND (JOB_DETAILS_FW.JOB_TYPE_FW = 'L' OR (JOB_DETAILS_FW.JOB_TYPE_FW IS NULL OR LENGTH(JOB_DETAILS_FW.JOB_TYPE_FW) = 0) ) )) )) AND JOB_STATUS_FW<>'CLOSED' AND JOB_STATUS_FW<>'COMPLETE' AND JOB_STATUS_FW<>'WFS_COMPLETE_FW' AND JOB_STATUS_FW<>'DFT' AND JOB_STATUS_FW<>'DEFERRED' AND JOB_STATUS_FW<>'WFS_C_FW' AND JOB_STATUS_FW<>'A' AND JOB_STATUS_FW<>'WFS_DFT_FW' AND STATUS_CODES_FW.CLOSING_FW=0) tmpsqltop WHERE ROWNUM <= 50 ORDER BY FITTER_NAME,PRIORITY_FW,JOB_DATE_FW,JOB_NUMBER_FW

Skills: Database Administration, Database Programming, Microsoft SQL Server, Oracle, SQL

See more: oracle sql format currency query, formatting decimal places oracle sql query, oracle sql query displaying, oracle query optimization tool, oracle sql optimizer, query optimization in sql server, performance tuning in oracle 11g with examples, sql query optimization, query optimization in oracle with example, how to make select query faster in oracle, oracle query optimization interview questions, sql, database administration, oracle, database development, database programming, oracle sql query display lastname dont manager, oracle sql order date query example, oracle sql query display larger, write query top salary oracle sql

About the Employer:
( 0 reviews ) United Kingdom

Project ID: #17081358

60 freelancers are bidding on average £31/hour for this job

truongnguyen86

Hello there, i'm expert on Oracle, i'm also Oracle DBA, i've done many tuning job before with many DBMS including oracle, sql server, mysql. After seeing your queries, i can see where it make slows: order by, too many More

£30 GBP / hour
(173 Reviews)
6.7
aanvikm

Hello Sir, I am an expert with query optimization and would like to discuss this project with you. Requesting you to open chat for discussion. Thanks Aanvik

£30 GBP / hour
(42 Reviews)
6.3
Yknox

Dear employer. I am Gang Lee, web developer . I've just read your job posting and I'm very interested. I'm a certificated freelancer with almost 1000 good reviews from clients. I'm a Good ASP.NET , MS SQL ,Oracle, More

£30 GBP / hour
(18 Reviews)
6.0
sopholos

I'm experienced SQL Server and Oracle DB. Your query looks very difficult and I'm not surprised that you have some problems with performance on it. These checks with lengths could eliminate any indexes (even in case More

£52 GBP / hour
(18 Reviews)
5.8
jagdishbhatt38

I am having more than 9 years of experience in IT industry.I have worked on .net(c#.net,vb.net,asp,asp.net,mvc) with sql server 2005/2008/2012/2014/2016 and oracle(pl/sql). I can provide you better solution by utili More

£30 GBP / hour
(33 Reviews)
5.2
qualifiedcoders

Hi, Good Day. I have read your post and would like to discuss things further as I am an expert database developer and administrator as I am having advanced knowledge of sql and query optimizations.I have worked i More

£30 GBP / hour
(13 Reviews)
5.2
£20 GBP / hour
(59 Reviews)
5.4
zyeloncomputech

Hi sir, Thank you for giving opportunity for biding... we have gone through your requirements and we can do Oracle/SQL Query Optimization tasks according to your exact requirements. Please Before Awarding this More

£18 GBP / hour
(2 Reviews)
4.6
jlsproject

Hello. My name is Denis. I am not DBA, but I have big experience in SQL tuning and optimization. I have 10+ years experience in Oracle (9i-12c), SQL, SQL Tuning, PL/SQL. I worked with Toad, SQL Navigator, PL/SQL More

£30 GBP / hour
(24 Reviews)
4.6
sreenivas2903

Hi i could able to perform optimizations

£33 GBP / hour
(7 Reviews)
3.3
dzdzegc

Hello! I have experience in enterprise etl/dwh development using Oracle db for 5 years. Projects i've been working for were for credit organizations with huge amount of data, big projects: terrabytes of data, avg proj More

£26 GBP / hour
(7 Reviews)
3.4
clagtech

Hi, I have 8+ year’s experience in web development, software development using different platform with HTML5, CSS3, ASP.NET/C#, JavaScript, BootStrap etc. Application Proficiencies – C#, MVC, ASP.NET, JavaScript. More

£30 GBP / hour
(1 Review)
2.6
pratikgedia

As an Oracle Database Administrator have performance tuned SQL's using SQL hints, Tuning Advisor,Segment Advisor, ADDM reports,ASH reports,AWR reports. Could we please have an discussion? Thanks,

£22 GBP / hour
(3 Reviews)
2.3
donovan6

I'm an Oracle DBA/Developer with over 15 years experience and that is one of the most horrible queries I have ever seen - I'm not surprised your users are having performance issues. I can sort this out for you but I More

£26 GBP / hour
(1 Review)
2.1
£33 GBP / hour
(2 Reviews)
2.3
asif2478

hi there! I am an expert in oracle database management and administration. I have already completed similar tasks with high reviews. I can provide you the MOST OPTIMIZED AND FASTEST FUNCTIONING SQL QUERIES. It s More

£25 GBP / hour
(4 Reviews)
2.4
kurbsky

I've selected the most time consuming query from your data (that with duration of 747,89) and tried to rewrite it. Please tell me whether this attempt brings any improvement. select * from ( select SH.RECORD_NUMB More

£24 GBP / hour
(4 Reviews)
1.7
£30 GBP / hour
(0 Reviews)
0.0
£22 GBP / hour
(0 Reviews)
0.0
adilruk

My Profile: [login to view URL] I've lot of experience of database optimization both instance tuning and SQL tuning. have been used lot of mechanisms (indexing, partitioning, query analyzing, p More

£27 GBP / hour
(0 Reviews)
0.0