[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 9 21:52:26 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
sys@ORCL11G 2023-03-09 21:52:27> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.13
sys@ORCL11G 2023-03-09 21:52:46> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.02
sys@ORCL11G 2023-03-09 21:53:01> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:53:02> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:53:03> select * from scott.emp where empno = 7566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:53:22> select * from v$sysstat where name like '%parse%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------------------------------------------------------------------- ---------- ---------- ----------
264 ADG parselock X get attempts 1 0 564381705
265 ADG parselock X get successes 1 0 3808229129
622 parse time cpu 64 79 206905303
623 parse time elapsed 64 118 1431595225
624 parse count (total) 64 10148 63887964
625 parse count (hard) 64 2339 143509059
626 parse count (failures) 64 6 1118776443
627 parse count (describe) 64 0 469016317
8 rows selected.
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:53:28> select * from scott.emp where empno = 7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:53:39> select * from v$sysstat where name like '%parse%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------------------------------------------------------------------- ---------- ---------- ----------
264 ADG parselock X get attempts 1 0 564381705
265 ADG parselock X get successes 1 0 3808229129
622 parse time cpu 64 79 206905303
623 parse time elapsed 64 118 1431595225
624 parse count (total) 64 10153 63887964
625 parse count (hard) 64 2341 143509059
626 parse count (failures) 64 6 1118776443
627 parse count (describe) 64 0 469016317
8 rows selected.
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:53:41> select * from scott.emp where empno = 7900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:54:25> select * from v$sysstat where name like '%parse%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------------------------------------------------------------------- ---------- ---------- ----------
264 ADG parselock X get attempts 1 0 564381705
265 ADG parselock X get successes 1 0 3808229129
622 parse time cpu 64 89 206905303
623 parse time elapsed 64 128 1431595225
624 parse count (total) 64 11150 63887964
625 parse count (hard) 64 2482 143509059
626 parse count (failures) 64 6 1118776443
627 parse count (describe) 64 0 469016317
8 rows selected.
Elapsed: 00:00:00.01
sys@ORCL11G 2023-03-09 21:54:28> select * from scott.emp where empno = 7902;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:54:33> select * from v$sysstat where name like '%parse%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------------------------------------------------------------------- ---------- ---------- ----------
264 ADG parselock X get attempts 1 0 564381705
265 ADG parselock X get successes 1 0 3808229129
622 parse time cpu 64 89 206905303
623 parse time elapsed 64 128 1431595225
624 parse count (total) 64 11204 63887964
625 parse count (hard) 64 2496 143509059
626 parse count (failures) 64 6 1118776443
627 parse count (describe) 64 0 469016317
8 rows selected.
Elapsed: 00:00:00.00
--强制共享游标，硬解析一致，不再增加。说明实现了绑定变量，解决重复SQL。
sys@ORCL11G 2023-03-09 21:54:35> alter session set cursor_sharing = force;
Session altered.
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:54:52> select * from scott.emp where empno = 7902;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:54:56> select * from v$sysstat where name like '%parse%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------------------------------------------------------------------- ---------- ---------- ----------
264 ADG parselock X get attempts 1 0 564381705
265 ADG parselock X get successes 1 0 3808229129
622 parse time cpu 64 89 206905303
623 parse time elapsed 64 128 1431595225
624 parse count (total) 64 11218 63887964
625 parse count (hard) 64 2499 143509059
626 parse count (failures) 64 6 1118776443
627 parse count (describe) 64 0 469016317
8 rows selected.
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:55:00> select * from scott.emp where empno = 7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:55:08> select * from v$sysstat where name like '%parse%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------------------------------------------------------------------- ---------- ---------- ----------
264 ADG parselock X get attempts 1 0 564381705
265 ADG parselock X get successes 1 0 3808229129
622 parse time cpu 64 89 206905303
623 parse time elapsed 64 128 1431595225
624 parse count (total) 64 11222 63887964
625 parse count (hard) 64 2499 143509059
626 parse count (failures) 64 6 1118776443
627 parse count (describe) 64 0 469016317
8 rows selected.
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:55:12> select * from scott.emp where empno = 7566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:55:26> select * from v$sysstat where name like '%parse%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------------------------------------------------------------------- ---------- ---------- ----------
264 ADG parselock X get attempts 1 0 564381705
265 ADG parselock X get successes 1 0 3808229129
622 parse time cpu 64 89 206905303
623 parse time elapsed 64 128 1431595225
624 parse count (total) 64 11302 63887964
625 parse count (hard) 64 2499 143509059
626 parse count (failures) 64 6 1118776443
627 parse count (describe) 64 0 469016317
8 rows selected.
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:55:28> select sql_id,child_number,executions from v$sql where sql_text like 'select * from scott.emp where empno %';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
ap0bf21q5fnt8 0 2
3bmy624wjr841 0 2
33pyft6u53k30 0 2
g8qp3zuuhbvf0 0 1
Elapsed: 00:00:00.01
--改成精确匹配，不再绑定变量，走硬解析
sys@ORCL11G 2023-03-09 21:55:42> alter session set cursor_sharing = exact;
Session altered.
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:58:44> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.05
sys@ORCL11G 2023-03-09 21:58:53> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.05
sys@ORCL11G 2023-03-09 21:58:54> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
sys@ORCL11G 2023-03-09 21:58:55> select * from scott.emp where empno = 7566;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:59:00> select * from v$sysstat where name like '%parse%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------------------------------------------------------------------- ---------- ---------- ----------
264 ADG parselock X get attempts 1 0 564381705
265 ADG parselock X get successes 1 0 3808229129
622 parse time cpu 64 102 206905303
623 parse time elapsed 64 138 1431595225
624 parse count (total) 64 11897 63887964
625 parse count (hard) 64 2743 143509059
626 parse count (failures) 64 6 1118776443
627 parse count (describe) 64 0 469016317
8 rows selected.
Elapsed: 00:00:00.01
sys@ORCL11G 2023-03-09 21:59:11> select * from scott.emp where empno = 7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:59:25> select * from v$sysstat where name like '%parse%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------------------------------------------------------------------- ---------- ---------- ----------
264 ADG parselock X get attempts 1 0 564381705
265 ADG parselock X get successes 1 0 3808229129
622 parse time cpu 64 105 206905303
623 parse time elapsed 64 140 1431595225
624 parse count (total) 64 12411 63887964
625 parse count (hard) 64 2834 143509059
626 parse count (failures) 64 6 1118776443
627 parse count (describe) 64 0 469016317
8 rows selected.
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-09 21:59:32> select sql_id,child_number,executions from v$sql where sql_text like 'select * from scott.emp where empno %';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
3bmy624wjr841 0 1
33pyft6u53k30 0 1
Elapsed: 00:00:00.01
