Oracle programming Art Study Notes

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]

1. Define _ editor = vi
2. SetServeroutputOn Size1000000
3. SetTrimspoolOn
4. SetLong 1, 5000
5. SetLinesize 100
6. SetPagesize 9999
7. ColumnPlan_plus_exp format a80
8. ColumnGlobal_name new_value gname
9. SetTermoutOff
10. Define gname = idle
11. ColumnGlobal_name new_value gname
12. Select Lower(User) |'@'| Substr (global_name, 1,
13. Decode (dot, 0, length (global_name), dot-1) global_name
14. From(SelectGlobal_name, instr (global_name,'.') DotFromGlobal_name );
15. SetSqlprompt'& Gname>'
16. SetTermoutOn

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

1 2 3 4 5 6 7 8 9 .. 40 Next Page

[Content navigation]
Page 1: configure the environment	Page 1: Developed Oracle Applications
Page 1: SELECT FOR UPDATE SKIP LOCKED	Page 4th: Oracle Architecture
Page 1: Oracle files	Page 6th: Oracle file-parameter file
Page 1: Oracle file-Trace file	Page 8th: files in Oracle-warning log files
Page 1: Oracle files-data files	Page 1: SGA, PGA, and UGA
Page 11th: Oracle Process	Page 1: Oracle locks
Page 13th: Oracle concurrency and multi-Version Control	Page 1: Write consistency
Page 1: Transaction atomicity	Page 1: Transaction persistence
Page 1: DEFERRABLE Constraints	Page 1: REDO and UNDO
Page 1: Database Log Mode	Page 20th: clear blocks
Page 21st: ORA-01555: snapshot too old error	Page 1: Table type
Page 23rd: Segment	Page 24th: heap organization table
Page 25th: Index organizational tables	Page 26th: Index clustering table
Page 27th: Hash clustering table	Page 28th: ordered clustering table
Page 1: temporary tables and CBO	Page 2: B * tree indexes
Page 1: Under what circumstances is B * tree index suitable for use	Page 1: bitmap index)
Page 1: function-based indexes	Page 6: Data Type-string and binary string
Page 35th: Data Type-Value	Page 1: Data Type-Date and Time
Page 1: Data Type-LOB	Page 38th: Table partitions
Page 1: Index partitions	Page 1: Advantages of partitioning