advanced oracle sql tuning

oracle| Data | database | Performance Oracle is a high-performance database software. The user can adjust the parameters to achieve the optimization of performance. Performance optimization is mainly divided into two parts: first, the database administrator through the adjustment of system parameters to achieve the goal of optimization, the second is the development of the application of the optimization to

for media recovery.D.unused indicates that the log was never written, could have just been added, or was reset after resetlogs.6. Delete the old log groupsql> ALTER DATABASE drop logfile Group 1;sql> ALTER DATABASE drop logfile Group 2;sql> ALTER DATABASE drop logfile Group 3;7. Delete the Redolog log file under the operating systemMV/U01/ORACLE/ORADATA/ORCL/RED

nothing is perfect.The cost of the Begin Tran is that all the resources locked by the SQL statements cannot be released until the commit is committed before committing.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

The Association of SQL tuning and shared pool structures introduces three types of computer resources that affect performance: Memory, CPU, and I/O. By adjusting SGA and PGA to make full use of physical MEMORY, making full use of CPU through parallel processing, and making full use of Hard Disk processing power by adjusting I/O distribution. Server process and PGA are "two grasshoppers on one rope", so sp a

Tags: service simulation datapump tuning Management production practical fail controlOne: Download routeSecond: The book DrawingThree: the book catalogue The 1th chapter of database operation and maintenance1th. Database Installation Configuration1.1 Preparation for pre-installation 11.2 Installing database Software 51.2.1 Method 1:oui Installation 61.2.2 Method 2: Silent installation 81.2.3 Method 3: Clone installation 111.2.4 Diagnostic Case: Un

-------------------------------------------------------------------------| Id | Operation | Name | Cost (%CPU) |-------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 (0) || 1 | TABLE ACCESS by GLOBAL INDEX rowid| SALES | 4 (0) || 2 | INDEX RANGE SCAN | Index_qu_sold | 3 (0) |-------------------------------------------------------------------------9 Rows selectedSql> alter session Set "_use_nosegment_indexes" =true;Session altered.Sql> CREATE index

Do you want to learn some tips for tuning SQL PL? If this is the case, this article will describe some common examples of tuning ibm®db2®universal database™ (UDB) V8.2 for linux,unix®, and Windows®, and will pay special attention to porting from other database management systems Come over the process. Motivation SQL p

Tags: managing tuning direction Cache ACK Analysis binding shared pool SQLAnalysis of effective cursor management"Thinking Analysis"The ability to interpret cursors as shared run schedules when SQL is not shared. There are two directions to the conventional approach:1. Adjust the size of the shared pool (shared operation plan in the library buffer of the shared pool);2,

An analysis of tuning "session management Overhead"IntroductionDuring tuning, the management of sessions is a common problem because the overhead of maintaining sessions is relatively high."The process behaves as follows"Client request (SID) → Listener received → listener derived new process (systemprocess ID) → Client processComments:Spid:system process ID, which represents the process ID of the serverproc

Tracefiledatatablewhere eventclass=12--equals 12 for batchcompleted events and cpu2. What are the most resource-intensive queries?? Is the amount of CPU time, and the number of read/write Io. Recommended events include Connect, Disconnect, ExistingConnection, sql:batchcompleted, rpc:completed, and columns that contain WRITES,READS,CPU.3. Detect deadlocks?? In a database with a large number of accesses and concurrency, if the design is slightly unreasonable, it is possible to create deadlocks th

does not exist in the table. (2) ' | | ' is a character join function. As with other functions, the index is deactivated. (3) ' + ' is a mathematical function. As with other mathematical functions, the index is deactivated. (4) The same index columns cannot be compared to each other, which will enable full table scanning.(+)A. If the number of records in a table that has more than 30% data is retrieved. Using indexes will have no significant efficiency gains.B. In certain situations, using an i

1 exp Tuning 1.1 Use direct and recordlength options The direct parameter defines whether to use direct path (direct = y) or conventional path (direct = n) for export ). Conventional path export uses the SQL SELECT statement to extract data from the table. direct path export reads data directly from the disk to the PGA and writes the data to the export file as is, this avoids the data conversion process

-------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 11 | 189 (1) | 00:00:03 || 1 | RESULT CACHE | 27yjysxpdun18b2utun82bynny | | | | || 2 | SORT AGGREGATE | | 1 | 11 | | ||* 3 | INDEX RANGE scan| idx$$_003600

availability. 32-bit operating systems have 4G of memory limitations, and some UNIX systems (and non-advanced versions of Windows) have limits on the number of CPUs. While cluster technology works together by assembling multiple machines, this restriction is broken horizontally. Through the RAC , a single instance of the server, multiple machines form an instance service set, and the client connects to it. This technology, we sometimes say to the cus

Note: This article source: Small yan Kevin "Oracle Database Memory adjustment increased memory "Simulated operating system memory increased from 2G to 8G, adjust the database memory parameters, the example parameter is not as the actual production environment reference, because the need to take, adjust the parameters need to adjust according to the database, to avoid the calf pull cart, Daniel pull the car phenomenon.View RAW configuration Data1[Email

to select the crosstab (intersection table) as the underlying table, which is the table referenced by the other table.Like what: The EMP table description describes the intersection of the location table and the category table. SELECT * FROM location L, CATEGORY C, EMP E WHERE e.emp_no between, and e.cat_no = C.cat _no and e.locn = L.LOCNwill be more efficient than the following SQL: SELECT * from EMP E, location L, CATEGORY

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

In Oracle database systems, the parameters that play the role of tuning are called initialization parameters, which are recorded in the Initsid.ora file in Oracle 8i and previous versions, while Oracle 9i/10g/ These parameters are recorded in the Spfilesid.ora binary in 11gBasic parameters: A set of adjustable paramete

Tags: instance default help input extern data package MSDN One fixOriginal: SQL Server performance Tuning resource waiting network I/OI. OverviewThe main wait for network I/O is async_network_io, when SQL Server returns a data result set to the client, the result set is populated into the output cache (ouput cache), and the network layer begins to package the dat

Oracle Advanced Programming, oraclesql Advanced Programming Oracle's advanced programming mainly involves the use of the ten objects in the database. Here, I will share with you some of my notes on these skills. Hope to help you! Before reading this article, you can also refer to my previous database introduction: Basi