Toad World® Forums

complex query produces no alternatives


#1

Hi, I tried to optimize the following query. I clicked index and optimize in the tuning lab. It ran for 3 hours and produced no alternatives. Any ideas?

select per.employee_number,
per.national_identifier,
per.full_name,
per.last_name,
per.first_name,
per.middle_names,
per.suffix,
per.sex,
per.person_id,
per.date_of_birth,
per.original_date_of_hire,
per.start_date,
per.marital_status,
rslt.prtt_enrt_rslt_id,
rslt.pgm_id,
rslt.pl_id,
rslt.oipl_id,
rslt.ler_id,
rslt.per_in_ler_id,
rslt.ENRT_CVG_STRT_DT,
rslt.enrt_cvg_thru_dt,
per.person_type_id,
substr(addr.address_line1, 1, 30),
substr(addr.address_line2, 1, 30),
addr.town_or_city,
addr.region_2,
addr.postal_code,
addr.country,
addr.addr_attribute20,
typ.person_type_id,
typ.user_person_type,
null,
null,
null,
null,
null
from apps.per_all_people_f per,
ben.ben_prtt_enrt_rslt_f rslt,
apps.per_addresses addr,
apps.per_person_types typ,
apps.per_person_type_usages_f usg,
ben.ben_pl_typ_f pltyp
where per.person_id = rslt.person_id
and per.person_id = addr.person_id
and per.person_id = usg.person_id
and typ.person_type_id = usg.person_type_id
and ‘07-JUL-2008’ between usg.effective_start_date and usg.effective_end_date
and typ.system_person_type in (‘EMP’, ‘EX_EMP’)
and rslt.pl_typ_id = pltyp.pl_typ_id
and pltyp.name = ‘Medical’ – and rslt.pl_id in (5,81)
and rslt.oipl_id not in (55, 57, 130, 132) --shared family POS & CDHP
and addr.date_to is null
and addr.primary_flag = ‘Y’
and rslt.prtt_enrt_rslt_stat_cd is null --and per.person_id in (65550, 7802, 8681)
and ‘07-JUL-2008’ between per.effective_start_date and per.effective_end_date
and ‘07-JUL-2008’ between rslt.effective_start_date and rslt.effective_end_date
and ‘07-JUL-2008’ between rslt.enrt_cvg_strt_dt and rslt.enrt_cvg_thru_dt
and per.person_id not in (select per.person_id
from apps.per_all_people_f per,
ben.ben_prtt_enrt_rslt_f rslt,
apps.per_addresses addr,
apps.per_person_types typ,
apps.per_person_type_usages_f usg,
ben.ben_per_in_ler ler,
ben.ben_ler_f lerf,
ben.ben_pl_typ_f pltyp
where per.person_id = rslt.person_id
and per.person_id = addr.person_id
and per.person_id = usg.person_id
and ler.per_in_ler_stat_cd = (‘PROCD’)
and ler.procd_dt between add_months(‘07-JUL-2008’, -3) and ‘07-JUL-2008’
and lerf.ler_id = ler.ler_id
and rslt.ler_id = ler.ler_id
and ler.ler_id in (16, 129, 130, 198, 224, 265, 284, 344, 464, 524, 584, 624, 644, 664, 684, 685, 704, 765)
and per.person_id = ler.person_id
and typ.person_type_id = usg.person_type_id
and ‘07-JUL-2008’ between usg.effective_start_date and usg.effective_end_date
and typ.system_person_type in (‘EMP’, ‘EX_EMP’)
and rslt.pl_typ_id = pltyp.pl_typ_id
and pltyp.name = ‘Medical’ – and rslt.pl_id in (5,81)
and rslt.oipl_id not in (55, 57, 130, 132) --shared family POS & CDHP
and addr.date_to is null
and addr.primary_flag = ‘Y’
and rslt.prtt_enrt_rslt_stat_cd is null
and ‘07-JUL-2008’ between per.effective_start_date and per.effective_end_date
and (ler.lf_evt_ocrd_dt) between rslt.effective_start_date and rslt.effective_end_date
and (ler.lf_evt_ocrd_dt - 3) between rslt.enrt_cvg_strt_dt and rslt.enrt_cvg_thru_dt
and rslt.enrt_cvg_thru_dt <= ‘07-JUL-2008’) --<> ‘31-dec-4712’)
order by 1, 32 asc


#2

Hi Wendy,

First of all, thank you for testing the latest beta and sending us feedback. It is great to see you around.

Regarding your question, you mentioned that “It ran for 3 hours and produced no alternatives”. Does that mean the product is still running after 3 hours, or that it had stopped but with no alternatives returned?

If it is the first one, then may be you can give it more time to run as the optimization process sometimes do take a long time to finish, esp when you SQL statement is long and complex. The time to optimize is actually related to the Intelligence Level you used to optimize. So you may try lower the Intelligence Level to try again. But please keep in mind that the number of alternatives returned will be less if you used a lower Intelligence Level.

If the problem is the second one, then it may be either that there is really no alternatives for your SQL or that the process was terminated abnormally. Looking at you SQL, it is not likely that there is not a single alternatives. So, I would suggest you generate a Support Bundle if this is the case. Send back the Support Bundle and we would be able to tell more.

To generate a Support Bundle follow these steps:

  1. Launch Optimizer
  2. Click Help | Support Bundle (a new page called Support Bundle will be created)
  3. Open Tuning Lab (please make sure the turn on Support Bundle before opening the Lab)
  4. Repeat the optimization until it is finished
  5. Tab back to the Support Bundle page
  6. Select Send Back.

Thanks,
Alex


#3

Hi Alex, I am not running the beta, I have the demo version installed.
I believe the optimization process finished normally–there was no error
message and when I checked periodically it had messages like-- 62%
complete-- generating hints… But when it was done, there were no
alternatives listed. Do you still want me to get you a support bundle?


#4

Hi Wendy,

Yes, that would help if you can generate the Support Bundle and send back.

Also, if you have not tried the latest beta of version 7.4, please do. We will appreciate any feedback from you.

Thanks,
Alex


#5

Hi Alex, I generated the support bundle and sent it back> Wendy


#6

Hi Alex, I installed the beta and started the statement optimizing at
12:16 pm, it’s now 4:21 pm and it says: applying hints (80%). Do you
want me to try doing the support bundle? Or is there someway I should
simplify this statement to make it easier to optimize? Wendy


#7

Hello Wendy,

I have seen this problem before. The problem is caused by a lack of memory on your computer, since SQL Optimizer has found lots of alternatives and when the memory runs low the process gets stopped, and you get no alternatives displayed.

Are you working with a packaged application? It seems tome that on your FROM clause you have actual views and not tables. When the optimizer rewrites your SQL it uses pre-defined optimization option, intelligence level 4. The higher the level the more rewrites you will get. For this type of SQL you should get several rewrites. With inteligence level 4 (go to the Options -> Tuning Lab -> Optimizer -> Intelligence) there is an optimization option enabled called View to Inline View transformation. If you SQL has views, the view will be replaced with the actual SQL and the whole statement will be optimized. Packed applications have lot sof views, and you may need to keep using them, so this optimization setting may not ne helpful to you. You can either disabled this option under Options -> Tuning Lab -> Optimizer -> Intelligence: Select Custom. Then go to the Optimization options an turn this setting off.

The best thing you could do to test the optimizer and verify that everything is working fine, is to use the Tuning Lab window, select from the tool bar/link Intelligence Level 2 (the option I mention above is not enabled in this level), click on Optimize (only, let’s do indexes later).

Please, let me know if this works.

If this works, you can increase the intelligence level to get more rewrite and increase the chance of higher performance improvement,but keep in mind to disable the option I mention above.

Thanks,

Claudia


#8

Hi Wendy,

Add to what Claudia said, if the problem is caused by the lack of memory, there should be a log to indicate the termination in the Messages window. So if you still saw no alternatives after doing what Claudia said, you may check the log in the Messages window to see if the process was really terminated because of insufficient memory. Note that the Messages window may be hidden but it can be shown again by clicking on the hyperlinked text on the top-left of the screen (below the toolbar).

Thanks,
Alex


#9

I have 3GB of memory on my machine, should I close other applications?
I did get a message saying all processing complete. These are all tables
not views.

I tried the same statement in the beta version. I did get alternatives
this time, but it took 12+ hours to generate the alternatives. Is this
normal? I told it to run selected. It’s been running about 3 hours now
and so far all the alternatives have cancelled themselves presumably
because they took longer than the 00:00:01.920 that the original took?


#10

Hi Wendy,

Here are some things for you to try to shorten the time it takes for the optimization process.

If your SQL statement is using Views instead of Tables, change the optimization settings are follows:

  1.   On the **Tuning Lab | Optimizer | Intelligence** page, select Intelligence Level **4** using the slider.
    
  2.   Select **Custom Settings**.
    
  3.   On the **Tuning Lab |Optimizer | Optimization** page, clear **Transform view to inline view**.
    

The Transform view to inline view option rewrites the SELECT statement for the View as well as your original SQL statement. Say you are using three Views in your SQL statement. The optimization process would then rewriting four SQL statements (the original and all three views). You can see how this would add a lot of time to the optimization process particularly if any of the views are quite complex.

You can also specify to have the optimization process just transform the syntax of the SQL statement and not apply any of the Oracle optimization hints.

  1.   On the **Tuning Lab | Optimizer | Intelligence** page, select Intelligence Level **4** using the slider.
    
  2.   Select **Predefined**.
    
  3.   In the list next to Predefined, select **Do not use Oracle optimization hints**.
    

Note: You can select any of the other options that apply a few of the hints. Or you can select the Custom Settings and only apply a few hints that you choose.

Rene


#11

Hi Wendy,

For the question about the amount of memory that is being used on your computer by Quest SQL Optimizer, you can check the Memory Thresholds option setting:

On the Tuning Lab | Memory Threshold page, you can see the percentage of memory that is currently being used and set the threshold for stopping the optimization process if it takes too much of the memory on your computer.

Rene


#12

Is 3GB of memory adequate to run this application?

The objects in the select statement are all Tables, not Views.

Is it normal for the optimizer to take 12 hours to come up with 300
alternatives? That seems a little excessive.

None of the 300 alternatives generated ran any quicker than the
original. With the full table scans in the explain plan of the original
SQL, I would expect there to be a better alternative. Is playing with
the hint options going to buy me anything, or should I just give up on
optimizing this sql?


#13

Hi Wendy,

The time and memory needed to optimize a SQL depends on the complexity of your SQL statement. Basically, the optimization can be divided into 2 parts: (i) generate different alternatives based on the Intelligence Level used; and (ii) obtain execution plan for each alternative from database.

Regarding time, part (i) is done using the CPU of your machine but part (ii) is relying on how fast Oracle returned a plan and the network travel. Usually getting a plan from Oracle is a very fast action. But when you have a lot of alternatives to get plan, the most of the time spent may be in part (ii).

Regarding memory, it is determined by the length of your SQL as well as the length of the plan. Note that when you received 300 alternatives, there may be over a thousand SQL alternatives investigated internally before you can obtain that 300 alternatives (because we eliminate SQL with duplicate plans and give you only the alternatives with distinct plans).

I hope this will give you more insight on the optimization process and understanding on the time and memory required. Optimizing complex SQL is really an intensive process, even if this is done in a PC. Even so, IMHO, the time to optimize a SQL using the product is much faster than doing it manually. And the depth of investigating different possible alternatives using a tool is also much deeper than that of a human being can do in the same time. Take your 300 alternatives as an example, it would be impossible for a human being to investigate in 12 hours. However, the fact is that there is no guarantee on finding a better alternatives. In your case, if you have already tried 300 different alternatives but still with no better altenative found, I would suggest you try the indexing. Or you will have to use your expertise to tune the SQL manually. Understanding your data will help you do better the the product to tune SQL sometimes.

Thanks,
Alex


#14

Ok, Thank you. I don’t mind if it runs 12 hours if that is what it is
supposed to do. I just wasn’t sure if running that long might indicate
that there was a problem, or if I should cut some of the detail from the
SQL statment before optimizing.


#15

For this king long SQL with short run time like 00:00:01.920, the parsing time is long ,user should execute each SQL twice to get actual runing time. For 300 alterntives,there must some alternatives close or even better than the original SQL, the test run method is not correct for this kind of SQL statement.

Richard