[Q] If you set the automatic tracking [A] Log in to execute $ oracle_home / rdbms / admin / utlxplan.sql Create a schedule to create a Plustrace role If you want the schedule to make each user if you want the schedule to create a Plustrace role Can use, then SQL> CREATE PUBLIC SYNONYM Plan_Table for Plan_Table; SQL> GRANT All on Plan_Table to public; Stop Tracking Set Autotrace ON | Off | ON Explain | On Statistics | Traceonly | Traceonly Explain [q] If you trace your own session or someone else's session [A] Track your 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 of Exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID, Serial #, TRUE | FALSE) tracking can be found in the user_dump_dest directory or through the following script Name (for WIN Environment, If you are UNIX, you need to make a certain modification) Select P1.Value || '/' || p2.value || '_ora_' || p.spid || '. Mar' 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 tracking file through Tkprof. Such as TKPROF original file target file sys = n [q] How to set the entire database system tracking [A] Actue on the document ALTER SESTEM SET SQL_TRACE = TRUE is unsuccessful but can complete this work by setting events, etc. 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 events'10046 trace name context off'; where Level 1 is the tracking level Level 1: Track SQL statement, equal to SQL_TRACE = TrueElevel 4: More information including variables Level 8: A Waiting for Level 12: Includes Binding Variables and Waiting Events [Q] How to quickly get DB process information and the statement in executing according to the OS process [a Some time, we operate on the OS, I get the OS process after Top, how can I get DB information according to the OS information? We can write the following script: $ more whoit.sh #! / Bin / shssqlplus / nolog <<
from ( 'ss yyyy / mm / dd hh24:: mi' logon_time,) EOF connect / as sysdba col machine format a30 col program format a40 set line 200 select sid, serial #, username, osuser, machine, program, process, to_char v / $ session where paddr in (select addr from v / $ process where spid in ($ 1)); select sql_text from v / $ sqltext_with_newlines 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, as long as we execute as follows in the OS environment, we can analyze the table or index [a] command line method can be analyzed in the OS environment. using analyze command 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], etc. 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 select / * index (cbotab) * / col1 from cbotab; select / * index (cbotab1) * / col1 from cbotab; select / * index (a cbotab1) * / col1 from cbotab; SELECT / * INDEX (A cbotab1) * / col1 from cbotab; SELECT / * INDEX (A CBOTAB1) * / COL1 FROM CBOTAB A; where Table_Name is a must be written, and if you use the alias in the query, you must use the alias of the table instead of the table name; index_name can not be written, Oracle will select an index according to the statistical value; if The index name or table name is wrong, then this hint will be ignored; [q] How to quickly copy the table or insert data [a] Quick replication table can specify NOLogging options such as: Create Table T1 Nologging As SELECT * FROM T2 Quick inserting data can specify an APPEND prompt, but you need to pay attention to NOLOGGING mode in NOAHIVELOG mode. 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; Do you 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 erronely use indexing and lead to efficiency The significant decline, we can use a little skill to avoid the use of the index that should not be used, such as: Table Test, with fields A, B, C, D, establish a joint index inx_a (A, B,) on A, B, C c) The index inx_b (b) is established 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 = truealter system / session set use_stored_outlines = true [q] V $ systat Represents what [A] statistics category 1 representative example activity 2 Representative Redo Buffer Activity 4 Representative Lock 8 Represents Data Buffer Activities 16 Represents OS Activities 32 Represents Parallel Events 64 Representative Table Access 128 Represents Default [Q] How to Kill Specific Database Session [A] ALTER System Kill Session 'SID, Serial # '; Or alter system disconnect session' sID, serial # 'immediate; on WIN,
It is also possible to kill a thread with ORAKILL (actually a Oracle process) on Linux / UNIX, you can directly use KILL to kill the database process. OS process [Q] How to quickly find lock and lock waiting [a] database The lock is a comparative resource, especially when there is a lock waiting, we must find a waiting lock, there is a possibility, 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 to V $ rollname, where xidusn is the USN [Q] how to effectively delete a big table (many of the number of extents) [A] a lot (100K) EXTENT's table, if only Drop Table is simply used, it will consume a lot of CPU (Oracle to operate the FET $, UET $ Data Dictionary), which may be used for a few days, better ways Extents are removed multiple times to mitigate this consumption: 1. Truncate Table Big-Table Reuse Storage; 2. Alter Table Big-Table Deallocate Unused Keep 2000M (N-1 / N of the original size); 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 below the following version uses ALTER DATABASE DATAFILE 'File Name' Resize 100m Similar statement 9i or above The ALTER DATABASE TEMPFILE 'File Name' Resize 100M is noted, and the temporary data file generally cannot shrink unless the database is disconnected, and the use of the temporary data file is stopped.
[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 tablespaces, TS # = 2147483647 [q] How to dump database internal structure, such as the structure of the control file displayed above [A] Frequently 1, analyze data file block, turn Data file N block Malter System DUMP DATAFILE N Block M2, Analysis Log File ALTER System Dump Logfile LogfileName; 3, Analysis Control File Content ALTER SESSION SEVENTS '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, ALTER session set events' immediate trace name redohdr level 10'6, analyzing system status, best every 10 minutes, do three comparison Alter session set events' Immedi ate trace name SYSTEMSTATE level 10'7, analysis process status alter session set events' immediate trace name PROCESSSTATE level 10'8, analyze the details of the Library Cache alter session set events' immediate trace name library_cache level 10 '[Q] How do I get all Event code [A] Event code range is generally from 10,000 to 10999, and the following list is listed in Event Code and Information SET Serveroutput OnDeclare Err_msg varcha2 (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%' THHENDBMS_OUTPUT.PUT_LINE (ERR_MSG); END IF; End loop; end; / on UNIX system,
