1)sql bind variable
1@@@@analyzed
@@@run the part 2 shell, and analyzed the result as below.
<1>
SQL> SELECT /* mysql */ * FROM customers WHERE cust_id =10;
Statistics
----------------------------------------------------------
1226 recursive calls
0 db block gets
294 consistent gets
0 physical reads
0 redo size
2159 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
26 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT /* mysql */ * FROM customers WHERE cust_id =20;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
2150 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
@@@
@@@comment:
@@@26 sorts (memory) and 0 sorts (memory) ==> cache work
@@@1226 recursive calls and 1 recursive calls ==> soft parse work
<2>
SQL> exec :cid := 10
SQL> SELECT /* mynewsql */ * FROM customers WHERE cust_id =:cid;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
2159 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> exec :cid := 20
SQL> SELECT /* mynewsql */ * FROM customers WHERE cust_id =:cid;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
2150 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
@@@
@@@comment:
@@@due to the shared pool, the part 2 of lab less then the part 1 of lab.
@@@we focus on the second time.
@@@1 recursive calls was saved. when the number is huge, that would be great.
<3>
SQL>
SELECT sql_text, version_count, loads,
invalidations, parse_calls, sorts
FROM v$sqlarea
WHERE parsing_user_id > 0
AND command_type = 3
AND lower(sql_text) LIKE '%my%'
ORDER BY sql_text
/
SQL>
SQL_TEXT VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS SORTS
------------------------------ ------------- ---------- ------------- ----------- ----------
SELECT /* mynewsql */ * FROM 2 21 20 3 0
customers WHERE cust_id =:cid
SELECT /* mysql */ * FROM cust 1 1 0 1 0
omers WHERE cust_id =10
SELECT /* mysql */ * FROM cust 1 1 0 1 0
omers WHERE cust_id =20
SELECT /* mysql */ * FROM cust 1 1 0 1 0
omers WHERE cust_id =30
SELECT DISTINCT SID FROM V$MYS 1 1 0 1 0
TAT
SELECT sql_text, version_count 1 1 0 1 1
, loads, invalidations,
parse_calls, sorts FROM v$sq
larea WHERE parsing_user_id >
0 AND command_type = 3 A
ND lower(sql_text) LIKE '%my%
' ORDER BY sql_text
@@@
@@@comment:
@@@the LOADS and INVALIDATIONS is accumulated in v$sqlarea;
2@@@@the lab shell.
@@@run the shell by oracle user.
@@@ensure sechma of sh was installed.
@@@ensure firefox was installed.
####begin_2-1#####################################################################
#!/bin/sh
#use comment /* my... */ to trace the sql in the v$sqlarea
#run as oracle user.
flash_shared_pool(){
sqlplus / as sysdba<
alter system flush shared_pool;
EOF
}
sql_bind(){
sqlplus / as sysdba<
alter system flush shared_pool;
EOF
sqlplus sh/sh<
set linesize 200
set autot traceonly
set serverout on
set feedback off
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line('SELECT /* mysql */ * FROM customers WHERE cust_id =10;');
SELECT /* mysql */ * FROM customers WHERE cust_id =10;
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line('SELECT /* mysql */ * FROM customers WHERE cust_id =20;');
SELECT /* mysql */ * FROM customers WHERE cust_id =20;
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line('SELECT /* mysql */ * FROM customers WHERE cust_id =30;');
SELECT /* mysql */ * FROM customers WHERE cust_id =30;
variable cid number
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line('SELECT /* mynewsql */ * FROM customers WHERE cust_id =:cid;');
exec :cid := 10
SELECT /* mynewsql */ * FROM customers WHERE cust_id =:cid;
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line('SELECT /* mynewsql */ * FROM customers WHERE cust_id =:cid;');
exec :cid := 20
SELECT /* mynewsql */ * FROM customers WHERE cust_id =:cid;
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line(chr(13));
exec dbms_output.put_line('SELECT /* mynewsql */ * FROM customers WHERE cust_id =:cid;');
exec :cid := 50
SELECT /* mynewsql */ * FROM customers WHERE cust_id =:cid;
set autot off
set pagesize 30
set linesize 200
col SQL_TEXT format a30
SELECT sql_text, version_count, loads,
invalidations, parse_calls, sorts
FROM v\$sqlarea
WHERE parsing_user_id > 0
AND command_type = 3
AND lower(sql_text) LIKE '%my%'
ORDER BY sql_text;
clear col
EOF
}
#may be check the execute plan without shared pool
#sleep 7 && flash_shared_pool &
sql_bind > /tmp/sub_bind.tmp
firefox /tmp/sub_bind.tmp
######end_2-1#####################################################################