tuning oracle sql queries

Transferred from: http://www.cnblogs.com/MR_ke/archive/2010/08/25/1807856.htmlSQL 2005 Performance TuningSQL Server runs for a period of time, and as data accumulates, SQL runs less efficiently, and in order to use business system normal actions, IT departments often need to spend a high price on SQL tuning experts to solve. In fact,

, if a field has an index and a field is not indexed, it will cause the engine to discard using the index for a full table scan, such as:Select ID from t where num= or Name = ' admin 'You can query this:Select ID from t where Name = ' admin '5. In and not are also used with caution, otherwise it will cause a full table scan, such as:Select ID from t where num in (1,2,3)For consecutive values, you can use between instead of in:1 3A lot of times it's a good choice to replace in with exists:Select

first wait event is read by other session. Top 5 Timed Foreground Events Read by other session is defined as follows: Read by other session Definition: When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. if two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait. in previous versions this wait was clas

the number of CPUs on the server, it will automatically allocate the appropriate number of sub-processes to improve the response time of parallel queries. Of course, there will be other external factors, such as the table division and the layout of the disk input/output subsystem, however, setting the parallel_max_servers parameter based on cpu_count gives Oracle a reasonable basis to select the degree of

Label: Oracle Query Table space usage Select A.tablespace_name "Table space name",Total "Table space size",Free "Table space remaining size",(Total-free) "Tablespace usage Size",Total/(1024 * 1024 * 1024) "Table space Size (G)",Free/(1024 * 1024 * 1024) "Table space remaining size (G)",(total-free)/(1024 * 1024 * 1024) "Table space use Size (G)",Round ((total-free)/Total, 4) * 100 "Utilization%"From (SELECT tablespace_name, SUM (bytes) freeFrom Dba_

used to find rows in the table Keys: Index used by actual execution plan Key_len: Index length to use Ref: Shows which column or constant is used together with key to select rows from the table. Rows: Displays the number of rows that MySQL needs to check for execution queries. Extra: This column contains important information to resolve the query , very important ! Show index from TableNameThis command helps us to check the

Recently, the following project encountered an urgent problem, my old horse and young people to fight together. The problem is that many queries are incredibly slow when the pressure data is poured into the database. Said there must be some basic guidelines for performance issues. The order of performance problems tunning 1 Architecture design (software architecture and database design, poor design is almost fatal) 2 Code defects (90% of performanc

things you need to be aware of during programming: 1, Return only the data you need Return data to the client needs at least database extraction data, network transmission data, client receive data and client processing data, and so on, if the return of unwanted data, will increase the server, network and client ineffective labor, the harm is obvious, to avoid such incidents need attention: A, landscape, do not write select * statements, but select the fields you need. B In Portrait view, write

%" can use indexes. can establish Fulltext or Sphinx (Sphinx Division)6: Do not perform calculations on columnsSELECT * from the users where year (adddate) 7: Do not use not in and Not-in and OtherTry to use connection queries instead of subqueries (nested queries)Index issue for ORDER byShow Processlist View ThreadsShow Full ProcesslistUptimeIn a production environment, if it is a large table, creating an

number of CPUs on the server, it will automatically allocate the appropriate number of sub-processes to improve the response time of parallel queries. Of course, there will be other external factors, such as the table division and the layout of the disk input/output subsystem, however, setting the parallel_max_servers parameter based on cpu_count gives Oracle a reasonable basis to select the degree of para

Some time ago, I had a performance problem with my website, and sometimes the page was very slow to open. It would take more than 10 seconds. I tried a lot of methods, including cache, disable viewstate, and Disable debug mode. The improvements were not obvious. Later, I ran into SQL Server Profiler to check the cause: a slow query dragged down the entire page. Later, the query was optimized, but not many improvements were made. Finally, an index i

, and this is where Oracle is widely praised. SQL Server reads and writes are blocked from each other, in order to improve concurrency performance, for some queries, you can add nolock, so that the reading can be allowed to write, but the disadvantage is that it is possible to read the dirty data uncommitted. There are 3 principles for using NOLOCK. (1) The resul

copy, which is placed in the undo table space. In this way, Oracle's read and write can be mutually exclusive, and this is where Oracle is widely praised. SQL Server reads and writes are blocked from each other, in order to improve concurrency performance, for some queries, you can add nolock, so that the reading can be allowed to write, but the disadvantage is

. It can be seen that if the BEGIN TRAN too many SQL statements, the performance of the database is poor. Before this large transaction commits, it is bound to block other statements, resulting in many blocks. The principle of the BEGIN TRAN is that the SQL statement that begin Tran is getting better with the consistency of the data! In some cases, triggers can be used to synchronize data, not necessarily w

the Where condition.Now let's delete the histogram for all the columns in the table.We execute the following SQL so that the owner column is placed in the Where condition.Next we refresh the database monitoring information.We collect statistics on the table using the method_opt = ' For all columns size auto ' method.Then we look at the histogram information.From the above query we can see that Oracle autom

indexed fieldsH, do not use functions on indexed fieldsI, do not make multi-field connections to indexed fields3. Return only the data you needThe return data to the client needs at least the database extracts data, the network transmits the data, the client receives the data as well as the client processing data and so on, if returns the unnecessary data, will increase the server, the network and the client invalid labor, its harm is obvious, avoids this kind of event to need to notice:A, in t

Label: Content from: Oracle®database SQL Language Reference 11g Release 2 (11.2) e41084-03. Empolyees table from the HR scheme, warehouses from the OE scheme. If your table contains hierarchical data, you can use hierarchical query clauses to select rows of data in a hierarchical order, forming a hierarchical tree in the following form: Here is the train diagram for the hierarchy query sent

Http://www.diybl.com/course/7_databases/oracle/oraclejs/20100713/436400.html There are a number of reasons why queries are slow, often as follows: 1, no index or no index (this is the most common problem of query slow, is the defect of program design) 2, I/O throughput is small, creating a bottleneck effect. 3. No computed columns were created to cause the query to be not optimized. 4, not enough memory 5,

The PL/SQL cursor allows the program to select multiple rows of data from the database, and then separately process each row of data, it provides Oracle with a method to indicate and control each stage of SQL processing. I think you have some knowledge about PL/SQL. Through this article, you will learn: Creation of

Buy Oracle Database Course package and enjoy 85 discount!!Package Address: http://edu.51cto.com/pack/view/id-807.htmlPerformance optimization for Oracle 11g R2 databaseDatabase performance problem is the most discussed topic in database field, because it involves database principle, IO storage, server performance, foreground application and so on, so the performance problem is very test the optimization of