![]() ![]() R.orig_no orig_no, COUNT (r.orig_no) number_of_calls, My another query is when all the operations of this query have finished why is v$session_longops is still reporting this query? and v$session_wait's column "seconds_in_wait" is incresing contuniously. I have queried the V$ views while this report is running. My query is, where am I facing the problem? is it at db level in executing the query or there is something woring with the oracle report (taking time in formatting pages). I had an another same report that had 635K rows and that report executed in just one hour. The query used in report is given under, it is to return 592K rows. I am facing a problem that my report (oracle report) hangs and after 5-6 hours I have to kill it. Here it isĭoes this indicate anything? Or should I run some other scripts? I have taken the dump for this session from V$PROCESS also. RESOURCE_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP I have taken the dump for this session from V$SESSION. There is no entry for this session in V$SESSION_LONGOPS So its not doing an index scan for the count(*)ġ 0 TABLE ACCESS (FULL) OF 'IF_CTC_SUMMARY'Īnd when I a do * from if_ctc_summary it returns 5 rows and then just hangs ![]() SQL> select count(*) from if_ctc_summary Ģ 1 TABLE ACCESS (FULL) OF 'IF_CTC_SUMMARY' This is the execution plan for the slect * against the select count(*) The table is small with a few columns and normal data types like char, number etc. In fact, I cannot get it to "not" show up. ![]() It takes a couple of seconds - but it shows up. MESSAGE : Table Scan: BIG_TABLE.BIG_TABLE: 1600 out of 22822 Blocks done Popped over to another window begin print_table( 'select * from v$session_longops where sid = 15' ) Which shows how long it took, how long it will probably take and where it is in the processing. MESSAGE : Table Scan: OPS$TKYTE.T: 30735 out of 38337 Blocks done You can simply open another session, use my showsql.sql to find your session (search for showsql on my site) and then query v$session_longops like this: Anything that takes more then a couple of seconds will start to populate the v$session_longops table. I do not see why you would need to count the totally number of records ( would need to know more about your processing to suggest a way to AVOID having to do that ) but you can monitor the progress. Well, sqlplus isn't hanging, its just waiting for the answer. ![]() How should I do it, is there a script which I can write, to do this. I know it is going to take time to return the total number, but what I want is a routine which can tell me how many records have been counted at a particular given point of time, or whether the count has stopped inbetween due to some reason. I need a solution becasue this is taking toomuch of effort and time. I have other tables in which I have 2 to 10 million records. How should I know what is happening within, whether the sqlplus has hanged up, or it it is going to return with the total number of records. When I do a select count(*) the sqlplus hangs up, and never returns it is as if it is going on for ever and ever. I have this table t, which has a million records.įor my work, I need to keep checking the number of total records, many number of times in a day. These days Iam facing a huge problem which is wasting a lot of mytime. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |