oracle實驗記錄 （子游標與解析）

SQL> select distinct sid from v$mystat;

       SID
----------
       144     

SQL> select distinct sid from v$mystat;

       SID
----------
       144

SQL> show user
USER is "SYS" 

SQL> select distinct sid from v$mystat;

       SID
----------
       138

SQL> show user
USER is "SYS"        

SQL> select distinct sid from v$mystat;

       SID
----------
       139

SQL> show user
USER is "XH"        

SQL> select name,sum(value)  from v$sesstat a,v$statname b where a.statistic#=b.
statistic# and name like 'parse%' and sid in (144,138,139) group by name
  2  ;

NAME                                                             SUM(VALUE)
---------------------------------------------------------------- ----------
parse count (failures)                                                    3
parse count (hard)                                                       42
parse count (total)                                                     225
parse time cpu                                                            3
parse time elapsed                                                       19

SQL> select sql_text,parse_calls,hash_value,username,address from v$sql a,dba_us
ers b where sql_text='select count(*) from test' and a.parsing_user_id=b.user_id
;

no rows selected

 

 SQL> conn xh/a831115
Connected.
SQL> create table test as select * from sys.test;

Table created.
SQL> alter system flush shared_pool;

System altered.

NAME                                                             SUM(VALUE)
---------------------------------------------------------------- ----------
parse count (failures)                                                    4
parse count (hard)                                                       46
parse count (total)                                                     239
parse time cpu                                                            3
parse time elapsed                                                       19

SQL> select count(*) from test;

  COUNT(*)
----------
         2             SID 144 user sys

 

NAME                                                             SUM(VALUE)
---------------------------------------------------------------- ----------
parse count (failures)                                                    4
parse count (hard)                                                       47
parse count (total)                                                     245
parse time cpu                                                            7
parse time elapsed                                                       21

SQL> select count(*) from test;

  COUNT(*)
----------
         2               sid 139 uer xh
NAME                                                             SUM(VALUE)
---------------------------------------------------------------- ----------
parse count (failures)                                                    4
parse count (hard)                                                       49
parse count (total)                                                     255
parse time cpu                                                            7
parse time elapsed                                                       21

SQL> select count(*) from test;

  COUNT(*)
----------              sid 138 user sys
         2
 
NAME                                                             SUM(VALUE)
---------------------------------------------------------------- ----------
parse count (failures)                                                    4
parse count (hard)                                                       49
parse count (total)                                                     256
parse time cpu                                                            7
parse time elapsed                                                       21

SQL> select count(*) from test;

  COUNT(*)
----------               sid 138 user sys
         2

NAME                                                             SUM(VALUE)
---------------------------------------------------------------- ----------
parse count (failures)                                                    4
parse count (hard)                                                       49
parse count (total)                                                     257
parse time cpu                                                            7
parse time elapsed                                                       21

 

SQL> select count(*) from test;

  COUNT(*)
----------
         2           sid 144  user sys

NAME                                                             SUM(VALUE)
---------------------------------------------------------------- ----------
parse count (failures)                                                    4
parse count (hard)                                                       49
parse count (total)                                                     258
parse time cpu                                                            7
parse time elapsed                                                       21

 

SQL> select sql_text,parse_calls,hash_value,username,address from v$sql a,dba_us
ers b where sql_text='select count(*) from test' and a.parsing_user_id=b.user_id
;

SQL_TEXT
--------------------------------------------------------------------------------

PARSE_CALLS HASH_VALUE USERNAME                       ADDRESS
----------- ---------- ------------------------------ --------
select count(*) from test
          4  297253644 SYS                            6A83D464

select count(*) from test
          2  297253644 XH                             6A83D464

 

SQL> select sql_text ,hash_value,version_count,executions,parse_calls from v$sql
area where sql_text='select count(*) from test';

SQL_TEXT
--------------------------------------------------------------------------------

HASH_VALUE VERSION_COUNT EXECUTIONS PARSE_CALLS
---------- ------------- ---------- -----------
select count(*) from test
 297253644             2          6           6

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~還需要進一步討論，其實是user之間的問題

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-607883/，如需轉載，請註明出處，否則將追究法律責任。