( , ) | | | |
| ( , ) | ( , )
vd version version vw vw
v vd
(1) The version definition is the installed version of Oracle DB and Application Server.
Except for subject, application, and platform, the product version is still an attribute.
Oracle DB versions have tree structure stuff; as shown in Figure 5.7.
M ain version w eight of case M ain version of query V ersion sim ilarity
version
Oracle DB
Oracle 8 Oracle 9 Oracle 10
8.0.5 8.1.7 9.0.1 9.2.0.7 10.0.1 10.1.3.0.1
Weighted Database Version Tree
Figure 5.7. Weighted database version tree
(2) Nowadays, the most common used version are Oracle 8, 9 and Oracle 10g in Oracle database. Each version has its own sub version, e.g., Oracle 9 have 9.0.1 ~ 9.2.0.7 sub versions. The main version e.g., the weight of Oracle 8, is higher than the sub versions, e.g., 8.0.5 , weights are designed by experts.
'
Wi '
1
Wi
Example 5.4. Version Similarity.
Version similarity was shown as below.
( , ) | 0.9 0.8 | | 0.03 0.02 | 0.11
In Example 5.4, firstly, calculate similarity distance since database versions have tree structure in nature, secondly, using similarity equation to determine similarity. The similarity between two versions (Oracle database 9.0.1 and 8.1.7) is 0.89, which is lower than similarity value 0.99 in versions Oracle database 8.0.5 and 8.1.7.
As we know, different log will trigger different weight, e.g., log pattern “enquence”
means “version weight” need to adjust higher value by experts. e.g., = 0.3 * + 0.25* + 0.05 * + 0.4 * , where 0.4 is the version weight.
( , ) S q c ( , )
S q cs S q ca( , ) S q cp( , ) S q cv( , )
Example 5.5. Case Similarity.
Case 1: S q c = 0.3x0.66+0.25x0.66+0.05x0.995+0.4x0.99 = 0.808( , )
= 0.3x0.00+0.25x0.13+0.05x0.785+0.4x0.89 = 0.427 ( , )
S q c Case 2:
In Example 5.5, the similarity values are 0.808 and 0.427 in case1 and case2, respectively. From experiment, case 1 is similar than case 2 in the problem “system pending”.
Chapter 6. Implementation and Evaluations
In our experiment, the main operating system deployed is Microsoft 2003 and Solaris 8;
the expert system tool is DRAMA enterprise 2.5 [22]; the application server is Oracle Application Server 10g (9.0.4) [19]; the database server is Oracle database (9.2.0.1) [20]; the implementation of SRS is in Oracle Jdeveloper 10g [21]. The Small device platform simulator is Windows CE 5.0 with 320 by 240 dimensions.
In the following, we will focus on the system problem diagnosis module (Rule-Based Inference) and solution retrieving module (Case-Based Reasoning). Figure 6.1 displays the user interface of SRS.
Figure 6.1. SRS User Interface
6.1. Implementation of SRS
When the on-duty employees receive the error warning from short message server, traditionally they use KM Center or Search Engine as their approaches to solve problems, e.g.,
“Metalink” (https://metalink.oracle.com) or Google (http://www.google.com). Although they found the solution eventually, the processing of system diagnosis and solution retrieving are still not efficient due to lack of domain knowledge and experience. Therefore, we propose the SRS to assist on-duty employees to deal with those problems efficiently. SRS is composed of two main modules, problem diagnosis module and solution retrieving module.
In problem diagnosis module, we use rule-based inference approach which imitates expert’s inference model [22]. In other words, SRS provides a system problem diagnosis module. As system is abnormal, monitoring daemons will collect error logs and system information and trigger preprocessor to translate related logs into the facts, inference module [22] continues to infer the possible error types.
In solution retrieving module, we use CBR approach to calculate similarity between query and cases in case base. In other worlds, SRS provides a solution retrieving module that assist users to find out the proper solutions.
When the error type is referred by problem diagnosis module, then the solution retrieving module will retrieve the cases, reuse the solution cases and feedback solution to users. When no proper solution is found, on-duty employees will call expert for help. The experts will be on site to solve problem, and revise the attribute values or attributes in case base to full fill the needs of the new problem.
Example 6.1. System Problem Diagnosis Example of Database Pending.
We use database pending as an example and capture the symptoms of system pending as inference information. In SRS, users will receive the diagnosed error type [1][15] by small
devices, then user can input keywords to search the solutions, as shown in Figure 6.1 and Figure 6.2.
Figure 6.2. SRS User Interface in Query
After submitting the query, the solution lists will be displayed [13]. Each solution contains subject, application, version and platform, and the probabilistic similarity will be displayed by descending list, as shown in Figure 6.3. Therefore, users can choose solutions depending on probabilistic similarity [2] [11].
Figure 6.3. Solution List in SRS
Eventually, the solution will show the know-how and assist users step by step to solve system problems efficiently, as shown in Figure 6.4 [3].
Figure 6.4. Solution Description in SRS System
6.2. Evaluation of SRS
In this thesis, we use questionnaire to evaluate our system. Firstly, we find the expert in our company to diagnose problems, and we find five users in our company to test the SRS.
Finally, we use 6 kinds of predefined problems to test the accuracy and performance between SRS and KM center. Hence, both the users satisfaction and SRS accuracy could be evaluated.
In this experiment, we use about 10800 rules to infer the error type, and use 36 real cases in case base that contains six kinds of error type and the corresponding solutions. We use 54 keywords in index table to assist preprocess precisely. SRS retrieves top 10 solutions list to assist employees trouble shooting; hence we compare probabilistic similarity toward 6 kinds of problem. Details are described in Table 6.1
Table 6.1
Error Types of System Diagnosis Error Type Description
db crash Db crash means database crashed and unable to startup, this problem could cause other related issues, e.g., missing data integrity.
missing redo log
Redo log is a buffer that save data in memory for data caching, once missing redo log, this database will crash immediately.
archive log Archive log is for data compression in database, once archive log is missing or crashing will cause data compression issue.
Data file Data file is the file for saving data records those are committed in database.
Once data file is missing or crashing, it will cause data lost and database crash.
control file Control file is for controlling data file profile and related database parameters setting. Once control file is missing or crashing, it will cause database crash
system monitoring
System monitoring (SMON) performs instance recovery following an instance crash, combine free spaces in database, and managed spaces used for sorting. Once SMON is crashing, it will cause database crash immediately.
Experiment Case. Solutions list probability on similarity towards 6 kinds of error type SRS training cases are listed in Table 6.2 which shows solutions list probability on similarity between solution cases towards 6 kinds of error type.
Table 6.2
SRS system toward 6 kinds of error type on similarity Error Types
Top3 Lists db crash Missing redo log archive log data file control file system monitoring
top1 64.50% 87.00% 87% 70.00% 92.00% 93.00%
top2 59.00% 61.00% 15.00% 50.50% 35.00% 78.50%
top3 59.00% 15.00% 9.50% 32.00% 9.50% 58.00%
The distributed probability is shown in Table 6.2. From the experiment result, top 3 solutions list in error type “db crash” is closer on accuracy. In error type “control file” and
“system monitoring” of top1 solutions probability are more accurate than others. On average, SRS system solutions probability is over 60 percent.
Evaluation 1. Problem solving on accuracy between SRS and expert
Since occurred keywords are changeable in real cases; hence we use Table 6.3 to
describe experiment the number of keywords, test times, KM Center hit the problem times in solution top 10 list, SRS hit the problem times in solution top 10 list to compare hit ratio of problem solving accuracy between SRS and expert. Besides, test times are based on keywords to set different numbers of keyword.
Table 6.3
Problem Solving on Precision Error Types
data file control file system monitoring
SOS accuracy 80% 100% 83% 75% 75% 80%
The diagram of precision evaluation table (Table 6.3) is shown in Figure 6.5.
0%
Figure 6.5. Problem Solving Precision
From experiment of Figure 6.5, SRS is more precise than KM Center. The solutions occurs in top 10 list towards six problems.
Evaluation 3. system diagnosis and solution retrieving in time aspect.
In time evaluation, SRS system listed in Table 6.4 is more quick than expert.
Table 6.4
Time evaluation between SRS and KM Center
Solution Retrieving Time (minutes)
Evaluation
data file control file system monitoring
SOS (min) 2.00 2.00 1.00 2.00 2.00 3.00
KM (min) 8.00 6.00 6.00 7.00 6.00 7.00
The diagram of time evaluation table (Table 6.4) is shown in Figure 6.6. Figure 6.6 shows the comparison result between SRS and KM Center towards system diagnosis and solution retrieving in time aspect.
0 1 2 3 4 5 6 7 8 9
db crash
missing redo log
archive log
data file
control file
system monitoring
error types
time (minutes)
SOS (min) KM (min)
Figure 6.6. Time evaluation between SRS and expert
In time evaluation, SRS is quicker than KM Center in problem diagnosis and retrieving the corresponding solutions towards six kinds of error type. From experiment, the error type
“db crash” is the complicated problem, and using KM approach needs eight minutes to do problem diagnosis and solution retrieving.
Chapter 7. Conclusion and Future Work
System diagnosis and solution retrieving are very important for IT employees, until now, most of them are unable to find a better way to solve problem, even though they have document center and search engine to assist employees to solve problems, but the cost is still too high and not efficient. Unlike traditional mechanism, SRS system is a hybrid system, using rule-based inference and case-based reasoning. It is proper for applying system diagnosis and solution retrieving. Thereby we can refine rule base and revise case base quickly, while new version created and new problem occurs. Besides, SRS system integrates with monitoring tool and mobility devices that make problem solving more efficient.
In this thesis, we design and implement Solution Offering System (SRS) to assist employee to solve problem and discover problem easily. Our main contributions are: (1) Provide hybrid architecture to solve system problem, use case-based inference to infer the error type and increase performance, and use case-based reasoning retrieving solved case to assist employee deal with the abnormal situations. (2) Define NORM structure of Oracle Application Server and Oracle database to enhance inference. (3) Define attributes in query and case to increase accuracy for finding solutions.
In future work, we wish to apply this hybrid architecture in different domain to help more employees, e.g., IC design and supply chain. Due to they have tree structure stuff on modules and the attributes could be identified and compared. Depending on tree structure, each rule class has its own objects and facts, the object name, fact name and fact value need to be modified to fulfill new related domains. On the other hand, attributes need to be modified in query and case to satisfy the similarity comparison in new domain. Basically, the architecture of SRS system could be kept for new related domains because this architecture imitates the thinking model from experts.
Bibliography
[1] Y. I. Chang and W. H. Hsieh, “An efficient scheduling method for query-set-based broadcasting in mobile environments,” Distributed Computing Systems Workshops, 2004.
Proceedings. 24th International Conference, Page(s):478 – 483, 2004.
[2] Y. F. Chen, H. Huang, R. Jana, S. John, S. Jora, A. Reibman and B. Wei, “Personalized multimedia services using a mobile service platform,” Wireless Communications and Networking Conference, 2002. WCNC2002. 2002 IEEE, Page(s):918 - 925 vol.2, 17-21 March 2002.
[3] Y. Chen, W. Y. Ma and H. J. Zhang, “Detecting web page structure for adaptive viewing on small form factor devices,” ACM TXN, 2003.
[4] M. Gu, X. Tong and A. Agnar, “Comparing similarity calculation methods in conversational CBR, “Information reuse and integration, Conf, 2005. IRI -2005 IEEE International Conference, Page(s):427 – 432, 15-17 Aug. 2005.
[5] M. J. Hajar and S. P. Lee, PhD, “Applying machine learning using case-based reasoning (CBR) and rule-based reasoning (RBR) approaches to object-oriented application framework documentation,＂ Information Technology and Applications, 2005.
ICITA 2005. Third International Conference Volume 1, Page(s):52 - 57 vol.1, 4-7 July 2005.
[6] S. Hayashi, T. Asakura and S. Zhang, “Study of machine fault diagnosis system using neural networks,” Neural Networks, 2002. IJCNN '02. Proceedings of the 2002 International Joint Conference on Volume 1, Page(s):956 – 961, 12-17 May 2002.
[7] X. Hou, J. Gu, X. Shen and W. Yan, “Application of data mining in fault diagnosis based on ontology”, Proceedings of the Third International Conference on Information Technology and Applications (ICITA’05), 2005.
[8] S. Krishnaswamy, S. W. Loke and A. Zaslasvky “A hybrid model for improving response time in distributed data mining,” Systems, Man and Cybernetics, Part B, IEEE Transactions Volume 34, Issue 6, Page(s):2466 – 2479, Dec. 2004.
[9] F. R. Kumar, S. Gopalan and V. Sridhar, “ Context enabled Multi-CBR based Recommendation Engine for E-commerce,＂ e-Business Engineering, 2005. ICEBE 2005.
IEEE International Conference, Page(s):237 – 244, 12-18 Oct. 2005.
[10] G. Lambert-Torres, H. G. Martins, R. Rossi and L. E. B. da Silva, “Using similarity assessment in case-based reasoning to solve power system substation problems,”
Electrical and Computer Engineering, 2003. IEEE CCECE 2003. Canadian Conference Volume 1, Page(s):343 - 346 vol.1, 4-7 May 2003.
[11] T. Lemlouma and N. Layaida, “Adapted content delivery for different contexts,”
Applications and the Internet, 2003. Proceedings. 2003 Symposium, Page(s):190 - 197, 27-31 Jan. 2003.
[12] W. Y. Lum and F. C. M. Lau, “User-centric adaptation of structured Web documents for small devices,” Advanced Information Networking and Applications, 2005. AINA 2005.
19th International Conference, Volume 1, Page(s):507 - 512 vol.1, 28-30 March 2005.
[13] W. C. Peng and M. S. Chen, “Developing data allocation schemes by incremental mining of user moving patterns in a mobile computing system,” IEEE Transactions on Knowledge and Data Engineering, Volume 15, Issue 1, Page(s):70 – 85, Jan.-Feb. 2003.
[14] I. Rish, M. Brodie, S. Ma, N. Odintsova, A. Beygelzimer, G. Grabarnik and K.
Hernandez, “Adaptive diagnosis in distributed systems ,” IEEE Transactions on neural networkds, VOL. 16, NO. 5, SEPTEMBER 2005.
[15] Y. L. Wai and F. C. M. Lau, ” User-centric content negotiation for effective adaptation service in mobile computing,” IEEE Transactions on Software Engineering, Page(s):1100 – 1111, Volume 29, Issue 12, Dec. 2003.
[16] D. Zhang, S. Dai, Y. Zheng, R. Zhang and P. Mu, “Researches and application of a hybrid fault diagnosis expert system,” Intelligent Control and Automation, 2000.
Proceedings of the 3rd World Congress on Volume 1, Page(s):215 - 219 vol.1, 28 June-2 July 2000.
[17] Oracle Admin, “Oracle9i Application Server Architecture and Components,”
http://www.huihoo.com/oracle/application_server/9i.html.
[18] Oracle Admin, “Oracle to answer Regis MSCD 640 Oracle Admin on Windows 2000 Vocabulary Assignment Answers,” http://www.wilsonmar.com/1oraarch.htm.
[19] Oracle, OTN, http://www.oracle.com/technology/software/products/ias/index.html.
[20] Oracle, OTN,
http://www.oracle.com/technology/software/products/database/oracle10g/index.html.
[21] Oracle, OTN, http://www.oracle.com/technology/software/products/jdev/index.html.
[22] 曾憲雄等, 人工智慧與專家系統─理論、實務、應用 ,2005, 旗標。
Appendix A: AT of Rule class Daemon home
x>=1000 x>=1200 1<=x<=100 x>=1500 x>=500 x>=600
Not commit transaction
Yes No Slightly Slightly Slightly Sometimes
Transaction abort
Yes No Slightly Slightly Sometimes Sometimes
CKPT not trigger yet
No Yes Slightly Slightly Sometimes Slightly
System pending
Very Often Very Often Sometimes Often Often Often
Idle process
Slightly Slightly No Slightly Sometimes Yes
Write back to date file
Slightly Slightly No No Sometimes yes
Write back to log file
Slightly Slightly sometimes No Yes sometimes
Where objects are listed as follows:
TROLL: Transaction Rollback TREC: Transaction Recovery
CIP: Check Idle ProcessesCDFH: Check data file header WLTD: Write log to db
CFDF: Update control file and data file Facts are listed as follows:
Memory loading: The loading status of hardware memory is in DB.
CPU loading: The loading status of CPU utility is in DB.
Transaction number: The number of transaction processes in DB.
Not Commit Transaction: The transactions those are modified but are not commit yet.
Transaction abort: The transaction those are error or other related reason to trigger transaction abort.
CKPT not trigger yet: The DB module “check point” is not been triggered yet.
System Pending: The whole system (DB and Application server) are pending.
Idle Process number: The processes number those are idle locate in current system.
Disk Full: The volume of hardware disk that is full.
Write back to control file: When system parameters are modified, the DB module will write modified information into control file.
Write back to data file: When system data are modified, the DB module will write data into data files which are located in DB.
Write back to log file: When system logs are modified and exceed the redo log buffer, the DB module will write logs into log files which are located in DB.
AOT of Rule class Daemon home
D: dominate the relationship X: no relation
No. : 1~ 5 relationship strength
Appendix B: AT of Rule class DB Daemon
System monitoring
Process monitoring
Check point Log writer
Transaction rollback no yes slightly partial
Transaction recovery no yes no slightly
Clear idle process partial Yes slightly no
Clear data file header no No slightly no
Write log into disk sometimes sometimes sometimes yes Update control file and data file no sometimes yes yes
Instance recovery yes no no no
Temp segment recovery yes no no no
Connect fragment yes no no no
Sub processes 1 1 1 1-10
Where objects are System monitoring, Process monitoring, Check point, and Log writer, please refer the Appendix C to reach the details.
The Facts are listed as follows:
Transaction rollback: When errors occurs in data saving, the related transactions will be roll back to their previous status.
Transaction recovery: Transaction Recovery is an application recovery whereby the effects of specific transactions during a specified timeframe are removed from the database.
Clear idle process: When idle processes exceed the numbers of system predefined setting, the DB module will clear idle processes.
Clear data file header: When writing data file errors, the data file header will be clear by DB module.
Write log into disk: When system produces some information, the DB module will write logs into disks.
Update control file and data file: When system parameters or data have been modified, the DB module will update control file and data file which are located in DB.
Instance recovery: Occurs when a software or hardware problem prevents an instance from continuing work.
Temp segment recovery: Occurs when a segment space is not enough or software error.
Connect fragment: When system fragments are exceeding a value, then the DB module will connect the fragment block into a larger block for DB reuse.
Sub process: Sub process is the number of processes fork by main process.
AOT of Rule class DB Daemon
System monitoring
Process monitoring
Check point Log writer
Transaction rollback 2 D 1 3
Transaction recovery 2 D X 1
Clear idle process 3 D 2 X
Clear data file header X X 1 X
Write log into disk 2 2 2 D
Update control file and datafile 1 2 D D
Instance recovery D 0 2 1
Temp segment recovery D 0 1 1
Connect fragment D 0 0 0
Sub processes 3 3 2 2
Where,
D: dominate the relationship X: no relation
No. : 1~ 5 relationship strength
Appendix C: Oracle database 10g architecture
Components:
SGA are made up three require components and three optional components:
Shared Pool – Cache the most recently used SQL statements that have been issued by database users.
Database buffer Cache—Cache the data that has been recently accessed by database users.
Redo log buffer—Store transaction information for recovery purpose.
Java pool—Caches the most recently used Java objects and application code when Oracle’s JVM option is used.
Large pool—Cache data for large operations such as Recovery Manager backup and restore activity and Shared Server components.
Stream pool—Cache the data associated with queued message requests when Oracle’s Advanced Queuing option is used.
Oracle background process
Repository.
Memory Manager (MMAN)—Manages the individual SGA component when Automatic Shared Memory Management feature is used.
Memory Monitor Light (MMNL)--Gather and analyze statistics used by Automatic Workload Repository feature, Flushed every 30 minutes or when buffer is full.
System monitor (SMON)—Performs instance recovery following an instance crash, combine free spaces in database, and managed spaces used for sorting.
Process Monitor (PMON)—Clean up failed database connections.
Database Writer (DBWn)—Writes modified db blocks from the SGA’s db buffer cache to the datafiles.
Log Writer (LGWR)—Writes transaction recovery information from the SGA’s Redo Log Buffer to the online redo log file.
Checkpoint (CKPT)—Update the database files following a Checkpoint Event.
Archiver (Arcn)—archive redo log in second place for recovery
Archiver (Arcn)—archive redo log in second place for recovery