实验环境
操作系统版本 | 银河麒麟Linux kylin10 4.19.90-24.4.v2101.ky10.x86_64 |
数据库版本 | DM Database Server 64 V8 |
一条SQL执行过程:
(1)语法分析(字典缓冲区)
(2)语义分析(字典缓冲区)
(3)权限分析(字典缓冲区)
(4)查找数据块是否在内存的缓冲池中(不在内存,产生物理读,在内存,产生逻辑读)(数据缓冲区,SQL缓冲区)
(5)是否有可用执行计划(检查SQL缓冲区是否有相同的执行计划,有相同的执行计划,直接重用执行计划,如果没有就生成执行计划,运行SQL将结果返回)(字典缓冲区,SQL缓冲区,排序区,hash区)
硬解析 (hard parse):当前SQL缓冲区中没有该SQL执行计划,重新生成该SQL执行计划。
软解析 (soft parse):有相同的执行计划,直接重用执行计划。
软硬解析实验:
1、查看表的统计信息
会话 1 sysdba用户,生成表的统计信息,并查看
使用模式:DMHR ,表:EMPLOYEE
SQL> DBMS_STATS.GATHER_TABLE_STATS('DMHR','EMPLOYEE');
DMSQL 过程已成功完成
SQL> DBMS_STATS.TABLE_STATS_SHOW('DMHR','EMPLOYEE');
行号 NUM_ROWS LEAF_BLOCKS LEAF_USED_BLOCKS
---------- -------------------- -------------------- --------------------
1 856 16 12
--列解释
NUM_ROWS 记录表的行数
LEAF_BLCOKS 记录表的总页数
LEAF_USED_BLOCKS 已经使用的页数
--查看DBA_TABLES
SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,LAST_ANALYZED from dba_tables where TABLE_NAME='EMPLOYEE' and owner='DMHR';
行号 TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED
---------- ---------- -------- ------ ------------ -------------------
1 EMPLOYEE 856 16 4 2024-08-30 12:41:44
--列解释
TABLE_NAME 表名
NUM_ROWS 表总行数
BLOCKS 总页数
EMPTY_BLOCKS 空页数
LAST_ANALYZED 最后统计时间
2、软解析和硬解析
会话 2 新开会话,使用sysdba用户登陆,查询数据
SQL> select * from DMHR.EMPLOYEE where EMPLOYEE_ID=11126;
行号 EMPLOYEE_ID EMPLOYEE_NAME IDENTITY_CARD EMAIL PHONE_NUM HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ----------- ------------- ------------------ ------------------- ----------- ---------- ------ ----------- -------------- ----------- -------------
1 11126 郑佳 220107198703306000 zhengjia@dameng.com 18912370948 2012-11-25 42 5615 0 11004 1104
会话 1 查询解析情况
SQL> select SESSID,SESS_SEQ,SQL_ID,SQL_TXT,PARSE_CNT,HARD_PARSE_CNT from v$sql_stat where sql_txt like '%select * from DMHR%' and sql_txt not like '%v$sql%';
行号 SESSID SESS_SEQ SQL_ID SQL_TXT PARSE_CNT HARD_PARSE_CNT
---------- -------------------- ----------- ----------- ---------------------------------------------------- -------------------- --------------------
1 139796152 111 204 select * from DMHR.EMPLOYEE where EMPLOYEE_ID=11126; 1 1
首次查询产生一次硬解析。
--列解释
SESSID 会话 ID,系统内部标识
SESS_SEQ 会话序列号,每创建一个会话,系统自动为其生成一个全局递增的序列号
SQL_TXT语句
SQL_ID语句编号
PARSE_CNT解析次数
HARD_PARSE_CNT硬解析次数
SQL> select SESS_ID,SESS_SEQ,SQL_ID,N_LOGIC_READ,N_PHY_READ,AFFECTED_ROWS,HARD_PARSE_FLAG from v$sql_history where sql_id=204;
行号 SESS_ID SESS_SEQ SQL_ID N_LOGIC_READ N_PHY_READ AFFECTED_ROWS HARD_PARSE_FLAG
---------- -------------------- ----------- ----------- ------------ ----------- ------------- ---------------
1 139796152 111 204 4 0 1 2
--列解释
SEQ_NO 序列号
SQL_ID 当前语句的 SQL ID
SESS_ID会话 ID,系统内部标识
SESS_SEQ 会话序列号,每创建一个会话,系统自动为其生成一个全局递增的序列号
N_LOGIC_READ 语句逻辑读的次数
N_PHY_READ 语句物理读的次数
AFFECTED_ROWS 语句影响的行数
HARD_PARSE_FLAG 语句硬解析标记, 0:软解析; 1:语义解析; 2:硬解析
会话3 新开会话,使用sysdba用户登录,执行与会话2相同的SQL
SQL> select * from DMHR.EMPLOYEE where EMPLOYEE_ID=11126;
行号 EMPLOYEE_ID EMPLOYEE_NAME IDENTITY_CARD EMAIL PHONE_NUM HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ----------- ------------- ------------------ ------------------- ----------- ---------- ------ ----------- -------------- ----------- -------------
1 11126 郑佳 220107198703306000 zhengjia@dameng.com 18912370948 2012-11-25 42 5615 0 11004 1104
会话1 查看解析情况
SQL> select SESSID,SESS_SEQ,SQL_ID,SQL_TXT,PARSE_CNT,HARD_PARSE_CNT from v$sql_stat where sql_txt like '%select * from DMHR%' and sql_txt not like '%v$sql%';
行号 SESSID SESS_SEQ SQL_ID SQL_TXT PARSE_CNT HARD_PARSE_CNT
---------- -------------------- ----------- ----------- ---------------------------------------------------- -------------------- --------------------
1 139796152 111 204 select * from DMHR.EMPLOYEE where EMPLOYEE_ID=11126; 1 1
2 140427464641464 199 204 select * from DMHR.EMPLOYEE where EMPLOYEE_ID=11126; 1 0
上述SQL查询结果可以看出第二次不同会话的查询,没有进行硬解析。
SQL> select SESS_ID,SESS_SEQ,SQL_ID,N_LOGIC_READ,N_PHY_READ,AFFECTED_ROWS,HARD_PARSE_FLAG from v$sql_history where sql_id=204;
行号 SESS_ID SESS_SEQ SQL_ID N_LOGIC_READ N_PHY_READ AFFECTED_ROWS HARD_PARSE_FLAG
---------- -------------------- ----------- ----------- ------------ ----------- ------------- ---------------
1 140427464641464 199 204 4 0 1 0
2 139796152 111 204 4 0 1 2
不同会话执行相同的SQL,第一次执行进行硬解析,生成相应的执行计划,第二次执行进行软解析,重用上次生成的执行计划。
会话2 SQl语句的条件增加单引号,查询条件不变,执行查询
SQL> select * from DMHR.EMPLOYEE where EMPLOYEE_ID='11126';
行号 EMPLOYEE_ID EMPLOYEE_NAME IDENTITY_CARD EMAIL PHONE_NUM HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ----------- ------------- ------------------ ------------------- ----------- ---------- ------ ----------- -------------- ----------- -------------
1 11126 郑佳 220107198703306000 zhengjia@dameng.com 18912370948 2012-11-25 42 5615 0 11004 1104
会话1 查看解析情况
SQL> select SESSID,SESS_SEQ,SQL_ID,SQL_TXT,PARSE_CNT,HARD_PARSE_CNT from v$sql_stat where sql_txt like '%select * from DMHR%' and sql_txt not like '%v$sql%';
行号 SESSID SESS_SEQ SQL_ID SQL_TXT PARSE_CNT HARD_PARSE_CNT
---------- -------------------- ----------- ----------- ------------------------------------------------------ -------------------- --------------------
1 139796152 111 454 select * from DMHR.EMPLOYEE where EMPLOYEE_ID='11126'; 1 1
2 140427464641464 199 204 select * from DMHR.EMPLOYEE where EMPLOYEE_ID=11126; 1 0
SQL> select SESS_ID,SESS_SEQ,SQL_ID,N_LOGIC_READ,N_PHY_READ,AFFECTED_ROWS,HARD_PARSE_FLAG from v$sql_history where sql_id=454;
行号 SESS_ID SESS_SEQ SQL_ID N_LOGIC_READ N_PHY_READ AFFECTED_ROWS HARD_PARSE_FLAG
---------- -------------------- ----------- ----------- ------------ ----------- ------------- ---------------
1 139796152 111 454 4 0 1 2
因为查询条件发生了变化,SQL执行后,虽然查询结果没有发生变化,但数据库认为是不同的SQL查询语句,重新进行硬解析。
会话2 改变查询条件,重新执行
SQL> select * from DMHR.EMPLOYEE where EMPLOYEE_ID=11127;
行号 EMPLOYEE_ID EMPLOYEE_NAME IDENTITY_CARD EMAIL PHONE_NUM HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ----------- ------------- ------------------ --------------------- ----------- ---------- ------ ----------- -------------- ----------- -------------
1 11127 李家胜 440101197804021000 lijiasheng@dameng.com 14512368999 2008-10-06 42 5625 0 11004 1104
会话1 查看解析情况
SQL> select SESSID,SESS_SEQ,SQL_ID,SQL_TXT,PARSE_CNT,HARD_PARSE_CNT from v$sql_stat where sql_txt like '%select * from DMHR%' and sql_txt not like '%v$sql%';
行号 SESSID SESS_SEQ SQL_ID SQL_TXT PARSE_CNT HARD_PARSE_CNT
---------- -------------------- ----------- ----------- ---------------------------------------------------- -------------------- --------------------
1 139796152 111 498 select * from DMHR.EMPLOYEE where EMPLOYEE_ID=11127; 1 1
2 140427464641464 199 204 select * from DMHR.EMPLOYEE where EMPLOYEE_ID=11126; 1 0
SQL> select SESS_ID,SESS_SEQ,SQL_ID,N_LOGIC_READ,N_PHY_READ,AFFECTED_ROWS,HARD_PARSE_FLAG from v$sql_history where sql_id=498;
行号 SESS_ID SESS_SEQ SQL_ID N_LOGIC_READ N_PHY_READ AFFECTED_ROWS HARD_PARSE_FLAG
---------- -------------------- ----------- ----------- ------------ ----------- ------------- ---------------
1 139796152 111 498 4 0 1 2
如果用户查询的内容不变,其查询条件的内容发生变化,这样每条查询语句执行后,都会重新生成执行计划,进行硬解析;为减少硬解析,可以使用绑定变量的方法。
会话2 执行带变量的SQL查询
SQL> select * from DMHR.EMPLOYEE where EMPLOYEE_ID=:emp_id;
请输入参数1的值:11128
行号 EMPLOYEE_ID EMPLOYEE_NAME IDENTITY_CARD EMAIL PHONE_NUM HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ----------- ------------- ------------------ ----------------------- ----------- ---------- ------ ----------- -------------- ---------- -------------
1 11128 卓建荣 630102197909033000 zhuojianrong@dameng.com 13312350611 2009-04-22 42 5641 0 11004 1104
已用时间: 1.561(毫秒). 执行号:11007.
SQL>
SQL> select * from DMHR.EMPLOYEE where EMPLOYEE_ID=:emp_id;
请输入参数1的值:11129
行号 EMPLOYEE_ID EMPLOYEE_NAME IDENTITY_CARD EMAIL PHONE_NUM HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ----------- ------------- ------------------ ---------------------- ----------- ---------- ------ ----------- -------------- ----------- -------------
1 11129 陈柳春 340105198309284000 chenliuchun@dameng.com 18012356042 2013-03-03 42 5652 0 11004 1104
已用时间: 0.308(毫秒). 执行号:11008.
SQL> select * from DMHR.EMPLOYEE where EMPLOYEE_ID=:emp_id;
请输入参数1的值:11130
行号 EMPLOYEE_ID EMPLOYEE_NAME IDENTITY_CARD EMAIL PHONE_NUM HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ----------- ------------- ------------------ ---------------------- ----------- ---------- ------ ----------- -------------- ----------- -------------
1 11130 江葛娇 510106197408089000 jianggejiao@dameng.com 13912372021 2009-02-27 42 5662 0 11004 1104
会话1 查看解析情况
SQL> select SESSID,SESS_SEQ,SQL_ID,SQL_TXT,PARSE_CNT,HARD_PARSE_CNT from v$sql_stat where sql_txt like '%select * from DMHR%' and sql_txt not like '%v$sql%';
行号 SESSID SESS_SEQ SQL_ID SQL_TXT PARSE_CNT HARD_PARSE_CNT
---------- -------------------- ----------- ----------- ------------------------------------------------------ -------------------- --------------------
1 139796152 111 1006 select * from DMHR.EMPLOYEE where EMPLOYEE_ID=:emp_id; 2 2
SQL>
SQL> /
行号 SESSID SESS_SEQ SQL_ID SQL_TXT PARSE_CNT HARD_PARSE_CNT
---------- -------------------- ----------- ----------- ------------------------------------------------------ -------------------- --------------------
1 139796152 111 1006 select * from DMHR.EMPLOYEE where EMPLOYEE_ID=:emp_id; 1 0
已用时间: 0.331(毫秒). 执行号:629.
SQL>
SQL>
SQL> /
行号 SESSID SESS_SEQ SQL_ID SQL_TXT PARSE_CNT HARD_PARSE_CNT
---------- -------------------- ----------- ----------- ------------------------------------------------------ -------------------- --------------------
1 139796152 111 1006 select * from DMHR.EMPLOYEE where EMPLOYEE_ID=:emp_id; 1 0
已用时间: 0.384(毫秒). 执行号:630.
SQL>
可以看出只有第一次执行进行了硬解析,执行执行的查询没有进行硬解析。