MSSQL网页编程之数据库功能优化剖析案例---办理SQL语句...

支持多种存储引擎。办理|数据|数据库|成绩|功能|优化|语句
成绩形貌：
10月25日上午滨州网通的工程师呈报OSS使用体系运转迟缓，详细操纵是经由过程OSS体系查询话单，很长工夫才干前往了局，严峻影响了客户的一般利用。

成绩处置：
1.上岸数据库主机，用sar命令看到idle的值延续为0，CPU的资本已耗尽：

bz_db1#sar24

SunOSkest5.8Generic_108528-19sun4u10/26/04

10:56:46%usr%sys%wio%idle
10:56:4814950
10:56:5015940
10:56:5206930
10:56:5416930

Average15940


2.利用TOP命令看到有两个分明占用CPU使用率太高的历程,以下是top命令的了局:

bz_db1#top

lastpid:1664;loadaverages:3.26,3.24,3.69
159processes:152sleeping,2running,2zombie,1stopped,2oncpu
CPUstates:1.5%idle,72.5%user,17.9%kernel,8.0%iowait,0.0%swap
Memory:2.0Greal,233Mfree,2.0Gswapinuse,3.4Gswapfree

PIDUSERNAMETHRPRNCESIZERESSTATETIMEFLTSCPUCOMMAND
27420oracle11001.3G1.2Gcpu0122.9H231.94%oracle
27418oracle11001.3G1.2Grun23.0H626.86%oracle
5943oracle15901.3G1.2Gsleep25:26374.92%oracle
6295oracle15501.3G1.2Grun25:14744.90%oracle
7778oracle14301.3G1.2Gsleep11:431104.86%oracle
13270oracle15901.3G1.2Gsleep210.6H00.96%oracle
13056oracle14801.3G1.2Gsleep303:3000.37%oracle
10653root15802560K1624Kcpu000:0000.32%top
18827oracle15801.3G1.2Gsleep18.4H00.31%oracle
12748oracle2585801.3G1.2Gsleep555:1400.21%oracle
10634oracle15901.3G1.2Gsleep0:0100.21%oracle
28458oracle15801.3G1.2Gsleep535:0200.18%oracle
13075oracle15901.3G1.2Gsleep326:3300.15%oracle
13173oracle15801.3G1.2Gsleep593:0700.13%oracle
4927oracle15901.3G1.2Gsleep33.4H00.11%oracle

能够看到这两个历程号分离是27420和27418.

3.捕捉占用CPU使用率太高的SQL语句：

以下用到了我总结的SQL语句：

SQL>setline240
SQL>setverifyoff
SQL>columnsidformat999
SQL>columnpidformat999
SQL>columnS_#format999
SQL>columnusernameformatA9heading"ORAUser"
SQL>columnprogramformata29
SQL>columnSQLformata60
SQL>COLUMNOSnameformata9Heading"OSUser"
SQL>SELECTP.pidpid,S.sidsid,P.spidspid,S.usernameusername,
S.osuserosname,P.serial#S_#,P.terminal,P.programprogram,
P.background,S.status,RTRIM(SUBSTR(a.sql_text,1,80))SQL
FROMv$processP,v$sessionS,v$sqlareaAWHEREP.addr=s.paddr
ANDS.sql_address=a.address(+)ANDP.spidLIKE%&1%;

Entervaluefor1:27420（注重这里应输出占用CPU最高的历程对应的PID）

失掉以下SQL语句：

Selectnvl(sum(LOCALCHARGE),0),nvl(sum(usage),0)fromLOCALUSAGEwhereto_char(ENDTIME,YYYYMMDD)=20041016
andLOCALCHARGE>0andcallerlike0543886%;

27418历程对应的SQL语句以下：
selectnvl(sum(LOCALCHARGE),0)fromLOCALUSAGEwhereto_char(ENDTIME,YYYYMMDD)=20041016andcallerlike0543888%;


4.利用相干用户毗连到数据库，反省其实行企图:
SQL>connectwacos/oss
Connected.

SQL>@?/rdbms/admin/utlxplan.sql
Tablecreated.

SQL>setautotraceon

SQL>settimingon

SQL>Selectnvl(sum(LOCALCHARGE),0),nvl(sum(usage),0)fromLOCALUSAGEwhereto_char(ENDTIME,YYYYMMDD)=20041016
andLOCALCHARGE>0andcallerlike0543886%;

NVL(SUM(LOCALCHARGE),0)NVL(SUM(USAGE),0)
----------------------------------------
00

Elapsed:00:02:56.37

ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=13435Card=1Bytes=5
3)

10SORT(AGGREGATE)
21PARTITIONRANGE(ALL)
32TABLEACCESS(FULL)OFLOCALUSAGE(Cost=13435Card=1
81Bytes=9593)

Statistics
----------------------------------------------------------
258recursivecalls
0dbblockgets
88739consistentgets
15705physicalreads
0redosize
580bytessentviaSQL*Nettoclient
651bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
8sorts(memory)
0sorts(disk)
1rowsprocessed

发明对localusage表做了全表扫描，甚么纪录也没有前往竟然用了2分多钟。

SQL>selectnvl(sum(LOCALCHARGE),0)fromLOCALUSAGEwhereto_char(ENDTIME,YYYYMMDD)=20040816andcallerlike0543888%;

NVL(SUM(LOCALCHARGE),0)
-----------------------
27.6

Elapsed:00:03:56.46

ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=13435Card=1Bytes=4
0)

10SORT(AGGREGATE)
21PARTITIONRANGE(ALL)
32TABLEACCESS(FULL)OFLOCALUSAGE(Cost=13435Card=3
615Bytes=144600)

Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
88588consistentgets
15615physicalreads
0redosize
507bytessentviaSQL*Nettoclient
651bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed

这个SQL语句有了局前往，发明也是对localusage表做了全表扫描，但速率也很慢，用了3分多钟。

SQL>selectcount(*)fromlocalusage;

COUNT(*)
----------
5793776

该表有579万多笔记录，数据量很年夜，全表扫描已不再合适。

5.反省该表的范例：

SQL>SELECTINDEX_NAME,TABLE_NAME,STATUS,PARTITIONEDFROMUSER_INDEXESWHERETABLE_NAME=LOCALUSAGE;

INDEX_NAMETABLE_NAMESTATUSPAR
-----------------------------------------------------------------------
I_LOCALUSAGE_SIDLOCALUSAGEN/AYES
UI_LOCALUSAGE_ST_SEQLOCALUSAGEN/AYES

SQL>SELECTindex_name,table_name,localityFROMuser_part_indexeswheretable_name=LOCALUSAGE;

INDEX_NAMETABLE_NAMELOCALI
------------------------------------------------------------------
I_LOCALUSAGE_SIDLOCALUSAGELOCAL
UI_LOCALUSAGE_ST_SEQLOCALUSAGELOCAL

发明该表是分区表，并在SERVICEID,STARTIME和CDRSEQUENCE列上创建了分区索引，索引范例是local索引。

6.检察分区索引的索引键值:

SQL>selectINDEX_NAME,COLUMN_NAME,INDEX_OWNERfromdba_ind_columnswhereTABLE_NAME=LOCALUSAGE;

INDEX_NAMECOLUMN_NAMEINDEX_OWNER
----------------------------------------------------------------------
I_LOCALUSAGE_SIDSERVICEIDWACOS
UI_LOCALUSAGE_ST_SEQSTARTTIMEWACOS
UI_LOCALUSAGE_ST_SEQCDRSEQUENCEWACOS

发明在endtime和caller列上都没有创建索引，这也是招致SQL语句做全表扫描的终极缘故原由。

7.决意创立新的分区索引以打消全表扫描：

(1).起首检察localusage表分区情形：

SQL>selectPARTITION_NAME,tablespace_namefromuser_tab_partitionswheretable_name=LOCALUSAGE;

PARTITION_NAMETABLESPACE_NAME
------------------------------------------------------------
LOCALUSAGE_200312WACOS
LOCALUSAGE_200401WACOS
LOCALUSAGE_200402WACOS
LOCALUSAGE_200404WACOS
LOCALUSAGE_200405WACOS
LOCALUSAGE_200406WACOS
LOCALUSAGE_200407WACOS
LOCALUSAGE_200409WACOS
LOCALUSAGE_200410WACOS
LOCALUSAGE_200411WACOS
LOCALUSAGE_200403WACOS
LOCALUSAGE_200408WACOS
LOCALUSAGE_200412WACOS

13rowsselected.

(2).在caller列上创立local分区索引：
SQL>settimingon
SQL>createindexI_LOCALUSAGE_CALLERonlocalusage(caller)
LOCAL
(
PARTITIONLOCALUSAGE_200312,
PARTITIONLOCALUSAGE_200401,
PARTITIONLOCALUSAGE_200402,
PARTITIONLOCALUSAGE_200404,
PARTITIONLOCALUSAGE_200405,
PARTITIONLOCALUSAGE_200406,
PARTITIONLOCALUSAGE_200407,
PARTITIONLOCALUSAGE_200409,
PARTITIONLOCALUSAGE_200410,
PARTITIONLOCALUSAGE_200411,
PARTITIONLOCALUSAGE_200403,
PARTITIONLOCALUSAGE_200408,
PARTITIONLOCALUSAGE_200412
)
TABLESPACEwacos
STORAGE(
INITIAL6553600
NEXT6553600
MAXEXTENTSunlimited
PCTINCREASE0)
PCTFREE5
NOLOGGING;

Indexcreated.

Elapsed:00:06:27.90(因为数据量对照年夜，耗时6分钟）

8.再次检察实行企图：
SQL>Selectnvl(sum(LOCALCHARGE),0),nvl(sum(usage),0)fromLOCALUSAGEwhereto_char(ENDTIME,YYYYMMDD)=20041016
andLOCALCHARGE>0andcallerlike0543886%;

NVL(SUM(LOCALCHARGE),0)NVL(SUM(USAGE),0)
----------------------------------------
00

Elapsed:00:00:03.00

ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=22Card=1Bytes=53)
10SORT(AGGREGATE)
21PARTITIONRANGE(ALL)
32TABLEACCESS(BYLOCALINDEXROWID)OFLOCALUSAGE(Cost=22Card=181Bytes=9593)
43INDEX(RANGESCAN)OFI_LOCALUSAGE_CALLER(NON-UNIQUE)(Cost=14Card=65063)

Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
16813consistentgets
569physicalreads
0redosize
580bytessentviaSQL*Nettoclient
651bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed

此次走了索引后速率分明快多了，用了3秒钟就前往了却果。

SQL>selectnvl(sum(LOCALCHARGE),0)fromLOCALUSAGEwhereto_char(ENDTIME,YYYYMMDD)=20040816andcallerlike0543888%;

NVL(SUM(LOCALCHARGE),0)
-----------------------
27.6

Elapsed:00:00:24.73

ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=22Card=1Bytes=40)
10SORT(AGGREGATE)
21PARTITIONRANGE(ALL)
32TABLEACCESS(BYLOCALINDEXROWID)OFLOCALUSAGE(Cost=22Card=3615Bytes=144600)
43INDEX(RANGESCAN)OFI_LOCALUSAGE_CALLER(NON-UNIQUE)(Cost=14Card=65063)

Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
129336consistentgets
7241physicalreads
0redosize
507bytessentviaSQL*Nettoclient
651bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed

这个SQL语句走了索引，用了24秒钟前往了局，功能分明进步了良多。

这能找出所有错误的99.99%。它不能找出的是仅仅涉及数据文件的损坏(这很不常见)。如果你想要检查一张表，你通常应该没有选项地运行myisamchk或用-s或--silent选项的任何一个。

从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。

是要和操作系统进行Socket通讯的场景。否则建议慎重！

然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情

外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

个人感觉没有case直观。而且默认的第三字段（还可能更多）作为groupby字段很容易造成新手的错误。

两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书