Non-recursive method for parameter evaluation within an information management system

• US 5,852,818 A
• Filed: 12/23/1996
• Issued: 12/22/1998
• Est. Priority Date: 12/23/1996
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Riel, Jonathan, Wilson, Geoffrey, Smith, Gregory, Guay, Todd, Royal, Gaylen
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Coby, Frantz
• Application Number:
  US08/772,298
• Time in Patent Office: 729 Days
• Field of Search: 707/1, 707/101, 707/2, 707/100
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

A method for non-recursive data driven parameter evaluation within an information management system (e.g., a relational database management system, RDBMS). Parameters are attributes within an RDBMS system whose value can be modified to influence the manner in which a database environment responds. Depending on the character of the data within a database environment or the hardware resources allocated to the database, the parameter values have a large influence on the performance efficiency of the database. For instance, a parameter value may influence the memory size of certain data storage areas within a database environment and, if set too small, can cause an inefficiently large amount of input/output accesses to occur as data is moved in and out of disk storage. Also, many parameters are data dependent on other parameters. The novel system presents an automated system for evaluating parameters using a non-recursive evaluation process. By using a non-recursive approach, the evaluation process is streamlined in that only one pass yields an optimum parameter evaluation and the results are reproducible. The novel system identifies a set of parameters within an environment to be tuned, eliminates cyclic dependencies, and orders the parameters based on independence, levels of dependence, impact on system performance and common data usage. The present invention is data driven because rules within a respective parameter are then evaluated based on data availability to arrive at a set of parameter value recommendations. In one embodiment instance, tablespace, system environment, and schema parameters can be evaluated within a RDBMS tuning session.

Claims

1. In a relational database management system having a processor coupled to a bus and a computer readable memory unit coupled to said bus, a method of performance tuning, said method comprising the steps of:

a) automatically generating a non-recursive parameter evaluation sequence from said computer readable memory unit, said non-recursive parameter evaluation sequence containing rules for evaluating a plurality of parameters which adjust resources within a database environment wherein parameter dependencies exist within parameters of said plurality of parameters, said step a) comprising the steps of;

removing circular dependencies within said plurality of parameters; and

ordering said plurality of parameters into said non-recursive evaluation sequence by placing independent parameters first and placing dependent parameters following parameters on which said dependent parameters depend wherein parameters are placed higher in said non-recursive parameter evaluation sequence based on the number of parameters that are data dependent on said parameters;

b) evaluating said plurality of parameters using said non-recursive parameter evaluation sequence to determine a set of recommended values for said plurality of parameters, said step of evaluating performed in a single non-recursive evaluation path; and

c) performance tuning said database environment by applying said set of recommended values to said plurality of parameters.

2. A method as described in claim 1 wherein said step a) further comprises the step of:

for a first set of parameters having no dependencies or equal dependencies, ordering parameters of said first set of parameters based on their impact on system performance and further based on their common data usage.

3. A method as described in claim 1 wherein said step b) comprises the step of evaluating individual rules that are associated with a respective parameter of said plurality of parameters in sequence according to data availability for said individual rules.

4. A method as described in claim 1 further comprises the step of accessing resource statistics and information regarding hardware resources pertinent to said database environment to be tuned and accessing current values for said plurality of parameters in advance of said step b).

5. A method as described in claim 1 further comprising the step of allowing a user to select a particular selected instance for performance tuning and identifying a plurality of parameters within said database environment pertinent to said selected instance.

6. A computer readable medium containing program instructions stored therein that implement a method of performance tuning when executed over a processor within in a relational database management system, said method comprising the steps of:

a) automatically generating a non-recursive parameter evaluation sequence from said computer readable memory unit, said non-recursive parameter evaluation sequence containing rules for evaluating a plurality of parameters which adjust resources within a database environment wherein parameter dependencies exist within parameters of said plurality of parameters, said step a) comprising the steps of;

removing circular dependencies within said plurality of parameters; and

ordering said plurality of parameters into said non-recursive evaluation sequence by placing independent parameters first and placing dependent parameters following parameters on which said dependent parameters depend wherein parameters are placed higher in said non-recursive parameter evaluation sequence based on the number of parameters that are data dependent on said parameters;

b) evaluating said plurality of parameters using said non-recursive parameter evaluation sequence to determine a set of recommended values for said plurality of parameters, said step of evaluating performed in a single non-recursive evaluation path; and

c) performance tuning said database environment by applying said set of recommended values to said plurality of parameters.

7. A computer readable medium as described in claim 6 wherein said step b) comprises the step of evaluating individual rules that are associated with a respective parameter of said plurality of parameters in sequence according to data availability for said individual rules.

8. A computer readable medium as described in claim 6 further comprises the step of accessing resource statistics and information regarding hardware resources pertinent to said database environment to be tuned and accessing current values for said plurality of parameters in advance of said step b).

9. A computer readable medium as described in claim 6 further comprising the step of allowing a user to select a particular selected instance for performance tuning and identifying a plurality of parameters within said database environment pertinent to said selected instance.

10. A method for evaluating parameters within a relational database management system comprising the steps of:

a) identifying a plurality of parameters to be tuned within said relational database management system wherein parameter dependencies exist within parameters of said plurality of parameters;

b) removing circular dependencies among said plurality of parameters;

c) automatically generating a non-recursive evaluation sequence by ordering said plurality of parameters according to the steps of;

c1) placing independent parameters first in said non-recursive evaluation sequence and placing dependent parameters following parameters on which said dependent parameters depend; and

c2) placing parameters of said plurality of parameters higher in said non-recursive evaluation sequence based on the number of parameters that are data dependent on said parameters; and

d) using a computer implemented process, evaluating said plurality of parameters using said non-recursive parameter evaluation sequence to determine a set of recommended values for said plurality of parameters.

11. A method as described in claim 10 further comprising the step of e) applying said set of recommended values to said plurality of parameters to tune said relational database management system.

12. A method as described in claim 11 wherein said step c) further comprises the step of, for a first set of parameters having no dependencies or equal dependencies, ordering parameters of said first set of parameters based on their impact on system performance and based on their common data usage.

13. A method as described in claim 12 wherein said step d) comprises the step of evaluating individual rules that are associated with a respective parameter of said plurality of parameters in sequence according to data availability for said individual rules.

14. A method as described in claim 12 further comprises the step of, in advance of said step d), accessing resource statistics and information regarding hardware resources pertinent to said relational database management system to be tuned and accessing current values for said plurality of parameters.

15. A method as described in claim 12 wherein said plurality of parameters are instance parameters.

16. A method as described in claim 15 wherein said step a) comprises the step of allowing a user to select a particular selected instance for tuning and identifying a plurality of parameters within said relational database management system pertinent to said selected instance.

17. A method as described in claim 12 wherein, with respect to a first parameter and a second parameter that are interdependent, said step b) comprises the step of breaking a circular dependency between said first parameter and said second parameter by inserting an intermediate attribute upon which said first parameter and said second parameter are dependent.

18. In a relational database management system having a processor coupled to a bus and a computer readable memory unit coupled to said bus, a method for tuning a database environment, said method comprising the steps of:

a) identifying a plurality of parameters within said database environment wherein parameter dependencies exist within parameters of said plurality of parameters;

b) removing circular dependencies within said plurality of parameters;

c) automatically generating a non-recursive evaluation sequence by ordering said plurality of parameters according to the steps of;

c1) placing independent parameters first in said non-recursive evaluation sequence and placing dependent parameters following parameters on which said dependent parameters depend; and

c2) for a first set of parameters having no dependencies or equal dependencies, ordering parameters of said first set of parameters based on their impact on system performance and further based on their common data usage;

d) using a computer implemented process, evaluating said plurality of parameters using said non-recursive parameter evaluation sequence to determine a set of recommended values for said plurality of parameters; and

e) tuning said database environment by applying said set of recommended values to said plurality of parameters.

19. A method as described in claim 18 wherein said step c1) comprises the step of placing parameters of said plurality of parameters higher in said non-recursive evaluation sequence based on the number of parameters that are data dependent on said parameters.

20. A method as described in claim 18 wherein said step d) comprises the step of evaluating individual rules that are associated with a respective parameter of said plurality of parameters in sequence according to data availability for said individual rules.

21. A method as described in claim 20 further comprises the step of accessing resource statistics and information regarding hardware resources pertinent to said database environment to be tuned and accessing current values for said plurality of parameters in advance of said step d).

22. A method as described in claim 18 wherein said plurality of parameters are instance parameters.

23. A method as described in claim 22 wherein said step a) comprises the step of allowing a user to select a particular selected instance for tuning and identifying a plurality of parameters within said database environment pertinent to said selected instance.

24. A method as described in claim 18 wherein, with respect to a first parameter and a second parameter that are interdependent, said step b) comprises the step of breaking a circular dependency between said first parameter and said second parameter by inserting an intermediate attribute upon which said first parameter and said second parameter are dependent.