) Oracle Study Notes

I. Improve database performance
1. Use explain plan to check SQL statement Performance
Specific operations:
Generate a plan_table table
Run rdbms \ admin \ utlxplain. SQL
Explain plan set statement_id = 'statement1 'into plan_table
For select * from table_name;

Select statement_id, operation, options position from plan_table
Note: position is the description of the sales.
Operation object
Option
2. Use set autotrace
Set autotrace on
Set autotrace off
Or
Set autot on
Set autot off

3. Select the method to optimize SQL statements
1) cost-based Optimization
Two parameters: frist_rows and all_rows
Use the following method to modify the optimizer_mode parameter:
The optimizer_mode parameter is in init. ora.
The optimizer_mode parameter can also be set to choose.
Modify the session:
Alter session set optimizer_goal = frist_rows
Alter session set optimizer_goal = all_rows
Use the following methods to modify the prompt:
Select -- + frist_rows
* From table_name where "condition"
Or
Select/* + frist_rows */
* From table_name where "condition"
4. The above are cost-based optimizations, and rule-based optimizations are not commonly used.
5. Use indexes to improve performance
1) index creation
Create [unique] index index_name
On Table table_name (Field 1, Field 2)
Note: the unique option indicates that a unique index is created.
Note: The index has a single column index and a duplicate index;
Primary keys and candidate keys do not need to be indexed;
It is best for Foreign keys to be indexed;
It is best to create an index on a column with a unique value with a high query frequency in a large table;
LONG Columns cannot be indexed by keys;
Too many indexes should not be created.
1) delete an index
Drop index index_name;
6. Compile existing SQL statements in the Shared Pool
Use select SQL _text from V $ sqlarea to view the existing SQL statements in the shared pool.

Ii. Adjust database performance
1. provide enough memory to reduce I/O operations.
Set the size of shared_pool_size, db_block_size, and db_block_buffer. (In init. ora)
2. Reduce disk Competition
1) place data files and log files on different Disks
For example, create database db_name
Datafile 'C: \ oracle \ data \ db_name01.dbf'
Logfile 'd: \ oracle \ data \ log_name01.dbf'
Archivelog
2) create indexes and data on different Disks
Create the index tablespace and data table space on different disks, and create tables and indexes based on the two tablespaces respectively.
Create table table_name
(NO number (5) primary key,
Name varchar2 (10)
)
Tablespace tablespace_in_c;

Create index index_name
On table_name (NO)
Tablespace tablespace_in_d;
3. Adjust the rollback segment
Rollback segment:
Create public rollback segment rollback_name
Tablespace RBS
Optimal to 500 K;
Note: After a rollback segment is created, it is in the same offline status as the tablespace. Use the tablespace modification command to bring it online.
Modify rollback segments:
Alter rollback segment rollback_name
Online | offline
Storage ......
Shrink ...... ;
Use rollback segments:
Savepoint savepoint_7;
Rollback to savepoint_7;

4. Adjust the network multi-thread Server
1) set the number of network protocol process scheduling
Number of network protocol process scheduling Parameters
Mts_dispatachers = 'tcp, 5', 'ipc, 5'
Mts_max_dispatachers = 30
Modify the number of network protocol process scheduling Parameters
Alter system mts_dispatchers 'tcp, 8', 'ipc, 8 ',
2) observe the V $ dispatacher data dictionary.
Select name, network, busy, idle from V $ dispatacher;
3) set and modify the number of Server Processes
Mts_servers = 5
Mts_max_servers = 20
Alter system mts_servers 8;
4) Reduce Detection Points
Log_checkpoint_interval = 1200
Log_checkpoint_timeout = 0
5) Start the monitoring process of the detection site
Checkpoint_process = true

5. Add a log group file.
1) create database db_name
Datafile 'C: \ oracle \ data \ db_name.dbf'
Logfile group 1 'C: \ oralce \ log01.dbf'
Logfile group 2 'd: \ oralce \ log01.dbf'
Logfile group 3 'e: \ oralce \ log01.dbf'
Archivelog;
2) alter database db_name
Add logfile group 4'f:/oracle/log4.daf ';
3) alter database db_name
Drop logfile group 4;
Iii. Accelerating data collection
1. Create an index
Create [unique] index index_name
On table_name (Field 1, Field 2)
Tablespace tablespace_name;
2. If the table is often deleted or modified, the index should be deleted and re-created.
3. Manual mandatory Indexing
Select */* + index (Field 1) */from table_name
4. Create a data cluster
Create cluster cluster_name (id interger)
Size 50
Hash is id
Hashkey 500;
Create table table_name
(Define sub-segments ...... ) Cluster cluster_name ()
5. Parallel Processing
The parallel processing mechanism is applicable to the multi-machine processing mechanism. It can use operations such as classification, connection, Table query, and index creation for parallel processing, in particular, the processing of multiple large tables is very effective.
Parallel_max_servers = 50
Parallel_min_servers = 5
Parallel_server_idle_time = 10 (in minutes)
Degree of parallelism = Parallel_default_size
= Number of rows in the table/Parallel_default_scansize
Maximum degree of parallelism = Parallel_default_max_scan = 50

When creating a table, add the parallel option:
Create table table_name
(Field 1,
......
)
Parallel 15

Select -- + parallel (table_name, 50)
From table_name;
Select -- + noparallel (table_name)
From table_name;
Concurrent processing of indexes:
Alter table table_name
Enable primary key
Using index parallel
Alter table table_name
Enable primary key
Using index noparallel

4. Prevent Access conflicts
1. Lock
Lock table table_name | view_name
In lock_type mode [nowait]
Lock_type includes row share, row exclusive, share updata, share, exclusive, and share row exclusive.

[2002-3-12]
Common Operations on oracle database objects in Databases
I. Objects: tables, views, snapshots, indexes, clusters, sequences, synonyms, database chains, stored procedures, functions, triggers, packages, and aggregation.

1. table:
Table creation:
Create table table_name (
Field_name_1 type (length) [not null],
......
Unique ()
Primary key ()
Foreign key () references other_table.field on delete/updata cascade
......
) Tablespace tablespace_name;

Create table table_name
Select * from other_table

Query
Select field1, field2 ,...... From table_name
Where condition
Group by field
Having Condition
Orader by field asc | desc

Delete:
Drop table table_name;
Modify:
Alter table table_name
Add (field_1 type [not null],
......)
Alter table table_name
Modify (field_1 type [not null],
...... )
Insert into table_name (field1, field2, field3)
Values (val1, val2, val3 );

Insert into table_name select * from table2

Update table_name field set field = values where ......

Alter table table_name add constraints pk_name primary key ("field_name ");

Alter table table_name drop constraints pk_name;

Alter table child_table_name add constraints fk_name foreign key ("child_field_name.field") references parent_table_name (parent_table_name) on delete cascade;

Alter table child_talbe_name drop constrints fk_name cascade;

2. View
Create View
Create view view_name
Select * from table_name
Delete View
Drop view view_name
3. Snapshots
Create Snapshot
Create snapshot snapshot_name
Build immediate
Using index tablespace 'tablespace _ name' storage (initial 12 k next 12 k pctincrease 10)
Refresh [complete/force/fast] with [rowed/primary key]
Using local rollback segment "rbs0"
On [commit/demand] for update
As select * from table_name

4. Index
Create index index_name on table table_name (field1, field2 );
Drop index index_name;
5. Clusters
Create cluster cluster_name (val type)
Size 100
Storage ()
[Index] [hashkeys 200];

Create table tab_1 (
Field1 type
......)
Cluster cluster_name;

6. Sequence
Create sequence sequence_name
Increment by 1
Start with 1
Max value 1024
Minvalue 1
Nocycle/cycle
Cache 22
Noorder/order
7. Synonyms
Create synonym synonym_name for view/table;
Create synonym synonym_name for view/Table @
8. Data Connection
Create database link link_name connect to user_name identified by password using 'nstude'
9. Stored Procedure
Create [or replace] Procedure procdure_name
(<Parameter 1> in/out <type>,
......
)
Is |
Internal Variable 1 type;
Internal Variable 2 type;
Begin
......
End

Run: Execute procdure_name
View print parameter 1

10. partitions
Create table table_name (
Field1 type,
Field2 type)
Partition by range (field1)
(Partition part1 values less than (value)
Tablespace tableapace_name1,
Partition part1 values less than (value)
Tablespace tableapace_name2,
Partition part1 values less than (value)
Tablespace tableapace_name3
)

11. Functions
Create [or replace] function function_name
(<Parameter 1> in/out <type>,
......
)
Return type
Is |
Internal Variable 1 type;
Internal Variable 2 type;
Begin
......
End

Execute value: = function_name (parameter 1 );

12. triggers

13. Package

Ii. Database Security
1. Role
Create role role_name not identified
Create role role_name identified by password
Create role role_name identified externally
Create role role_name identified globally
Grant permission to role_name;

2. Users
Create a user:
Create user user_name
Profile "default"
Identified by "password"
Default tablespace tablespace_name
Temporary tablespace temp_tablespace_name
Account unlock
Modify user:
Alter user user_name

Iii. Data Storage
1. tablespace
Create tablespace tablespace_name
Logging datafile 'd: \ ORACLE \ ORADATA \ ORASTUDY \ TEST. ora 'size 5 M
Default storage (initial 10 k next 10 k pctincrease 20)

Alter tablespace tablespace_name default storage (initial 40 k)
Alter database rename file 'path \ filename. ora 'to 'path1 \ filename1.ora'
Alter database datafile 'path \ filename. ora 'resize 7 m
Alter database tablespace_name minimum extent 25 k
Alter database tablespace_name online
Alter database tablespace_name offline [normal/temporary/immediate]
Note: For details about how to select the normal, temporary, and immediate parameters, see p295.

2. rollback segments
Create rollback segment rollback_name tablespace "RBS"
Storage (INITIAL 10 k next 10 k optimal 13 k minextents 5 MAXEXTENTS 120)
Alter rollback segment rollback_name online
Alter rollback segment rollback_name offline

Note that the created tablespace is offline and needs to be changed to online.

3. Archive and redo log groups
Add a log Group
Alter database add logfile group N ('path \ logn. ora ') size 1024 K
Modify a log Group
Alter database rename file ('path \ logn. ora ')
To ('other _ path \ other_logn.ora ')
Add log group members
Alter database add logfile member 'path \ name_file.ora 'to group N

Iv. Backup and reply
Exp
IMP
(1) Cold backup:
1. Shut down the database
2. Create a backup path
3. Copy data files, control files, and system initialization files.
4. Open the database.
(2) Hot Backup:
1. Switch to archive mode.
Connect internal
Archive log start
View the status of the archive mode: archive log list
2. Set it to the backup status.
Alter tablespace temp begin backup
3. Copy data files
$ Copy path1 \ name1 path2 \ name2;
5. End backup
Alter tablespace temp end backup
6. Set system Detection Points
Alter system checkpoint
6. Backup Control Files
Alter Database Backup controlfile to path \ Name
7. Disable archive log
Archive log stop

(3) Data reply
Use v $ log_history to view the log file information:
Recid
Stamp
Thread # thread number
SEQUENCE # serial number
FIRST_CHANGE # First Change number
FIRST_TIME the first change time
NEXT_CHANGE # last change time
1. incomplete reply
1) incomplete reply based on time points
Connect internal
Startup mount
Recover database until time '2017/99 12:43:55 ';
Alter database open
2) incomplete response based on changed numbers
Connect internal
Startup Mount
Recover database until change 12345;
Alter database open
3) Stop-based recovery
Connect internal
Startup Mount
Recover database;
(When prompted: <ret> = suggest | filename | auto | cancel) make the appropriate selection
Alter database open;

2. Full recovery
Connect internal
Startup mount
Recover database
Alter database open
3. Only reply to table space
Recover tablespace tablespace_name
Alter database open
4. Online recovery
When the database is in archive mode and the system tablespace is not damaged, online recovery can be performed.
Alter tablespace tablespace_name offline;
Recover tablespace tablespace_name;
Alter tablespace tablespace_name online;
5. rollback segment recovery
Create rollback segment rollback_name
Tablespace tablespace_name;

Savepoint savepoint_8

Rollback to savepoint_8