1 / 39

ASE ASE 12.5.0.3 MDA Monitoring and Diagnostic Access sp_sysmon ASE ASE ASE

3.

corinne
Download Presentation

ASE ASE 12.5.0.3 MDA Monitoring and Diagnostic Access sp_sysmon ASE ASE ASE

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ??????????? ???????·??????????

    2. 2 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????????? ASE ????????????????????????????????????????????? ASE 12.5.0.3 ???????? ??? MDA ??????????????? Monitoring and Diagnostic Access ???????????????????????????? sp_sysmon ????????????????ASE ??????? ??????????????????????? ASE ??????????????????? ASE ???????????????????????? ?????????·???????????????

    3. 3 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ????????????????? ??????????????????????????????????????????????????????? ?????????????????????????? ?????????????????????????? ???????????????????????? ???????????I/O?????????????? ????????????SQL???????? ??????????????????????? ??????????????????? ???????·?????????????????

    4. 4 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ????????? ··· sp_sysmon ??? ?????????????? sp_sysmon ?????????

    5. 5 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. sp_sysmon ??? sp_sysmon ?ASE????????????????? "Transaction Profile" ???? ASE ?????????????????????? ?????? insert / update / delete ????????? "Lock Management" ???? ASE ??????????????????????? ?????????????????????????? sp_sysmon ????????????????? ?~ ????????? ?~ ??????? sp_sysmon ?????????????????????

    6. 6 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ??????????? sp_sysmon ????? ????????????????????????? sp_sysmon ??????????????????????? sp_sysmon ? ASE ?????????????? ??????????????????????????????????

    7. 7 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ??????????????????????? ?????????????????????ID???????ID?????????????? ?????ID?????????????????? ?????????????????????? ????·??????????? sysobjects?sysindexes?sysprocesses ??? ??????? ID ??????????????? ?: ??????ID????????? ??????ID????????? ??????ID?????????

    8. 8 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved.

    9. 9 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ??? ID ??? ASE ???????????????????????????????ID???????? ???ID??: ??????????????ID: sysdatabases ? name, dbid ??????????????ID: sysobjects ? name, id ??????????ID: syslogins ? name, suid ????????ID: sysusers ? name, uid ??????? ID ??????? ASE???????ID???????????

    10. 10 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ??? ID ??? ??????????????ID??? select dbid from sysdatabases select name from sysdatabases where name = "master" where dbid = 1 go go ------------- ------------- 1 master ??????ID <-> ??????? ?????????????????????? select db_id("master") select db_name(1) go go --------------- -------------- 1 master

    11. 11 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ??? ID ??? (2) ??????? ? ??????ID select object_id("titles") ??????ID ? ??????? select object_name(144000513) select name from sysobjects where id=144000513 || select object_name(144000513) object_name ????dbid ?????????????·??????? ??DB??????????2??? dbid ????? select object_name(144000513, 5) ??????ID ??????ID

    12. 12 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. sysobjects ???? ?????????????????????????? ??????????????? sysobjects ???? insert ???? sysobjects ?????? id ???????????????ID uid ????????????????ID type ????????? "U" ???????????"P" ?????????"TR" ?????"V" ???? … sysstat2 ?????????????? crdate ?????????? instrig insert ??????????ID updtrig update ??????????ID deltrig delete ??????????ID

    13. 13 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. sysindexes ???? ????????????????????????????? ???????????????????1?????????? sysindexes ?????? name ???????????????? id ?????????????????????ID indid ??????ID status ?????????????????? first ???????????????????? root ??????·????????????? status2 ?????????????????? crdate ??????????? (12.5 ??)

    14. 14 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. sysindexes ???????????ID ???????????sysindexes ?? indid = 0 ????1??????? indid = 0 : ??????????????? ?????????????·??????????????sysindexes ?????????????????????????????? APL ??? indid = 0 ???? indid ? 1 ????? sysindexes ????????? (??? indid = 0 ? 1 ?????????) DOL ??? sysindexes ? indid ? 2 ?1????????? (DOL????? indid = 1 ??????) ????? APL ?????????·????????? ??

    15. 15 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. sysindexes ???????????ID (2) ?????????????·???????????????indid ? 2 ?????????? (APL, DOL ???) ???????????·???????1?????????sysindexes ????1????? ??????????? indid ????max(indid)+1 ?????????????????????? indid ?????????????? (?????????????) ???????? indid ?????????????? select name, indid from sysindexes where id = object_id("tablename")

    16. 16 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved.

    17. 17 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ?????????????????????? installmontables ???????????? master ???????????? ?????? mon ????????????? ?????????????????????????????????? ???????? Read Only ?????? ????????????? master ??????????????? ???? insert / update / delete ??? truncate table ???????? ?????????????????????? ????????????????????????????????/?????? ASE ??????????????????????? = ????????????????????????????????

    18. 18 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????????? monOpenObjectActivity ASE ?????????(???????????????????…) ?????????? monProcessActivity ASE ???????????????????? ????CPU???????I/O??????/????????? monCachedProcedures ??????·???????????????·??????????· ??????????????? monCachedObject ???·?????????????????????????? ??????

    19. 19 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????????????????????

    20. 20 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????? ???????????????????????????????? ASE ? 12.5.0.3 ?????????? select @@version go ----------------------------------------------------------------------- Adaptive Server Enterprise/12.5.0.3/EBF 10973 ESD… mon_role ??????????????? select * from master..syssrvroles where name = "mon_role" ?????????installmaster ??? cd $SYBASE/ASE-12_5/scripts isql -Usa -P -i installmaster

    21. 21 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????? (2) loopback ?????? sp_addserver 'loopback', null, ASE_servername ?????????????????·???????? cd $SYBASE/ASE-12_5/scripts isql -Usa -P -i installmontables mon_role ????? sp_role "grant", mon_role, sa ??????????? sp_configure "(?????????????)", 1

    22. 22 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????????????? ???????????????2????????? ?1??: ????????? ?????????????????????????????????? ?:monOpenObjectActivity ?????????????? "per object statistics active" ?1???? sp_configure "per object statistics active", 1 ?2??: ????????? sp_configure "enable monitoring", 1 ??????????????

    23. 23 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????????????? ????????????????????????????????????????????????? select ???????? select * from monOpenObjectActivity go Msg 12036, Level 17, State 1: Collection of monitoring data for table 'monOpenObjectActivity' requires that the 'enable monitoring', 'per object statistics active' configuration option(s) be enabled. ???????????????????????? ??????????? sp_configure ???????? sp_configure "per object statistics active", 1 sp_configure "enable monitoring", 1

    24. 24 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????????????????? ??????? ??? ?????????????????????? select * into mon_before from master..monOpenObjectActivity ???????????????????? ?????????????????

    25. 25 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. monOpenObjectActivity ???? ??????????????????????????????? ASE ?????????????? ???????????? insert / update / delete ??? ????????????? ?????????? ???????????? ???????????????????????????????? ???????? insert ???? ?????????????/??????????????????????????????????????

    26. 26 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. monOpenObjectActivity ??????????

    27. 27 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. monOpenObjectActivity ???? ?: testdb ? test1 ???????? indid=2 ???????? ????? ?????????? test1 ?????????????????DB?sysindexes ????? test1 ???(DBID, ObjectID, IndexID)? monOpenObjectActivity ? insert ???? ??????????? indid =2 ???? UsedCount ???1????LastUsedDate ???????????????? ???test1 ??????????????????? indid ???? UsedCount ?+1?LastUsedDate ?????????????? insert / update / delete ??????????????????? ?????????+1????

    28. 28 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved.

    29. 29 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ????????? insert/update/delete ??????? ?1:pubs2 ?????????????????? insert / update / delete ???????????? select object_name(ObjectID,DBID), RowsInserted, RowsUpdated, RowsDeleted from master..monOpenObjectActivity where DBID = db_id("pubs2") and IndexID in (0,1) go RowsInserted RowsUpdated RowsDeleted ------------------------- ------------------ ------------------- ------------------ salesdetail 531 104 2 roysched 32 0 0 titles 3 0 0

    30. 30 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ????????????????????? ?2: testdb ???????????????????????? ???????????????????????? select object_name(ObjectID,DBID), LockWaits from master..monOpenObjectActivity where DBID = db_id("testdb") order by LockWaits desc ?3: testdb ?????????????????????(??? ???????)???????? ?????? LockWaits ? LogicalReads ?????

    31. 31 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????????????? ?4: testdb ??????????????????????? ???????????? select object_name(ObjectID), IndexID, UsedCount from master..monOpenObjectActivity where DBID = db_id("testdb") go --------------------- ------------- --------------- account 0 9 account 1 510 salesdetail 0 1287 salesdetail 1 1536 salesdetail 2 243 ?????? UsedCount ?0?????????????????????????????

    32. 32 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????????????? ????LastUsedDate ?????? ??????????????????LastUsedDate = "1900/1/1" ?5: testdb ???????????????????????? ??????ID ??????? select object_name(ObjectID,DBID), IndexID from master..monOpenObjectActivity where DBID = db_id("testdb") and LastUsedDate = "1900/1/1" go ----------------------- ------------ test 3 customers 2 sysindexes ?????????????indid?????????????

    33. 33 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????·?????????? ???????????? ??????????????? alter table tablename lock datarows ????????????? insert/update/delete ????????????????? drop index tablename.indexname ??????????????????? ??SQL??????????????? - ???? update (index) statistics - SQL ????

    34. 34 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. DBXray ????? ASE ???????????? 12.5.0.3 ???????? BMC Software ????? ??????? ASE ????????????? CPU?I/O ??????? ???????? ··· ?????????????????????? ?????????????? DBXray ?????????????GUI???? ?????????????????????????? ????????????????????????

    35. 35 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved.

    36. 36 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ??????? ?????????? http://www.ctc-g.co.jp/~sybase/books.htm ?Sybase Transact-SQL:?????????? ?? ?? DBA??????? SQL ??????&????? ?? SQL ?????????????! "The Complete Sybase ASE Quick Reference Guide" Rob Verschoor ASE ????????????????????·??? Sybase ??????10????????????1? "Tips, Tricks & Recipes for Sybase ASE" Rob Verschoor ASE ???????????????? ????????????!??????????!?

    37. 37 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ???????·?????? ???????????? http://www.ctc-g.co.jp/~sybase/ ? ?????? ? ??????????? sp_create_table create table ???? sp_create_index create index ???? sp_find_obj ?????DB????????????? ??DB?????? sp_lock_scheme ?????????????????? sp_proc_trees ?????????????????? ??????????OK?

    38. 38 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. ????????(???????)/???? ASE ???????????????????????????? http://www.ctc-g.co.jp/~sybase/ ? ?????/????????

    39. 39 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved. CTC ?????????????? CTC ?????????????????????????

    40. 40 ©2003 ITOCHU TECHNO-SCIENCE Corp.All Rights Reserved.

More Related