Oracle programming Art Study Notes

Oracle programming art study notes, configuration environment, successfully developed Oracle Applications, SELECTFORUPDATESKIPLOCKED, Oracle files, Oracle files

Oracle programming art study notes, configuration environment, successfully developed Oracle Applications, select for update skip locked, Oracle files, Oracle files

1) SQL * Plus login. SQL File
When you use SQL * PLUS to log on, SQL * PLUS reads and executes the script in the following order:
· $ Oracle_home/sqlplus/admin/glogin. SQL
· Login. SQL in the current directory, or set an environment variable SQLPATH (using the registry in Windows) to specify a directory

[SQL]

Define _ editor = vi

Define gname = idle

Decode (dot, 0, length (global_name), dot-1) global_name

Script description:
· DEFINE_EDITOR = VI: sets the Default Editor used by SQL * Plus. You can set the default editor as your favorite Text Editor (instead of word processor), such as Notepad or emacs.
· Set serveroutput on size 1000000: this will open DBMS_OUTPUT by default (so you don't have to type this command every time ). In addition, the default buffer size is also set as large as possible.
· Set trimspool on: when the text is output offline, spaces at both ends of the text line are removed, and the row width is not fixed. If it is set to OFF (the default setting), the width of the text line output from offline is equal to the LINESIZE.
· Set long 5000: SET the default number of bytes displayed when LONG and CLOB columns are selected.
· Set linesize 100: SET the text line width displayed in SQL * Plus to 100 characters.
· Set pagesize 9999: PAGESIZE can control how often SQL * Plus prints the title. Here, we SET PAGESIZE To a large number (so each page has only one SET of titles ).
· COLUMN PLAN_PLUS_EXP FORMAT A80: Set the default width of explain plan output obtained by AUTOTRACE. A80 is usually enough to put down the entire plan.
· This part is then used to create the SQL * Plus prompt: COLUMN GLOBAL_NAME NEW_VALUE GNAME command to tell SQL * Plus to get the last VALUE IN THE GLOBAL_NAME COLUMN and assign this value to the replace variable GNAME. Next, select GLOBAL_NAME from the database and connect to my logon username. In this way, you can know who I am and where I am.

2) Set SQL * Plus AUTOTRACE
(1) cd [ORACLE_HOME]/rdbms/admin;
(2) log on to SQL * Plus as a SYSTEM;
(3) Run @ utlxplan;
(4) Run create public synonym PLAN_TABLE FOR PLAN_TABLE;
(5) run grant all on PLAN_TABLE to public.
The next step is to create and grant the PLUSTRACE role:
(1) cd [ORACLE_HOME]/sqlplus/admin;
(2) log on to SQL * Plus as SYS or SYSDBA;
(3) Run @ plustrce;
(4) run grant plustrace to public.

You can control the report by setting the AUTOTRACE system variable:
· Set autotrace off: No AUTOTRACE report is generated. This is the default setting.
· Set autotrace on explain: only the optimizer execution path is displayed in the AUTOTRACE report.
· Set autotrace on statistics: The AUTOTRACE report only displays the execution STATISTICS of SQL statements.
· Set autotrace on: The AUTOTRACE report includes both the optimizer execution path and SQL statement execution statistics.
· Set autotrace traceonly: similar to set autotrace on, but does not display the user's query output (if any ).

In addition, you can use the explain plan statement to obtain the execution plan. By default, the execution plan is saved to plan_table. You can use the DBMS_XPLAN package to view the result. For example:
Explain plan for select * from emp where deptno = 10;
Select * from table (dbms_xplan.display );

3) Configure statspack
Install
Statspack can be installed only when it is connected as SYSDBA.
Run the spcreate. SQL script in the [ORACLE_HOME] \ rdbms \ admin directory.
During the installation process, the created PERFSTAT user needs to specify the password, default tablespace and temporary tablespace?
To reinstall Statspack, use spdrop. SQL to delete the user (PERFSTAT) and installed views.

Generate Report
Execute statspack. snap to generate a System Snapshot and run it twice. Then execute @ spreport. SQL to generate a report based on two time points. (Use perfstat or sys to log on. Otherwise, many tables do not have access permissions)

You can use spauto. SQL to define automatic data collection tasks. The key content of spauto. SQL is as follows:
Dbms_job.submit (: jobno, 'statspack. snap; ', trunc (sysdate + 1/24, 'hh'), 'trunc (SYSDATE + 1/24, ''hh '')', TRUE,: instno );
Let's take a look at the definition of dbms_job.submit:

PROCEDURE SUBMIT
Parameter Name type input/output default value?
-------------------------------------------------------------------
JOB BINARY_INTEGER OUT
WHAT VARCHAR2 IN
NEXT_DATE DATE IN DEFAULT
INTERVAL VARCHAR2 IN DEFAULT
NO_PARSE BOOLEAN IN DEFAULT
INSTANCE BINARY_INTEGER IN DEFAULT
FORCE BOOLEAN IN DEFAULT

Therefore, the above statement means to execute statspack. snap once every one hour after the next hour of the current time.
You can modify the interval by modifying spauto. SQL.
Run the following statement to view the task running time:
Select job, next_date, next_sec from user_jobs where job =: jobno;

The collection task should be removed in a timely manner through the following statement:
Execute dbms_job.remove (: jobno)

Delete historical data
To delete historical data, use the @ sptrunc. SQL script to delete all data. You can also delete the stats $ snapshot table. Data in other tables will also be deleted in cascade mode.
Declare
Snapid number;
Begin
Select max (snap_id) into snapid from stats $ snapshot;
Delete from stats $ snapshot where snap_id <= snapid;
End;
/

Data collection options
Statspack has two types of collection options
· Level: controls the types of collected data.
There are three snapshot levels. The default value is 5.
A. level 0: general performance statistics. Including wait events, system events, system statistics, rollback segment statistics, row cache, SGA, session, lock, buffer pool statistics, and so on.
B. level 5: Add SQL statements. In addition to all content of level0, it also includes SQL statement collection, and the collection results are recorded in stats $ SQL _summary.
C. level 10: Add sub-lock statistics. Includes all content of level5. The attached sub-locks are stored in stats $ lathc_children. Exercise caution when using this level. We recommend that you use this level under the guidance of Oracle support.

You can use the following statement to modify the default level settings. If you only change the level of this collection, you do not need to specify the I _modify_parameter parameter.
Execute statspack. snap (I _snap_level => 0, I _modify_parameter => 'true ');

· Threshold (threshold): set the threshold of collected data.
The Snapshot threshold applies only to the SQL statements obtained from the stats $ SQL _summary table. There are the following thresholds:
A. executions_th this is the number of SQL statements executed (the default value is 100)
B. disk_reads_tn this is the number of disk reads executed by the SQL statement (default value: 1000)
C. parse_calls_th this is the number of resolution calls executed by SQL statements (the default value is 1000)
D. buffer_gets_th this is the number of buffers obtained by the SQL statement execution (the default value is 10000)
Any SQL statement that exceeds the above threshold value is collected and generates a record.

You can call the statspack. modify_statspack_parameter function to change the default value of the threshold. For example:
Execute statspack. modify_statspack_parameter (I _buffer_gets_th = & gt; 100000, I _disk_reads_th = & gt; 100000 );