Toad World® Forums

Filter Packages


#1

I think I posted this before (but I can’t find it). In DB Explorer, after I type in a filter at the top, then open the Package Bodies. Everything works great, and fast (as expected). But then when i click on a package name, I get an hourglass for at least 10 seconds. This seems to be better than before, but is very annoying to have to wait to open a package.

I am using beta 6.3.0.1625.

Thanks.
-Charlie


#2

Is there any update to this? Is this something that will be fixed?

Thanks.
-Charlie


#3

Hi Charlie,

Sorry for the delay. Could you send us the SQL Monitor trace, we want to be sure which query takes that long.

Thank you,
Roman


#4

Can you give me the steps to take? I’m not exactly sure how to use the SQL Monitor?

Thanks.
-Charlie


#5

Hi Charlie,

Please do the following:

  • Launch SQL Navigator. Open the code editor and have the filter in DB explorer displayed.

  • Launch SQL Monitor from Start->All Programs->Quest Software->SQL Navigator 6.2.1->SQL Monitor. Please make sure the check box sqlnavigator.exe is selected as attached image.

  • Please repeat the steps in DB explorer filter when you have performance issue.

The SQL Monitor should capture all the sql fired from SQL Navigator. Please send us all the sql statements in the sql monitor window.

Thanks and regards,
Bruce
sqlmonitor.jpeg


#6

Just to be clear on where the delay is, these are my steps:

  1. in DB Explorer, I type in my filter XXVG_QP% and press .
  2. expand the Package Bodies and I see the 5 package bodies that match.
    Note - I have not clicked on a package name yet
    (both of the above steps are fast)
  3. start SQL Monitor as directed
  4. click on a package name (clicked on one of the five listed)
    • I saw the SQL statement appear in SQL Monitor immediately
      - it took 35 seconds before the package i clicked on became hightlighted.
      This is my issue
      5. closed SQL Monitor. Below is the SQL statement from SQL Monitor:
      select USER owner,a.object_name,a.object_id,a.created,a.last_ddl_time,decode(a.status,‘VALID’,0,‘INVALID’,1,2) status
      from user_objects a
      where object_name like :object_name
      and object_type=:object_type
      object_type = ‘P’
      object_name = '%'

      This is not even a valid SQL Statement. The last two lines do not even have the AND in front of them, and if they did, the query would return zero rows. (there is no object_type = ‘P’ and no object_name = ‘%’).

Thanks.
-Charlie


#7

Hi Charlie,

Don’t worry about those two lines. They just show the values of bind variables. The value are not displayed correctly since we use Unicode, but it doesn’t matter. It’s weird that this simple statement takes so long. Does the same thing happen in DB Navigator? If yes, perhaps you should talk to your DBA about this issue?

Regards,
Roman


#8

There are still a couple things that I think are wrong here.

  1. the bind variable for object_name = ‘%’. So the SQL statement is going to get every row in the table. Why does this not filter by my filter ‘XXVG_QP%’ ?
  2. the other bind variable, object_type=‘P’. There are no rows in the where the object_type = ‘P’. Since i clicked on Package Bodies, shouldn’t this bind be ‘PACKAGE BODY’ ?

BTW, our user_objects table has about 150k records. 40k of which are package bodies.

Thanks.
-Charlie


#9

Hi Charlie,

We have a design issue here. DB Navigator performs this query when you expand the packages node, therefore it doesn’t do it again when you select a package. In DB Explorer, we use a different query to populate packages, but the query still has to be executed when a package is selected for the IDE to function correctly (e.g. actions in the popup menu to be enabled/disabled etc.). Ideally, we should either do the same in DB Explorer as DB Nav, or find another way to pass information about packages to IDE. Anyway, this is not a simple task, but we’ll discuss this issue with the team ASAP.

Cheers,
Roman