- Structure of the Trace Files. - Overview of the Package dbms_stats. - The first takes advantage of the output of instrumentation code (see Chapter 3 for more information). - Users are unsatisfied with the current performance of the application.. - You can find a discussion about it in the book Oracle Insights: Tales of the Oak Table (Apress, 2004).. - To do so, it is essential to focus on the most time-consuming part of the processing.. - In the following query, the selectivity of the operation accessing the table is 1. - In the following query, the selectivity of the operation accessing the table is rows returned out of 10,000):. - In the following query, the selectivity of the operation accessing the table is 0 (0 rows returned out of 10,000):. - In this type of situation, it is not possible to compute the selectivity of the access operation based on the number of rows returned by the query (in this case 1). - Here, the selectivity of the access operation is . - The key information associated with the parent cursor is the text of the SQL statement.. - identify the root cause of the problem. - T oo often we hear things like “The database has performance problems!” Sometimes this is true, sometimes it is not. - The SQL statement and response time of the interactions between the data access object (AccountDao) and the database. - Profiling analysis Always-available coverage of the whole application.. - Multitier profilers provide end-to-end view of the response time.. - size of the attribute's value OCI_ATTR_CLIENT_IDENTIFIER. - Overview of the response time for all requests. - Response time of the action Sign-on. - Correlation of the response time for the action Sign-on with other metrics. - Request tree of the action Sign-on. - Notice that this class is part of the Spring framework. - With the zoom capabilities of the JProbe Console, it is also possible to get a more readable call graph. - Line-level statistics of the method getAccount. - Here is an excerpt of the trace file related to the previous sample. - Levels of the Debugging Event 10046 Level Description. - The process type is available in the column type of the view v$session.. - The name of the trace file itself used to be version and platform dependent. - instance name: This is the lowercase value of the initialization parameter instance_name.. - It is available in the column instance_name of the view v$instance.. - For parallel slave processes, it is found in the column server_name of the view v$px_process. - For most other background processes, it is found in the column name of the view v$bgprocess.. - Its value is found in the column spid of the view v$process.. - The value of the parameter tracefile_identifier is available in the column traceid of the view v$process. - The structure of the trace file shown on the right in Figure 3-19 may be generated with the following PL/SQL block:. - As of Oracle Database 10g, the format of the trace files has changed in this area. - The explanations are based on a trace file generated during the execution of the following PL/SQL block:. - sort=option Set of zero or more of the following sort options:. - Note that this is done independently of the number of SQL statements present in the trace file. - The text of the SQL statement in some situations is formatted. - Note that the ID of the SQL statement is available only as of Oracle Database 11g.. - cost is the estimated cost of the operation. - For example, the writing of the trace file itself is not accounted for. - In the analysis of the wait events, the key is knowing to which operation they are related.. - You can find a short description of most of them in the appendixes of the Oracle Database Reference manual.. - Optionally, change the default value of the command-line arguments. - number of statements) in the output file (integer number >= 0, unlimited = 0) -o,--output output file name (a temporary XML file with the same name but with the extension xml is also created) -r,--release major release of the database engine that generated the input trace file . - input specifies the name of the input file. - output specifies the name of the output file. - In the HTML version of the output file, you can simply click that ID to locate the SQL statement details. - At first, general information about the execution environment and the text of the SQL statement is given. - Notice that the primary keys consist of the underlined columns.. - The package runs with the privileges of the user executing it. - State diagram of the profiler. - The code of the procedure is available in the script perfect_triangles.sql.. - Performance of the I/O subsystem. - Performance of the CPU. - mode of the procedure gather_system_stats to stop, as shown in the following command:. - If one of the I/O statistics cannot be computed, it is set to NULL (as of Oracle Database 10g) or -1 (in Oracle9i).. - Note that the execution of the previous command does not take 30 minutes. - The computation of mreadtim based on noworkload statistics and the block size of the database. - System statistics make the query optimizer aware of the system where the database engine is running. - The following is an explanation of the table statistics returned by this query:. - chain_cnt is the sum of the rows in the table that are chained or migrated to another block (chained and migrated rows will be described in Chapter 12). - To get the low and high value of the column val1, you can use the following query:. - The following is an explanation of the column statistics returned by this query:. - The column endpoint_number provides the cumulated frequency of the value. - To create a height-balanced histogram, think of the following procedure. - Figure 4-6 shows a graphical representation of the data it returns. - The histogram does not store the frequency of the values.. - As shown in the output of the next query, the hidden columns have the same name as the extensions. - leaf_blocks is the number of leaf blocks of the index.. - Computation of the average number of leaf blocks that store a single key. - Computation of the average number of data blocks referenced by a single key. - Parameters of the Procedures Used for Gathering Object Statistics. - ownname specifies the name of the schema to be processed. - indname specifies the name of the index to be processed. - tabname specifies the name of the table to be processed. - The other columns are counts of the number of utilizations (not executions, so be careful). - statown specifies the owner of the table specified with the parameter stattab. - In Oracle Database 10g, you can change the default values of the parameters cascade, estimate_. - The following query shows the effect of the setting performed on the previous PL/SQL blocks. - This is the name of the new scheduler available as of Oracle Database 10g.. - You can do this by executing one of the following procedures in the package dbms_stats. - This is carried out by executing one of the following procedures:. - Be aware that the package dbms_stats isn’t the only one that gathers object statistics and, therefore, is affected by locks on object statistics. - The following example, which is a continuation of the previous one, shows this behavior:. - You can simply take advantage of the new functions in the package dbms_stats.. - The following example, which is an excerpt of the output generated by the script comparing_. - The following example compares the current object statistics of the table t with a set of object statistics named set1 and stored in the backup table mystats:. - The following example compares the current object statistics of the table t with the object statistics of one day ago (for example, prior to a gathering of statistics that was executed during the night):. - The following example compares the current statistics of the table t with the pending statistics:. - Therefore, it is essential to take advantage of the Table 4-10. - Parameters of the Procedures Used for Deleting Object Statistics. - The following sections describe several services of the package dbms_stats that are used for both system statistics and object statistics.. - -1 disables the purging of the history.. - With the following query, it is possible to display when the object statistics of the table tab$ in the schema sys where modified:. - As shown in the following example, its creation is a matter of specifying the owner (with the parameter ownname) and the name (with the parameter stattab) of the backup table.. - If the parameter tblspace is not specified, by default, the table ends up in the default tablespace of the user.
Xem thử không khả dụng, vui lòng xem tại trang nguồn hoặc xem
Tóm tắt