Skip to Content
author's profile photo Former Member
Former Member

DW SQL Painter Executing Bad Oracle SQL

PB 12.5.1 Build 4015

When going into the DW SQL Painter PB will execute the following Constraint Select:

SELECT SYS.ALL_CONS_COLUMNS.COLUMN_NAME, SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME FROM SYS.ALL_CONSTRAINTS, SYS.ALL_CONS_COLUMNS WHERE ....

These selects will cause my DW to not return for minutes. Is it possible to tell PB NOT to execute the Constraint Select when going into the DW SQL Painter or is there a way to have it use a different table instead of the SYS ones? I might understand why it needs it for Graphics mode but I am in Syntax mode.

Hopefully someone has a workaround for this because I am getting really tired of waiting minutes every time I go into the SQL.

Thanks,

Chris Craft

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2014 at 08:50 PM

    Chris,

    You did not mention Oracle version nor way you connect to it.

    Did you try to run this sql from sqlplus? Might be you need some stats being refreshed.Have you checked explain plan for this sql?

    I must admit sometimes I just modify sql in Edit source, if there's not many changes it might work.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 24, 2014 at 10:35 AM

    I haven't PB 12.5 installed but in PB 11.5 :

    - open any DW

    - go to menu Design..Option

    - General Tab

    - Check OFF the Retrieve on Preview option

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 24, 2014 at 01:54 PM

    Try to execute this Oracle command at the schema level for the SYS owner:

    exec dbms_stats.gather_schema_stats(ownname => ‘SYS’,cascade => TRUE);

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Jacob,

      This does not help. Our DBA has put in place some Views to try and speed this up but it is not a very good solution. I am gathering their is not a hidden PB.INI setting that allows us to turn this off then?

      Chris

  • author's profile photo Former Member
    Former Member
    Posted on Dec 09, 2014 at 02:27 PM

    Have you looked at the PBODB125.ini file?

    In the [Rdb_SYNTAX] section there exists a PBFKeyListSyntax item. It's contents are only place in the entire file with the word "CONSTRAINTS" (plural) in it.

    Try commenting out the line completely, or making a copy of the line to try changes to it to see what you can make happen. Just make sure you comment out the original. A comment is a semi-colon at the beginning of the line ";".

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      We use the ORA driver not ODBC but I tried it anyway - no effect.

      I forgot to mention the Oracle version, as Krzysztof kindly reminded me. We are currently on Oracle 12.1.0.2.0 but I believe this started happening when we went to Oracle 11. There are multiple posts out there stating the Constraint Views do not perform after upgrading. If there is some way to change what schema/tables to use (like that ODB ini setting) then we would be golden!

      Chris

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.