FAQs about Oracle-Performance Tuning

Part 4 Performance Adjustment[Q] If you set Automatic Tracing[A] log on to the system and run $ ORACLE_HOME/rdbms/admin/utlxplan. run $ ORACLE_HOME/sqlplus/admin/plustrce to create the SQL schedule. SQL creates a plustrace role. If you want to use a schedule for every user, SQL> create public synonym plan_table for plan_table; SQL> grant all on plan_table to public; if you want the automatically tracked role to be available to every user, SQL> grant plustrace to public; use the following statement to enable or stop a trace set autotrace on | OFF | on explain | on statistics | TRACEONLY EXPLAIN[Q] If you are tracking your own sessions or others' sessions[A] It is easy to trace your own sessions. Alter session set SQL _trace true | falseOrExec dbms_session.set_ SQL _trace (TRUE); if you are tracking others' sessions, you need to call A package exec dbms_system.set_ SQL _trace_in_session #, true | false) the trace information can be found in the user_dump_dest directory or obtained through the following script (applicable to the Windows environment, if it is unix, You need to modify it) 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') at the end, you can use Tkprof to parse the tracking file, for example, Tkprof's original file's target file sys = n[Q] how to set the tracking of the entire database system[A] In fact, the alter system set SQL _trace = true in the document is not successful, but you can set the event to complete this work. The role is equal to that of alter system set events '10046 trace name context forever, level 1 '; If Tracing is disabled, use the following statement alter system set events '10046 trace name context off'. The level 1 and above 8 are trace level 1: trace SQL statement, equal to SQL _trace = truelevel 4: including detailed information about variables level 8: including waiting events level 12: including binding variables and waiting events[Q] How can I quickly obtain DB process information and statements being executed based on the OS process?[A] Sometimes we operate on the OS, like the OS process we get after TOP, how can we quickly obtain DB Information Based on OS information? We can write the following Script: $ more whoit. sh #! /Bin/shsqlplus/nolog <EOFconnect/as sysdbacol machine format a30col program format a40set line 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 (select addr from v \ $ process where spid in ($1 )); select SQL _text from v \ $ sqltext_with_newlineswhere hash_value in (select SQL _HASH_VALUE from v \ $ session wherepaddr in (select addr from v \ $ process where spid = $1) order by piece; exit; EOF then, we only need to execute the following in the OS environment $. /whoit. sh Spid[Q] how to analyze tables or indexes?[A] You can use the analyze command in the command line mode, for example, 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 CHA Ined rows [INTO TableName] and so on. If you want to analyze the entire User or database, you can also use the toolkit. You can analyze Dbms_utility in parallel (the toolkit before 8i) Dbms_stats (the Toolkit provided after 8i) such as dbms_stats.gather_schema_stats (User, estimate_percent => 100, cascade => TRUE); dbms_stats.gather_table_stats (User, TableName, degree => 4, cascade => true ); this is a summary of the commands and toolkit. 1. For partitioned tables, we recommend that you use DBMS_STATS instead of the Analyze statement. A) It can be performed in parallel. for multiple users and multiple tablebs, the data of the entire Partition Table and the data of a single partition can be obtained. C) You can Compute Statistics at different levels: single partition, sub-partition, full table, all partitions d) You can generate statistical information e) users can automatically collect statistical information. 2. disadvantages of DBMS_STATS. a) Validate Structureb is not supported. chained rows cannot be collected, and cluster table information cannot be collected. The two still need to use the Analyze statement. C) DBMS_STATS does not perform Analyze on indexes by default. Because the default Cascade is False, you must manually specify it as True3. For External tables in oracle 9, Analyze cannot be used. You can only use DBMS_STATS to collect information.[Q] How to quickly restructure Indexes[A] using the rebuild statement, you can quickly restructure or move indexes to other tablespaces. rebuild has the function of recreating the entire index number, you can change the storage parameter syntax of the index without deleting the original index to alter index index_name rebuild tablespace ts_namestorage (......); You can use the following script to quickly rebuild indexes for the entire user. Of course, you need to modify the SQL statement as needed> set heading offSQL> set feedback offSQL> spool d: \ index. sqlSQL> SELECT 'alter Index' | index_name | 'rebuild' | 'tablespace INDEXES storage (initial 256 K next 256 K pctincrease 0); 'FROM all_indexesWHERE (tablespace_name! = 'Indexes' OR next_extent! = (256*1024) AND owner = USERSQL> spool off another statement used to merge indexes is alter index index_name coalesce. This statement is only used to merge the leaf blocks at the same level of indexes, which consumes a little time, it may be helpful if there is a large amount of space waste in some indexes.[Q] How to Use Hint prompt[A] Write/* + hint */After select/delete/update/such as select/* + index (TABLE_NAME INDEX_NAME) */col1... note: there cannot be spaces between/* and +. If you use hint to specify an index, select/* + index (cbotab) */col1 from cbotab; select/* + index (cbotab cbotab1) */col1 from cbotab; select/* + index (a cbotab1) */col1 from cbotab a; where TABLE_NAME must be written, if the table alias is used in the query, the table alias is used in the hint instead of the table name. You do not need to write INDEX_NAME. Oracle selects an index based on the statistical value; if the index or table name is wrong, the hint will be ignored;[Q] How to quickly copy a table or insert data[A] You can specify the Nologging option for A quick copy table, for example, Create table t1 nologgingas select * from t2. You can specify the append prompt for fast data insertion. However, note that in noarchivelog mode, append is the nologging mode by default. In archivelog, you need to set the table to the Nologging mode. For example, insert/* + append */into t1select * from t2. Note: if force logging is set in 9i, the above operations are invalid and will not be accelerated. Of course, you can use the following statement to set it to no force logging. Alter database no force logging; whether force logging is enabled, you can use the following statement to view SQL> select force_logging from v $ database;[Q] How to avoid using specific indexes?[A] in many cases, Oracle mistakenly uses indexes, leading to A significant reduction in efficiency. We can use A little trick to avoid using indexes that shouldn't be used, such as the table test, joint index inx_a (a, B, c, d) is created on fields a, B, and c ), an index Inx_ B (B) is created separately on B ). Under normal circumstances, where a =? And B =? And c =? Index inx_a, where B =? Index inx_ B will be used. However, where a =? And B =? And c =? Which index does group by B use? Oracle often uses the index inx_ B when the analysis data is incorrect (not analyzed for a long time) or is not analyzed at all. Through the analysis of the Execution Plan, the use of this index will greatly consume the query time. Of course, we can avoid using inx_ B instead of inx_a through the following techniques. Where a =? And B =? And c =? Group by B | ''-- if B is a character where a =? And B =? And c =? Group by B + 0 -- if B is a number, it can often be many times as long as the query time is submitted. Of course, we can also use the no_index prompt. I believe many people have never used it, it is also a good method: select/* + no_index (t, inx_ B) */* from test twhere a =? And B =? And c =? Group by B[Q] When Will Oracle use skip index scanning?[A] This is a new feature of 9i. Index Skip Scan. For example, if the table has an index Index (A, B, c), when the query condition is where B =? Index (a, B, c), for example, the following plan appears in the execution plan: INDEX (SKIP SCAN) OF 'test _ idx' (NON-UNIQUE) the Oracle optimizer (here it refers to CBO) can query the application Index Skip Scans with at least a few conditions: 1 The optimizer thinks it is appropriate. 2. The number of unique values in the leading column in the index can meet certain conditions (for example, there are many duplicate values ). 3. The optimizer needs to know the value distribution of the leading column (obtained through analysis/Statistical table ). 4. Suitable SQL statements.[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 in which session, you can use implicit parameters to process alter session set "_ use_nosegment_indexes" = true; you can use the explain plan for select ...... Let's take a look at the effects of the virtual index. Run @ $ ORACLE_HOME/rdbms/admin/utlxpls to check the execution plan. At the end, we can delete the virtual index as needed, such as drop index virtual_index_name. Note: virtual indexes do not exist physically. Therefore, virtual indexes are not equivalent to physical indexes. Do not use automatic tracking to Test Virtual indexes because they are actually executed and cannot be used.[Q] How to monitor useless Indexes[A] Oracle 9i and above can monitor the usage of indexes. If indexes are not used for A period of time, the useless index syntax is: start monitoring: alter index index_name monitoring usage; check the usage Status: select * from v $ object_usage; Stop monitoring: alter index index_name nomonitoring usage; of course, if you want to monitor the index of the entire user, you can use the following script: set heading offset echo offset feedback offset pages 10000 spool start_index_monitor.sqlSELECT 'alter Index' | owner | '. '| index_name | 'monitoring usage;' FROM dba_indexesWHERE owner = USER; spool offset heading onset echo onset feedback on specified set heading offset echo offset feedback offset pages 10000 spool stop_index_monitor.sqlSELECT 'alter Index' | owner | '. '| index_name | 'nomonitoring usage;' FROM dba_indexesWHERE owner = USER; spool offset heading onset echo onset feedback on[Q] How can I fix my execution plan?[A] You can use OUTLINE to fix the execution plan of SQL statements. You can use the following statement to create an OUTLINECreate oe replace outline OutLn_Name onSelect Col1, Col2 from Tablewhere ...... To delete Outline, you can use Drop Outline OutLn_Name. For the created OutLine, you can use update OUTLN to store some statements in the OL $ hints table of the outln user. ol $ hints to update outline such as update outln. ol $ hints (ol_name, 'test1', 'test2', 'test2', 'test1) where ol_name in ('test1', 'test2'); this way, you can swap Test1 OUTLINE with Test2 OUTLINE. To use an existing OUTLINE, you need to set the following parameter Alter system/session set Query_rewrite_enabled = trueAlter system/session set use_stored_outlines = true.[Q] What do classes in v $ sysstat represent?[A] statistical category 1 stands for case activity 2 stands for Redo buffer Activity 4 stands for lock 8 stands for data buffer activity 16 stands for OS activity 32 stands for parallel activity 64 stands for table access 128 stands for 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, you can also use orakill provided by oracle to kill a thread (in fact, an Oracle process) on Linux/Unix and directly use kill to kill the OS process corresponding to the database process.[Q] How to quickly search for locks and lock waits[A] database locks are resource-consuming. In particular, when A lock wait occurs, we must find the lock waiting. If possible, kill the process. This statement finds the locks generated by all the DML statements in the database. It can also be found that any DML statement actually produces two locks, one is the table lock and the other is the row lock. You can use alter system kill session 'sid, serial # 'to kill session SELECT/* + rule */s. username, decode (l. type, 'TT', '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 = o. object_id (+) AND s. username is not null if a lock wait occurs, we may want to know who has locked the table and who is waiting. The following statement can query who has locked the table and who is waiting. SELECT/* + rule */lpad ('', decode (l. xidusn, 0, 3, 0) | l. oracle_username User_name, o. owner, o. object_name, o. 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. the query results above object_id and xidusn DESC are in a tree structure. If a subnode exists, a wait occurs. If you want to know which rollback segment the lock uses, you can also associate it with V $ rollname. xidusn is the USN of the rollback segment.[Q] how to effectively delete a large table (there are many extent tables)[A] A table with A lot of extent (100 k), if you simply use drop table, it will consume a lot of CPU (Oracle needs to perform operations on the fet $ and uet $ data dictionaries). It may take several days. The better way is to delete the extent multiple times, to reduce this consumption: 1. truncate table big-table reuse storage; 2. alter table big-table deallocate unused keep 2000 m (n-1/n of the original size); 3. alter table big-table deallocate unused keep 1500 m ;.... 4. drop table big-table;[Q] How to shrink the size of temporary data files[A] versions earlier than 9i use statements similar to alter database datafile 'file name' RESIZE 100M. For Versions later than 9i, use alter database tempfile 'file name' RESIZE 100M. Note, temporary data files cannot be shrunk during use unless you close the database or disconnect all sessions and stop using temporary data files.[Q] how to clear temporary segments[A] use the following method: 1. Use the following statement to check who is using the temporary segment SELECT username, sid, serial #, SQL _address, machine, program, tablespace, segtype, contentsFROM v $ session se, v $ sort_usage suWHERE se. saddr = su. session_addr2. SQL> Alter system kill session 'sid, serial # '; 3. shrink the TEMP tablespace SQL> Alter tablespace TEMP coalesce; you can also use diagnostic Event 1 to determine the ts # SQL> select ts #, name FROM v $ tablespace of the TEMP tablespace; TS # NAME---0 syem1 RBS2 USER S3 * TEMP ...... 2. Execute the cleanup operation alter session set events 'immediate trace name DROP_SEGMENTS level TS # + 1'. Note: TS # Of temp tablespace is 3 *, so TS # + 1 = 4 if you want to clear the temporary segments of all tablespaces, TS # = 2147483647[Q] How to dump the internal database structure, as shown in the control file structure shown above[A] common examples: 1. analyze data file blocks, dump data file n blocks malter system dump datafile n block m2, and analyze log files alter system dump logfile logfilename; 3. Analyze the control file content alter session set events 'immediate trace name CONTROLF level 10' 4. analyze all data file headers alter session set events 'immediate trace name FILE_HDRS level 10' 5. Analyze log File Header alter session set events 'immediate trace name REDOHDR level 10' 6. analyze the system status, it is best to do this once every 10 minutes, compare alter session set events 'immediate trace name SYSTEMSTATE level 10' 3. Analyze the Process status alter session set events' immediate trace name PROCESSSTATE level 10' 8. Analyze the details of Library Cache alter session set events 'immediate trace name library_cache level 10'[Q] How to obtain all Event code[A] The range of Event code is generally from 10000 to 10999. The following lists the Event code and information in this range. set serveroutput ONDECLAREerr_msg VARCHAR2 (120); BEGINdbms_output.enable (1000000 ); FOR err_num IN 10000 .. 10999LOOPerr_msg: = SQLERRM (-err_num); IF err_msg not like '% message' | err_num |' not found % 'THENdbms_output.put_line (err_msg); end if; end loop; END; /On Unix systems, put the event information in a text file $ ORACLE_HOME/rdbms/mesg/oraus. msg you can use the following script to view event information event = 10000 while [$ event-ne 10999] doevent = 'expr $ event + 1' oerr ora $ eventdone for the/being tracked event, run the following script to obtain the 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 can I use it?[A] Statspack is an excellent performance monitoring and diagnosis tool provided by Oracle 8i and above. It basically includes the BSTAT/ESTAT function, for more information, see $ ORACLE_HOME/rdbms/admin/spdoc.txt. Install Statspack: cd $ ORACLE_HOME/rdbms/adminsqlplus "/as sysdba" @ spdrop. SQL -- Uninstall. sqlplus "/as sysdba" @ spcreate is not required for the first time. SQL -- enter the tablespace name using Statspack: sqlplus perfstat/perfstatexec statspack as prompted. snap; -- collects information and collects statistics. Each operation generates a snapshot number. To obtain a snapshot number, you must have more than two snapshots to generate the select SNAP_ID report, SNAP_TIME from STATS $ SNAPSHOT; @ spreport. SQL -- enter the start snapshot number and end snapshot number to be viewed. Other related scripts s: spauto. SQL-use dbms_job to submit a job and automatically collect STATPACK information and collect statistics on sppurge. SQL-to clear statistics within a period, you must provide the start snapshot and end snapshot number sptrunc. SQL-clear all statistics