[ Pobierz całość w formacie PDF ]
.If you do not use them accordingly by spreading apart thevital database components, you are limiting the performance capabilities.Maximizing the use of system resources is just as important as maximizing the useof the database server capabilities.Not performing frequent COMMITs--Failing to use periodic COMMITs or ROLLBACKsduring heavy batch loads will ultimately result in database bottlenecks.Allowing batch loads to interfere with daily processing--Running batch loadsduring times when the database is expected to be available will cause problemsfor everybody.The batch process will be in a perpetual battle with end users forsystem resources.Being careless when creating SQL statements--Carelessly creating complex SQLstatements will more than likely contribute to substandard response time.TIP: You can use various methods to optimize the structure of an SQLstatement, depending upon the steps taken by the database server duringSQL statement processing.Running batch loads with table indexes--You could end up with a batch load thatruns all day and all night, as opposed to a batch load that finishes within a fewhours.Indexes slow down batch loads that are accessing a high percentage of therows in a table.Having too many concurrent users for allocated memory--As the number ofconcurrent database and system users grows, you may need to allocate morememory for the shared process.See your system administrator.Creating indexes on columns with few unique values--Indexing on a column suchas GENDER, which has only two unique values, is not very efficient.Instead, try toindex columns that will return a low percentage of rows in a query.Creating indexes on small tables--By the time the index is referenced and thedata read, a full-table scan could have been accomplished.Not managing system resources efficiently--Poor management of system resourcescan result from wasted space during database initialization, table creation,uncontrolled fragmentation, and irregular system/database maintenance.Not sizing tables and indexes properly--Poor estimates for tables and indexes thatgrow tremendously in a large database environment can lead to seriousfragmentation problems, which if not tended to, will snowball into more seriousproblems. Built-In Tuning ToolsCheck with your DBA or database vendor to determine what tools are available to youfor performance measuring and tuning.You can use performance-tuning tools toidentify deficiencies in the data access path; in addition, these tools can sometimessuggest changes to improve the performance of a particular SQL statement.Oracle has two popular tools for managing SQL statement performance.These tools areexplain plan and tkprof.The explain plan tool identifies the access path that willbe taken when the SQL statement is executed.tkprof measures the performance by timeelapsed during each phase of SQL statement processing.Oracle Corporation alsoprovides other tools that help with SQL statement and database analysis, but the twomentioned here are the most popular.If you want to simply measure the elapsed time of aquery in Oracle, you can use the SQL*Plus command SET TIMING ON.SET TIMING ON and other SET commands are covered in more depth on Day 20,"SQL*Plus."Sybase's SQL Server has diagnostic tools for SQL statements.These options are in theform of SET commands that you can add to your SQL statements.(These commands aresimilar to Oracle's SET commands).Some common commands are SET SHOWPLAN ON, SETSTATISTIC IO ON, and SET STATISTICS TIME ON.These SET commands displayoutput concerning the steps performed in a query, the number of reads and writesrequired to perform the query, and general statement-parsing information.SQL ServerSET commands are covered on Day 19, "Transact-SQL: An Introduction."SummaryTwo major elements of streamlining, or tuning, directly affect the performance of SQLstatements: application tuning and database tuning.Each has its own role, but onecannot be optimally tuned without the other.The first step toward success is for thetechnical team and system engineers to work together to balance resources and takefull advantage of the database features that aid in improving performance [ Pobierz całość w formacie PDF ]

  • zanotowane.pl
  • doc.pisz.pl
  • pdf.pisz.pl
  • hanula1950.keep.pl

    		•