Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to Change parameter at statement level

Re: how to Change parameter at statement level

From: chao_ping <zhuchao_at_gmail.com>
Date: 24 May 2005 21:01:47 -0700
Message-ID: <1116993707.929417.253930@g47g2000cwa.googlegroups.com> 





Test result:


done on solaris 10/oracle 8174 with directio. No San Cache here, just 2
internal scsi disk.



SQL> @a


11:53:46 SQL>  ALTER TABLESPACE KANA_ADMIN OFFLINE;


Tablespace altered.



Elapsed: 00:00:00.33


11:53:46 SQL>   ALTER TABLESPACE KANA_ADMIN ONLINE;


Tablespace altered.



Elapsed: 00:00:00.31


11:53:47 SQL>   ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=8;


Session altered.



Elapsed: 00:00:00.04


11:53:47 SQL> SELECT /*+FULL(A)*/ COUNT(*) FROM KANA.KC_MESSAGEHISTORY

A;



  COUNT(*)





  10221222



Elapsed: 00:00:15.37



Execution Plan




   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7082 Card=1)
   1    0   SORT (AGGREGATE)


   2    1     TABLE ACCESS (FULL) OF 'KC_MESSAGEHISTORY' (Cost=7082 Ca
          rd=3811108)









Statistics



          0  recursive calls
          6  db block gets
      46659  consistent gets
      46660  physical reads
          0  redo size
        370  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed





11:54:02 SQL>  ALTER TABLESPACE KANA_ADMIN OFFLINE;


Tablespace altered.



Elapsed: 00:00:00.15


11:54:02 SQL>   ALTER TABLESPACE KANA_ADMIN ONLINE;


Tablespace altered.



Elapsed: 00:00:00.12


11:54:02 SQL>   ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;


Session altered.



Elapsed: 00:00:00.00


11:54:02 SQL> SELECT /*+FULL(A)*/ COUNT(*) FROM KANA.KC_MESSAGEHISTORY

A;



  COUNT(*)





  10221222



Elapsed: 00:00:11.10



Execution Plan




   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1142 Card=1)
   1    0   SORT (AGGREGATE)


   2    1     TABLE ACCESS (FULL) OF 'KC_MESSAGEHISTORY' (Cost=1142 Ca
          rd=3811108)









Statistics



          0  recursive calls
          6  db block gets
      46659  consistent gets
      46660  physical reads
          0  redo size
        370  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

11:54:14 SQL>
11:54:14 SQL> @a




11:54:34 SQL> SET TIME ON TIMING ON ECHO ON AUTOTRACE ON

11:54:34 SQL>  ALTER TABLESPACE KANA_ADMIN OFFLINE;


Tablespace altered.



Elapsed: 00:00:00.16


11:54:34 SQL>   ALTER TABLESPACE KANA_ADMIN ONLINE;


Tablespace altered.



Elapsed: 00:00:00.10


11:54:34 SQL>   ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=8;


Session altered.



Elapsed: 00:00:00.02


11:54:34 SQL> SELECT /*+FULL(A)*/ COUNT(*) FROM KANA.KC_MESSAGEHISTORY

A;



  COUNT(*)





  10221222



Elapsed: 00:00:13.60



Execution Plan




   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7082 Card=1)
   1    0   SORT (AGGREGATE)


   2    1     TABLE ACCESS (FULL) OF 'KC_MESSAGEHISTORY' (Cost=7082 Ca
          rd=3811108)









Statistics



          0  recursive calls
          6  db block gets
      46659  consistent gets
      46660  physical reads
          0  redo size
        370  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed





11:54:48 SQL>  ALTER TABLESPACE KANA_ADMIN OFFLINE;


Tablespace altered.



Elapsed: 00:00:00.14


11:54:48 SQL>   ALTER TABLESPACE KANA_ADMIN ONLINE;


Tablespace altered.



Elapsed: 00:00:00.11


11:54:48 SQL>   ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;


Session altered.



Elapsed: 00:00:00.01


11:54:48 SQL> SELECT /*+FULL(A)*/ COUNT(*) FROM KANA.KC_MESSAGEHISTORY

A;



  COUNT(*)





  10221222



Elapsed: 00:00:11.47



Execution Plan




   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1142 Card=1)
   1    0   SORT (AGGREGATE)


   2    1     TABLE ACCESS (FULL) OF 'KC_MESSAGEHISTORY' (Cost=1142 Ca
          rd=3811108)









Statistics



          0  recursive calls
          6  db block gets
      46659  consistent gets
      46660  physical reads
          0  redo size
        370  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

11:54:59 SQL>
11:54:59 SQL> @a




11:55:05 SQL> SET TIME ON TIMING ON ECHO ON AUTOTRACE ON

11:55:06 SQL>  ALTER TABLESPACE KANA_ADMIN OFFLINE;


Tablespace altered.



Elapsed: 00:00:00.17


11:55:06 SQL>   ALTER TABLESPACE KANA_ADMIN ONLINE;


Tablespace altered.



Elapsed: 00:00:00.12


11:55:06 SQL>   ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=8;


Session altered.



Elapsed: 00:00:00.02


11:55:06 SQL> SELECT /*+FULL(A)*/ COUNT(*) FROM KANA.KC_MESSAGEHISTORY

A;



  COUNT(*)





  10221222



Elapsed: 00:00:18.06



Execution Plan




   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7082 Card=1)
   1    0   SORT (AGGREGATE)


   2    1     TABLE ACCESS (FULL) OF 'KC_MESSAGEHISTORY' (Cost=7082 Ca
          rd=3811108)









Statistics



          0  recursive calls
          6  db block gets
      46659  consistent gets
      46660  physical reads
          0  redo size
        370  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed





11:55:24 SQL>  ALTER TABLESPACE KANA_ADMIN OFFLINE;


Tablespace altered.



Elapsed: 00:00:00.19


11:55:24 SQL>   ALTER TABLESPACE KANA_ADMIN ONLINE;


Tablespace altered.



Elapsed: 00:00:00.17


11:55:24 SQL>   ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;


Session altered.



Elapsed: 00:00:00.00


11:55:24 SQL> SELECT /*+FULL(A)*/ COUNT(*) FROM KANA.KC_MESSAGEHISTORY

A;



  COUNT(*)





  10221222



Elapsed: 00:00:12.85



Execution Plan




   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1142 Card=1)
   1    0   SORT (AGGREGATE)


   2    1     TABLE ACCESS (FULL) OF 'KC_MESSAGEHISTORY' (Cost=1142 Ca
          rd=3811108)









Statistics



          0  recursive calls
          6  db block gets
      46659  consistent gets
      46660  physical reads
          0  redo size
        370  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Received on Tue May 24 2005 - 23:01:47 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US