7 Replies Latest reply: Feb 24, 2013 12:48 PM by JohnWatson RSS

    running query

    864103
      Hi all ,

      oracle 11g.2 under OL 5.6

      from yesterday i do i delete statemant of 33.000,000 record in my tables i apply it vie crontab and i put log file so i can know th number of records that deleted after commit;
      but today morning i can't found any thing in the log file also i have query to know the complete % of my quert it's done (i mean there is no records on this query )

      alter session enable parallel dml;
      DELETE FROM  MTR_EPPC_CALLED_DATA
      WHERE rowid not in
      (SELECT MIN(rowid)
      FROM MTR_EPPC_CALLED_DATA
      GROUP BY  FILE_CODE,STREAMNUMBER,SERVICEKEY,CALLINGPARTYNUMBER,CALLEDPARTYNUMBER,CHARGEPARTYINDICATOR
      ,CHARGETYPE,ROAMFLAG,ROAMAREANUMBER,SCPID,MSCADDRESS,MSCID,LAI,CELLID,MIN,CALLSTARTTIME,CALLSTOPTIME
      ,CALLDURATION,CALLCOST,MOBILECOST,ROAMCOST,TOLLCOST,EXTRACOST,CALLINGVPLMN,CALLINGHPLMN,CALLEDVPLMN
      ,CALLEDHPLMN,CALLINGVPROVNO,CALLINGHPROVNO,CALLEDVPROVNO,CALLEDHPROVNO,CHARGERULE,USERTYPE,CALLMODE
      ,TAX,BALANCEBEFORECALL,EXTROAMFEE,COLLECTCALLFEE,CARRIERCODE,ACCOUNTID,ACCOUNTTYPE,CHARGPARTYNUMBE
      ,CALLTYPE,CHARGETYPE2,ROAMFLAG2,TALKTYPE,IVRCHARGETYPE,SEQ_NO);
      commit;
      and the below is my 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
      also be informed yesterday when i run the query i found the percentage is 10% and today morning is no row selected
      so how can i know is my query still running or not ??? so if is not i can reexcuted again ???


      thanks
        • 1. Re: running query
          rp0428
          Why would you reexecute it if it is done?

          Did you check the table to see if the rows had been deleted?
          • 2. Re: running query
            864103
            i'm not sure it's done or not and now i will check the duplicate records in my tables and i will see if it's deleted or not
            but i don't know why it's not right anything in log file , i delete the duplicated record in 5 tables and it right in log file
            but this last one i don't know why not write anything ? this tables contain 153,000.000 and the delete must be 31,000,000?

            thanks
            • 3. Re: running query
              864103
              i check it again vie apply the query that retrive the dupliacted record it show me the same number 33,000,000
              i will run the delete again , but i'm confuse why from yesterday till today morning and there is no result in log file
              and i check there is no one kill my process?
              • 4. Re: running query
                864103
                Hi , i set the below parameter and till now it's same issue there is no result
                SQL> alter system set aq_tm_processes=1;
                and i found the below wait
                  COUNT(*) EVENT
                ---------- ----------------------------------------------------------------
                         1 ASM background timer
                         9 SQL*Net message from client
                         1 wait for unread message on broadcast channel
                         1 Streams AQ: waiting for messages in the queue
                         1 asynch descriptor resize
                         1 DFS lock handle
                         1 ges remote message
                         2 gcs remote message
                        25 rdbms ipc message
                         1 pmon timer
                         1 db file sequential read
                         2 class slave wait
                         1 Streams AQ: qmn slave idle wait
                         1 GCR sleep
                         1 Space Manager: slave idle wait
                         1 VKTM Logical Idle Wait
                         1 Streams AQ: waiting for time management or cleanup tasks
                         1 Streams AQ: qmn coordinator idle wait
                         2 DIAG idle wait
                         1 PING
                please advice
                • 5. Re: running query
                  864103
                  any help guys , it's take 7 hour and till now it's not end ???

                  please you suggestion will be a high appreciated.
                  • 6. Re: running query
                    864103
                    any help in this
                    PLAN_TABLE_OUTPUT
                    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    Plan hash value: 364879819
                    
                    ------------------------------------------------------------------------------------------------------
                    | Id  | Operation             | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                    ------------------------------------------------------------------------------------------------------
                    |   0 | DELETE STATEMENT      |                      |   129M|  2968M|       |  8887K  (1)| 29:37:32 |
                    |   1 |  DELETE               | MTR_EPPC_CALLED_DATA |       |       |       |            |          |
                    |*  2 |   HASH JOIN ANTI      |                      |   129M|  2968M|  2968M|  8887K  (1)| 29:37:32 |
                    |   3 |    TABLE ACCESS FULL  | MTR_EPPC_CALLED_DATA |   129M|  1484M|       |  1138K  (1)| 03:47:39 |
                    |   4 |    VIEW               | VW_NSO_1             |   129M|  1484M|       |  7452K  (1)| 24:50:29 |
                    |   5 |     SORT GROUP BY     |                      |   129M|    27G|    41G|  7452K  (1)| 24:50:29 |
                    |   6 |      TABLE ACCESS FULL| MTR_EPPC_CALLED_DATA |   129M|    27G|       |  1145K  (1)| 03:49:05 |
                    ------------------------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       2 - access(ROWID="MIN(ROWID)")
                    thanks
                    • 7. Re: running query
                      JohnWatson
                      861100 wrote:
                      any help guys , it's take 7 hour and till now it's not end ???

                      please you suggestion will be a high appreciated.
                      If you query v$sql_monitor you will be able to see the progress of the statement.
                      --
                      John Watson
                      Oracle Certified Master DBA
                      http://skillbuilders.com