优化记录

来源：互联网发布：unity3d圣典app 编辑：程序博客网时间：2024/05/22 03:03

数据库内存情况

下图显示了主机内存的总量情况

这里写图片描述

下图给出了oracle针对SGA内存大小调整的建议

这里写图片描述

从上面说明可以看出当提高SGA大小时，有比较明显的性能提升，可以减少较多的物理读。

SQL优化部分

SQL_ID：fvdwtfv18yy0m

SELECT cu.ecno,    cu.NAME,    cu.birthday,    cu.sex,    cu_id.idtype,    cu_id.idcard,    reg.usernameFROM customer cu,    customerid cu_id,    customerreg regWHERE cu.ecno = cu_id.ecno    AND cu.ecno = reg.ecno    AND upper(reg.username) = upper('13979083223@139.com');

SELECT TABLE_NAME,COLUMN_NAME,INDEX_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME IN ('CUSTOMER','CUSTOMERID','CUSTOMERREG');TABLE_NAME                     COLUMN_NAME                    INDEX_NAME------------------------------ ------------------------------ ----------------------------CUSTOMER                       MOBILE                         IND_CUSTOMERCUSTOMER                       ECNO                           SYS_C0015620CUSTOMERID                     ECNO                           IDX_CUS_IDCUSTOMERREG                    USERNAME                       USERNAMECUSTOMERREG                    ECNO                           SYS_C0015621

检查发现真实的执行计划如下

---------------------------------------------------------------------------------------| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT       |              | 15626 |   976K| 11775   (1)| 00:02:22 ||*  1 |  HASH JOIN             |              | 15626 |   976K| 11775   (1)| 00:02:22 ||*  2 |   HASH JOIN            |              | 15594 |   746K|  9370   (1)| 00:01:53 ||*  3 |    TABLE ACCESS FULL   | CUSTOMERREG  | 15594 |   517K|  5635   (1)| 00:01:08 ||   4 |    INDEX FAST FULL SCAN| SYS_C0015620 |  1489K|    21M|  3732   (1)| 00:00:45 ||   5 |   TABLE ACCESS FULL    | CUSTOMERID   |  1501K|    21M|  2401   (1)| 00:00:29 |---------------------------------------------------------------------------------------

这个执行计划应该是正确的。

0 0