Oracle Common FAQ V1.0 (4)

Oracle part fourth, performance tuning
[Q] If automatic tracking is set
[A] Login with System
Execute $oracle_home/rdbms/admin/utlxplan.sql Create a schedule
Perform $oracle_home/sqlplus/admin/plustrce.sql create plustrace role
If you want to schedule a table to be used by each user,
Sql>create public synonym plan_table for plan_table;
Sql> Grant all on plan_table to public;
If you want to make the role of automatic tracking available to every user,
Sql> Grant plustrace to public;
Turn on/off tracing with the following statement
SET Autotrace on | Off
| On EXPLAIN | On STATISTICS | traceonly | Traceonly EXPLAIN
[Q] If you keep track of your session or someone else's session
[A] It's easy to track your conversations
Alter Session Set Sql_trace True|false
Or
Exec Dbms_session.set_sql_trace (TRUE);
If you are tracking someone else's session, you need to call a package
EXEC dbms_system.set_sql_trace_in_session (sid,serial#,true|false)
The tracked information can be found in the User_dump_dest directory or the file name is obtained using the following script (for Win environment, if UNIX requires some modification)
SELECT p1.value| | ' \'|| p2.value| | ' _ora_ ' | | p.spid| | '. Ora ' filename
From
V$process p,
V$session S,
V$parameter P1,
V$parameter P2
WHERE p1.name = ' user_dump_dest '
and p2.name = ' db_name '
and p.addr = S.paddr
and S.audsid = USERENV (' SESSIONID ')
Finally, you can resolve trace files by 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 you can do this by setting up events that work equally
Alter system SET Events
' 10046 Trace Name context Forever,level 1 ';
If tracing is turned off, you can use the following statement
Alter system SET Events
' 10046 Trace name context off ';
Level 1 and 8 above are the tracking levels.
Level 1: Trace SQL statements, equal to Sql_trace=true
Level 4: Include details of variables
Level 8: Include Waiting events
Level 12: Include binding variables and wait events
[Q] How to quickly get DB process information and executing statements based on the OS process
[A] Sometimes we operate on the OS, like the OS processes we get after top, how do we get db information quickly from OS information?
We can write the following script:
$more whoit.sh
#!/bin/sh
Sqlplus/nolog 100,cascade=> TRUE);
Dbms_stats.gather_table_stats (User,tablename,degree => 4,cascade => true);
This is a summary of the commands and toolkits
1. For partitioned tables, it is recommended to use Dbms_stats instead of using analyze statements.
A) can be done in parallel, for multiple users, multiple table
b) The data for the entire partitioned table and the data for a single partition can be obtained.
c) can be compute at different levels Statistics: Single partition, sub partition, whole table, all partitions
D) can be poured out of statistical information
E users can collect statistical information automatically
2, Dbms_stats's shortcomings
A) cannot validate Structure
b) can not collect chained ROWS, can not collect cluster table information, these two still need to use the Analyze statement.
c) Dbms_stats The index is not analyze by default because the default cascade is false and needs to be manually specified as True
3, for Oracle 9 inside the external table,analyze can not be used, can only use Dbms_stats to collect information.
[Q] How to quickly reorganize the index
[A] Through the Rebuild statement, you can quickly restructure or move the index to another tablespace
Rebuild has the ability to rebuild the entire index, and can change the index's storage parameters without deleting the original index
Syntax is
Alter index INDEX_NAME rebuild tablespace ts_name
Storage (...);
If you want to quickly rebuild the entire user index, you can use the following script, of course, you need to make the appropriate changes according to your own situation
Sql> set Heading off
Sql> Set Feedback off
Sql> Spool D:\index.sql
sql> SELECT ' alter index ' | | index_name | | ' Rebuild '
||' Tablespace INDEXES Storage (initial 256K next 256K pctincrease 0); '
From All_indexes
WHERE (tablespace_name!= ' INDEXES ')
OR next_extent!= (256 * 1024)
)
and owner = USER
Sql>spool off
Another statement that merges the index is
Alter INDEX INDEX_NAME COALESCE, this statement is only the same level of leaf block in the merged index
Consumption is small, for some indexes there is a lot of space waste in the case, there are some effects.
[Q] How to use hint hints
[A] After select/delete/update write/*+ hint * *
such as SELECT/*+ Index (table_name index_name) * * Col1 ...
Note * and + cannot have spaces between
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;
which
table_name must be written, and if the alias of the table is used in the query, the table alias should be substituted for the table name in hint;
Index_name may not have to write, Oracle will select an index based on statistical values;
If the index name or table name is wrong, the hint is ignored;
[Q] How to quickly copy a table or insert data
[A] Quick Copy table can specify nologging options
such as: Create table T1 nologging
As select * from T2;
Quick Insert Data You can specify append hints, but be aware
Noarchivelog mode, the default append is Nologging mode.
Under Archivelog, you need to set the table to 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 be accelerated, of course, it can be set to No FORCE LOGGING by the following statement.
Alter database no force logging;
If force LOGGING is turned on, you can view it with the following statement
Sql> select force_logging from V$database;
[Q] How to avoid using a specific index
[A] In many cases, Oracle incorrectly uses the index to cause a noticeable decrease in efficiency, and we can use a little bit of skill to avoid using indexes that should not be used, such as:
Table test, with Field A,b,c,d, a federated Index Inx_a (A,B,C) is established on A,b,c, and a separate index Inx_b (b) is established on B.
Under normal circumstances, where a=? and b=? and c= will use index inx_a,
Where b=? The index is used Inx_b
But where a=? and b=? and c=? Which index does GROUP by B use? Oracle tends to use index inx_b in cases where the analysis data is incorrect (for a long time without analysis) or if no data is analyzed at all. By performing the planned analysis, the use of this index will greatly cost the query time.
Of course, we can use the following techniques to avoid using Inx_b, while using Inx_a.
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
Through such simple changes, often can be a query time submitted many times
Of course, we can also use no_index hints, I believe many people have not used, but also a good way:
Select/*+ no_index (t,inx_b) * * FROM Test t
where a=? and b=? and c=? Group BY B
[Q] When will Oracle use a skip-index scan
[A] This is a new feature of 9i Jumping Index Scan (index Skip Scan).
For example, the table has indexed index (A,B,C), when the query condition is
Where b= may be used for indexing 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 refers to the CBO) can apply the index SKIP scans to queries at least a few conditions:
The 1 optimizer considers it appropriate.
The number of unique values for the leading columns in the 2 index can meet certain criteria, such as a lot of duplicate values.
The 3 optimizer should know the value distribution of the leading columns (obtained by analysis/statistics).
4 Appropriate SQL statements
Wait
[Q] How to create a use 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 the implied parameters to handle the
Alter session Set "_use_nosegment_indexes" = true;
You can use explain plan for select ... To see the effect of a virtual index
View execution plans with @ $ORACLE _home/rdbms/admin/utlxpls
Finally, as needed, we can delete virtual indexes, like normal indexes
Drop index Virtual_index_name;
Note: The virtual index is not physical, so the virtual index is not the same as the physical index, and do not test the virtual index with automatic tracing, because that is the effect of the actual execution, is not used to the virtual index.
[Q] How to monitor useless indexes
A Oracle 9i above, you can monitor the use of the index, if not used for a period of time index, is generally useless index
The 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 you want to monitor an entire user's index, you can use the following script:
Set Heading off
Set echo off
Set Feedback off
Set pages 10000
Spool Start_index_monitor.sql
SELECT ' alter index ' | | owner| | '. ' | | index_name| | ' monitoring usage; '
From Dba_indexes
WHERE owner = USER;
Spool off
Set heading on
Set echo on
Set feedback on
------------------------------------------------
Set Heading off
Set echo off
Set Feedback off
Set pages 10000
Spool Stop_index_monitor.sql
SELECT ' alter index ' | | owner| | '. ' | | index_name| | ' nomonitoring usage; '
From Dba_indexes
WHERE owner = USER;
Spool off
Set heading on
Set echo on
Set feedback on
[Q] How can I fix my plan of execution
[A] You can use outline to fix the execution plan for the SQL statement
You can create a outline with the following statement
Create OE Replace outline outln_name on
Select Col1,col2 from Table
where ...
If you want to remove outline, you can use the
Drop Outline Outln_name;
For outline that have already been created, they are stored under the Ol$hints table of the OUTLN user
For some statements, you can use the 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 ');
So, you swap Test1 outline with Test2 outline.
If you want to take advantage of existing outline, you need to set the following parameters
Alter system/session Set query_rewrite_enabled = True
Alter system/session Set use_stored_outlines = True
[What the class in Q]v$sysstat represents
[A] Statistical category
1 Representative Case activity
2 represents Redo buffer activity
4 stands for the lock
8 Representing data buffering activities
16 Representing OS activity
32 representing parallel activities
64 represents table Access
128 represents debugging information
[Q] How to kill a particular database session
[A] Alter system kill session ' sid,serial# ';
Or
Alter system disconnect session ' sid,serial# ' immediate;
On win, you can also use Oracle-supplied Orakill to kill a thread (in fact, an Oracle process)
On Linux/unix, you can kill the OS process corresponding to the database process directly using Kill
[Q] How to quickly find locks and lock waiting
[A] Database locks are more resource-intensive, especially when lock-waiting occurs, we must find the lock that occurs and, if possible, kill the process.
This statement will find the lock generated by all DML statements in the database, and it can also be found that any DML statement actually produces two locks, one is a table lock and one is a row lock.
You can kill sessions by using ALTER system kill session ' sid,serial# '
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.osuser
From V$session s,v$lock l,dba_objects o
WHERE L.sid = S.sid
and L.ID1 = o.object_id (+)
And S.username is not NULL
If there is a lock waiting, we might be more interested in knowing who locked the table and who's waiting.
The following statement can query who 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 s
WHERE l.object_id=o.object_id
and L.session_id=s.sid
ORDER BY O.object_id,xidusn DESC
The above query result is a tree structure, and if there are child nodes, there is a wait to occur. If you want to know which rollback segment the lock uses, you can also associate to V$rollname, where Xidusn is the USN of the rollback segment.
[Q] How to effectively delete a large table (extent a number of tables)
[A] a table with many (100k) extent, if simply using the drop table, will consume a lot of CPU (Oracle to the fet$, uet$ Data dictionary operation), may take several days, the better way is to delete the extent 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 a temporary data file
[a]9i the following version uses
ALTER DATABASE datafile ' file name ' RESIZE 100M a similar statement
9i above version adopts
ALTER DATABASE tempfile ' file name ' RESIZE 100M
Note that temporary data files are generally not shrunk when used, unless you close the database or disconnect all sessions and stop using temporary data files.
[Q] How to clean up the temporary section
[A] You can use the following methods
1, use the following statement to see who is using the temporary paragraph
SELECT Username,sid,serial#,sql_address,machine,program,
Tablespace,segtype, Contents
From V$session se,v$sort_usage su
WHERE se.saddr=su.session_addr
2, those who are using the temporary segment of the process
Sql>alter system kill session ' sid,serial# ';
3, the temp table space back to shrink
Sql>alter tablespace TEMP Coalesce;
You can also use diagnostic events
1, determine the temp table space ts#
Sql> Select ts#, name from V$tablespace;
ts# NAME
-----------------------
0 Sysyem
1 RBS
2 USERS
3* TEMP
......
2. Perform cleanup operations
Alter session SET Events ' immediate trace name drop_segments level ts#+1 '
Description
The temp table space is ts# to 3*, so ts#+ 1 = 4
If you want to clear all the temporary segments of the table space,
ts# = 2147483647
[Q] How to dump the internal structure of the database, such as the structure of the control file shown above
[A] It is common to have
1, the analysis of data file block, dump data file n block m
alter system dump DATAFILE n block m
2, analysis log files
alter system dump logfile LogFileName;
3, analysis of the contents of the control document
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, analysis log file header
Alter session SET Events ' immediate trace name REDOHDR level 10 '
6, analysis System State, preferably every 10 minutes, do three times comparison
Alter session SET Events ' immediate trace name systemstate level 10 '
7. Analyze Process Status
Alter session SET Events ' immediate trace name processstate level 10 '
8, analysis of the library cache details
Alter session SET Events ' immediate trace name Library_cache level 10 '
[Q] How to get all the event codes
[A] The event code range is generally from 10000 to 10999, which lists the event code and information for this scope
SET Serveroutput on
DECLARE
Err_msg VARCHAR2 (120);
BEGIN
Dbms_output.enable (1000000);
For Err_num in 10000..10999
LOOP
Err_msg: = SQLERRM (-err_num);
IF err_msg not like '%message ' | | err_num| | ' Not found% ' THEN
Dbms_output.put_line (ERR_MSG);
End IF;
End LOOP;
End;
/
On UNIX systems, event information is placed in a text file
$ORACLE _home/rdbms/mesg/oraus.msg
You can view event information with the following script
event=10000
While [$event-ne 10999]
Todo
Event= ' expr $event + 1 '
Oerr Ora $event
Done
For events that have been ensured/are being tracked, you can use the following script to obtain
SET Serveroutput on
DECLARE
L_level number;
BEGIN
For l_event in 10000..10999
LOOP
Dbms_system.read_ev (L_event,l_level);
IF l_level > 0 THEN
Dbms_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 and how do I use it?
A Statspack is a very good performance monitoring and diagnostic tool provided by Oracle 8i, which basically contains all the bstat/estat features and more information
You can refer to the accompanying documentation $oracle_home/rdbms/admin/spdoc.txt.
Install Statspack:
CD $ORACLE _home/rdbms/admin
Sqlplus "/As SYSDBA" @spdrop. SQL-Uninstall, the first time you do not need
Sqlplus "/As SYSDBA" @spcreate. SQL--You need to enter the table space name as prompted
Use Statspack:
Sqlplus Perfstat/perfstat
exec Statspack.snap; -Information gathering statistics, each run will produce a snapshot number
--Get the snapshot number, you must have more than two snapshots to generate the report
Select snap_id, snap_time from Stats$snapshot;
@spreport. SQL--Enter the start and end snapshot numbers you want to see
Other Related scripts s:
Spauto.sql-Submit a job using Dbms_job to automate Statpack information collection statistics
Sppurge.sql-Clear a range of statistics, need to provide start and end snapshot number
Sptrunc.sql-Clear (TRUNCATE) all statistical information