pl sql performance tuning techniques

whenever possible. This section will be a topic at the end of this series. A detailed description of the process is presented here (simplification – Exploration – implementation).Summarize:This paper introduces the process of compiling query, and the concrete content of generating cache, reusing cache, recompiling, etc. There are a number of things to note about optimizing our T-SQL statements, and the more hits we have on the execution plan cache, t

rows reserved data index_size unused TB_WCB 9439661 317208 kb 167168 KB 149872 KB 168 KB */ we found that the size before index compression was 329M. And after compression is 149M, the compression ratio is 45%. The effect is also very obvious. Summarize: Compression through tables and indexes. We can reduce the disk space occupied by the table, this is only part of it, and more importantly, reading the same amount of data, just need to read less data pages,

One of the practical skills that can greatly improve the performance of SQL TUNING optimization. When we perform SQL optimization, we often encounter the need to sort a large number of datasets and then retrieve the first part of the results from the sorted set. In this case, when we write

Label:Original: T-SQL performance Tuning-information collectionIO information (starting from server startup)--database IO Analysiswith iofordatabase as (SELECT db_name (vfs.database_id) as DatabaseName, case if Smf.type = 1 Then ' log_file ' ELSE ' data_file ' END As Databasefile_type, sum (vfs.num_of_bytes_written) as Io_write, sum (VFS.N

/1000 N ' Total time spent in MS ',total_worker_time/1000 N ' Total CPU time MS ',Total_physical_reads N ' Total number of physical reads ',Total_logical_reads/execution_count N ' Each logical read ',Total_logical_reads N ' Logical reads total number of times ',Total number of total_logical_writes N ' logical writes ',Execution_count N ' execution times ',Creation_time N ' statement compilation Time ',Last_execution_time N ' Last Execution time ',SUBSTRING (St.text, (QS.STATEMENT_START_OFFSET/2)

compression is 167M, the size of the table after compression is only 40% of the original table, the effect is obvious, and because most of the table's fields are only IDs, the relative repetition value is not too much.However, we see that the size of the index varies substantially, so we continue to compress the index:5. Compression indexAlter index idx_tb_wcb_id on Tb_wcbrebuildwith (Data_compression=row)6, the comparison after the index compressionsp_spaceused ' ms_visit_qst_opt '/*name

summary data is based is updated frequently, it is easier to use a view than to write SQL every time 2. Create a ViewThis is how the CREATE view is defined in the MySQL documentation: CREATE [OR REPLACE] [algorithm = {UNDEFINED | MERGE | TempTable}] [definer = {User | Current_User}] [SQL SECURITY {definer | INVOKER}]VIEW view_name [(column_list)]As Select_statement[With [cascaded | LOCAL] CHECK

Microsoft, one is not perfect function also dare to take Out (2000) The second functional architecture of the outstanding design makes the function has a strong continuity and Scalability (2005 2000 of the function to retain and improve). The optimization tool is simple to use 1: Use SQL Server Porfiler (Event Viewer) to record all the steps of the operation database in the business system and save it as a working file. 2: Open sql2005 's database

1. Import the trace file into the tableSELECT IDENTITY (BIGINT11 as RowNumber,* into TableNamefrom fn_trace_gettable ('trace.trc'default)2. Querying for statements with large CPU overheadSELECT Top -Total_worker_time/Execution_count asAvg_cpu_cost, Plan_handle, Execution_count, (SELECT SUBSTRING(text, Statement_start_offset/2 + 1, ( Case whenStatement_end_offset= -1 Then LEN(CONVERT(nvarchar(Max),text))* 2 ELSEStatement_end_offsetEND -Statement_start_offset)/2) fro

TRIGGERCREATETYPECREATEOPERATORCREATEIndextypeTenrows selected. CHF@XFF>SELECTtable_name fromuser_tab_privs; no rows selectedCreate a CHF user, authorize Create Session,resource, no V$session,v$sesstat and v$statname view access, log in with Plsql Dev and query the User_tables table (no prompt at logon , only the user will be prompted when the query is executed or the related class is performed)Second, solve the problemDepending on the warning, there are two ways to resolve this warning1. Turn

Performance | Statement One, the problem of the proposed In the early stage of the application system development, because the development database data is few, to query the SQL statement, the complex view compilation and so on cannot realize the SQL statement various writing performance good or bad, but if the applica

ObjectiveAn index is one of the most important objects in a relational database and can significantly reduce the consumption of disk I/O and logical reads to improve the lookup performance of a SELECT statement. But it is a double-edged sword, and improper use can affect performance: He needs extra controls to hold the index information, and when the data is updated, it takes some extra overhead to keep the