Forum Stats

  • 3,734,267 Users
  • 2,246,934 Discussions
  • 7,857,216 Comments

Discussions

Improve the performance speed of Oracle with pushing data to a remote server using a DB link

3251409
3251409 Member Posts: 8
edited Oct 21, 2016 10:56PM in General Database Discussions

Hello,

Recently I have been trying to figure out ways to improve the performance of a concurrent program that moves data over to another server using a DB link. In the past the concurrent program would update all of the records on remote database within 30 minutes. Now the concurrent program runs for hours as our databases have become quite large in size. Any suggestions on how to improve the speed of the information being pushed over to the remote database?

Thanks!

Tagged:
3251409

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Oct 21, 2016 4:11PM
    3251409 wrote:Hello,Recently I have been trying to figure out ways to improve the performance of a concurrent program that moves data over to another server using a DB link. In the past the concurrent program would update all of the records on remote database within 30 minutes. Now the concurrent program runs for hours as our databases have become quite large in size. Any suggestions on how to improve the speed of the information being pushed over to the remote database?Thanks!

    Get faster hardware.

    How to improve the performance of my query? / My query is running slow.

     


  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited Oct 21, 2016 4:12PM
    3251409 wrote:Hello,Recently I have been trying to figure out ways to improve the performance of a concurrent program that moves data over to another server using a DB link. In the past the concurrent program would update all of the records on remote database within 30 minutes. Now the concurrent program runs for hours as our databases have become quite large in size. Any suggestions on how to improve the speed of the information being pushed over to the remote database?Thanks!

    What process are you using to move the data? Do you have MView setup to replicate data from source to target or what specific method are you using? Do use parallel?

  • jgarry
    jgarry Member Posts: 13,842
    edited Oct 21, 2016 4:13PM

    Materialized views, depending on version and whether your metadata and purpose are compatible.

    Golden Gate.

    Replication.

    Fix the program to only move necessary data.

    Pluggable tablespaces.

    expdp impdp with network parameter.

    What version are you on, and what are you doing?

  • 3251409
    3251409 Member Posts: 8
    edited Oct 21, 2016 4:31PM

    No so far materialized views aren't setup to replicate the data from the source to target. I am using PL/SQL statements and a cursor to grab records from the Master table, and passing this cursor into a separate function where insert statements are used to insert a new record, update statements are used to update a record, and delete statements are used to delete records. I'm new to materialized views, I understand the concept but not really sure how to implement one.

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited Oct 21, 2016 4:38PM

    I see. I'll suggest you read up on Materialized view. It's not that hard to implement and I believe that it can save you time and give you less headache. You can get up to speed within 10 mins.

    3 Materialized View Concepts and Architecture

    https://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm

    32514093251409
  • 3251409
    3251409 Member Posts: 8
    edited Oct 21, 2016 4:57PM

    Thanks for the link! I will definitely read through it. How are materialized views different than temporary tables? The data I am remotely porting over is first pushed to an interface table using triggers. The data from the interface table is what's being pushed out remotely to another database. Since the data is being read from only one source how would a materialized view be any different from just pushing out data from the interface table directly to another table on the remote database? A materialized view is only a snapshot of an instance in time of the master data table right?

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Oct 21, 2016 8:48PM
    3251409 wrote:Hello,Recently I have been trying to figure out ways to improve the performance of a concurrent program that moves data over to another server using a DB link. In the past the concurrent program would update all of the records on remote database within 30 minutes. Now the concurrent program runs for hours as our databases have become quite large in size. Any suggestions on how to improve the speed of the information being pushed over to the remote database?Thanks!

    What is this concurrent program? What does it actually do? Have you used extended sql trace to see where the time is going?

    I would suggest you ensure you are only updating records that have updated and you have an efficient (but correct) way of finding this data. Ensure you are using bulk operations (and that doesn't mean use bulk collect!) and take full advantage of the power of SQL. Materialized views are usually appropriate with materialized view logs to support fast refresh.

  • Unknown
    edited Oct 21, 2016 10:56PM
     Any suggestions on how to improve the speed of the information being pushed over to the remote database?

    No - it is not possible to offer suggestions about some unknown process that unknown things to unknown data.

    You need to provide info about:

    1. WHAT you do

    2. HOW you do it

    3. WHAT results you get

    All you told us is it used to take 30 minutes but now your data is 'large' it takes hours.

    Ok - if 'pushing' 1 GB takes 30 minutes then  wouldn't you EXPECT it to take 'hours' to push 10 GB?

    See the problem when we don't have any info to work with. Based ONLY on what you posted we have no reason to think you have any problem at all.

    And before you start talking about 'solutions' (MVs or otherwise) you first have to know:

    1. WHAT the actual problem is

    2. WHAT the cause(s) of the problem are

    3. WHAT the REQUIREMENTS of the solution are

    4. WHAT possible solutions meet those 'requirements'.

    What are the requirements? Offline pushes once a night? Online pushes in real-time?

This discussion has been closed.