Oracle Frequent problem sets (Iv.)

Oracle 21. Replace distinct
with exists




when submitting a search that contains a list of multiple tables (such as the Department table and the employee table), avoid using distinct in the SELECT clause.





can generally consider using exist for











For example:





Low efficiency:





SELECT DISTINCT dept_no,dept_name





from DEPT d,emp E





WHERE d.dept_no = E.dept_no





High Efficiency:





SELECT Dept_no,dept_name





from DEPT D





WHERE EXISTS (SELECT ' X '





from EMP E





WHERE e.dept_no = d.dept_no);











EXISTS makes the inquiry more rapid, because the RDBMS core module will return to the results immediately after the conditions of the subquery are satisfied.











22. Recognizing the ' inefficient ' SQL sentence











use the following SQL tools to find inefficient sql:











SELECT Executions, disk_reads, Buffer_gets,





ROUND ((buffer_gets-disk_reads)/buffer_gets,2) Hit_radio,





ROUND (disk_reads/executions,2) Reads_per_run,





Sql_text





from V$sqlarea





WHERE executions>0





and buffer_gets > 0





and (buffer_gets-disk_reads)/buffer_gets < 0.8





ORDER by 4 DESC;











(Translator by: Although the current graphical tool for SQL is not poor, writing your own SQL tools to solve the problem is the best way to do it)











23. Use the Tkprof tool to search SQL performance status










The
SQL Trace Tool collects the performance status of the SQL being executed and records it in a tracking file.





This tracking file provides a lot of useful information, such as parsing the number of times, CPU time and so on. These resources will be used to prioritize your system.











Setup SQL Trace at the conversation level: effective











ALTER session SET sql_trace TRUE











set SQL TRACE to follow the entire repository, you must set the Sql_trace parameters to true in Init.ora,





user_dump_dest parameters that generate a tracking file











(Translator Press: In this episode, the author does not mention the use of tkprof, nor is the use of SQL Trace accurate, set SQL





Trace First has to set the timed_statistics in the Init.ora to get those important time configurations.




The trace file generated by
is unreadable, so the tkprof tool is used to convert it, and Tkprof has a number of parameters to perform.





you can take a look at Oracle's manual to understand the configuration of the body. )











24. Analyzing SQL language sentences with explain plan





EXPLAIN Plan is a good tool for analyzing SQL sentences, and it can even parse the language without SQL.





through analysis, we can know how Oracle connects tables, how to scan the table (Index scan or full table scan), and what index names are used.





you need to read the results of the analysis in order from the inside out, from top to bottom. The results of EXPLAIN plan analysis are arranged in a scaled format,




The innermost operation of the
will be first read, and if the two operations are in the same layer, the smallest operator will be executed first.





NESTED Loop is a small number of operations that are not handled in accordance with the above rules, and the correct path is to check the NESTED




The
loop provides data operations in which the smallest operator is handled first.











Translator by:











through the practices, it is more convenient to use the Set TRACE function in Sqlplus.





For example:











sql> List





1 SELECT *





2 from Dept, EMP





3* WHERE emp.deptno = Dept.deptno





sql> Set Autotrace traceonly/*traceonly can be performed without a display/





sql>/





rows selected.





Execution Plan





----------------------------------------------------------





0 SELECT STATEMENT optimizer=choose





1 0 NESTED LOOPS





2 1 TABLE ACCESS (full) ' EMP '





3 1 TABLE ACCESS (by INDEX ROWID) of ' DEPT '





4 3 INDEX (unique SCAN) of ' pk_dept ' (unique)











Statistics





----------------------------------------------------------





0 Recursive calls





2 db block gets





consistent gets





0 Physical reads





0 Redo Size





2598 Bytes sent via sql*net to client





503 Bytes received via sql*net from client





2 sql*net roundtrips to/from client





0 Sorts (memory)





0 Sorts (disk)





rows processed











through the above analysis, you can come to the actual steps are:





1. TABLE ACCESS (Full) ' EMP '





2. INDEX (unique SCAN) of ' pk_dept ' (unique)





3. TABLE ACCESS (by INDEX ROWID) of ' DEPT '





4. NESTED LOOPS (joining 1 and 3)





Note: At present, many third-party tools such as Toad and Oracle's own tools such as OMS SQL Analyze provide extremely convenient explain





the Plan tool. Maybe Friends of the graphical interface can choose to use them.





25. Using indexes to improve efficiency





An index is a conceptual part of a table that is used to improve the efficiency of the check data. In fact, Oracle uses a complex, b-tree, self balanced structure.





Usually, the index is faster than the entire table scan to find information. Oracle will use the index when Oracle finds the best way to perform the search and update syntax.





can also be more efficient when using indexes in multiple tables. Another good thing about using indexes is that it provides a unique primary of the primary key.





except for those long or long raw data types, you can index almost all of the columns. In general, using indexes in large tables is particularly effective. Of course, you will also find that





when scanning small tables, using indexes can improve efficiency.





Although indexing can be used to improve efficiency, we must also pay attention to its value. Indexes need space to





storage also requires regular maintenance, and every time a record is added to the table or indexed columns are modified, the index itself is modified. This means that each recorded insert,





DELETE, update will pay 4, 5 times for this magnetic disc I/O.





because indexes require extra storage space and processing, the unnecessary indexing slows down the retrieval response time of the search.











Translator by:




It is necessary to
regular refactoring indexes.





ALTER INDEX REBUILD











26. Operation of the index











Oracle has two interview modes for indexes.











index unique Scan (index unique SCAN)











Most of the cases, the designer has access to index through the WHERE clause.