Toad World® Forums

Long runnning extract ddl


#1

Hi!

As you know ddl from subpartitioned table is running extremely slow, i’m sure you know why (if not i can tell), but i want to present other strange situation during generation of ddl.

  1. Create table from attachement. This is subpartitioned table, simply, i want to simulate long running extract ddl.
  2. Try to extract ddl this table, watch CPU usage… afther fev seconds CPU should be 100% consumed for few seconds, afther that … do following:
    3.Open sqlEditor and try execute any statement (during this time session is busy with creation ddl) for example select sysdate from dual …
  3. Than you will see dialog with question to use anothers session to execute because current session is busy. Click NO … sql will be executed!!
    Watch trace log from attachement, at the end of file you can see seperate sqls selecting value of subpartitions, but between those sqls you can see “select sysdate from dual” - executed as I presented. I think ddl mechanism from subpartitioned/partitioned tables should be improve… (by the way as I wrote i previous post values for subpartitions are in uppercase, but partition/subpartition keys are caseSensitive)

Regards Piter

navlog0003.zip (34.5 KB)


#2

Hi Piter,

Thanks for your feedback. I will investigate this and get back to you shortly.

regards,

Bernard


#3

Hi Piter,

In regards to the 4th step in the list of detailed steps from you, the behavior you experienced is actually intentional. That dialog was designed to give users the option to execute the SQL in another session, or execute the SQL in the current busy session (when Nav is able to).

Therefore, regardless of whether the user selects YES or NO for that dialog, the SQL will get executed. Hope this will clear things up a bit. Thanks.

regards,

Bernard


#4

Hi Bernard!
But what if user runs long running query/plsqll block during ddl, what with extraction of ddl, will be waiting until sql ends… I my opinion extract ddl should block session during execution, maybe some modal message could be helpful, during this operation.
By the way i would be nice to add such smart option to execute sqls from seperate windows in threads. Of course we can use this dialog described in my first post, but treads invisible for user could be smarter…

Regards Piter


#5

Hi Piter,

You have raised some good points that we will definitely take onboard in conjunction with your earlier posts when we revisit this area in the future. Thanks again for your input.

regards,

Bernard