Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: query plan question

    So, we're looking at a query which will run about 1000 times a day, and it takes about 5-10 seconds to run, which is fine. We're trying to plan for performance in our production environment and how this query would affect existing processes, and so we were looking into SQL tuning.

    The query plan generated from TOAD indicates that the cost of the query is around 8500. By adding the /*+ FIRST_ROWS(30) */ hint, the cost goes down to 400. However, the two queries run at the same speed. My thinking is that the version with a cost of 400 will integrate better into our production environment, but I'm concerned I'm understanding something incorrectly since the speed on both queries is the same.

    I ran a trace on the session, and there's no "cost" indicated in the tracefile query plan. And now I'm confused if I'm reading this diagnostic data correctly - due to the similar run times and the inflated fetch count for the query column in the tracefile for the SQL with the hint. Does the "cost" in TOAD's version mean anything?

    Code:
    SELECT ...
     FROM pa_employer, pa_name, pa_mailing_addr 
     WHERE pa_employer.status IN ('01','02') 
      AND pa_employer.employer_no = pa_name.employer_no (+) 
      AND pa_employer.employer_no = pa_mailing_addr.employer_no (+) 
      AND pa_name.type NOT IN ('9','91','92') 
      AND (pa_employer.legal_name LIKE '%YAKIMA%' OR pa_name.name LIKE '%YAKIMA%') 
     ORDER BY  pa_employer.employer_no
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.02       0.01          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      5.77       6.08      13952      23350          0           9
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      5.79       6.10      13952      23350          0           9
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 64  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          9  SORT ORDER BY (cr=23350 pr=13952 pw=854 time=6088251 us)
          9   HASH JOIN OUTER (cr=23350 pr=13952 pw=854 time=6088084 us)
          9    HASH JOIN  (cr=19383 pr=10072 pw=854 time=4464653 us)
      94673     TABLE ACCESS FULL PA_EMPLOYER (cr=10157 pr=6534 pw=0 time=379460 us)
     677407     TABLE ACCESS FULL PA_NAME (cr=9226 pr=2684 pw=0 time=1355114 us)
     373543    TABLE ACCESS FULL PA_MAILING_ADDR (cr=3967 pr=3880 pw=0 time=382432 us)
    
    
    SELECT /*+ FIRST_ROWS(30) */ ...
     FROM pa_employer, pa_name, pa_mailing_addr 
     WHERE pa_employer.status IN ('01','02') 
      AND pa_employer.employer_no = pa_name.employer_no (+) 
      AND pa_employer.employer_no = pa_mailing_addr.employer_no (+) 
      AND pa_name.type NOT IN ('9','91','92') 
      AND (pa_employer.legal_name LIKE '%YAKIMA%' OR pa_name.name LIKE '%YAKIMA%') 
     ORDER BY  pa_employer.employer_no
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.02       0.01          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      6.45       6.47      10456     453030          0           9
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      6.47       6.49      10456     453030          0           9
    
    Misses in library cache during parse: 1
    Optimizer mode: FIRST_ROWS
    Parsing user id: 64  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          9  NESTED LOOPS OUTER (cr=453030 pr=10456 pw=0 time=6473343 us)
          9   NESTED LOOPS  (cr=452994 pr=10456 pw=0 time=6472908 us)
      94673    TABLE ACCESS BY INDEX ROWID PA_EMPLOYER (cr=10901 pr=8815 pw=0 time=1
    897281 us)
     372408     INDEX FULL SCAN PA_EMPLOYER_PK (cr=749 pr=210 pw=0 time=372454 us)(o
    bject id 160436)
          9    TABLE ACCESS BY INDEX ROWID PA_NAME (cr=442093 pr=1641 pw=0 time=4344
    665 us)
     207490     INDEX RANGE SCAN PA_NAME_EMPLOYER_NO_IX (cr=284460 pr=1229 pw=0 time
    =1914282 us)(object id 160619)
          9   TABLE ACCESS BY INDEX ROWID PA_MAILING_ADDR (cr=36 pr=0 pw=0 time=374 
    us)
          9    INDEX RANGE SCAN PA_MAILING_ADDR_PK (cr=27 pr=0 pw=0 time=166 us)(obj
    ect id 160443)

    -Chuck

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    DON'T USE TOAD for that stuff!!

    You cost is totally screwed up on TOAD because it gives you the cost of displaying the data to the SCREEN (only what you see and not the whole output). If your screen only shows 10 lines then TOAD will show you the cost to display 10 lines, and NOT the cost to display the WHOLE 100 rows (or whatever you get back from your specific query).

    In your case it is telling you the cost to display the FIRST ROW (and not all 9).

    Regardless, let's tune your query. This is the driving force.
    WHY do you need the outer joins? Are they ABSOLUTELY necessary?
    Include the columns in the select statement and also post the explain PLAN.

    BTW - you need to disconnect the session in order to get timing and costs, etc into the trace file and to show in TKPROF.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I agree with Duck ... Let's look at tuning the SQL statement and maybe looking at the indexes involved ... ie, pa_employer.status and pa_name.type .
    Are these columns candidates for bitmap indexes ??? How many distinct values do you have in those columns.
    How many rows in each of the tables... Let's try to make the driving table
    the one that will return the smallest result set first and let the remainder
    of the where clauses work from that.
    You might also look at placing some of the indexes in the keep buffer pool
    if they are pretty static.

    There's a lot of disk io there that might could be dealt with.
    What's the size of the tables and indexes ?

    Gregg

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    I HAVE TO SPREAD THIS OUT ACCROSS A COUPLE OF POSTS

    I am in the process of finding out if the line
    Code:
      AND pa_employer.employer_no = pa_mailing_addr.employer_no (+)
    is necessary, but the other OUTER JOIN is required.

    Here's how things boil down:
    pa_employer.employer_no PK
    pa_name.employer_no FK indexed
    pa_mailing_addr.employer_no composite index by (NAME, EMPLOYER_NO)
    pa_employer.status indexed
    pa_name.type indexed

    table statistics are updated.

    Code:
    SQL> select count(*) from pa_employer;
    
      COUNT(*)
    ----------
        372409
    
    SQL> select count(*) from pa_employer where pa_employer.status IN ('01','02'
    
      COUNT(*)
    ----------
         94673
    
    SQL> select count(*) from pa_name;
    
      COUNT(*)
    ----------
        740411
    
    SQL> select count(*) from pa_name where pa_name.type NOT IN ('9','91','92');
    
      COUNT(*)
    ----------
        677408
    Here's the entire output from TKPROF. From my limited experience with SQL Tuning, I think the best approach would be to perform a TABLE SCAN on pa_employer (due to the wildcard search), and then use a nested loops join to the other two tables based on the index on EMPLOYER_NO.

    I tried to create the EXPLAIN PLAN, but I'm being prevented. I have the ability to CREATE and DROP a table

    Code:
    SQL> connect forbesc@dev
    Enter password: *******
    Connected.
    SQL> create table drop_this (f1 number);
    
    Table created.
    
    SQL> drop table drop_this;
    
    Table dropped.
    But I am prevented from creating an EXPLAIN PLAN with TKPROF using the Syntax:

    Code:
    tkprof dev_s001_22566.trc chuck_tkprof2.out EXPLAIN=forbesc/******
    Last edited by chuck_forbes; 01-12-06 at 18:14.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    [CODE]
    Code:
    TKPROF: Release 10.1.0.3.0 - Production on Thu Jan 12 13:57:17 2006
    
    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
    
    
    [oracle@ora4 bdump]$ more chuck_tkprof2.out 
    
    TKPROF: Release 10.1.0.3.0 - Production on Thu Jan 12 13:57:17 2006
    
    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
    
    Trace file: dev_s001_22566.trc
    Sort options: default
    
    ********************************************************************************
    count    = number of times OCI procedure was executed
    cpu      = cpu time in seconds executing 
    elapsed  = elapsed time in seconds executing
    disk     = number of physical reads of buffers from disk
    query    = number of buffers gotten for consistent read
    current  = number of buffers gotten in current mode (usually for update)
    rows     = number of rows processed by the fetch or execute call
    ********************************************************************************
    
    Error in CREATE TABLE of EXPLAIN PLAN table: FORBESC.prof$plan_table
    ORA-00922: missing or invalid option
    
    parse error offset: 1052
    EXPLAIN PLAN option disabled.
    ********************************************************************************
    
    
    SELECT pa_employer.employer_no, 
     pa_employer.legal_name, 
     pa_employer.ppb_address2, 
     pa_employer.ppb_city, 
      pa_employer.ppb_state, 
      pa_employer.ppb_zip, 
      pa_mailing_addr.addr_line2, 
      pa_mailing_addr.city, 
      pa_mailing_addr.state, 
      pa_mailing_addr.zip, 
      pa_name.name, 
      pa_name.type, 
      pa_name.name as name_ex, 
      soundex(pa_employer.legal_name) as lglnm_ex 
     FROM pa_employer, pa_name, pa_mailing_addr 
     WHERE pa_employer.status IN ('01','02') 
      AND pa_employer.employer_no = pa_name.employer_no (+) 
      AND pa_employer.employer_no = pa_mailing_addr.employer_no (+) 
      AND pa_name.type NOT IN ('9','91','92') 
      AND (pa_employer.legal_name LIKE '%YAKIMA%' OR pa_name.name LIKE '%YAKIMA%') 
     ORDER BY  pa_employer.employer_no
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        3      0.04       0.03          0          0          0           0
    Execute      3      0.00       0.00          0          0          0           0
    Fetch        3     18.66      18.84      29325      70051          0          27
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        9     18.70      18.88      29325      70051          0          27
    
    Misses in library cache during parse: 2
    Optimizer mode: ALL_ROWS
    Parsing user id: 64  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          9  SORT ORDER BY (cr=23352 pr=11297 pw=854 time=5751470 us)
          9   HASH JOIN OUTER (cr=23352 pr=11297 pw=854 time=5751085 us)
          9    HASH JOIN  (cr=19384 pr=11297 pw=854 time=4180818 us)
      94673     TABLE ACCESS FULL PA_EMPLOYER (cr=10157 pr=5480 pw=0 time=379036 us)
     677408     TABLE ACCESS FULL PA_NAME (cr=9227 pr=4963 pw=0 time=678035 us)
     373544    TABLE ACCESS FULL PA_MAILING_ADDR (cr=3968 pr=0 pw=0 time=373670 us)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       6        0.00          0.00
      SQL*Net message from client                     4        0.00          0.00
      direct path write temp                        244        0.01          0.03
      db file scattered read                       2364        0.03          0.44
      db file sequential read                      1377        0.01          0.33
      direct path read temp                         244        0.00          0.00
    ********************************************************************************

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    SELECT /*+ FIRST_ROWS(30) */ 
     pa_employer.employer_no, 
     pa_employer.legal_name, 
     pa_employer.ppb_address2, 
     pa_employer.ppb_city, 
      pa_employer.ppb_state, 
      pa_employer.ppb_zip, 
      pa_mailing_addr.addr_line2, 
      pa_mailing_addr.city, 
      pa_mailing_addr.state, 
      pa_mailing_addr.zip, 
      pa_name.name, 
      pa_name.type, 
      pa_name.name as name_ex, 
      soundex(pa_employer.legal_name) as lglnm_ex 
     FROM pa_employer, pa_name, pa_mailing_addr 
     WHERE pa_employer.status IN ('01','02') 
      AND pa_employer.employer_no = pa_name.employer_no (+) 
      AND pa_employer.employer_no = pa_mailing_addr.employer_no (+) 
      AND pa_name.type NOT IN ('9','91','92') 
      AND (pa_employer.legal_name LIKE '%YAKIMA%' OR pa_name.name LIKE '%YAKIMA%') 
     ORDER BY  pa_employer.employer_no
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        4      0.04       0.03          0          0          0           0
    Execute      4      0.00       0.00          0          0          0           0
    Fetch        4     24.82      24.48      27063    1812129          0          36
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       12     24.86      24.52      27063    1812129          0          36
    
    Misses in library cache during parse: 2
    Optimizer mode: FIRST_ROWS
    Parsing user id: 64  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          9  NESTED LOOPS OUTER (cr=453033 pr=6191 pw=0 time=6088603 us)
          9   NESTED LOOPS  (cr=452997 pr=6190 pw=0 time=6088077 us)
      94673    TABLE ACCESS BY INDEX ROWID PA_EMPLOYER (cr=10902 pr=5617 pw=0 time=1612086 us)
     372409     INDEX FULL SCAN PA_EMPLOYER_PK (cr=749 pr=2 pw=0 time=372455 us)(object id 160436)
          9    TABLE ACCESS BY INDEX ROWID PA_NAME (cr=442095 pr=573 pw=0 time=4268048 us)
     207492     INDEX RANGE SCAN PA_NAME_EMPLOYER_NO_IX (cr=284460 pr=572 pw=0 time=1907991 us)(object id 160619)
          9   TABLE ACCESS BY INDEX ROWID PA_MAILING_ADDR (cr=36 pr=1 pw=0 time=467 us)
          9    INDEX RANGE SCAN PA_MAILING_ADDR_PK (cr=27 pr=1 pw=0 time=233 us)(object id 160443)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       8        0.00          0.00
      SQL*Net message from client                     7        0.00          0.00
      db file sequential read                     27063        0.01          0.49
    ********************************************************************************
    
    BEGIN dbms_monitor.session_trace_disable; END;
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        3      0.01       0.00          0          0          0           0
    Execute      3      0.00       0.00          0          0          0           3
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        6      0.01       0.00          0          0          0           3
    
    
    Misses in library cache during parse: 2
    Optimizer mode: ALL_ROWS
    Parsing user id: 64  
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message from client                     1        0.00          0.00
    ********************************************************************************
    
    select 'x' 
    from
     dual
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch        2      0.00       0.00          0          0          0           2
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        6      0.00       0.00          0          0          0           2
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 64  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  FAST DUAL  (cr=0 pr=0 pw=0 time=4 us)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       4        0.00          0.00
      SQL*Net message from client                     4        0.00          0.00
    ********************************************************************************
    
    
    begin :id := sys.dbms_transaction.local_transaction_id; end;
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           1
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          0          0           1
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 64  
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       1        0.00          0.00
      SQL*Net message from client                     1        0.00          0.00
    
    
    
    ********************************************************************************
    
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse       14      0.09       0.08          0          0          0           0
    Execute     18      0.00       0.02          3         89          0           8
    Fetch       10     49.90      49.62      65942    2335213          0          74
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       42     49.99      49.73      65945    2335302          0          82
    
    Misses in library cache during parse: 6
    Misses in library cache during execute: 2
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                      26        0.00          0.00
      SQL*Net message from client                    26        0.00          0.00
      db file scattered read                       2364        0.03          0.44
      db file sequential read                     37994        0.01          0.98
      direct path write temp                        244        0.01          0.03
      direct path read temp                         244        0.00          0.00
    
    
    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        0      0.00       0.00          0          0          0           0
    Execute      0      0.00       0.00          0          0          0           0
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        0      0.00       0.00          0          0          0           0
    
    Misses in library cache during parse: 0
    
       18  user  SQL statements in session.
        0  internal SQL statements in session.
       18  SQL statements in session.
        0  statements EXPLAINed in this session.
    ********************************************************************************
    Trace file: dev_s001_22566.trc
    Trace file compatibility: 10.01.00
    Sort options: default
    
           9  sessions in tracefile.
          76  user  SQL statements in trace file.
           0  internal SQL statements in trace file.
          18  SQL statements in trace file.
           7  unique SQL statements in trace file.
       41283  lines in trace file.
       14786  elapsed seconds in trace file.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Derrr...
    The Table Scan on both PA_EMPLOYER and PA_NAME makes sense, since there's a wildcard search on a field in either table ... sorry. It still seems like the PA_MAILING_ADDR.EMPLOYER_NO field could be sought after using the INDEX rather than a fill table scan

    -cf

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1. It looks like the all_rows version is much more efficient
    2. Unfortunately most of your criteria are not very selective which is why the indexes dont help much.
    3. TOAD is fine for tuning you just have to remember to stick autotrace on (right click in the sql editor window). Also remember to run your query twice to elimanate caching differences.

    As for improving performance you will have to try out various permutations on your production environment (or your test environment if it is very close) and see what comes out best. Even Einstein would have trouble second guessing the optimizer so dont bother trying.

    Some things which may help are removing the outer joins as mentioned earlier, having pa_mailing_addr.employer_no as the leading column in your composite index, compressed indexes etc. The other main problem area is the LIKEs, if you can elimanate these (maybe an app/schema change) then you can reduce the cost quite a lot.

    Alan

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Quote Originally Posted by The_Duck
    DON'T USE TOAD for that stuff!!

    You cost is totally screwed up on TOAD because it gives you the cost of displaying the data to the SCREEN (only what you see and not the whole output). If your screen only shows 10 lines then TOAD will show you the cost to display 10 lines, and NOT the cost to display the WHOLE 100 rows (or whatever you get back from your specific query).
    No, not true. TOAD asks Oracle to generate the execution plan and so it (including the cost) is identical to the one you'll see anywhere else. I think what you mean is that on completion of the query, TOAD may be showing you the elapsed TIME to only fetch the first ten rows from server to client.

    In the case of a large query where the byte count of the returned data is high and the network speed is a factor in the timing, then yes the TOAD elapsed time can be misleading.

    If returning the entire dataset were a pre-requisite for tuning, then some of the 4 hour jobs you read about on here could only possibly be tuned twice a day

    Regardless of this, the expected cost is not a good indicator of speed anyway. You can have a query with a high cost execute much quicker than one with a low cost.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    What is the meaning of cost then, the accumulative I/O (the addition of both physical and logical I/O)? That would make sense, then, that the cost is not necessarily a predictor of the amount of time it takes to return a resultset, but that cost would be a good indicator of efficiency.

    Is that an accurate statement?

    -cf

  11. #11
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Cost is an estimate of IO required by a query (10g can also take into account CPU cycles). However the optimizer can easily get this wrong as it depends on the quality of the statistics (both object and system) and also the optimizer is not perfect (it makes lots of assumptions, has bugs or simply doesnt take into account certain facts like how many blocks will be in the cache).

    You should always go by the stats after executing the query as shown by autotrace or tkprof.

    Alan
    Last edited by AlanP; 01-13-06 at 12:01.

  12. #12
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Chuck,

    Quote Originally Posted by chuck_forbes
    What is the meaning of cost then
    That, my friend, is the 64 million dollar question

    Quote Originally Posted by chuck_forbes
    that the cost is not necessarily a predictor of the amount of time it takes to return a resultset
    True.

    Quote Originally Posted by chuck_forbes
    but that cost would be a good indicator of efficiency.
    Yes and no, Oracle state that cost is purely an arbitrary number used by the optimiser to come up with what it thinks is most efficient. Comparing costs against the same query during tuning does give a 'relative' idea. Comparing costs against two completely seperate queries has no use at all.

    And again, cost represents some arbitrary "work unit", but how these units bear on final execution speed is, I believe, unknown.

    Tom Kyte has a good discussion about it here...
    http://asktom.oracle.com/pls/ask/f?p...40112614814595

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  13. #13
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    This quote is from an article on costing:

    The cost is an estimated value proportional to the expected elapsed time needed to execute the statement using the execution plan. The optimizer calculates the cost based on the estimated computer resources including but not limited to I/O, CPU time, and memory required to execute the statement using the plan. Execution plans with greater costs take more time to execute than those with smaller costs.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  14. #14
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Yes there are an awful lot of articles and differing opinions out there about cost. Personally I'll take Tom Kyte's view on it.

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  15. #15
    Join Date
    Jul 2003
    Posts
    2,296
    Does this version show any improvement?
    PHP Code:
    select 
        p_emp
    .employer_no
        
    p_emp.legal_name
        
    p_emp.ppb_address2
        
    p_emp.ppb_city
        
    p_emp.ppb_state
        
    p_emp.ppb_zip
        
    p_mail.addr_line2
        
    p_mail.city
        
    p_mail.state
        
    p_mail.zip
        
    p_name.name
        
    p_name.type
        
    p_name.name as name_ex
        
    soundex(p_emp.legal_name) as lglnm_ex 
    from 
        
    (select 
            
    from pa_employer where status in ('01','02')) p_emp,
        (
    select employer_nonametype 
            from pa_name where type NOT IN 
    ('9','91','92')) p_name,
        
    pa_mailing_addr p_mail
    where
            p_emp
    .employer_no p_name.employer_no (+) 
       and 
    p_emp.employer_no p_mail.employer_no (+) 
       and (
    p_emp.legal_name like '%YAKIMA%' 
            
    or p_name.name like '%YAKIMA%'); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •