Closed

Oracle distributed query optimization

Looking for tuning help on Oracle SQL block. I have written the query myself and it works fine. I just want it to execute more efficiently and hopefully faster.

1. Query is 4500 lines, but it is composed of about 25 shorter and similar blocks.

2. It currently is executed in the scheduler as a stored procedure

3. Query updates tables on one schema, but all using “Merge” over a linked database where all the source tables exist.

a. It looks to the last 3 days of changed data.

4. There are no materialized view logs available nor can I create anything on the linked database.

5. I am unable to provide direct access to the databases or network

a. I will provide the SQL

b. For each table I can provide the number of rows in the table and any indexes

c. There are about 25 total tables and each is used multiple times

d. I can provide an approximation of how many rows the queries would result in

e. I cannot make any new indexes on the source database

f. I have little access to statistics or other things on the source database

6. Both databases are 12c Enterprise [login to view URL] and are 64bit

7. I suspect most of the optimization is going to come from hints in the queries and other ways of optimizing how best to manage the distributed nature of the query.

Skills: Database Administration, Database Development, Database Programming, Oracle, SQL

See more: oracle improve dblink performance, oracle driving_site hint insert, oracle driving_site hint multiple tables, dblink hint driving_site, oracle 11g dblink performance issues, oracle driving_site hint subquery, oracle dblink slow performance, how to use driving_site hint in oracle, looking for freelance oracle apex developers in south africa, looking for dba oracle, looking for a marketing company to help and market my b b in cape town, looking for a developer programmer job which has php java oracle postgres or mysql requirements, list of companies looking for oracle dba australia, im looking for oracle it, i am looking for a freelance oracle apex developer, company looking for oracle developer in qatar, brent d who is looking for a freelance graphic artist for his clothing company, looking for oracle job saudi arabia, looking for freelance oracle sde development houston consultant, i am looking for remote desktop support techs to handle virus removal diagnosis optimization data back up

About the Employer:
( 1 review ) West Bloomfield, United States

Project ID: #17134476

28 freelancers are bidding on average $185 for this job

adeeladeel1234

Hi Hope you doing Good I got your scenario , actually i have more than 10 years of Experience in SQL,PLSQL as well as oracle database Performance Tuning and SQL Optimization and Tuning .Please share your code and Fu More

$200 USD in 1 day
(2 Reviews)
0.0
$155 USD in 3 days
(0 Reviews)
0.0
vw8113236vw

Thanks for providing the information. I am new to this freelancer portal but have good real world working exposure. Would be great to get a opportunity to work together. Looking forward for your positive resp More

$155 USD in 3 days
(0 Reviews)
0.0
$222 USD in 5 days
(0 Reviews)
0.0
PMAKARM

• Implementing, support and maintaining Oracle Financial modules Account Receivables, Account Payable ,GL ,Inventory , Purchasing, Order Management , Purchasing and modules for R12 version. • Sharing in Oracle R12 up More

$30 USD in 1 day
(0 Reviews)
0.0
$155 USD in 3 days
(0 Reviews)
0.0
$255 USD in 5 days
(0 Reviews)
0.0
$77 USD in 3 days
(0 Reviews)
0.0
vasanthsesumnit

with 10+ year experience in PLSQL performance tuning and optimization. The SQL can be tuning after verification, For taking over this project, I may need the current timings of PL/SQL run for each blocks and expl More

$133 USD in 3 days
(0 Reviews)
0.0
sumanthplsql

 Having around 4 Years of experience in IT industry as a Programmer (PL/SQL Developer) as well as production support engineer.  Experience in SQL and PL/SQL.  Experience in writing, Cursors, Joins, Exceptions, Sub More

$155 USD in 3 days
(0 Reviews)
0.0
dzdzegc

Had a lot of work with sql queries for reports for credit organizations, also used to optimize that.

$200 USD in 1 day
(6 Reviews)
0.0
mithlesh1984

I have 12 years experience as Oracle DBA. I have real time experience in RAC, Data Guard, Golden Gate and database & query tuning.

$155 USD in 3 days
(0 Reviews)
0.0
Allatonesolution

We woked with application team, mostly from financial sector. having very good exposure to tune of log reports queries. for more please check [login to view URL]

$511 USD in 3 days
(0 Reviews)
0.0
jyotigiri001

Hello , I have about 7 yrs of experience in Oracle PL/SQL and have worked on performance enhancements. I have worked with fortune 10 companies and delivered high class code . Thanks, Jyoti

$88 USD in 2 days
(0 Reviews)
0.0
$166 USD in 5 days
(0 Reviews)
0.0
ysmaelms

It has more than 8 years of experience working with Oracle, for your requirement we can use many new features that brings the Oracle version 12c, such as RESULT CACHE, we hope you contact us and can coordinate a good d More

$247 USD in 4 days
(0 Reviews)
0.0
truongnguyen86

Hello there, I’ve worked with so over 10k lines before, I think to tune this so we should go by each block, and if you can provide the cost for each block it will be good for tuning Relevant Skills and Experience Data More

$250 USD in 5 days
(165 Reviews)
0.0
jagdishbhatt38

I am having more than 9 years of experience in sql server 2005/2008/2012/2014/2016 and oracle(pl/sql). I can provide you better solution by utilizing my knowledge and skills.

$250 USD in 6 days
(28 Reviews)
0.0
Tanmaya90

My expertise in performance tuning for better sql optimization. Relevant Skills and Experience Need to Chek where exactly performance is dropping. That may be because of db(Memory latches) or in the sql itself.

$151 USD in 4 days
(0 Reviews)
0.0
voronkov

Hello, I work with Oracle many years. I can advice these approaches: 1. Try to make copy of remote tables in your database, create indexes and collect statistic, after this update your tables, do not use MERGE. Clea More

$222 USD in 10 days
(1 Review)
0.0