What is this sql doing?

From: chao_ping <chao_ping_at_163.com>
Date: 27 Jun 2002 06:14:28 GMT
Message-ID: <3d1aad44$1@usenetgateway.com>

Sun Solaris 7 with 12CPU/12G mem. Oracle 8.1.7.2 64bit. About 350 concurrent connected users. Simple SQL:
SQL> set linesize 200 alter session set events '10046 trace name context SQL> forever,level 12';

Session altered.

Elapsed: 00:00:00.04
SQL> select username,event,sum(total_waits),sum(total_timeouts),sum(ti- SQL> me_waited),avg(average_wait),max(max_wait)   2 from v$session a,v$session_event b 3 where a.sid=b.sid 4 group by   username,event;

It takes this long time: 145 rows selected.

Elapsed: 00:03:30.02

During the time the sql is executing, the process is consuming 100% of a single cpu.(from top) And during the time the sql is executing, the wait event is like:(i execute the sql via perfstat user)

SQL> select sid,event,p1,p2 from v$session_wait where sid in(select sid SQL> from v$session where username='PERFSTAT');

       SID EVENT ----------
       ----------------------------------------------------------------
       P1 P2 ---------- ---------- 100 SQL*Net message from client
       1650815232 1

What is going on on earth? And through the trace file tkprof from the 9i :

 select username,event,sum(total_waits),sum(total_timeouts),sum(time_waited),  avg(average_wait),max(max_wait) from v$session a,v$session_event  b where a.sid=b.sid group by username,event

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.05 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 11 207.72 209.87 0 0 0 145 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 13 207.74 209.92 0 0 0 145

Misses in library cache during parse: 1 Optimizer goal: FIRST_ROWS Parsing user id: 62

Rows Row Source Operation -------

--------------------------------------------------- 145 SORT GROUP BY

2903 NESTED LOOPS 2904 NESTED LOOPS 365 FIXED TABLE FULL X$KSUSE 3267 FIXED TABLE FULL X$KSLES 2903 FIXED TABLE FIXED INDEX #2 X$KSLED Elapsed times include waiting on following events: Event waited on Times

Max. Wait Total Waited ---------------------------------------- Waited

---------- ------------ SQL*Net message to client 11 0.00 0.00 SQL*Net message from client 11 332.38 332.43

Can someone explain it for me?

--
Posted via dBforums
http://dbforums.com

Received on Thu Jun 27 2002 - 01:14:28 CDT