Oracle Learning Notes (1)

This is the first of my Oracle Learning Notes series, which summarizes the architecture of Oracle, and to learn Oracle, it is important to understand how Oracle operates and how it works. The content of today's study is compiled with a diagram, memo, click to enlarge.

In addition, there are several statements (Sql*plus):

SYS User Login

 as SYSDBA;

Unlock Scott Users

Sql> alter user Scott identified by Tiger account unlock;

Switch to Scott Users

Sql> Conn Scott/tiger;

Null value participates in column calculation

Select ename, Sal, Comm, (SAL+NVL (comm,0 as from EMP;

Querying with variables in Sql*plus

sql> variable VA number; SQL> declare:va:=7788; SQLSelectfromwhere empno =: VA;

Look at the SQL statements in memory that contain "SELECT * from"

Select  from where ' %select * from% ';

See Memory (SGA) resource conditions

Select  from V$sgastat;

Brush memory Sharing Pool

Sql> alter system flush Shared_pool;

To force a restart of an instance

sql> startup force;

To modify the date format in a session

set nls_date_format='yyyy-mm-dd';

If you want to permanently modify, Windows can establish a DWORD value named Nls_date_format in the registry, Unix/linux directly configure the environment variable.

About the relationship of shared pools and SQL statements to performance:

Because the attributes of the most recent SQL statement are cached in the shared pool, it is important to note when building SQL statements that different case, space, and order will be considered different SQL statements by Oracle, and only identical SQL statements will be reused from the SQL statements saved in the shared pool. And because writing an SQL statement's nonstandard will produce performance problems, so to do:

1, as far as possible to standardize the SQL statements, with a unified format, case, order, space, indentation, variable name format have the corresponding norms and strictly abide by;

2, in a large number of the same format of SQL query (such as only the value of the Where condition is different), should try to use variables, otherwise the shared pool will be a large number of garbage query full and so the common SQL is not cached, affecting efficiency;

3, PL/SQL or complex data query, can write stored procedures, with the way to ensure that the shared pool of SQL statements consistent;

4. Adjust the size of the shared_pool_size appropriately.

Bottom line: Backup and restore databases should be backed up in a physical way rather than exported and imported, unless your database data is small and unimportant.

Oracle Learning Notes (1)