FAQ 2 of Oracle

Part 4: Performance adjustment
[Q] If Automatic Tracking is set
[a] Log On With System
run $ ORACLE_HOME/rdbms/admin/utlxplan. SQL create a schedule
run $ ORACLE_HOME/sqlplus/admin/plustrce. 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;
, then
SQL> grant plustrace to public;
enable or stop a trail using the following statement
set autotrace on | off
| on explain | on Statistics | traceonly explain

[Q] it is very easy to track your own sessions or others' sessions
[a] tracking your sessions
alter session set SQL _trace true | false
or
exec dbms_session.set_ SQL _trace (true );
to track others' sessions, you need to call a package
exec dbms_system.set_ SQL _trace_in_session (SID, serial #, true | false)
the tracking information can be found in the user_dump_dest directory or obtained through the following script (applicable to the win environment, which must be modified if it is UNIX)
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 use tkprof to parse the trace file, for example,
tkprof: 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 unsuccessful.

However, you can set an event to complete this task.

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 ';

Both Level 1 and above 8 are tracking levels.

Level 1: trace SQL statements, equal to SQL _trace = true

Level 4: including detailed information about variables

Level 8: including waiting events

Level 12: Includes variable binding and wait 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?

You can write the following script:

$ More whoit. Sh

#! /Bin/sh

Sqlplus/nolog <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 (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_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, 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 statistics

For Table

For all [local] Indexes

For all [indexed] columns;

Analyze table tablename Delete statistics

Analyze table tablename validate ref update

Analyze 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 the toolkit to perform parallel analysis.

Dbms_utility (Toolkit earlier than 8i)

Dbms_stats (Toolkit provided after 8i)

For example

Dbms_stats.gather_schema_stats (user, estimate_percent = & gt; 100, cascade = & gt; 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 tables.

B) data of the entire Partition Table and data of a single partition can be obtained.

C) Compute statistics at different levels: single partition, sub-partition, full table, all partitions

D) generate statistical information.

E) users can automatically collect statistics.

2. disadvantages of dbms_stats

A) It cannot be validate structure.

B) You cannot collect chained rows or cluster Table information. You still need to use the analyze statement.

C) dbms_stats does not perform analyze on the index by default. Because the default cascade is false, you must manually specify it as true.

3. 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 rebuilding the entire index number. You can change the index storage parameters without deleting the original index.

Syntax:

Alter index index_name rebuild tablespace ts_name

Storage (......);

If you want to quickly rebuild the index for the entire user, you can use the following script. Of course, you need to modify the index based on your own situation.

SQL> set heading off

SQL> set feedback off

SQL> spool D: \ index. SQL

SQL> select 'alter Index' | index_name | 'rebuilt'

| 'Tablespace indexes storage (initial 256 K next 256 k pctincrease 0 );'

From all_indexes

Where (tablespace_name! = 'Indexes'

Or next_extent! = (256*1024)

)

And owner = user

SQL> spool off

Another statement used to merge indexes is

Alter index index_name coalesce. This statement only merges the leaf blocks at the same level in the index.

It does not consume much, but it has some effect 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 */

For example, select/* + index (table_name index_name) */col1...

Note that there must be no space between/* and +.

For example, 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;

Where

Table_name must be written. If the table alias is used in the query, the table alias must be used in the hint instead of the table name;

Index_name does not need to be written. 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] The nologging option can be specified for a quick copy table
example: create Table T1 nologging
as select * From T2;
You can specify the append prompt for fast data insertion, however, you must note that in noarchivelog mode, append is the nologging mode by default.
under archivelog, you need to set the nologging mode for the table.
for example, insert/* + append */into t1
select * From t2
note: if force logging is set in 9i environment, 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 a specific index
[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:
table test, with fields a, B, c, d, in A, B, the joint index inx_a (A, B, C) is created on C, and an index inx_ B (B) is created separately on B ).
normally, where a =? And B =? And c =? Index inx_a is used,
where B =? Index inx_ B is used
but 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 use the following tips to avoid inx_ B and 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
with this simple change, the query time can be submitted many times
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 T
where 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 (A, B, C ),

Where B =? The index (a, B, c) may be used)

For example, the following plan appears in the execution plan:

Index (skip scan) of 'test _ idx' (NON-UNIQUE)

The Oracle optimizer (CBO here) has at least a few conditions for querying the index skip scans of an application:

1. The optimizer considers it 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] nosegment options can be used, as shown in figure

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 for processing.

Alter session set "_ use_nosegment_indexes" = true;

You can use the explain Plan for select ...... View the effects of virtual Indexes

Use @ $ ORACLE_HOME/rdbms/admin/utlxpls to view the execution plan

Finally, we can delete virtual indexes as needed, just like normal indexes.

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, virtual indexes are not used.

[Q] How to monitor useless Indexes

[A] Oracle 9i and above can monitor index usage. If indexes are not used for a period of time, they are generally useless indexes.

Syntax:

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 indexes of the entire user, you can use the following script:

Set heading off

Set echo off

Set feedback off

Sets 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

Sets 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 execution plan?

[A] You can use outline to fix the execution plan of SQL statements.

Use the following statement to create an outline

Create OE replace outline outln_name on

Select col1, col2 from table

Where ......

If you want to delete outline, you can use

Drop outline outln_name;

The created outline is stored in the ol $ hints table of the outln user.

For some statements, you can use update outln. Ol $ hints to update outline.

For example, update outln. Ol $ hints (ol_name, 'test1', 'test2', 'test2', 'test1)

Where ol_name in ('test1', 'test2 ');

In this way, you can swap test1 outline with Test2 outline.

To use an existing outline, you must set the following parameters:

Alter system/session set query_rewrite_enabled = true

Alter system/session set use_stored_outlines = true

[Q] What do classes in V $ sysstat represent?

[A] statistical category

1. Example Activity

2 represents the redo buffer Activity

4 represents the lock

8 indicates data buffer Activity

16 represents OS Activity

32 represents parallel activities

64 represents Table Access

128 indicates 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;

In Windows, Oracle can also be used to kill a thread (an oracle process)

On Linux/Unix, kill can be used directly 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 a 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. 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 a lock wait occurs, we may want to know who has locked the table and who has waited.

The following statement can be used to 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 s

Where l. object_id = O. object_id

And l. session_id = S. Sid

Order by O. object_id, xidusn DESC

The preceding query result is a tree structure. If a subnode exists, it indicates that 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 a temporary data file
[a] 9i or earlier versions
alter database datafile 'file name' resize statements similar to 100 m
9i or later versions adopt
alter database tempfile 'file name' resize m
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 segments: temporary section
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. processes using temporary segments
SQL> alter system kill session 'sid, serial #';
3. Reduce the temp tablespace
SQL> alter tablespace temp coalesce;
you can also use diagnostic events

1. Determine the TS #
SQL> select ts #, name from V $ tablespace;
TS # name
---------------------
0 sysyem
1 RBS
2 users
3 * temp
......
2. Execute the cleanup operation
alter session set events 'immediate trace name drop_segments level ts # + 1'
description:
the temp tablespace ts # is 3 *, So ts # + 1 = 4
to clear the temporary segments of all tablespaces, then
TS # = 2147483647

[Q] How to dump the internal database structure, as shown in the control file structure shown above

[A] common

1. analyze data file blocks and dump data file n blocks M

Alter system dump datafile n block m

2. 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 the Log File Header

Alter session set events 'immediate trace name redohdr level 10'

6. analyze the system status, preferably once every 10 minutes for three comparisons

Alter session set events 'immediate trace name systemstate level 10'

7. 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 events 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 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 stored in a text file.

$ ORACLE_HOME/rdbms/mesg/oraus. msg

You can run the following script to view event information:

Events = 10000

While [$ event-ne 10999]

Do

Event = 'expr $ event + 1'

Oerr ora $ event

Done

You can use the following script to obtain the events that have been confirmed/are being tracked:

Set serveroutput on

Declare

Rochelle 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? How can I use it?

[A] statspack is a good performance monitoring and diagnostic tool provided by Oracle 8i and above. It basically contains the bstat/estat function and more information.

For more information, see $ ORACLE_HOME/rdbms/admin/spdoc.txt.

Install statspack:

CD $ ORACLE_HOME/rdbms/admin

Sqlplus "/As sysdba" @ spdrop. SQL -- Uninstall. This is not required for the first time.

Sqlplus "/As sysdba" @ spcreate. SQL -- enter the tablespace name as prompted

Use statspack:

Sqlplus perfstat/perfstat

Exec statspack. Snap; -- collects statistics and generates a snapshot number for each operation.

-- To obtain the snapshot number, you must have more than two snapshots to generate a report.

Select snap_id, 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 collect statistics on statpack information automatically.

Sppurge. SQL-to clear statistics within a period, you must provide the start snapshot and end snapshot number.

Sptrunc. SQL-clear all statistics