Explain the cost of the plan and lead time

Question

Before that, I found Cost in terms of execution as a good indicator of relative lead time. Why is this case different? Am I really a fool, thinking that the execution plan matters? What exactly can I try to improve v_test performance?

Thanks.

Using Oracle 10g I have a simple query view below

create or replace view v_test as select distinct u.bo_id as bo_id, upper(trim(d.dept_id)) as dept_id from cust_bo_users u join cust_bo_roles r on u.role_name=r.role_name join cust_dept_roll_up_tbl d on (r.region is null or trim(r.region)=trim(d.chrgback_reg)) and (r.prod_id is null or trim(r.prod_id)=trim(d.prod_id)) and (r.div_id is null or trim(r.div_id)=trim(d.div_id )) and (r.clus_id is null or trim(r.clus_id )=trim( d.clus_id)) and (r.prod_ln_id is null or trim(r.prod_ln_id)=trim(d.prod_ln_id)) and (r.dept_id is null or trim(r.dept_id)=trim(d.dept_id)) 

to replace the following type

  create or replace view v_bo_secured_detail select distinct Q.BO_ID, Q.DEPT_ID from (select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D where U.ROLE_NAME = R.ROLE_NAME and R.ROLE_LEVEL = 'REGION' and trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG)) union all select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D where U.ROLE_NAME = R.ROLE_NAME and R.ROLE_LEVEL = 'RG_PROD' and trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG)) and trim(R.PROD_ID) = UPPER(trim(D.PROD_ID)) union all select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D where U.ROLE_NAME = R.ROLE_NAME and R.ROLE_LEVEL = 'PROD' and trim(R.PROD_ID) = UPPER(trim(D.PROD_ID)) union all select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D where U.ROLE_NAME = R.ROLE_NAME and R.ROLE_LEVEL = 'DIV' and trim(R.DIV_ID) = UPPER(trim(D.DIV_ID)) union all select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D where U.ROLE_NAME = R.ROLE_NAME and R.ROLE_LEVEL = 'RG_DIV' and trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG)) and trim(R.DIV_ID) = UPPER(trim(D.DIV_ID)) union all select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D where U.ROLE_NAME = R.ROLE_NAME and R.ROLE_LEVEL = 'CLUS' and trim(R.CLUS_ID) = UPPER(trim(D.CLUS_ID)) union all select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D where U.ROLE_NAME = R.ROLE_NAME and R.ROLE_LEVEL = 'RG_CLUS' and trim(R.REGION) = UPPER(trim(D.CHRGBACK_REG)) and trim(R.CLUS_ID) = UPPER(trim(D.CLUS_ID)) union all select U.BO_ID BO_ID, UPPER(trim(D.DEPT_ID)) DEPT_ID from CUST_BO_USERS U, CUST_BO_ROLES R, CUST_DEPT_ROLL_UP_TBL D where U.ROLE_NAME = R.ROLE_NAME and R.ROLE_LEVEL = 'PROD_LN' and trim(R.PROD_LN_ID) = UPPER(trim(D.PROD_LN_ID)) union all select U.BO_ID BO_ID, UPPER(trim(R.DEPT_ID)) DEPT_ID from CUST_BO_USERS U, CUST_BO_ROLES R where U.ROLE_NAME = R.ROLE_NAME and R.ROLE_LEVEL = 'DEPT') Q 

in order to remove the dependency on the ROLE_LEVEL column.

The execution plan for v_test is significantly lower than v_bo_secured_detail for a simple

 select * from <view> where bo_id='value' 

inquiries. And significantly lower when used in the real world

  select CT_REPORT.RPT_KEY, CT_REPORT_ENTRY.RPE_KEY, CT_REPORT_ENTRY.CUSTOM16, Exp_Sub_Type.value, min(CT_REPORT_PAYMENT_CONF.PAY_DATE), CT_REPORT.PAID_DATE from CT_REPORT, <VIEW> SD, CT_REPORT_ENTRY, CT_LIST_ITEM_LANG Exp_Sub_Type, CT_REPORT_PAYMENT_CONF, CT_STATUS_LANG Payment_Status where (CT_REPORT_ENTRY.RPT_KEY = CT_REPORT.RPT_KEY) and (Payment_Status.STAT_KEY = CT_REPORT.PAY_KEY) and (Exp_Sub_Type.LI_KEY = CT_REPORT_ENTRY.CUSTOM9 and Exp_Sub_Type.LANG_CODE = 'en') and (CT_REPORT.RPT_KEY = CT_REPORT_PAYMENT_CONF.RPT_KEY) and (SD.BO_ID = 'JZHU9') and (SD.DEPT_ID = UPPER(CT_REPORT_ENTRY.CUSTOM5)) and (Payment_Status.name = 'Payment Confirmed' and (Payment_Status.LANG_CODE = 'en') and CT_REPORT.PAID_DATE > to_date('01/01/2008', 'mm/dd/yyyy') and Exp_Sub_Type.value != 'Korea') group by CT_REPORT.RPT_KEY, CT_REPORT_ENTRY.RPE_KEY, CT_REPORT_ENTRY.CUSTOM16, Exp_Sub_Type.value, CT_REPORT.PAID_DATE 

WILDLY runtimes vary. Viewing v_test takes 15 hours, and v_bo_secured_detail takes a few seconds.

---

Thanks to all who responded

This is the one I have to remember. Places where the theory and mathematics of expressions correspond to the reality of hardware execution. Uch.

Answer 1

Like Oracle documentation , cost is an approximate cost relative to a specific implementation plan. When you set up a query, a specific execution plan is calculated, the cost of which can be changed. Sometimes sharply.

The problem with v_test performance is that Oracle cannot think that it cannot execute it, except for executing a nested loop, for each cust_bo_roles, scan all cust_dept_roll_up_tbl to find a match. If the table has sizes n and m, it takes n * m time, which is slow for large tables. In contrast, v_bo_secured_detail is configured so that it is a series of requests, each of which can be executed through some other mechanism. (Oracle has a number that it can use, including using an index, building a hash on the fly, or sorting and combining datasets. These operations are all O (n * log (n)) or better.) A small series of fast queries are performed fast.

However painful it may be, if you want this query to be fast, you need to break it down just like the previous query.

Answer 2

The execution plan is a theory, the execution time is a reality.

The plan shows you how the engine begins to execute your request, but some steps can lead to an excessive amount of work to resolve the request. Using "x is null or x = y" smells bad. If r and d are large tables, you may have some kind of combinatorial explosion that amazes you, and the request for cycles is infinite through large lists of disk blocks. I assume that at runtime you see a lot of I / O.

Combined samples, on the other hand, are short and enjoyable, and therefore it is possible to reuse many disk blocks that still lie around from earlier samples, and / or you have some degree of parallelism that benefits from reading on the same blocks drive.

Also, using trim () and upper () all over the place looks a bit suspicious. If your data is so unclean, it may sometimes require periodic cleaning, so you can say “x = y” and know that it works.

update: you asked for tips to improve v_test. Clear the data so that there is no need to trim () and the top (). They may hamper the use of indexes (although this will affect the combined version of the selection).

If you cannot get rid of "x is null or x = y", then y = nvl (x, 'does-not-exist') may have better characteristics (provided that "does-not-exist" is "not may be "id value).

Answer 3

One aspect of inexpensive - high runtime is that when you look at large data sets, it is often more efficient to do things in bulk, whereas if you want fast results, it is more efficient to do as little work as possible to get the first record . The repeatability of small operations, which lead to a quick response, is unlikely to give a good result when working with large sets.

Many times when you need a quick result, a hint from the USE_NL optimizer will help.

In addition, in your test case, it relies on IS NULL ... NULL cannot use an index and cannot use a function such as cropping with the "table-side" parameter.

Answer 4

Have you collected optimizer statistics in all base tables? Without them, optimizer ratings can be completely volatile with reality.

Answer 5

When you say that the “request plan is lower,” do you mean that it is shorter or that the actual cost estimates are lower? One obvious problem with your replacement view is that the connection to cust_dept_roll_up_tbl uses almost exclusively unfulfilled criteria (null tests can be met by an index, but those related to setting up a call for each argument cannot be), so the scheduler should Perform at least one and possibly several consecutive table scans to satisfy the request.

I'm not sure that Oracle has this limitation, but many databases can only perform one index scan on an included table, so even if you clear your join conditions to be indexable, it can satisfy only one condition with a scan index and should use sequential scanning for the remainder.

Answer 6

To learn a little about the cost.

In Oracle 9 / 10g, simplifying the bit, the cost is determined by the formula:

Cost = (SrCount * SrTime + MbrCount * MbrTime + CpuCyclesCount * CpuCycleTime) / SrTime

In cases where SrCount - counts the total number of single reads, SrTime - the average time of one single block, read in accordance with the collected statistics of the system, MbrCount and MbrTime, the same for multi-block reading, respectively (used during full table scan and quick scan ), Cpu-related metrics are self-explanatory .. and all are divided into a single block read time.