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.
29 freelancers are bidding on average $184 for this job
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.
Hey hi let’s have quick discussion. I hope I could help you in this regards. After all performance tuning need lots of parameters to consider but I will try my best.