Section IV, Performance Adjustment [Q] If set automatic tracking [A] Login with system login to execute $ oracle_home / rdbms / admin / uTLXPLAN.SQL Create a schedule Perform $ ORACLE_HOME / RDBMS / Admin / PlusTrce.sql Create a Plustrace Role If you want to plan The table allows each user to use, then SQL> CREATE PUBLIC SYNONYM Plan_Table for Plan_Table; SQL> GRANT All on Plan_Table to public; ; ON | ON Explain | on statistics | TRACEONLY | TRACEONLY | Traceonly Explain
[Q] If you track your own session or someone else's session [A] Track your own session very simple ALTER session set SQL_TRACE TRUE | FALSEOREXEC DBMS_SESSION.SET_SQL_TRACE (TRUE); if you track the session of others, you need to call a package EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID, SERIAL #, TRUE | FALSE) Tracking information You can find or get file names (for WIN environments, if you are UNIX, if you are UNIX, if you need to make certain modifications) Select P1.Value || '/' | | p2.value || '_ora_' || p.spid || '.ora' FileNameFromv $ Process P, V $ Session S, V $ Parameter P1, V $ Parameter P2where P1.Name = 'user_dump_dest'and p2.name = 'db_name'and p.addr = s.paddrand s.Audsid = Userenv (' sessionID ') Finally, you can parse the trace file through TKPROF, such as TKPROF original file target file sys = n
[Q] How to set up the entire database system tracking [A] In fact, the ALTER SYSTEM SET SQL_TRACE = TRUE on the document is unsuccessful but can complete this work by setting events, and the active alter system set events'10046 Trace Name Context Forever, Level 1 '; If the trace is closed, you can use the following statement alter system set off'; where Level 1 and the above 8 are trace level Level 1: Track SQL statements, equal to SQL_TRACE = TrueElevel 4: including variables Details Level 8: Includes Waiting Level 12: Includes Binding Variables and Waiting Events
[Q] How to quickly get DB process information and the statement in the OS process [A] Some time, we can operate on the OS, I get the OS process we get after Top, how quickly I get DB information according to the OS information? We can write the following scrub: $ more whoit.sh #! / Bin / shsqlplus / nolog << EOF Connect / as sysdba col Machine Format A30 Col g os 4 s l 200 Select Sid, Serial #, Username, Osuser, Machine, Program , Process, To_Char (Logon_Time, 'YYYY / MM / DD HH24: MI: SS') from V / $ Session Where Paddr in ($ 1)); SELECT SQL_TEXT from V / $ SQLText_with_newline WHERE Hash_Value in (SELECT SQL_HASH_VALUE FROM V / $ SESSION WHERE PADDR in (SELECT AddR from V / $ Process Where SPID = $ 1)) Order by Piece;
EXIT; EOF then, we can do it as follows in the OS environment. / Whoit.sh SPID
[Q] how to analyze a table or index [A] command line may be employed analyze commands such as Analyze table tablename compute statistics; Analyze index | cluster indexname estimate statistics; ANALYZE TABLE tablename COMPUTE STATISTICSFOR TABLEFOR ALL [LOCAL] INDEXESFOR ALL [INDEXED] COLUMNS ; ANALYZE TABLE tablename DELETE STATISTICSANALYZE TABLE tablename VALIDATE REF UPDATEANALYZE TABLE tablename VALIDATE STRUCTURE [CASCADE] | [INTO TableName] ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName] and so on. If you want to analyze the entire user or database, you can also use a toolkit to analyze DBMS_UTILITY (8i previous toolkit) dbms_stats (toolkit provided after 8i) such as dbms_stats.gather_schema_stats (user, estimate_percent => 100, cascade => true ); dbms_stats.gather_table_stats (user, tablename, deterree => 4, cascade => true); this is some summary of commands and kits 1. For partition tables, it is recommended to use dbms_stats instead of using the Analyze statement. a) can perform parallelism, multiple users, multiple Table B) can obtain data of the entire partition table and data of a single partition. c) COMPUTE STATISTISTISTISTISTICS: Single Partition, Subsence, Full Table, All Partitions D) can be poured out of statistics E) Can automatically collect statistics 2, DBMS_STATS Disadvantages a) cannot be validate structure b) Can't collect CHAINED ROWS, you cannot collect information about Cluster Table, which still needs to use the Analyze statement. c) DBMS_STATS The default is not analog ZE, because the default cascade is false, you need to manually specify TRUE 3, for the external table inside Oracle 9, Analyze cannot be used, can only use dbms_stats to collect information.
[Q] How to quickly reorganize index [A] Via the Rebuild statement, you can quickly reorganize or move the index to other tablespace Rebuild's feature to rebuild the number of indexes, and can change the index without deleting the original index. Parameter syntax is ALTER INDEX INDEX_NAME REBUILD TABLESPACE TS_NAMESTORAGE (...); if you want to quickly rebuild the index of the entire user, you can use the following scripts, of course, you need to modify SQL> Set Heading Offsql> Set Feedback Offsql> based on your own situation. spool d:! /index.sqlSQL> SELECT 'alter index' || index_name || 'rebuild' || 'tablespace INDEXES storage (initial next 256K pctincrease 0 256K);' FROM all_indexesWHERE (tablespace_name = 'INDEXES'OR next_extent =! (256 * 1024)) And Owner = UserSql> Spool Off This statement is ALTER INDEX INDEX_NAME COALESCE. This statement is merely the same level of Leaf Block in the same level in the index. There is a lot of space in some indexes. In case, there are some effects. [Q] How to use Hint Tips [A] Subway / * hint * / like SELECT / * INDEX (Table_Name Index_Name) * / COL1 ... Note / * and Can't have space between * and If you specify an index with Hint
Select / * index (cbotab) * / col1 from cbotab; SELECT / * INDEX (CBOTAB CBOTAB1) * / Col1 from cbotab; SELECT / * INDEX (a cbotab1) * / col1 from cbotab A; where Table_Name is necessary Written, and if you use the alias of the table in the query, you must use the alias of the table instead of the table name; index_name can do not need to write, Oracle will select an index according to the statistical value; if the index name or table name is wrong The hint will be ignored;
[Q] How to quickly copy the table or insert data [A] Quick copy table You can specify the NOLogging option such as: create Table T1 NOLGGING AS SELECT * FROM T2; fast insert data can specify the APPEND prompt, but you need to pay attention to NOARCHIVELOG mode, default It is NOLOGGING mode with append. Under ArchiveLog, you need to put the table settings NOLOGGING mode. Such as INSERT / * APPEND * / INTO T1 SELECT * FROM T2 Note: If the force logging is set in the 9i environment, the above operation is invalid and will not speed up, of course, can be set to no force logging by the following statement. . ALTER DATABASE NO Force Logging; Whether to open Force Logging, you can use the following statement to view SQL> SELECT force_logging from v $ database;
[Q] How to avoid using a specific index [A] In many cases, Oracle will use indexes to use indexes, we can use a little skill to avoid using the index that should not be used, such as: Table Test, with fields A, B, C, D, establish a combined index inx_a (A, B, C) on A, B, C, and an index inx_b (b) is created separately on B. Under normal circumstances, where a =? And b =? And c =? Use index inx_a, where b =? Use index inx_b but where a =? And b =? And c =? Group BY B will Which index is used? Oracle often uses index inx_b when the analysis data is incorrect (long time is not analyzed) or without analysis data. Through the analysis of the plan, this index will be greatly consumed. Of course, we can avoid using INX_B by tips, and use INX_A. WHERE A =? and b =? and c =? group by b || '' - If B is character where a =? and b =? and c =? group B 0 - If B is the number Simple change, it is often possible to have a lot of query time, we can also use no_index prompts. I believe that many people have not used them, and it is also a good way: SELECT / * NO_INDEX (T, Inx_B) * / * from test twhere A =? and b =? and c =? Group by b [q] Oracle When will Oracle use a jump index scan [A] This is a new feature jump index scan (INDEX SKIP Scan). For example, the table has an index INDEX (A, B, C), when the query condition is WHERE B =? may be used in index index (a, b, c), such as, the implementation plan appears as follows: Index (Skip Scan) of 'Test_IDX '(Non-Unique) Oracle Optimizer (herein, CBO) can apply at least a few conditions for querying INDEX SKIP SCANS: 1 Optimizer is considered appropriate. 2 The number of the unique values of the leading list in the index can meet certain conditions (such as a lot of repetitions). 3 Optimizer To know the value distribution of the leading list (obtained by analysis / statistics). 4 Suitable SQL statement, etc..
[Q] How to create a virtual index [A] You can use the Nosegment option, such as CREATE INDEX Virtual_index_name on table_name (col_name) nosegment; if you need to test the virtual index, you can use an implied parameter to handle the Alter Session Set "_USE_NOSEGMENT_INDEXES" = True; You can use Explain Plan for select ... to see the virtual index effect Use @ $ oracle_home / rdbms / admin / utlxpls to view the execution plan, according to need, we can delete the virtual index, such as the normal index, Drop Index Virtual_Index_name; Note: Virtual indexes are not physical existence, so virtual indexes are not equivalent to physical indexing, do not test virtual indexes with automatic tracking, because it is the actual implementation, it is not used to use virtual indexes.
[Q] How to monitor useless index [A] Oracle 9i or more, can monitor the use of indexes, if there is no index in a period of time, generally useless index syntax is: start monitoring: alter index index_name monitoring usage; check usage status: select * from v $ object_usage; stop monitoring: alter index index_name nomonitoring usage; of course, if the index want to monitor the entire user, a script may be used as follows: set heading offset echo offset feedback offset pages 10000spool start_index_monitor.sqlSELECT 'alter index '|| Owner ||'. '|| index_name ||' Monitoring Usage; 'from dba_indexeswhere Owner = User; Spool Off Set Heading OnSet Echo Onset Feedback ON ---------------------------------------------------------------------------------- ------------------------------- SET Heading Offset Echo Offset Feedback Offset Pages 1000spool Stop_index_monitor.sqlselect 'ALTER INDEX' || Owner || '.' || index_name || 'from dba_indexeswhere er = user; spool offet heading onset echo onset feedback on [q] How can I fix my execution plan [A] You can use Outline to fix SQL statement execution plan can create a OUTLINECreate oe replace outline OutLn_Name onSelect Col1 with the following statement, Col2 from Tablewhere ...... to delete Outline, may adopt Drop Outline OutLn_Name; to have created the OutLine, OUTLN stored in the user's OL $ HINTS The table below For some statements, you can use Update Outln.ol $ HINTS to update Outline such as Update Outln.ol $ HINTS (ol_name, 'test1', 'test2', 'test2', 'test1) where ol_name in (' Test1 ',' Test2 '); this, you will use Test1 Outline and Test2 Outline Changed if you want to use the existing OUTLINE, you need to set the following parameters alter system / session set query_rewrite_enabled = true_rewrite_store / session set use_stored_outlines = TRUE
[Q] V $ sysstat Represents what [A] statistics category 1 representative example activity 2 Representative Redo Buffer Activity 4 Representative lock 8 generation data buffer activity 16 represents OS activity 32 represents parallel activity 64 representative table Access 128 Representative debugging information
[Q] How to kill a specific database session [A] ALTER SYSTEM KILL Session 'SID, Serial #'; or Alter System Disconnect Session 'SID, Serial #' Immediate; on WIN, ORAKILL provided by Oracle can also kill A thread (actually a Oracle process) on Linux / UNIX, you can directly use KILL to kill the database process [Q] How to quickly find the lock and lock waiting [A] Database lock is a comparative resource, especially When there is a lock waiting, we must find a waiting lock, there is a possible voice, kill the process. This statement will find the lock generated by all DML statements in the database, and you can find that any DML statement actually produces two locks, one is a table lock, one is a line lock. You can kill the session through Alter System Kill Session 'SID, Serial #' to kill seres SELECT / * RULE * / S.USERNAME, DECODE (L.TYPE, 'TM', 'Table Lock', 'TX', 'Row Lock " , NULL) LOCK_LEVEL, O.OWNER, O.Object_name, O.Object_Type, S.SID, S.SERIAL #, S.TERMINAL, S.MACHINE, S.PROGRAM, S.OSUSERFROM V. SESSION S, V $ LOCK L , DBA_Objects Owhere L.SID = S.SIDAND L.ID1 = OBJECT_ID ( ) And S.Username Is Not Null If a lock is waiting, we may even want to lock the table and wait for the following The statement can check who is locked, and who is waiting. SELECT / * Rule * / LPAD ('', Decode (L.XIDUSN, 0, 3, 0)) || L. Oracle_Username User_Name, O.Owner, O.Object_name, Object_Type, S.SID, S. Serial # from v $ locked_Object L, DBA_Objects O, V $ session swhere l.object_id = o.Object_idand L.Session_ID = s.sidorder by O.Object_id, Xidusn DESC or above query results are a tree structure, if there is a child node, Then it is waiting to occur. If you want to know which roll band is used, you can also associate with V $ rollname, where Xidusn is the USN of the rollback segment.
[Q] How to effectively delete a big table (many tables) [A] a table with a lot of (100K) extents, if just simply use Drop Table, it will consume a lot of CPU (Oracle wants FET $ , The UET $ data dictionary is operated, which may be used for a few days, better ways to delete extents multiple times to mitigate this consumption: 1. Truncate Table Big-Table Reuse Storage; 2. Alter Table Big -Table Deallocate Unused Keep 2000M (original size N-1 / N); 3. ALTER TABLE BIG-TABLE DEALLOCATE UNUSED KEEP 1500M; .... 4. Drop Table Big-Table;
[Q] How to shrink the size of the temporary data file [A] 9i The following version uses ALTER DATABASE DATAFILE 'File Name' Resize 100m Similar statement 9i The above version uses ALTER DATABASE TEMPFILE 'File Name' Resize 100M Note, temporary data file is in use Generally, it is generally not shrinking unless there is a shutdown database or disconnect all sessions to stop the use of temporary data files. [Q] How to clean up the temporary section [A] Can be used as follows 1, use the following statement to see Who I do in using the Times Select Username, SID, Serial #, SQL_Address, Machine, Program, TableSpace, SegType, Contents from V $ Session, SEGTYPE SE, V $ sort_usage suwhere se.saddr = Su.Session_addr 2, those processes in use, SQL> ALTER SYSTEM KILL Session 'SID, Serial #'; 3, retract the TEMP table space SQL> ALTER TABLESPACE TEMP COALESCE Can also use diagnostic events 1, determine TEMP table space TS # SQL> SELECT TS #, Name from V $ TABLESPACE; TS # name ---------------------------------------------------------------------------------------------------------------- --- 0 Sysyem 1 RBS 2 Users 3 * Temp ... 2. Perform cleaning operation ALTER session set events 'immediate trace name drop_segments level ts # 1' Description: TEMP table space TS # is 3 *, SO TS # 1 = 4 If you want to clear all table spaces, TS # = 2147483647
[Q] How to Dump Database Internal Structure, Structure of Control Files shown above [A] Common, analyze data file block, dump data file N block Malter System Dump DataFile N Block M2, Analysis Log File ALter System Dump logfile logfilename; 3, the content of the Analysis Control file ALTER session set events 'immediate trace name controlf level 10'4, analyzing all data file headers ALTER session set events' immediate trace name file_hdrs level 10'5, analysis log file header ALTER Session set events' immediate trace name redohdr level 10'6, analyzing system status, best every 10 minutes, do three compared ALTER session set events' Immediate Trace Name SystemState Level 10'7, Analysis Process Status ALTER Session Set Events' Immediate TRACE NAME ProcessState Level 10'8 analyzes the details of Library Cache Alter Session Set Events 'Immediate Trace Name Library_Cache Level 10'
[Q] How to get all event code [A] Event code range is generally from 10,000 to 10999, and the following list is listed in the event code and information set serveroutput overdeclare err_msg varchar2 (120); begindbms_output.enable (1000000); for err_num IN 10000..10999LOOPERR_MSG: = SQlerRM (-ERR_NUM); if err_msg not limited '% message' || err_num || 'Not found%' thnds_output.put_line (err_msg); end if; end loop; end; / in UNIX system On, event information is placed in a text file $ ORACLE_HOME / RDBMS / MESG / ORAUS.MSG You can use the following scripts Event information Event = 10000WHile [$ Event -ne 10999] DoEvent = `expr $ Event 1`ooerr Ora $ EventDone for an event has been secured / being tracked, may be obtained by the following script SET SERVEROUTPUT ONDECLAREl_level NUMBER; BEGINFOR l_event iN 10000..10999LOOPdbms_system.read_ev (l_event, l_level); IF l_level> 0 THENdbms_output.put_line ( 'event' || TO_CHAR ( L_Event) || 'Is set at level' || to_char (l_level)); end if; end loop; end; / [q] What is statspack, how do I use it? [A] Statspack is a very good performance monitoring and diagnostic tool provided above Oracle 8i, basically all of the features of BSTAT / ESTAT, and more information can be referred to with document $ Oracle_Home / Rdbms / Admin / SPDoc.txt. Install Statspack: CD $ Oracle_Home / Rdbms / Adminsqlplus "/ as sysdba" @ spdrop.sql - Uninstall, the first time you don't need SQLPLUS "/ as sysdba" @ spcreate.sql - need to enter the table space name to use Statspack : SQLPlus Perfstat / PerfStatexec Statspack.snap; - Information Collection Statistics Snapshot; @ spreport.sql - Enter the startup number to view the startup number and end the snap number other related scripts S: spauto.sql - Submit a job with dBMS_JOB, automatically perform StatPack's information collection statistics sppurge.sql - clear a range Statistics, need to provide start snapshots and end snaps sptrunc.sql - clear (TRUNCATE) all statistics
