60.SQL的性能管理
[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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库