Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Performance issue with Oracle Views

  1. #1
    Join Date
    Dec 2010
    Posts
    193

    Question Performance issue with Oracle Views

    Hi,

    We are using Oracle DB connection via JDBC inside Table Input step to extract the data into delimited file. After setting defaultRowFetch the performance has increased dramatically. But still when comparing the statistics , the time taken to fetch the same amount of data from Table differs so much with data pulled from a view. How to increase the performance in case of Oracle views?

    One more thing, will restrictGettTables parameter has any effect on performance....

    Please help
    Sathish
    Back to Pentaho


    'Be the best Pearl in the ocean of wisdom'

  2. #2
    Join Date
    Sep 2007
    Posts
    16

    Default

    Just to make sure it is not a typo. The JDBC Parameter for the Oracle Driver is called defaultRowPrefetch.

    Do you see the difference in performance without using PDI too?
    Could you try to use the views query in the table input to make a comparison?

  3. #3
    Join Date
    Dec 2010
    Posts
    193

    Default

    Exactly.Sorry for the typo error, its defaultRowPrefetch only.

    Running the select count(*)=60000 from view itself taking time to fetch output (more than 10 mins) using Oracle Developer client and the database is not residing in the same server.

    The last possible option running the create view query inside a Table input will not work for us , since we perform loading for a series of tables by parameterizing the table names......
    Sathish
    Back to Pentaho


    'Be the best Pearl in the ocean of wisdom'

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by satran View Post
    the time taken to fetch the same amount of data from Table differs so much with data pulled from a view. How to increase the performance in case of Oracle views?
    That depends on what the view is doing.
    If you trigger a table-scan on a view, then it will be slow, and NOTHING we recommend can fix that. (eg. if your view is calculating the count of items in the table with the same department number per row)

    Look into the Oracle pl/sql command "explain" which will tell you what it plans on doing when you run a query. It should help you optimize your query so that it runs faster.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.