1. Pierre Couvreur
  2. PowerBuilder
  3. Thursday, 8 February 2018 09:05 AM UTC

PB 12.5

Oracle 12.1

PB driver O9, O10 or ORA

Hello,

It is very slow to open the datasource painter from the datawindow. The more tables are in the datasource, the more it is slow to open the painter. It's very painful.

I can see that PB generates some SELECT statements using Oracle repository, in particular that one is very slow (more than 10 seconds), and it is generated once for every table of the datasource :

  SELECT F.CONSTRAINT_NAME,
         F.OWNER,
         F.R_OWNER,
         P.TABLE_NAME,
         SYS.ALL_CONS_COLUMNS.COLUMN_NAME,
         F.DELETE_RULE
   FROM SYS.ALL_CONSTRAINTS F, SYS.ALL_CONS_COLUMNS, SYS.ALL_CONSTRAINTS P
   WHERE     F.OWNER = 'EFOR'
         AND F.TABLE_NAME = 'CRQ2'
         AND F.CONSTRAINT_TYPE = 'R'
         AND SYS.ALL_CONS_COLUMNS.CONSTRAINT_NAME = F.CONSTRAINT_NAME
         AND SYS.ALL_CONS_COLUMNS.TABLE_NAME = 'CRQ2'
         AND SYS.ALL_CONS_COLUMNS.OWNER = 'EFOR'
         AND P.OWNER = F.R_OWNER
         AND P.CONSTRAINT_NAME = F.R_CONSTRAINT_NAME
ORDER BY F.CONSTRAINT_NAME, SYS.ALL_CONS_COLUMNS.POSITION

Oracle Statistics have already been gathered for the sys and fixed objects.

Does anyone know how to fix this ?

Thank you very much.

Pierre

David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Thursday, 8 February 2018 13:36 PM UTC
  2. PowerBuilder
  3. # 1

Hi

It's a known problem with the way the Oracle Optimizer handles that query on the SYS tables. I do not believe you can gather stats for SYS tables and it make any difference. We have had this problem for a while now and as yet have not found a solution.

You could log a support call on Oracle for why the query performs badly, but I suspect they will suggest you refactor the SQL, add hints etc which we clearly cannot do.

I think you have to live with it.

Sorry

David

Comment
There are no comments made yet.
Govinda Lopez @Appeon Accepted Answer Pending Moderation
  1. Thursday, 8 February 2018 16:05 PM UTC
  2. PowerBuilder
  3. # 2

Hi Pierre,

This seems to be a problem with the way that Oracle handles the SYS table queries. Unfortunatelly, PowerBuilder's EOL was back on 2015. 

So, my advice, would also be to check with Oracle. They may be able to offer you some work around with this connection/query.


Regards,

Comment
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Thursday, 8 February 2018 23:27 PM UTC
  2. PowerBuilder
  3. # 3

This was NOT an issue before Oracle 11g, at least in our shop.

Oracle 10c worked just fine, thank you VERY much. then we "upgraded" to Oracle 12 and suddenly the first connection AND the first retrieval on ANY table was

S  T  U  P  I  D       S  L  O  W .

We tried to research it and could not identify a cause.

 

Olan

 

 

Comment
There are no comments made yet.
Pierre Couvreur Accepted Answer Pending Moderation
  1. Friday, 9 February 2018 07:29 AM UTC
  2. PowerBuilder
  3. # 4

Thanks for you answers. There was indeed no problem before Oracle 11.

Sadly PB team did not make any fix for such a problem, they should tune the generated query !

Pierre

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.