This discussion is archived
9 Replies Latest reply: Apr 1, 2013 1:17 AM by 864103 RSS

performance 11g

864103 Newbie
Currently Being Moderated
Hi all

oracle 11g.2 ASM RAC under OL 5.6

i have sql statemant running by dev team and this update contain rowid
and i know when using the rowid the update will take more time to finish any way
i use the below query to find the complete %
SQL> SELECT * FROM (select username,opname,sid,serial#,context,sofar,totalwork
,round(sofar/totalwork*100,2) "% Complete"
from v$session_longops where totalwork !=0)
WHERE "% Complete" != 100;  2    3    4

no rows selected

SQL>
and always it working fine with me , when i check the this query in OEM i found that query
first table scan and then sort and then update (path of the query)
when i run the above query it show me the % was increased till reach 100% (table-scan) and then finished
and in OEM it's done but till now the query still running and i don't know why my query return no rows and from OEM there is no record for this update
in top activity

also i recommend the dev to use the below parameter before running the update after i increase the undo and temp and replace the mod of this table to nologging
alter session enable parallel dml;
alter session set sort_area_size=1800000000;
alter session set hash_area_size=1800000000;
please any suggestion ??? and how can i improve the update ???
  • 1. Re: performance 11g
    Justin Cave Oracle ACE
    Currently Being Moderated
    I'm not sure what your question is.

    It is perfectly normal for a query to generate multiple rows in v$session_longops that will each go to 100% completion. A query that does a number of table scans, a bunch of sorts, etc. can easily generate multiple rows. A query will only have a row in v$session_longops when a single operation is long-running. A query that is doing a bunch of nested loop joins probably won't have anything in v$session_longops. So your description seems perfectly normal and expected.

    If you are asking us for help tuning your query, you're going to need to post a lot more information (the query you're trying to tune, for example). The FAQ has a list of the sort of information we'd need. It would probably also help to explain why you believe that setting a manual sort and hash size will improve performance.

    Justin
  • 2. Re: performance 11g
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    it was entirely accidental that this query was showing correct progress of the UDPATE statement, because it only works well if your query's execution time is mostly spent on one and only one long operation ("longop"). Apparently, because of change in the plan or the data, other operations started to take longer than they used to, or maybe concurrency issues are preventing the statement from progressing once the read part is done.

    If you have the Diagnostic Pack license, then the easiest way to provide us information necessary for tuning this statement is by posting here the result of select dbms_sqltune.report_sql_monitor(sql_id => :this_sql_id) from dual (be sure to use
     tags to preserve formatting) together with the explain plan with the predicate section (SQL monitor doesn't contain predicate information).
    
    Best regards,
      Nikolay
    
    Edited by: Nikolay Savvinov on Apr 1, 2013 11:03 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 3. Re: performance 11g
    864103 Newbie
    Currently Being Moderated
    kindly check the below
         update RGA_90DAYS t1
    set t1.RECHARGE=( select (case when (TRADETIME +90) ='1-Mar-2013' then 90
    when (TRADETIME+90)>'1-Mar-2013' then 80
    else 100 end )
    from MDN t4
    where T1.MDN=t4.MSISDN
    and trunc (TRADETIME)<='1-mar-2013')
    
    
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------                                                                                        --------------------------------------------------------------------------------                                                                                        --------------------------------------------------------------------------------                                                                                        ------------------------------------------------------------
    Plan hash value: 1450797432
    
    --------------------------------------------------------------------------------                                                                                        -
    | Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
    --------------------------------------------------------------------------------                                                                                        -
    |   0 | UPDATE STATEMENT   |            |   518K|    12M|   220M  (2)|736:04:36                                                                                         |
    |   1 |  UPDATE            | RGA_90DAYS |       |       |            |         |
    |   2 |   TABLE ACCESS FULL| RGA_90DAYS |   518K|    12M|   759   (2)| 00:00:10                                                                                         |
    |*  3 |   TABLE ACCESS FULL| MDN        |  1517 | 54612 |   425   (2)| 00:00:06                                                                                         |
    --------------------------------------------------------------------------------                                                                                        -
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("T4"."MSISDN"=:B1 AND TRUNC(INTERNAL_FUNCTION("TRADETIME"))
                  <=TO_DATE(' 2013-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    20 rows selected.
    please share with me how can improve this statemant please ?
  • 4. Re: performance 11g
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    according to the plan Oracle has to execute the subquery involving a full table scan of the MDN table 0.5M times. Apparently, something is preventing the optimizer from transforming the subquery into a join (which would have been more less expensive) -- maybe a parameter in the optimizer environment, maybe something else. A detailed analysis of the 10053 trace file would most probably give the answer, but this is not an easy job.

    Alternatively, you could experiment with the query -- e.g. see if USE_HASH hint produces a better plan, or rewrite the UPDATE as a MERGE.

    Best regards,
    Nikolay
  • 5. Re: performance 11g
    864103 Newbie
    Currently Being Moderated
    thanks man for your support , i will be a high appreciated if you guide me how to use USE_HASH
    also kindly i need your help with me to replace this update with merge please.
  • 6. Re: performance 11g
    864103 Newbie
    Currently Being Moderated
    any help on my studied query .
  • 7. Re: performance 11g
    Purvesh K Guru
    Currently Being Moderated
    Looks like you are in a Hurry!!! But you have to understand, this isn't like a Paid support wherein you will have an SLA for response.

    This looks like a Query that might help you. Did you try Nikolay's suggestion of using Hint? What was the outcome?
    merge into rga_90days t1 using
    (
      select (
          case
            when
              (
                tradetime + 90
              )
              = '1-Mar-2013'
            then 90
            when
              (
                tradetime + 90
              )
              > '1-Mar-2013'
            then 80
            else 100
          end) col
      from mdn t4
      where trunc (tradetime) <= '1-mar-2013'
    )
    t4 on
    (
      t1.mdn = t4.msisdn
    )
    when matched then
      update
      set t1.recharge = t4.col;
  • 8. Re: performance 11g
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi
    861100 wrote:
    thanks man for your support , i will be a high appreciated if you guide me how to use USE_HASH
    also kindly i need your help with me to replace this update with merge please.
    thanks, but no

    go to Oracle online documentation ( http://www.oracle.com/pls/db112/homepage ) and learn how to do your work, or hire yourself a paid consultant

    Best regards,
    Nikolay
  • 9. Re: performance 11g
    864103 Newbie
    Currently Being Moderated
    yes , you're right.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points