ORACLE Study Notes-performance optimization (1)

1. query the execution plan of the statement being executed (that is, the actual statement execution plan)
Select * from v $ SQL _plan where hash_value = (select SQL _hash_value from v $ session where sid = 1111 );
The id and parent_id indicate the structure of the number of executions. The largest value is the first execution.

For example

ID PARENT_ID
-------------
0
1 0
2 1
3 2
4 3
5 4
6 3

 

 

The execution plan tree is
0
1
2
3
6 4
5

2. How to set Automatic Tracing
Log On With system
Run $ ORACLE_HOME/rdbms/admin/utlxplan. SQL to create a schedule.
Run $ ORACLE_HOME/sqlplus/admin/plustrce. SQL to create the plustrace role.
If you want to make the schedule available to 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 trail:
Set autotrace on | OFF | on explain | on statistics | TRACEONLY EXPLAIN

3. How to track your sessions or others' sessions
It's easy to track your own 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. The file name applies to the Windows environment. If it is unix, you must modify it)
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, as shown in figure
Tkprof original file target file sys = n

4. How to set a trail for the entire database system
In fact, the alter system set SQL _trace = true in the document is unsuccessful. However, you can set the 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

5. How can I quickly obtain DB process information and statements being executed based on the OS process?
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 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 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 oracle 9's External Table, Analyze cannot be used. You can only use DBMS_STATS to collect information.

6. How to quickly restructure Indexes
Using the rebuild statement, you can quickly reorganize 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 is only used to merge leaf blocks at the same level in the index, which consumes a small amount of data. It is useful when a large amount of space is wasted in some indexes.

7. How to Use Hint prompt
Write/* + hint */After select/delete/update */
For example, select/* + index (TABLE_NAME INDEX_NAME) */col1...

Note: 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;

8. How to quickly copy a table or insert data
You can specify the Nologging option for quick table copy.
For example, Create table t1 nologging
As select * from t2;

You can specify the append prompt when inserting data quickly. However, in noarchivelog mode, append is used by default in nologging mode. In archivelog, you need to set the table to the Nologging mode.
Such as 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 check whether force logging is enabled:
SQL> select force_logging from v $ database;

9. How to avoid using specific indexes
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 are not supposed to be used, such:
Table test, which has fields a, B, c, d. Joint index inx_a (a, B, c) is created on 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 =? The index inx_ B is used. However, where a =? And B =? And c =? Which index does group by B use? If the data is not correctly analyzed for a long time, or the data is not analyzed at all, oracle often uses the index inx_ B. 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

With such a simple change, the query time can often be submitted many times
Of course, we can also use the no_index prompt, I believe many people have not 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

Example:
The index IDX_CM_USER4 (ACC_ID) and IDX_CM_USER8 (BILL_ID) are originally available on CM_USER, but the execution of the following statement is slow.
Select * from CM_USER where access id = 1200007175
And user_status> 0 and bill_id like '000000' order by acc_id, bill_id

The explain analysis shows that the execution plan uses IDX_CM_USER8.
Select * from user_indexes where table_name = 'cm _ user'. I found that IDX_CM_USER8 has not been analyzed.

Use the following statement to change the execution plan
Select/* + INDEX (CM_USER IDX_CM_USER4) */* from CM_USER where acc_id = 1200007175 and user_status> 0 and bill_id like '2013' order by acc_id, bill_id

Or analyze the index
Exec dbms_stats.gather_index_stats (ownname => 'qacs1', indname => 'idx _ cm_user8', estimate_percent => 5 );
It can be found that the execution plan is back to normal.