A DBA test title note: The following topics can be selected according to their own situation, do not have to answer all your questions, you can also directly find the interview staff asking for the interview staff.
One: SQL TUNING class 1: list several table connection Hash Join / Merge Join / Nest loop (Cluster Join) / INDEX JOIN 2: Do not make use of third-party tools, how to view SQL execution plan SET autot on explain plan set statement_id = & item_id for & sql; select * from table; http://download-west.oracle.com/docs/cd/b14117_01/server.101/b10752/ex_plan.htm
3: How to use CBO, CBO and Rule, when Optimizer_Mode = Choose, if the table has statistics (partition estimates), the optimizer will select CBO, otherwise the RBO is selected.
RBO follows simple grading methodology, uses 15 level points, when receiving queries, the optimizer will evaluate the number of points used, then select the best level (minimum) execution path to run the query. CBO attempts to find the lowest cost access data, for maximum throughput or the fastest initial response time, calculate the cost of using different execution plans, and select one of the lowest cost, the statistics of the data content of the table are used for Determine the implementation plan.
4: How to locate the important (more resources) SQL SELECT SQL_TEXT from V $ SQL Where Disk_reads> 1000 or (Executions> 0 and Buffer_Gets / Executions> 30000);
5: How to track a session of SQL exec dbms_system.set_sql_trace_in_session (sid, serial #, & sql_trace); select sid, serial # from v $ session where sid = (select sid from v $ mystat where rownum = 1); exec dbms_system. Set_ev (& SID, & Serial #, & Event_10046, & Level_12, '');
6: SQL adjusts the most attention to what is what to see this SQL response Time (DB Block gets / consistent gets / physical reads / sorts (disk)
7: Talk about your understanding of the index (index structure, impact on DML, why improve query performance) B-Tree index / bitmap index / function index / patitional index (local / global) index usually improves SELECT / UPDATE / DELETE The performance will reduce the speed of INSERT, 8: Can I use an index query that can improve the performance of the query? Why indexes exist in order to improve query performance, if the index in the query does not improve performance, you can only say that it is used in the wrong index, or the case is different.
9: What is the binding variable? What is the advantages and disadvantages of binding variables? Bind variables are relatively text variables. The so-called text variable refers to the direct writing query conditions in SQL. Such SQL needs to be repeatedly parsed under different conditions, and the bind variable refers to the use of variables instead of direct writing conditions, querying Bind Value Pass at runtime, then bind execution. The advantage is to reduce hard narratisms, reduce the CPU's contention, saving the short-POOL disadvantage that Histogram, SQL optimization is more difficult
10: How stable (fixed) implementation plan query_rewrite_enabled = true star_transformation_enabled = true optimizer_features_enable = 9.2.0 create and use a stored outline http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/outlines .htm # 26854 This post: http://www.cnoug.org/viewthread.php?tid=27598 11: and sorting the internal existence 8i and 9i, how to adjust, temporary table space What is the role of 8i? Sort_Area_Size / sort_area_retained_size determines the memory required by the sort If the order operation cannot be done in sort_area_size, it will be used to use the TEMP table space.
When Workarea_SIZE_POLICY = Auto, sort in the PGA, usually the 1/20 of the PGA_AGGREGATE_TARGET can be used to perform DISK SORT; if Workarea_SIZE_POLICY = Manual, the sort required memory is determined by sort_area_size
When executing ORDER BY / GROUP BY / DISTINCT / UNION / CREATE INDEX / INDEX REBUILD / Minus, if you can't complete in PGA or Sort_Area_size, the sort will be done in the temporary table space (Disk sort), the main role of temporary table space is Complete DISK SORT in the system.
12: There are tables t (a, b, c, d), and after the field C is sorted, take the 21st 30 records, give SQL Create Table T (a Number (8), B Number (8), C Number (8), D Number (8)); / Begin for i in 1 .. 300 Loop Insert Into T Values (Mod (i, 2), I / 2, DBMS_RAndom.Value (1,300), I / 4) End loop; end; / selection *, Rownum as rn from (select * from t order by c dec) c) Where rn between 21 and 30; / select * from test Order By C Desc) x Where rownum <30 minus select * from (select * from test order by c desc) y where rownum <20 ORDER BY 3 DESC compared to Minus performance is poor
Two: Database Basic Concept Class
1: PCTUSED and PCTFREE indicates what meanings in which the PCTUSED and PCTFREE control data blocks appear in FreeList, and the PCTFree control data block is kept in the Update space. When the free space in the data block is smaller than the space set by PCTFree, The data block is removed from the freeelist, which is added to the Freelist Link mode when the block is greater than the space of the PCT_USED.
2: Simply describe the relationship between Table / Segment / Extent / Block TABLE creation, a Data Segment created by default, each Data Segment contains the number of extents specified by Min Extens, and each Extent is assigned by the storage parameters of the tablespace. Number of blocks
3: Describe the relationship between Tablespace and DataFile A tableSpace can have one or more DataFiles, each DataFile can only distribute data in TableSpace, TableSpace is logical in each DataFile in TableSpace, each data file. The concept, DataFile is physically stored in the database. 4: Local management table space and dictionary management table space, ASSM has something characteristic of local management table space (LMALLY Managed TableSpace referred to as LMT) 8i's management mode of a new table space, through bitmap management table space Space usage. Dictionary-management table space (DMT) 8i previously included a tablespace management mode that can also be used later, and is used through the space of the data dictionary management table space. Space Management (ASSM), it has appeared in Oracle920 in Oracle920, the link list FreeIst is replaced by bitmaps, which is a binary array that quickly and effectively manages storage extensions and remaining blocks (free block). Therefore, it is possible to improve the segmented storage nature, and the segment created on the ASSM table space has another called Bitmap Managed Segments (BMB).
5: The role of the rollback segment is what transaction rollback: When the data is modified in the table, the value before the data is modified (ie, the front image) will be placed in the rollback segment, when the user rolls back the transaction (ROLLBACK) Oracle will revert the modified data to the original value by using the data image in the rollove segment. Transaction Recovery: When the transaction is being processed, the routine fails, the information of the rollback segment is saved in the undo tablespace, and Oracle will use the rollback to restore uncommitted data when the database is turned on next time.
Read consistency: When a session is modifying data, other sessions will not see the modifications of the session. When a statement is being executed, the statement will not see uncommitted modifications from the statement (statement class read consistency) When Oracle executes the SELECT statement, Oracle changes in accordance with the current system change number (System Change Number- SCN) To ensure that any changes before the current SCN have not been processed by the statement. I can imagine: When a long query is being executed, if other sessions change the query to query a data block to query, Oracle will construct a read consistency view using the data image of the rollback segment. Http://www.itpub.net/showthread.php?s=&threadid=10190&highlight=
