9 Replies Latest reply: Apr 1, 2013 8:17 AM by 864103 RSS

    performance 11g

      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
      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
          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.

          • 2. Re: performance 11g
            Nikolay Savvinov

            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,
            Edited by: Nikolay Savvinov on Apr 1, 2013 11:03 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 3. Re: performance 11g
              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 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'))
                 - 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

                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,
                • 5. Re: performance 11g
                  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
                    any help on my studied query .
                    • 7. Re: performance 11g
                      Purvesh K
                      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 (
                                  tradetime + 90
                                = '1-Mar-2013'
                              then 90
                                  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
                        set t1.recharge = t4.col;
                      • 8. Re: performance 11g
                        Nikolay Savvinov
                        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,
                        • 9. Re: performance 11g
                          yes , you're right.