Skip to Main Content
  • Questions
  • sqlplus hangs and takes for ever to do this

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Nag.

Asked: August 16, 2001 - 5:03 pm UTC

Last updated: June 22, 2021 - 1:46 am UTC

Version: 8.1.5

Viewed 100K+ times! This question is

You Asked

Tom

These days Iam facing a huge problem which is wasting a lot of mytime.

I have this table t , which has a million records.

For my work, I need to keep checking the number of total records , many number of times in a day.

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.

How should I deal with it. 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.

I have other tables in which I have 2 to 10 million records. I need a solution becasue this is taking toomuch of effort and time.

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.

How should I do it, is there a script which I can write, to do this.
Please help, I need it desperately.

thank you



and Tom said...

Well, sqlplus isn't hanging, its just waiting for the answer.

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. Anything that takes more then a couple of seconds will start to populate the v$session_longops table. 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:

1 begin
2 print_table( 'select *
3 from v$session_longops
4 where sid =16
5 and time_remaining > 0 ' );
6* end;

(search my site for print_table as well)

It'll show you something like:

SID : 16
SERIAL# : 1556
OPNAME : Table Scan
TARGET : OPS$TKYTE.T
TARGET_DESC :
SOFAR : 30735
TOTALWORK : 38337
UNITS : Blocks
START_TIME : 16-aug-2001 19:32:51
LAST_UPDATE_TIME : 16-aug-2001 19:33:03
TIME_REMAINING : 3
ELAPSED_SECONDS : 12
CONTEXT : 0
MESSAGE : Table Scan: OPS$TKYTE.T: 30735 out of 38337 Blocks done
USERNAME : OPS$TKYTE
SQL_ADDRESS : 82E2A364
SQL_HASH_VALUE : 737329258
QCSID : 0

which shows how long it took, how long it will probably take and where it is in the processing..


Rating

  (46 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A possible alternative

Connor, August 17, 2001 - 5:42 am UTC

If you are not adding too many records per day, but counting them very frequently, you could have a row-trigger than adds/subtracts deltas and stores the count in a secondary table. (Presumably a materialised view could achieve the same thing)

Connor , tell us more about that

A reader, August 17, 2001 - 9:53 am UTC

How should we do what you are suggesting

Tom Kyte
August 17, 2001 - 12:49 pm UTC

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:424949760671 <code>
for a materialized view exmaple.

alternatively you can:

create table COUNTER ( x int );
insert into counter select count(*) from t;


create or replace trigger t_trigger
after insert or delete on T for each row
begin
if ( insertng ) then update counter set x = x+1;
else update counter set x = x-1;
end if;
end;


Beware -- both of the above would imply necessarily DECREASED concurrency on the table T. You would in effect be serializing inserts/deletes on T since every insert/delete must update a single row.



Analyze table

Andrew, August 17, 2001 - 12:12 pm UTC

If you don't need an exact number, then consider analyzing the table using "estimate".
analyze table ABC estimate statistics;
select table_name, num_rows from user_tables where table_name = 'ABC';

Connor's solution adds work and that may not help

Harrison, August 17, 2001 - 1:09 pm UTC

Hi: Connor's solution (a row level trigger) will add some processing to the database, and that may not be something the users can afford. It might be better to use table growth to estimate the number of rows (after you have a million, that should be pretty easy). Anyway, the hard part of this question is what can possibly be the difference between 1 million rows and 1.09 (the other hard question is why a smart person like Conner wants to leave a nice place like England for a colony? ). Anyway, Tom's suggestion is interesting, another table that I did not know existed, and a neat script to use it. With a slight bit of magic, we might make a web page updates the progress, and quiets the the complainers who need that number (when "A lot!" would do.)

but not working

Mohammed Ahmed, August 18, 2001 - 11:24 am UTC

I searched and found print_table script which you use 8.0 and below. It doesnt work.

It gives the following error:

PLS-00302: component 'DESC_TAB' must be declared


Tom Kyte
August 18, 2001 - 2:55 pm UTC

The URL you reference is:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:584023239495 <code>

That has the script printtbl8.sql that I use in 8.0 (due to no autonomous transactions).

In 7.3 and before, which you must be using, it'll not work since DBMS_SQL did not have a "describe" functionality in that release. I use that functionality to get the column names -- without it, I cannot do that.

I don't have a print_table script for 7.3 and before for this reason.

Alternative to PRINT_TABLE

Andrew, August 20, 2001 - 5:28 pm UTC

I haven't tested this in 7.x, but it looks like it will be OK.
</code> https://asktom.oracle.com/magazine-archive.htm <code>

Alternative to PRINT_TABLE

Andrew, August 20, 2001 - 5:31 pm UTC

Whoops the URL didn't come out:

asktom.oracle.com/magazine-archive.htm?120400.html

what about DDL monitoring?

Dean, September 27, 2002 - 3:31 pm UTC

I am trying to create an index for datawarhousing that will be several gigs in size. When I query v$session_longops, I can't see it.

How can I track the progress of building a big index before it becomes a perm segment?

TIA

Tom Kyte
September 27, 2002 - 4:49 pm UTC

It should be in v$session_longops.

I fired up a session (sid = 15).  I started a create unique index in it.  Popped over to another window and:

ops$tkyte@ORA817DEV.US.ORACLE.COM> begin  print_table( 'select * from v$session_longops where sid = 15' );
  2  end;
  3  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /
SID                           : 15
SERIAL#                       : 100
OPNAME                        : Table Scan
TARGET                        : BIG_TABLE.BIG_TABLE
TARGET_DESC                   :
SOFAR                         : 1600
TOTALWORK                     : 22822
UNITS                         : Blocks
START_TIME                    : 27-sep-2002 16:47:07
LAST_UPDATE_TIME              : 27-sep-2002 16:47:19
TIME_REMAINING                : 159
ELAPSED_SECONDS               : 12
CONTEXT                       : 0
MESSAGE                       : Table Scan:  BIG_TABLE.BIG_TABLE: 1600 out of 22822 Blocks done
USERNAME                      : BIG_TABLE
SQL_ADDRESS                   : 01A7FBF4
SQL_HASH_VALUE                : 0
QCSID                         : 0
-----------------

PL/SQL procedure successfully completed.


It takes a couple of seconds -- but it shows up. In fact, I cannot get it to "not" show up. 

query is hanging

Kapil, April 05, 2004 - 12:43 pm UTC

Hi Tom,

I am having a problem where a query of the form:
(SELECT * FROM table) is hanging after giving 5 rows but when I give a (SELECT COUNT(*) FROM table) it returns 57 records. The problem is specifically for this table. When I fire a SELECT * from any other table its fine.

What could be the reason? What should I check to debug this? Should I run any scripts to give you data to help me debug this problem?


Tom Kyte
April 05, 2004 - 5:02 pm UTC

hmmm, select * vs count(*) with no predicate...

or are we only seeing the tip of the iceberg that is your question.


I've a feeling we are comparing


select * from <something really complex>

vs

select count(*) from <something really complex>

you do understand that count(*) can do things entirely index based (using indexes as tall skinny tables without ever going to the table, or just skipping the table access by index rowid) vs count(*)

count(*) isn't very realistic as a "query performance evaluation"



No Entry in V$SESSION_LONGOPS too

Kapil, April 06, 2004 - 2:12 am UTC

The table is small with a few columns and normal data types like char, number etc.

This is the execution plan for the slect * against the select count(*)

SQL> select count(*) from if_ctc_summary;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'IF_CTC_SUMMARY'


So its not doing an index scan for the count(*)

SQL> select * from if_ctc_summary;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'IF_CTC_SUMMARY'


SQL> set autotrace off
SQL> select count(*) from if_ctc_summary;

  COUNT(*)
----------
        57

and when I a do * from if_ctc_summary it returns 5 rows and then just hangs

There is no entry for this session in V$SESSION_LONGOPS

I have taken the dump for this session from V$SESSION ... Here it is

SADDR                         : 000000038023BFB0
SID                           : 181
SERIAL#                       : 50352
AUDSID                        : 343247
PADDR                         : 00000003801AE3A8
USER#                         : 816
USERNAME                      : OR4
COMMAND                       : 3
OWNERID                       : 2147483644
TADDR                         : 0000000380512F40
LOCKWAIT                      :
STATUS                        : INACTIVE
SERVER                        : DEDICATED
SCHEMA#                       : 816
SCHEMANAME                    : OR4
PROCESS                       : 18602
TERMINAL                      : pts/24
TYPE                          : USER
SQL_ADDRESS                   : 000000038CD9A278
SQL_HASH_VALUE                : 1960482032
PREV_SQL_ADDR                 : 000000038CD9A278
PREV_HASH_VALUE               : 1960482032
MODULE                        : SQL*Plus
MODULE_HASH                   : 3669949024
ACTION                        :
ACTION_HASH                   : 4029777240
CLIENT_INFO                   :
FIXED_TABLE_SEQUENCE          : 61
ROW_WAIT_OBJ#                 : -1
ROW_WAIT_FILE#                : 0
ROW_WAIT_BLOCK#               : 0
ROW_WAIT_ROW#                 : 0
LAST_CALL_ET                  : 448
PDML_ENABLED                  : NO
FAILOVER_TYPE                 : NONE
FAILOVER_METHOD               : NONE
FAILED_OVER                   : NO
RESOURCE_CONSUMER_GROUP       : DEFAULT_CONSUMER_GROUP
PDML_STATUS                   : DISABLED
PDDL_STATUS                   : ENABLED
PQ_STATUS                     : ENABLED


I have taken the dump for this session from V$PROCESS also ... Here it is

ADDR                          : 00000003801AE3A8
PID                           : 188
SPID                          : 18613
SERIAL#                       : 27
TERMINAL                      : pts/24
TRACEID                       :
BACKGROUND                    :
LATCHWAIT                     :
LATCHSPIN                     :

Does this indicate anything? Or should I run some other scripts?
 

Tom Kyte
April 06, 2004 - 9:15 am UTC

status is inactive, if it is hanging, it is more likely a client issue -- inactive means "the server is waiting for client to tell it to do something"


perhaps you have "pause on" in sqlplus or something? did you hit "enter"?

Kapil, April 06, 2004 - 10:28 am UTC

the set pause is not on

the strange part is that it doesnt happen with the other tables which have even more rows i.e. it doesnt hang and all the rows are displayed

what else could it be?


Tom Kyte
April 07, 2004 - 8:30 am UTC

what client are you using, something is wrong *with the client*. Not a database issue here (you are not even in the database when this "hang" occurs, you are idle)

Did you query V$SESSION for the right SID?

Mark J. Bobak, April 07, 2004 - 3:40 am UTC

As Tom said, STATUS='INACTIVE'. That means it's not doing
anything. Also, LAST_CALL_ET=448. That means it hasn't done
anything in 448 seconds, or about 7 and 1/2 minutes.
If you look at V$SESION_WAIT for that session, I'd guess
it's waiting on 'sqlnet message from client', which would
also mean it's not doing anything.

-Mark

Resolved

Kapil, April 07, 2004 - 10:23 am UTC

There was some junk value inserted in one of the columns using a Pro*C executable. This column is a number datatype. When the select * was fired after updating this column, the rows were displayed. We were firing the select * commands from SQLPLUS all this while.

Thanks anyway for your help.

Regards
Kapil



Tom Kyte
April 08, 2004 - 8:59 am UTC

what did that pro*c code look like? it wasn't even trying to use the internal number format was it? that would be a horribly *bad* idea.

(and it came down to "client issue" -- sqlplus wasn't able to deal with that garbage number for whatever reason)

Job Terminate

mAg, September 06, 2005 - 7:31 am UTC

Hi Tom

I am interested in reading all your reply, thanks for all your answers.

We are using an DW database, the problem is... I want to terminate the queries which runs more than 5 minutes, but I should not kill the session, Can you give an example for this?

Thanks
mAg

Tom Kyte
September 06, 2005 - 8:42 am UTC

it does not work like that. setting a resource profile in place can stop the query after it uses "X amount of some resource", session terminated.


or you can set up a resource manager policy that won't let a query estimated to take more than 5 minutes even begin (no session termination)

But, in case or wrong statistics...

MAG, September 06, 2005 - 10:09 am UTC

Hi Tom,
Thanks a lot for your time and quick responce.

I will go for second option not to killing the session, let it to ingore the query for processing.

But, my question, if the query is refering to more than one table and some of tables have the old statistics where oracle will obtain a wrong execution plan and the query will not consider for execution. Is there any way to avoid same apart from fresh statistics on all the tables?

Thanks,
MAG



Tom Kyte
September 06, 2005 - 10:43 am UTC

it is called "garbage in, garbage out", if you have bad stats, bad plans will likely be the result.

The way to avoid garbage in/garbage out is to not put garbage in :)

valid statistics are somewhat crucial to the correct operation of the optimizer.

alt, November 14, 2005 - 10:57 am UTC

How i know that particulr session is hanging?
What all are the reason for session hanging?
How to resolve this?

Tom Kyte
November 14, 2005 - 1:42 pm UTC

usually the end user calls you ;)

but you can look for long running active sessions (last_call_et, which is in second, is large and status = ACTIVE in v$session)

they are not necessarily "hung", but they have spent a long time in the database without getting back to the client application. You use v$session_event/session_wait and such to see what they might be/have been waiting on.

Query hanged

Kuldeep, March 06, 2006 - 9:53 am UTC

Hi Tom,

I am facing a problem that my report (oracle report) hangs and after 5-6 hours I have to kill it. The query used in report is given under, it is to return 592K rows. I had an another same report that had 635K rows and that report executed in just one hour.

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 have queried the V$ views while this report is running. 

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.

Please help.

Thanks and regards,

-----------------------------------------
Oracle Version: Oracle8i 8.1.7.4.0
OS            : SunOS Generic_108528-13
-----------------------------------------

******* QUERY ********

SELECT   r.break_item,
         INITCAP (   vcs.address1
                  || DECODE (vcs.address1,
                             NULL, NULL,
                             DECODE (vcs.post_town, NULL, NULL, ',')
                            )
                  || vcs.post_town
                 ) break_desc,
         r.orig_no orig_no, COUNT (r.orig_no) number_of_calls,
         TRUNC (r.call_datetime) call_datetime, r.call_datetime call_time,
         r.destination_no destination_number, NVL (r.call_cost, 0) call_cost,
            vcc.call_code_desc
         || DECODE (vsis_shared.vsis_get_lcc (:customer_no, orig_no),
                    NULL, NULL,
                    ' - IP Call'
                   ) call_code,
         NVL (r.call_duration, 0) basic_call_duration,
         vsis_shared.vsis_display_duration (r.call_duration) call_duration
    FROM vsis_results_6856791 r, vsis_call_codes vcc, vsis_cust_sites vcs
   WHERE r.call_code = vcc.call_code(+) AND r.break_item = vcs.site_code(+)
         AND :customer_no = vcs.customer_no(+)
GROUP BY r.break_item,
         INITCAP (   vcs.address1
                  || DECODE (vcs.address1,
                             NULL, NULL,
                             DECODE (vcs.post_town, NULL, NULL, ',')
                            )
                  || vcs.post_town
                 ),
         r.orig_no,
         r.call_datetime,
         r.destination_no,
         vcc.call_code_desc,
         NVL (r.call_duration, 0),
         NVL (r.call_cost, 0),
         TRUNC (r.call_duration / 3600),
         vsis_shared.vsis_display_duration (r.call_duration)
ORDER BY 1 ASC,
         2 ASC,
         3 ASC,
         5 ASC,
         r.break_item DESC,
         r.orig_no DESC,
         TRUNC (r.call_datetime) DESC,
         call_time ASC

--- SID=68, Serial#=51258

EXPLAIN PLAN OF ABOVE QUERY
-------------------------------------------------- ------------------------------ -------------------- --------------------
OPERATION                                          OPS                            OBJ                              POSITION
-------------------------------------------------- ------------------------------ -------------------- --------------------
SELECT STATEMENT                                                                                                      23088
  SORT                                             ORDER BY                                                               1
    SORT                                           GROUP BY                                                               1
      MERGE JOIN                                   OUTER                                                                  1
        SORT                                       JOIN                                                                   1
          MERGE JOIN                               OUTER                                                                  1
            SORT                                   JOIN                                                                   1
              TABLE ACCESS                         FULL                           VSIS_RESULTS_6856791                    1
            SORT                                   JOIN                                                                   2
              TABLE ACCESS                         FULL                           VSIS_CALL_CODES                         1
        SORT                                       JOIN                                                                   2
          TABLE ACCESS                             FULL                           VSIS_CUST_SITES                         1
---------------------------------------------------------------------------------------------------------------------------



******* NOW MONITORING V$ VIEWS FOR THE ABOVE QUERY ****************

SQL> select * from v$session_longops where sid=68;

      SID   SERIAL# OPNAME             TARGET   TARGET_DESC        SOFAR TOTALWORK UNITS                            START_TIM LAST_UPDA TIME_REMAINING ELAPSED_SECONDS   CONTEXT MESSAGE                                                                   USERNAME SQL_ADDR SQL_HASH_VALUE     QCSID
--------- --------- ------------------ -------- -------------- --------- --------- -------------------------------- --------- --------- -------------- --------------- --------- --------------------------------------------------- ------------------------------ -------- -------------- ---------
       68     50756 SQL Execution                                   2521      2521 units                            06-MAR-06 06-MAR-06              0               9      1000 SQL Execution:  : 2521 out of 2521 units done                             OPS$VSIS B3B3B294      2.580E+09        68
       68     51258 Sort Output                                    10715     10715 Blocks                           06-MAR-06 06-MAR-06              0             222         0 Sort Output:  : 10715 out of 10715 Blocks done                            OPS$VSIS A76DAE60      2.920E+09         0
       68     51258 Sort Output                                     6069      6069 Blocks                           06-MAR-06 06-MAR-06              0             208         0 Sort Output:  : 6069 out of 6069 Blocks done                              OPS$VSIS A76DAE60      2.920E+09         0
       68     51258 Sort Output                                     4620      4620 Blocks                           06-MAR-06 06-MAR-06              0              22         0 Sort Output:  : 4620 out of 4620 Blocks done                              OPS$VSIS A76DAE60      2.920E+09         0
       68     51258 SQL Execution                                  23087     23088 units                            06-MAR-06 06-MAR-06              0             240         0 SQL Execution:  : 23087 out of 23088 units done                           OPS$VSIS A76DAE60      2.920E+09         0
       68     51258 Sort Output                                      995     13005 Blocks                           06-MAR-06 06-MAR-06            314              26         0 Sort Output:  : 995 out of 13005 Blocks done                              OPS$VSIS A76DAE60      2.920E+09         0
       68     50752 SQL Execution                                   2521      2521 units                            06-MAR-06 06-MAR-06              0              10      1000 SQL Execution:  : 2521 out of 2521 units done                             OPS$VSIS B40F4A00      3.503E+09        68


SQL> select * from v$session_event where sid=68
  2  ;

      SID EVENT                                                            TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT  MAX_WAIT
--------- ---------------------------------------------------------------- ----------- -------------- ----------- ------------ ---------
       68 latch free                                                                10             10          21          2.1         3
       68 db file sequential read                                                   92              0          52    .56521739         3
       68 db file scattered read                                                    38              0         172    4.5263158        14
       68 direct path read                                                       22308              0          16    .00071723         1
       68 direct path write                                                          8              0           0            0         0
       68 file open                                                                  8              0           0            0         0
       68 SQL*Net message to client                                              52885              0          13    .00024582         1
       68 SQL*Net more data to client                                               38              0           0            0         0
       68 SQL*Net message from client                                            52885              0        4984    .09424222        36


SQL> select * from v$session_wait where sid=68;

                 SID                 SEQ# EVENT                                                            P1TEXT                                                                             P1 P1RAW    P2TEXT                                                                             P2 P2RAW    P3TEXT                                                                             P3 P3RAW               WAIT_TIME      SECONDS_IN_WAIT STATE
-------------------- -------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------- -------- ---------------------------------------------------------------- -------------------- -------- ---------------------------------------------------------------- -------------------- -------- -------------------- -------------------- -------------------
                  68                30329 SQL*Net message to client                                        driver id                                                                  1111838976 42455100 #bytes                                                                              1 00000001                                                                                     0 00                         -1                    0 WAITED SHORT TIME  


*********** STATUS OF VIEWS AFTER FEW MINUTES **************************
*********** STATUS OF VIEWS AFTER FEW MINUTES **************************

SQL> select sid,serial#,substr(opname,1,20),sofar,totalwork,units,start_time,last_update_time,
  2      time_remaining, elapsed_seconds, context, message ,substr(target,1,10),substr(target_desc,1,10)
  3  from   v$session_longops where sid=68 order by totalwork
  4  /

                 SID              SERIAL# SUBSTR(OPNAME,1,20)                 SOFAR            TOTALWORK UNITS                            START_TIM LAST_UPDA       TIME_REMAINING      ELAPSED_SECONDS              CONTEXT MESSAGE                                           SUBSTR(TAR SUBSTR(TAR
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------------------- --------- --------- -------------------- -------------------- -------------------- ------------------------------------------------------------ ----------
                  68                50756 SQL Execution                        2521                 2521 units                            06-MAR-06 06-MAR-06                    0                    9                 1000 SQL Execution:  : 2521 out of 2521 units done     
                  68                50752 SQL Execution                        2521                 2521 units                            06-MAR-06 06-MAR-06                    0                   10                 1000 SQL Execution:  : 2521 out of 2521 units done     
                  68                51258 Sort Output                          4620                 4620 Blocks                           06-MAR-06 06-MAR-06                    0                   22                    0 Sort Output:  : 4620 out of 4620 Blocks done      
                  68                51258 Sort Output                          6069                 6069 Blocks                           06-MAR-06 06-MAR-06                    0                  208                    0 Sort Output:  : 6069 out of 6069 Blocks done      
                  68                51258 Sort Output                         10715                10715 Blocks                           06-MAR-06 06-MAR-06                    0                  222                    0 Sort Output:  : 10715 out of 10715 Blocks done    
                  68                51258 Sort Output                         13005                13005 Blocks                           06-MAR-06 06-MAR-06                    0                  320                    0 Sort Output:  : 13005 out of 13005 Blocks done    
                  68                51258 SQL Execution                       23088                23088 units                            06-MAR-06 06-MAR-06                    0                  240                    0 SQL Execution:  : 23088 out of 23088 units done   

7 rows selected.

SQL> select * from v$session_event where sid=68 order by total_waits;

                 SID EVENT                                                                     TOTAL_WAITS       TOTAL_TIMEOUTS          TIME_WAITED         AVERAGE_WAIT             MAX_WAIT
-------------------- ---------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------
                  68 direct path write                                                                   8                    0                    0                    0                    0
                  68 file open                                                                           8                    0                    0                    0                    0
                  68 db file scattered read                                                             38                    0                  172 4.526315789473684211                   14
                  68 SQL*Net more data to client                                                        38                    0                    0                    0                    0
                  68 db file sequential read                                                            92                    0                   52 .5652173913043478261                    3
                  68 latch free                                                                        132                   84                   30 .2272727272727272727                    3
                  68 direct path read                                                                33004                    0                   25 .0007574839413404436                    1
                  68 SQL*Net message from client                                                    348546                    0                41738 .1197488997148152611                  427
                  68 SQL*Net message to client                                                      348547                    0                   90 .0002582148175138504                    1

9 rows selected.

SQL> select * from v$session_wait where sid=68;

                 SID                 SEQ# EVENT                                                            P1TEXT                                                                             P1 P1RAW    P2TEXT                                                                             P2 P2RAW    P3TEXT                                                                             P3 P3RAW               WAIT_TIME      SECONDS_IN_WAIT STATE              
-------------------- -------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------- -------- ---------------------------------------------------------------- -------------------- -------- ---------------------------------------------------------------- -------------------- -------- -------------------- -------------------- -------------------
                  68                 9529 SQL*Net message from client                                      driver id                                                                  1111838976 42455100 #bytes                                                                              1 00000001                                                                                     0 00                          0                 1264 WAITING            

SQL> /

                 SID                 SEQ# EVENT                                                            P1TEXT                                                                             P1 P1RAW    P2TEXT                                                                             P2 P2RAW    P3TEXT                                                                             P3 P3RAW               WAIT_TIME      SECONDS_IN_WAIT STATE              
-------------------- -------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------- -------- ---------------------------------------------------------------- -------------------- -------- ---------------------------------------------------------------- -------------------- -------- -------------------- -------------------- -------------------
                  68                 9529 SQL*Net message from client                                      driver id                                                                  1111838976 42455100 #bytes                                                                              1 00000001                                                                                     0 00                          0                 1282 WAITING            



*********** AFTER SOME MORE TIME ***************
*********** AFTER SOME MORE TIME ***************
*********** AFTER SOME MORE TIME ***************

SQL> select * from v$session_wait where sid=68;

                 SID                 SEQ# EVENT                                                            P1TEXT                                                                             P1 P1RAW    P2TEXT                                                                             P2 P2RAW    P3TEXT                                                                             P3 P3RAW               WAIT_TIME      SECONDS_IN_WAIT STATE
-------------------- -------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------- -------- ---------------------------------------------------------------- -------------------- -------- ---------------------------------------------------------------- -------------------- -------- -------------------- -------------------- ------------------
                  68                 9529 SQL*Net message from client                                      driver id                                                                  1111838976 42455100 #bytes                                                                              1 00000001                                                                                     0 00                          0                 2784 WAITING

-----------------------***----------------------- 

analyze index hangs

A reader, November 30, 2006 - 12:40 pm UTC

Hi,

My analyze index statement

ANALYZE INDEX IDX_TRAN_DATE ESTIMATE STATISTICS SAMPLE 25 PERCENT ;

is not doing anything even though it is shown as active

When I check v$session_wait i find, that session is waiting for event 'library cache lock' and p1text/p2text are handle adress/lock address respectively for over 100 seconds

What could be the issue here ?

thanks
Anto


Tom Kyte
November 30, 2006 - 2:01 pm UTC

don't use that command, use dbms_stats. Analyze is deprecated for gathering statistics.

A reader, November 30, 2006 - 4:10 pm UTC

I know dbms_stats is the norm now, but this 8174 version of oracle and the 'analyze ...' is already embedded into the program

I searched your site for that wait event but could not find any

Anto

Tom Kyte
November 30, 2006 - 5:50 pm UTC

that command would want to invalidate the cursor cache among other things - needing the library cache locks and if someone else held it in an incompatible mode..

A reader, December 01, 2006 - 11:54 am UTC

thanks
Anto

sql plus

A reader, March 27, 2008 - 3:58 pm UTC

Tom:

Do you know why i get this when i do print_table on a large table sometimes.

is it because I have a varchar2(1500) column? I tried to expand the buffer size to 10000 and it wont accept it.


ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "MSGADMIN.PRINT_TABLE", line 40
ORA-06512: at line 1
Tom Kyte
March 30, 2008 - 8:38 am UTC

SQL> set serveroutput on size 1000000

or in 10g


SQL> set serveroutput on size unlimited

count(1) instead of count(*)

Dhairyasheel Ttawde, March 31, 2008 - 2:28 am UTC

Hi with regard to count(*) problems.
one can try count(1) instead it works faster.
or you can combine a parallel query for the count(1).

places where client is hanging one can try with
where rownum >0 predicate.

also I believe if you hit the compute statistics & later on check num_rows in user_tables, it shows the number of records. Although I'm not too sure about this one.
Tom Kyte
March 31, 2008 - 9:31 am UTC

stop it

it DOES NOT go faster.

In fact, in order to make count(1) work about as fast as count(*), a couple of releases ago we put a bug fix in that makes count(1) be written as count(*) internally

so as to fix the error programmers keep making over and over.

and I'll say hogwash to your rownum > 0 predicate - if you don't understand why something works - do not use it.


and if you compute statistics, num_rows will be set to a value that in most cases becomes immediately wrong - since tables are inserted and deleted from constantly.

To Dhairyasheel Ttawde

A reader, March 31, 2008 - 8:02 am UTC

Hi Dhairyasheel,

You wrote: "Hi with regard to count(*) problems.
one can try count(1) instead it works faster."
Count(1) isn't faster.

See:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245

Regards

print

A reader, April 08, 2008 - 5:48 pm UTC

Tom:

That first one solved the buffer overflow issue

How about this line length overflow? is there a setting for it.

BEGIN print_table('select * from log where mes_no=457'); END;

*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "XXX.PRINT_TABLE", line 40
ORA-06512: at line 1

Tom Kyte
April 09, 2008 - 2:44 pm UTC

10g solves that with a linesize of up to 32k - prior to that, it was 255 characters.

You would have to write your own little "p" function to substr the string and print only 255 at a time.

Speeding up create table script

MK, October 03, 2008 - 6:12 pm UTC

Hi,
I have got the weirdest problem happening to me. I am trying to run a SQL* Plus script that I wrote which has all the table and check constraint generation commands in one .sql file! 

Something like

--------
set echo off
set feedback off
set verify off

timing start table_create_timer

prompt  Creating table and constraints for - ARTIST  
create table artist  (
   id                   NUMBER(10)                     default NULL not null,
   primary_genre_id     NUMBER(10)                     default NULL not null,
   primary_story_id     NUMBER(10)                     default NULL,
   track_set_id         NUMBER(10)                     default NULL,
   primary_image_id     NUMBER(10)                     default NULL,
   image_set_id         NUMBER(10)                     default NULL,
   story_set_id         NUMBER(10)                     default NULL,
   celebrity_playlist_id NUMBER(10)                     default NULL,
   artist_influencers_set_id NUMBER(10)                     default NULL,
   artist_followers_set_id NUMBER(10)                     default NULL,
   artist_contemporaries_set_id NUMBER(10)                     default NULL,
   biography_story_id   NUMBER(10)                     default NULL,
   editor_note_id       NUMBER(10)                     default NULL,
   born_in_country      CHAR(2 CHAR),
   first_release_country CHAR(2 CHAR),
   enabled              NUMBER(1)                      default 1 not null,
   adult_content        NUMBER(1)                      default 0 not null,
   only_sell_complete_release NUMBER(1)                      default 0 not null,
   release_count        NUMBER(6)                      default NULL,
   enabled_date         TIMESTAMP(3),
   born_or_formed_date  TIMESTAMP(3),
   first_release_date   TIMESTAMP(3),
   kind                 VARCHAR2(32 CHAR)              default 'BAND',
   guid                 VARCHAR2(32 CHAR)               not null,
   artist_home_url      VARCHAR2(1024 CHAR)            default NULL,
   short_name           VARCHAR2(32 CHAR)               not null,
   name                 VARCHAR2(256 CHAR)              not null,
   description          VARCHAR2(256 CHAR)             default NULL,
   data_classification  NUMBER(10)                     default 400000 not null,
   created              TIMESTAMP(3)                   default SYSDATE not null,
   inserted             TIMESTAMP(3)                   default SYSDATE not null,
   modified             TIMESTAMP(3)                   default SYSDATE not null,
   constraint pk_artist primary key (id)
);

alter table artist
   add constraint ckc_id_artist check (ID >= 0);

alter table artist
   add constraint ckc_primary_genre_id_artist check (PRIMARY_GENRE_ID >= 0);

alter table artist
   add constraint ckc_primary_story_id_artist check (PRIMARY_STORY_ID is null or (PRIMARY_STORY_ID >= 0));

alter table artist
   add constraint ckc_track_set_id_artist check (TRACK_SET_ID is null or (TRACK_SET_ID >= 0));

alter table artist
   add constraint ckc_primary_image_id_artist check (PRIMARY_IMAGE_ID is null or (PRIMARY_IMAGE_ID >= 0));

alter table artist
   add constraint ckc_image_set_id_artist check (IMAGE_SET_ID is null or (IMAGE_SET_ID >= 0));

alter table artist
   add constraint ckc_story_set_id_artist check (STORY_SET_ID is null or (STORY_SET_ID >= 0));

alter table artist
   add constraint ckc_celebrity_playlis_artist check (CELEBRITY_PLAYLIST_ID is null or (CELEBRITY_PLAYLIST_ID >= 0));

alter table artist
   add constraint ckc_artist_influencer_artist check (ARTIST_INFLUENCERS_SET_ID is null or (ARTIST_INFLUENCERS_SET_ID >= 0));

alter table artist
   add constraint ckc_artist_followers__artist check (ARTIST_FOLLOWERS_SET_ID is null or (ARTIST_FOLLOWERS_SET_ID >= 0));

alter table artist
   add constraint ckc_artist_contempora_artist check (ARTIST_CONTEMPORARIES_SET_ID is null or (ARTIST_CONTEMPORARIES_SET_ID >= 0));

alter table artist
   add constraint ckc_biography_story_i_artist check (BIOGRAPHY_STORY_ID is null or (BIOGRAPHY_STORY_ID >= 0));

alter table artist
   add constraint ckc_editor_note_id_artist check (EDITOR_NOTE_ID is null or (EDITOR_NOTE_ID >= 0));

alter table artist
   add constraint ckc_born_in_country_artist check (BORN_IN_COUNTRY is null or (BORN_IN_COUNTRY = upper(BORN_IN_COUNTRY)));

alter table artist
   add constraint ckc_first_release_cou_artist check (FIRST_RELEASE_COUNTRY is null or (FIRST_RELEASE_COUNTRY = upper(FIRST_RELEASE_COUNTRY)));

alter table artist
   add constraint ckc_enabled_artist check (ENABLED in (0,1));

alter table artist
   add constraint ckc_adult_content_artist check (ADULT_CONTENT in (0,1));

alter table artist
   add constraint ckc_only_sell_complet_artist check (ONLY_SELL_COMPLETE_RELEASE in (0,1));

alter table artist
   add constraint ckc_kind_artist check (KIND is null or (KIND in ('BAND','ORCHESTRA','COMPOSER','SONGWRITER','CONDUCTOR','LYRICIST','MUSICIAN','SINGER','GROUP','BASS PLAYER','LEAD GUITAR','DRUMS','GUITAR','PERCUSSION','KEYBOARDS','MANAGER','ROADIE','EMPRESSARIO','PRODUCER','STRINGS','WIND INSTRUMENT','OTHER') and KIND = upper(KIND)));

alter table artist
   add constraint ckc_guid_artist check (GUID = lower(GUID) and length(GUID) = 32);

// -------------
// MANY OTHER SUCH TABLE GENERATION SCRIPTS....FOLLOW!!!
// -------------
TIMING OFF;

Now someone else has written an ANT Task in Java to do the same and his ANT task simply blitzes through this table creation in less than half the time it takes my SQL *Plus script! Its kind of weird. Below is the ant task. Doesn't seem to be doing anything special. Is there something I can do to achieve a speedup in my script? Should I be turning off something? What I don't understand is that he runs the exact same set of .sql files on my hard disk. This is on a local 10g XE box by the way.

---------
        <sql
            driver="${database.driver}"
            url="${database.url}"
            userid="${database.userid}"
            password="${database.password}"
            classpathref="buildutils.classpath"
            onerror="continue">
            <fileset dir="${database.ddl.dir}/tables">
                <include name="*.sql"/>
                <exclude name="create-tables.sql"/>
            </fileset>
        </sql>


Tom Kyte
October 06, 2008 - 2:43 pm UTC

tkprof it

and what is it "half of"?

and are they using sqlplus or something else to run the SQL?

Speeding up create table script

MK, October 03, 2008 - 6:29 pm UTC

To continue.. by the looks of it the ANT Task caches the JDBC driver and loader. There has to be some sort of caching going on to increase the speed with which it is able to create these tables etc. Can I do something similar with my sql file that gets called in SQL Plus? Is it possible for me to PIN this file into the Oracle 10g XE memory? or some sort of keep cache? Your suggestions would be valuable!

speeding up create table script

MK, October 03, 2008 - 7:21 pm UTC

Hi,
After doing some further investigation, I logged in as SYSTEM to query the v$sqlarea table and found that when the ANT taks was running it generated a whole bunch of statements like the one below for every single table.
Can you please explain how this works and how I can use this in my table generation script? I wasn't able to run the select as it complained that the table SDO_TOPO_METADATA_TABLE doesn't exist. I even looked it up in ALL_TABLES and ALL_OBJETS but couldn't find it. Do you think that the ANT task taps into some sort of metadata table to check if its already got something there and then if so it skips it for the table generation? Can I take advantage of this feature in my SQL *Plus script as well please?

-------
SELECT topology FROM SDO_TOPO_METADATA_TABLE a, TABLE(a.Topo_Geometry_Layers) b WHERE b.owner = 'TRASH' AND b.table_name = 'PROMOTION';
------


speeding up create table script

MK, October 03, 2008 - 7:32 pm UTC

Sorry still working on this speedup issue. I found this script in the v$sqlarea too which I thought might be of use.
I understand that this is using Oracle Spatial technology. Must say I have never dealt with it. But its really fast when it comes to creating new tables, indexes, foreign keys etc. The table generation scripts that are run usually are the same... they get dropped and recreated. I think the JDBC is comparing the script in the .sql file against what is in the metadata and only executing the script if the file is different. I need something of this sort too. Can you please shed some light on how best I can leverage this feature?


DECLARE
cnt NUMBER;
stmt VARCHAR(500);
topology VARCHAR(100);
REG_TBL EXCEPTION;

BEGIN

stmt := 'SELECT topology ' ||
' FROM SDO_TOPO_METADATA_TABLE a, TABLE(a.Topo_Geometry_Layers) b ' ||
' WHERE b.owner = ''' || ORA_DICT_OBJ_OWNER ||
''' AND b.table_name = ''' || ORA_DICT_OBJ_NAME || '''' ;
EXECUTE IMMEDIATE stmt into topology;
RAISE REG_TBL;

EXCEPTION
WHEN REG_TBL THEN
MDSYS.MDERR.raise_md_error(
'MD', 'SDO', -13199,
'Need use delete_topo_geometry_layer() to deregister table '
|| ORA_DICT_OBJ_NAME || ' from topology ' || topology ||
' before drop it');
WHEN OTHERS THEN
RETURN;
END;



Commands in SQLPLUS for non sys user hangs

Santosh Vijayan, February 04, 2009 - 6:08 am UTC

Hi TOM,

Thanks for your support.
And let me first apologise to you as I cannot provide you any stats or much information about the issue I faced.

Yesterday we faced an ususual problem.
We were not able to create any table or run purge user_recyclebin from any non-sys users.
There were enough free space on all the tablespaces on the database including SYSTEM and SYSAUX.
Also TEMP tablespace had enough free space, so do undo tablespace.
Also no locks were seen.

Se tried simple table creation such as

create table axx (a number);

The commands just used to be hung.
But through SYS user we were able to create table with no delays.

After a lot of investigation we had to restart the DB.

Bu the problem did not go away immediately after shutdown.
We had restarted the database in the evening, but could not create table or execute purge command at that time through any non-sys users.

We could only create a table the next morning.
Is it due to some undo activities?

The AWR report shows the below SQL consuming 75% of DB time, when the problem was seen.
=======================================================
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2

The ADDM report had the below suggestion
=======================================================
SQL statements consuming significant database time were found.

RECOMMENDATION 1: SQL Tuning, 69% benefit (2823 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"96g93hntrzjtr".
RELEVANT OBJECT: SQL statement with SQL_ID 96g93hntrzjtr and
PLAN_HASH 841937906
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1
and intcol#=:2
RATIONALE: SQL statement with SQL_ID "96g93hntrzjtr" was executed 197517
times and had an average elapsed time of 0.015 seconds.

=====================================================

We are not sure what was the issue?
Any clue on this will be of great help.
Tom Kyte
February 04, 2009 - 11:33 am UTC

what were the major waits (that sql statement is executed during a parse, it is not relevant to the issue at hand)

Are you sure someone did not 'accidentally' put a 'on create' trigger in place that was removed later?


SQL PLUS hangs

Santosh Vijayan, February 05, 2009 - 5:11 am UTC

Thanks a lot TOM,

No there was not any trigger creation or dropping of triggers at that time.

I can provide details from AWR REPORT about waits. In fact here is a section of AWR report

Load Profile

Per Second Per Transaction
Redo size: 44,243.69 23,675.29
Logical reads: 793.48 424.60
Block changes: 390.57 209.00
Physical reads: 3.42 1.83
Physical writes: 3.28 1.76
User calls: 2.95 1.58
Parses: 11.46 6.13
Hard parses: 0.04 0.02
Sorts: 6.30 3.37
Logons: 0.59 0.32
Executes: 50.74 27.15
Transactions: 1.87


% Blocks changed per Read: 49.22 Recursive Call %: 99.56
Rollback per transaction %: 0.20 Rows per Sort: 415.47


Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.76 Redo NoWait %: 100.00
Buffer Hit %: 99.76 In-memory Sort %: 100.00
Library Hit %: 100.07 Soft Parse %: 99.62
Execute to Parse %: 77.41 Latch Hit %: 99.59
Parse CPU to Parse Elapsd %: 77.06 % Non-Parse CPU: 99.72


Shared Pool Statistics

Begin End
Memory Usage %: 89.90 85.10
% SQL with executions>1: 96.06 94.61
% Memory for SQL w/exec>1: 95.97 95.09


Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 3,915 96.0
db file parallel write 8,832 222 25 5.5 System I/O
log file parallel write 6,661 115 17 2.8 System I/O
log file sync 4,452 114 26 2.8 Commit
os thread startup 662 48 73 1.2 Concurrency


Time Model Statistics

Total time in database user-calls (DB Time): 4079.5s
Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
Ordered by % or DB time desc, Statistic name

Statistic Name Time (s) % of DB Time
sql execute elapsed time 4,022.40 98.60
DB CPU 3,915.12 95.97
parse time elapsed 23.81 0.58
PL/SQL execution elapsed time 22.60 0.55
hard parse elapsed time 15.14 0.37
PL/SQL compilation elapsed time 7.71 0.19
repeated bind elapsed time 3.31 0.08
connection management call elapsed time 1.43 0.03
Java execution elapsed time 0.81 0.02
hard parse (sharing criteria) elapsed time 0.43 0.01
hard parse (bind mismatch) elapsed time 0.26 0.01
failed parse elapsed time 0.03 0.00
sequence load elapsed time 0.02 0.00
DB time 4,079.49
background elapsed time 511.16
background cpu time 112.94


Wait Class
s - second
cs - centisecond - 100th of a second
ms - millisecond - 1000th of a second
us - microsecond - 1000000th of a second
ordered by wait time desc, waits desc
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
System I/O 96,497 0.00 360 4 7.15
Commit 4,452 0.00 114 26 0.33
Concurrency 15,230 0.03 71 5 1.13
User I/O 29,306 0.00 53 2 2.17
Administrative 7,075 0.00 3 0 0.52
Application 765 0.00 2 3 0.06
Other 1,338 0.67 1 1 0.10
Configuration 34 0.00 1 27 0.00
Network 7,166 0.00 1 0 0.53


Wait Events
s - second
cs - centisecond - 100th of a second
ms - millisecond - 1000th of a second
us - microsecond - 1000000th of a second
ordered by wait time desc, waits desc (idle events last)

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
db file parallel write 8,832 0.00 222 25 0.65
log file parallel write 6,661 0.00 115 17 0.49
log file sync 4,452 0.00 114 26 0.33
os thread startup 662 0.00 48 73 0.05
Data file init write 6,208 0.00 25 4 0.46
db file sequential read 8,562 0.00 23 3 0.63
control file parallel write 3,127 0.00 20 6 0.23
buffer busy waits 13,720 0.00 11 1 1.02
row cache lock 14 21.43 11 774 0.00
db file scattered read 1,244 0.00 3 3 0.09
switch logfile command 7,075 0.00 3 0 0.52
control file sequential read 77,849 0.00 3 0 5.77
direct path write 2,236 0.00 2 1 0.17
enq: TX - row lock contention 698 0.00 2 2 0.05
log file switch completion 8 0.00 1 105 0.00
SQL*Net more data from client 486 0.00 1 2 0.04
rdbms ipc reply 26 0.00 1 24 0.00
enq: TX - index contention 3 0.00 0 167 0.00
enq: RO - fast object reuse 21 0.00 0 17 0.00
LGWR wait for redo copy 803 0.25 0 0 0.06
enq: CI - contention 8 0.00 0 17 0.00
SQL*Net break/reset to client 46 0.00 0 3 0.00
latch: cache buffers chains 729 0.00 0 0 0.05
latch: session allocation 359 0.00 0 0 0.03
log buffer space 26 0.00 0 3 0.00
direct path read 11,053 0.00 0 0 0.82
latch: shared pool 96 0.00 0 1 0.01
cursor: pin S wait on X 2 100.00 0 18 0.00
log file single write 14 0.00 0 3 0.00
enq: JS - queue lock 2 0.00 0 13 0.00
latch free 110 0.00 0 0 0.01
enq: CF - contention 2 0.00 0 11 0.00
SQL*Net message to client 6,525 0.00 0 0 0.48
jobq slave TJ process wait 1 100.00 0 18 0.00
db file single write 2 0.00 0 8 0.00
reliable message 21 0.00 0 1 0.00
read by other session 1 0.00 0 12 0.00
SQL*Net more data to client 155 0.00 0 0 0.01
latch: row cache objects 4 0.00 0 0 0.00
log file sequential read 14 0.00 0 0 0.00
buffer deadlock 6 100.00 0 0 0.00
SQL*Net message from client 6,524 0.00 292,773 44876 0.48
jobq slave wait 89,561 96.08 260,183 2905 6.63
virtual circuit status 241 100.00 7,051 29257 0.02
Streams AQ: waiting for messages in the queue 1,442 100.00 7,046 4887 0.11
Streams AQ: qmn slave idle wait 257 0.00 7,038 27384 0.02
Streams AQ: qmn coordinator idle wait 523 50.86 7,038 13456 0.04
Streams AQ: waiting for time management or cleanup tasks 10 100.00 2,129 212864 0.00
PL/SQL lock timer 7 100.00 205 29304 0.00
class slave wait 4 0.00 0 0 0.00

Tom Kyte
February 05, 2009 - 11:00 am UTC

just thought of something.

are you using shared server?

if a table partition has rows or not

Venkat, February 06, 2009 - 5:23 pm UTC

I have a DW database with a table that has about 800 million rows plus. It is daily partitioned and each month's partitions reside in a tablespace that holds all the partitions for the month. We are about to implement an archiving strategy that involves exporting old data using expdp, then truncating the partitions using alter table <table_name> truncate partition <part_name> for that month and resize the datafile back to a small number like 10M from its usual size of 800M for that data file.

Reason is that users may ask occasionally to load back the data.

The export script needs to be smart enough to figure out that a partition is already exported and truncated, do not try to export that again. If already done, such old partitions will have zero rows.

I was thinking of select count(rowid) from table_name partition(partition_name) to find that out.

But is there an easier way than doing a count(rowid) to know if a partition has any rows in it or not with putting minimal resource request on the database.

Thank you

Venkat
Tom Kyte
February 09, 2009 - 5:50 pm UTC

select count(*) from table partition(pname) where rownum = 1;

it'll use any existing index on at least a single non-null column and do a couple of IO's to see if there is a row or not.

by the way, why not "alter table t exchange the partition with an empty table"

and then you can either transport the tablespace (no real work to unload) or export data pump it - all of the empty tables could sit in the same tablespace waiting for the original data to be reloaded and "altered back in"

Commands in SQLPLUS for non sys user hangs

Santosh, February 09, 2009 - 4:45 am UTC

Hi Tom,

Nope.

The database does not use shared server processes.

Thanks

Santosh
Tom Kyte
February 09, 2009 - 7:18 pm UTC

ok, in your alert log - do you see


cannot allocate new log


or similar messages? (like all logs need archival)

Commands in SQLplus for non sys user hangs

Santosh Vijayan, February 10, 2009 - 10:52 am UTC

Hi Tom,

I found following details in the alert log for the time errors were seen.

Tue Feb 3 15:03:37 2009
Thread 1 advanced to log sequence 1813
Current log# 3 seq# 1813 mem# 0: /u03/oradata/SUSR5D2/redo03.log
Thread 1 cannot allocate new log, sequence 1814
Private strand flush not complete
Current log# 3 seq# 1813 mem# 0: /u03/oradata/SUSR5D2/redo03.log
Thread 1 advanced to log sequence 1814
Current log# 1 seq# 1814 mem# 0: /u03/oradata/SUSR5D2/redo01.log
Thread 1 cannot allocate new log, sequence 1815
Checkpoint not complete
Current log# 1 seq# 1814 mem# 0: /u03/oradata/SUSR5D2/redo01.log
Tue Feb 3 15:03:48 2009
Thread 1 cannot allocate new log, sequence 1815
Private strand flush not complete
Current log# 1 seq# 1814 mem# 0: /u03/oradata/SUSR5D2/redo01.log
Thread 1 advanced to log sequence 1815
Current log# 2 seq# 1815 mem# 0: /u03/oradata/SUSR5D2/redo02.log
Thread 1 advanced to log sequence 1816
Current log# 3 seq# 1816 mem# 0: /u03/oradata/SUSR5D2/redo03.log


Thanks

Santosh
Tom Kyte
February 11, 2009 - 9:22 am UTC

Thanks for the tip

Venkat, February 10, 2009 - 5:35 pm UTC


Thanks a lot for the reply. Great idea to do rownum = 1, so the first row comes in fast and the query is done.

On the transport tablespace, I tried that initially. Problem is that expdp wants to transport every object that has anything on that partition meaning it transports the entire table and not just the partitions. So, even if I do a alter table exchange partition with empty tables and get the data to tables, the empty partitions would still reside on this tablespace and expdp would end up exporting all of the table because of that.

I could do the alter table patition exchange and then do an export of those exchanged tables only using expdp. That would be very similar to what I am doing now without any significant advantage.

Regards and thanks a lot for your time and dedication.

Venkat

SQLplus hangs

Santosh Vijayan, February 13, 2009 - 5:27 am UTC

Thanks a lot TOM.


Session Hangs

Bhushan, May 28, 2009 - 11:26 am UTC

Dear Tom,
Very Great piece of information.
I have a theorotical problem, not sure how can one reproduce the same.But please give a pratical solution ;)
Background: DB Version 9.0.1.1.1
Client: PL/SQL Developer
I connect to the DB as SALES run a simple sql say
select * from table_A -- Table_A has less than 1000 Rows
Now the SQL hangs the whole window i cannot do anything
Now what i do is open new pl/sql developer window login as SALES to the same DB, as soon as i login to this new window the earlier window is active again and i get the output in a flash.
I have observed this kind of behaviour not only on my machine but also on users machine the difference is they are logged in from the application.
Is there any parameter which causes this??
The problem is i cannot reporduce this when i want it happens only when u dont want it to happen :(
Have you ever come across such a thing??

Thanks in Advance.

Best Regards,
Bhushan Salgar
Tom Kyte
May 28, 2009 - 2:06 pm UTC

I have a theoretical problem for which I'd like a practical solution.... Hmmm, interesting concept.

...
select * from table_A -- Table_A has less than 1000 Rows
Now the SQL hangs the whole window i cannot do anything
......

time to contact support, you have a problem, that is not normal and should not happen.

However, it sounds very much like a client application problem, not a database problem. What code exhibits this behavior?

Oracle Forms and also pl/sql developer

Bhushan, June 30, 2009 - 7:42 pm UTC

Hi Tom,
follow up on the previous post, its getting worse from bad now...the application is based on Oracle Forms and reports.. but the interesting thing is when the applications hangs..i run a piece of code (found on this same site) on the server to know the current running SQL's but it shows no SQL's from any client.. no Locks..and the session hanging is not only for the application this happens when connecting to the database using pl/sql developer as well...Can you please suggest if there is anyway we can still check and be 100% sure that it is noting related to DB or application but may be something about the network or LAN card speed (may be...) Surprisingly the applicaton is being used for almost 10 years and the users are facing this problem only in last 2-3 months..and the IT guys around claim nothing has changed .. however restarting the server solves the problem but only for couple of hours or so..the DBA's doubled the LARGE POOL anticipating that might be the cause but in vain...hhmmm too mch of information..i guess...any guidance will be very helpful...

Got the Reason for SQL hangs

Bhushan, July 03, 2009 - 4:56 am UTC

Dear Tom,
i got the reason for client connection delays and hanging windows. right ehre on your site :) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5269794407347
The office where i currently work is very very TINY 4 server for 4 companies.. not more then 6-8 users connecting to each server
so what i did was added this line in sqlnet.ora user_dedicated_server=on and now the spped is more than perfect everyone happy and enjoying however after 5-6 hours of work yesterday on Thursday we got one erroe max processes reached.
i checked the number defined it was mere 30 i bumped it to 250 and now everything is ok.
I am also constantly monitoring the v$session and v$process.never seen it go up more than 20.Hope ai am on the right track.
Any suggestion or guidelines form you on what else i would want to check to ensure we dont run into unpleasant surprises.

Thanks as usual,
Bhushan salgar

Are the above posts Ignored???

Bhushan, July 08, 2009 - 4:54 am UTC


Tom Kyte
July 08, 2009 - 3:08 pm UTC

ummm, you came along and said "found the answer, thanks" - and "if you have anything else to say - say it"

and I didn't, so.... not sure what you mean.


sounds like you were using a shared server configuration - when you shouldn't have been (definitely can cause what appears to be "a hang" situation - if you have access to either expert one on one oracle or expert oracle database archictecture I go over "why" this is so in detail), so you switched to dedicated server - and then adjusted max processes to be more appropriate for what you actually need to do.

And I had nothing to add - so, not sure why you are saying "are the above posts ignored"

Thanks..

Bhushan, July 08, 2009 - 4:07 pm UTC

I was not sure what i did was correct so was waiting for your opinion.But whatever you have said now helped me confirm that atleast i am on the right track...THanks Again.

Cheers!!!
Bhushan

Report from sqlplus

A reader, March 30, 2010 - 11:06 am UTC

Hi Tom,
I like to follow your words to use sql*plus rather some other database tools.
But i do see some difficulites when i use sql*plus for report generation.

I need your help for the following questions.

1.How to pull a report (excel or csv but not text file) which shows 25 columns in an well aligned manner. If I generate it shows in an imporper manner. Can you please suggest
the steps and setting that you makee in sql*plus to get a report which is having more number of columns(>20) in an well aligned manner.
Can you please share me how to pull a report into excel sheet rather csv file from sql*plus
Can you please share me how to proceed if i need to pull a report having 60lacks records.

Thanks for your great site.

Tom Kyte
April 05, 2010 - 10:46 am UTC

... But i do see some difficulites when i use sql*plus for report generation. ...

I'm very confused, I've written that sqlplus is a simple command line tool - not to be used for end user stuff, not to be used for applications and in the year 2010 - definitely not as your reporting tool.



If sqlplus must be the tool...

J. Laurindo Chiappa, April 05, 2010 - 11:21 am UTC

Yes, we all agree that a proper report tool must be the choice, but for now if none can be used, sqlplus allows to create a html report, and Excel can import html files... Check http://blog.tanelpoder.com/2007/08/07/sqlplus-is-my-second-home-part-1-htmlizing-your-sqlplus-output/ and the sqlplus documenttion for refs and examples.

bright and polite, May 18, 2011 - 10:08 am UTC

Hi Tom,

Can u please tell us under which tab you have written print_table & showsql script..It would be very helpful.

Thanx in advance.


Tom Kyte
May 18, 2011 - 10:44 am UTC


create or replace
procedure print_table
( p_query in varchar2,
  p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )

-- this utility is designed to be installed ONCE in a database and used
-- by all.  Also, it is nice to have roles enabled so that queries by
-- DBA's that use a role to gain access to the DBA_* views still work
-- that is the purpose of AUTHID CURRENT_USER
AUTHID CURRENT_USER
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab2;
    l_colCnt        number;
    l_cs            varchar2(255);
    l_date_fmt      varchar2(255);

    -- small inline procedure to restore the sessions state
    -- we may have modified the cursor sharing and nls date format
    -- session variables, this just restores them
    procedure restore
    is
    begin
       if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
       then
           execute immediate
           'alter session set cursor_sharing=exact';
       end if;
       if ( p_date_fmt is not null )
       then
           execute immediate
               'alter session set nls_date_format=''' || l_date_fmt || '''';
       end if;
       dbms_sql.close_cursor(l_theCursor);
    end restore;
begin
    -- I like to see the dates print out with times, by default, the
    -- format mask I use includes that.  In order to be "friendly"
    -- we save the date current sessions date format and then use
    -- the one with the date and time.  Passing in NULL will cause
    -- this routine just to use the current date format
    if ( p_date_fmt is not null )
    then
       select sys_context( 'userenv', 'nls_date_format' )
         into l_date_fmt
         from dual;

       execute immediate
       'alter session set nls_date_format=''' || p_date_fmt || '''';
    end if;

    -- to be bind variable friendly on this ad-hoc queries, we
    -- look to see if cursor sharing is already set to FORCE or
    -- similar, if not, set it so when we parse -- literals
    -- are replaced with binds
    if ( dbms_utility.get_parameter_value
         ( 'cursor_sharing', l_status, l_cs ) = 1 )
    then
        if ( upper(l_cs) not in ('FORCE','SIMILAR'))
        then
            execute immediate
           'alter session set cursor_sharing=force';
        end if;
    end if;

    -- parse and describe the query sent to us.  we need
    -- to know the number of columns and their names.
    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns2
    ( l_theCursor, l_colCnt, l_descTbl );

    -- define all columns to be cast to varchar2's, we
    -- are just printing them out
    for i in 1 .. l_colCnt loop
        if ( l_descTbl(i).col_type not in ( 113 ) )
        then
            dbms_sql.define_column
            (l_theCursor, i, l_columnValue, 4000);
        end if;
    end loop;

    -- execute the query, so we can fetch
    l_status := dbms_sql.execute(l_theCursor);

    -- loop and print out each column on a separate line
    -- bear in mind that dbms_output only prints 255 characters/line
    -- so we'll only see the first 200 characters by my design...
    while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
    loop
        for i in 1 .. l_colCnt loop
            if ( l_descTbl(i).col_type not in ( 113 ) )
            then
                dbms_sql.column_value
                ( l_theCursor, i, l_columnValue );
                dbms_output.put_line
                ( rpad( l_descTbl(i).col_schema_name || '.' ||
                        l_descTbl(i).col_name, 30 )
                || ': ' ||
                substr( l_columnValue, 1, 200 ) );
            end if;
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;

    -- now, restore the session state, no matter what
    restore;
exception
    when others then
        restore;
        raise;
end;
/


---------------------------------------------------------------------
column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a15 word_wrapped
column status format a10
column sid_serial format a15
set feedback off
set serveroutput on

set feedback on
select username, ''''||sid||','||serial#||'''' sid_serial, status , module, action, client_info,
last_call_et
from v$session
where username is not null
/
set feedback off

column username format a20
column sql_text format a55 word_wrapped

set serveroutput on size 1000000
declare
    x number;
    pid number;
procedure p ( p_str in varchar2 )
is
   l_str   long := p_str;
begin
   loop
      exit when l_str is null;
      dbms_output.put_line( substr( l_str, 1, 250 ) );
      l_str := substr( l_str, 251 );
   end loop;
end;
begin
    for x in
    ( select username||'('||sid||','||serial#||
                ') ospid = ' ||  process ||
                ' command = ' || command ||
                ' program = ' || program username,
             to_char(LOGON_TIME,' Day HH24:MI') logon_time,
             to_char(sysdate,' Day HH24:MI') current_time,
             sql_address, LAST_CALL_ET, sid, paddr
        from v$session
       where status = 'ACTIVE'
         and sid <> ( select sid from v$mystat where rownum = 1 )
         and rawtohex(sql_address) <> '00'
         and username is not null order by last_call_et )
    loop
        select max(spid) into pid from v$process where addr = x.paddr;

        dbms_output.put_line( '--------------------' );
        dbms_output.put_line( x.username || ' dedicated server=' || pid );
        dbms_output.put_line( x.logon_time || ' ' ||
                          x.current_time||
                          ' last et = ' ||
                          x.LAST_CALL_ET);
        for y in ( select sql_text
                     from v$sqltext_with_newlines
                    where address = x.sql_address
                      order by piece )
        loop
            p( y.sql_text );
        end loop;

    end loop;
end;
/

set feedback on



bright and polite, May 20, 2011 - 6:32 am UTC

Thanx a lott Tom.

U are a rock star !!!!!!!!



PLSQL hangs other

Nikunja Lamsal, July 17, 2019 - 7:23 am UTC

I have PLSQL version 13.0.0.1883 and whenever I open PLSQL my other apps like SECURE CRT, OFFICE, AND SOME OTHER APPS but not all APPS starts not responding or respond very slow until I close the PLSQL app. Can anyone suggest a solution? I have tried uninstalling and installing other versions as well but didn't work out.
Chris Saxon
July 17, 2019 - 10:18 am UTC

Buy a faster computer?

Seriously: if your machine is struggling when you have many applications open, then your options are basically:

- have fewer apps open at once
- get a machine with more capacity (more memory, faster CPUs, ...)

PS - there is no version 13 of PL/SQL! So I've no idea what you're using...

Update/Delete Query Hung

Joel, June 17, 2021 - 10:12 am UTC

Hi Tom,

I used your query however it does not return any data. I ran your query after 1 minute of running the update query which takes forever.

When I ran your query wihout time_remaining > 0; it returns

Table Scan: PRODDTA.F3111: 126800 out of 126800 Blocks done

Looks like as per your query the SQL should have completed however it is still in processing "Hung" state

Connor McDonald
June 22, 2021 - 1:46 am UTC

Try

select * from v$session_wait
where sid = [the session id]


to see if it is blocked on something. Also, check v$transaction to ensure the transaction is open and running. The USED_URECS increasing means we are still doing the update. If it is decreasing, it means it failed but we are still rolling back the transaction