This discussion is archived
7 Replies Latest reply: Feb 24, 2013 10:48 AM by JohnWatson RSS

running query

864103 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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