请教：v$sqlarea中的问题

oracleerp

如果沒有次數，且數據都時累加的，又怎麼知道那些SQL的咝行阅芎懿钅兀

biti_rainy

SQL> select count(*) from t;

  COUNT(*)
----------
     25374

SQL> select BUFFER_GETS    ,EXECUTIONS     from v$sqlarea where sql_text like 'select count(*) from
t%';

BUFFER_GETS EXECUTIONS
----------- ----------
        464          5

SQL> select count(*) from t;

  COUNT(*)
----------
     25374

SQL>  select BUFFER_GETS    ,EXECUTIONS     from v$sqlarea where sql_text like 'select count(*) from
t%';

BUFFER_GETS EXECUTIONS
----------- ----------
        546          6

SQL>

假如  DISK_READS  大当然说明不好了，该sql经常都要进行物理读，能不差么？
你可以说某次执行的时候未必差，但从整体上来讲，就是很差

oracleerp

請問，怎麼知道後台那些VIEW的欄位的含義。
什麼ADDRESS/SADDR等等，讓我難以認清他到底是什麼含義,
又沒有這樣的書呢？

biti_rainy

http://tahiti.oracle.com

oracleerp

thank you very much

rchsh

请教BITI:
在set autotrace on中：

recursive calls

db block gets

consistent gets

redo size

它们的具体的含义是什么呢？

Yong Huang

最初由 biti_rainy 发布
[B]buffer_gets =  consistent get +  db block gets  =  logic  IO

SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace on
SQL> select count(*) from t;

  COUNT(*)
----------
     25374


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=1
          2 Card=25376)

Statistics
----------------------------------------------------------
        242  recursive calls
          4  db block gets
        132  consistent gets   132 + 4 = 136
         80  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off
SQL> select buffer_gets,disk_reads from v$sqlarea where sql_text like 'select count(*) from t%';

BUFFER_GETS DISK_READS
----------- ----------
        136         80       132 + 4 = 136
[/B]

After some test and thinking, I think your conclusion is right: the number for buffer gets and disk reads is in blocks, not number of times. your test, however, doesn't seem to prove that. First, scanning indexes is db file sequential scan, which always scans 1 block at a time. In that case, there's no difference between the two units we don't agree on. Second, proving buffer gets is logical reads, which equals db block gets plus consistent gets, doesn't prove the number is blocks or number of times. It could be either blocks or number of times in both v$sqlarea (or v$sql) and in autotrace Statistics output at the same time.

But looking at tkprof output and system call tracing can almost certainly prove the unit is block. My database is 9.0.1.3 running on Solaris 2.6. db_file_multiblock_read_count is 8. I create a table with 500,000 rows. No index. Set 10046 event at level 12 in order to look at wait events. Set autotrace on. Select count(*) from thistable. Plan shows full scan on the table. v$sql.buffer_gets shows 780, disk_reads 760, which are equal to autotrace Statistics consistent gets and physical reads, respectively. grep ^WAIT on the trace file for the session shows 8 'db file sequential read's (and of course p3=1), 95 'db file scattered read's (p3=8). truss -t read,pread,readv,write,pwrite,writev -c -p <shadow process ID> also shows 95 calls of readv, consistent with scattered read wait events. 95 x 8 = 760, exactly the same as disk_reads. This proves that 760 is indeed the number of disk blocks that were read, and they were read in 95 operations of readv(2). Unfortunately, system call tracing doesn't reveal memory accesses (I wish Solaris had an ltrace utility as Linux does; maybe you can do ltrace if you find a Linux box for testing). So consistent reads does not tally with anything shown in truss output. But it's reasonable to think that 780 consistent reads are 780 buffers read; if it were 780 times of memory access, that would sound too many. I don't have better evidence to say against that hypothesis, so I assume otherwise.

For what's worth, the following is my tkprof output, which doesn't exactly match what I said above.

BTW, doing these tests doesn't need flushing shared pool. Since I'm using 9i, I can alter session set events = 'immediate trace name flush_cache' to conveniently flush buffer cache without bouncing the database.

Yong Huang


OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       11      0.14       0.33          2          2          0           0
Execute     11      0.03       0.10          0         12          3           3
Fetch       11      0.76       1.11        760        790          0          17
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.93       1.55        762        804          3          20

Misses in library cache during parse: 10

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      15        0.00          0.00
  SQL*Net message from client                    14       31.46         31.52
  db file sequential read                         3        0.00          0.00
  db file scattered read                         95        0.06          0.29


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       35      0.05       0.05          0          0          0           0
Execute     37      0.07       0.37          1          1          5           3
Fetch       68      0.00       0.03          8         94          0          45
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      140      0.12       0.46          9         95          5          48

Misses in library cache during parse: 24

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         9        0.01          0.02

   12  user  SQL statements in session.
   34  internal SQL statements in session.
   46  SQL statements in session.

biti_rainy

BTW: 关于用户接收数据的时候偶有几个地方不是很清楚
1: server  process在  data  buffer 获取数据 -- >  PGA  ---- >  CLIENT ? 这个跟  arraysize 有关？所以导致 consistent  gets 的变化？

2: to  yong huang , 关于 buffer  chain  的组织结构等 internal 的东西不太清晰，在 IXORA 上说的也是很零碎，能否有空探讨探讨.在这里的深入讨论去也可，顶上有连接
或者要是去 C N O U G 也可， www.C N O U G.org


SQL>set autotrace traceonly
SQL> select * from t;

30658 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=30658 Bytes=
          2636588)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=30658 Bytes=26365
          88)





Statistics
----------------------------------------------------------
        275  recursive calls
          0  db block gets
       2471  consistent gets
          1  physical reads
          0  redo size
    3581035  bytes sent via SQL*Net to client
     227198  bytes received via SQL*Net from client
       2045  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      30658  rows processed

SQL>  select buffer_gets,disk_reads from v$sqlarea where sql_text like '%select * from t%';

BUFFER_GETS DISK_READS
----------- ----------
       2471          1



SQL> set arraysize 1000
SQL> select * from t;

30658 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=30658 Bytes=
          2636588)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=30658 Bytes=26365
          88)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        451  consistent gets
          0  physical reads
          0  redo size
    3345514  bytes sent via SQL*Net to client
       3755  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      30658  rows processed

SQL>  select buffer_gets,disk_reads from v$sqlarea where sql_text like '%select * from t%';

BUFFER_GETS DISK_READS
----------- ----------
       2922          1 2471 + 451 = 2922

biti_rainy

select *
from
t


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.13       0.16          0         96          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       14      6.87       7.73        350        363          6       25374
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      7.00       7.89        350        459          6       25374

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 41  


set  arraysize 100

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.22       0.27          0         96          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      255      7.60       9.06        350        602          6       25374
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      257      7.82       9.33        350        698          6       25374

Misses in library cache during parse: 1

Yong Huang

最初由 biti_rainy 发布
[B]BTW: 关于用户接收数据的时候偶有几个地方不是很清楚
1: server  process在  data  buffer 获取数据 -- >  PGA  ---- >  CLIENT ? 这个跟  arraysize 有关？所以导致 consistent  gets 的变化？
[/B]

Indeed, sqlplus arraysize affects the number of consistent gets because it's the number of rows fetched out of the database at one shot. In fact, I usually set arraysize to a big number when I connect from here Houston, Texas, to some of our databases in Austin, Texas. That way, my sqlplus doesn't have too many "pauses" when it prints text on screen. Default arraysize 15 is too small in a lot of cases.

Unfortunately arraysize is a sqlplus setting. In other tools you have to use the tool-specific setting to control it.

You seem to have lost interest in whether buffer_gets (a.k.a logical reads) is in blocks or number of times. But I'll continue the research and may post you when I find the definitive answer.

Yong Huang