Oracle-DBA Summary

1. Data Dictionary

1.1 query the data dictionary.

Dictionary ----- this dictionary contains all dictionary Information
SQL> select * from dict where instr (comments, 'index')> 0; ----- query all data dictionary names related to indexes.
1.2 set the query result format.

Set heading on/off: enables/disables the display of the query result header. The default value is ON.
Set feedback on/off: displays the number of returned rows in the query results. The default value is ON.
Set echo on/off: enable/disable command echo. The default value is ON.
Set time on/off: the display of the open/close time. The default value is OFF.
2. System I/O Adjustment

2.1 query disk I/O information.

# Select d. tablespace_name tablespace, d. file_name, f. phyrds, f. phywrts
From v $ filestat f, dba_data_files d
Where f. file # = d. file_id; --------- query data file read/write status
# Db_file_multiblock_read_count --------- Number of read blocks at the session level.
 
3. SQL Adjustment

3.1 modify SQL initialization parameters.

# Cursor_sharing ------- whether the variable parameter will be automatically matched | exact (none at all) ------ if this parameter is not bound with the variable, the system overhead will be high | similar (Intelligent Matching) ------- recommended, but the performance is not as good as adding a variable to the exact | force ------- will generate a BUG |
# Check the SQL statement in sqlarea to determine the number of times the SQL statement is executed (field EXECUTIONS ). Its Dynamic Performance view is v $ sqlarea.
3.2 open the cursor and use the variable binding method.

Cursor: variable x refcursor ---- defines x as a cursor
Begin
Open: x for select XXXXXXX from XXXXXXXX;
End;
/
(If you want to read the number in the cursor, use print x)
Bind the variable in sqlplus.
SQL> var n varchar2 (30 );
SQL> exec: n: = 'im _ user ';
The PL/SQL process is successfully completed.
SQL> select * from tab where tname =: n;
TNAME TABTYPE CLUSTERID
-----------------------------------------------
IM_USER SYNONYM
3.3 Use SQL _trace.

# Open a trail File
Alter session set SQL _trace = true;
Grant alter session to xxx; --- grant alter session
Position of your tracking file (you must have the permission to search for the dynamic performance view to use the following statement)
Select c. value | '/' | d. instance_name | '_ ora _' | a. spid | '. trc' trace
From v $ process a, v $ session B, v $ parameter c, v $ instance d
Where a. addr = B. paddr and B. audsid = userenv ('sessionid') and c. name = 'user _ dump_dest ';
Tracking the location of other tracing files
Select c. value | '/' | d. instance_name | '_ ora _' | a. spid | '. trc' trace
From v $ process a, v $ session B, v $ parameter c, v $ instance d
Where a. addr = B. paddr and B. audsid = & a and c. name = 'user _ dump_dest ';
B. The value of audsid specifies that the session to be tracked can be obtained from select SID, SERIAL #, AUDSID, username from v $ session;
# Markup
Alter session set tracefile_identifier = 'tag name you want to add ';
Then open the tracking file.
# Tracking others' SQL statements
To track others' sessions, you need to call a package
Exec dbms_system.set_ SQL _trace_in_session (sid, serial #, true | false)
The trace information can be found in the user_dump_dest directory.
You can use Tkprof to parse the trace file, as shown in figure
Tkprof original file target file sys = n
 
3.4 10046 tarce

10046 is an improvement of SQL _trace, which can provide more detailed information
Alter session set events '10046 trace name context forever, level xx'; (timed_statistics must be set to true)
10046event tracing levels include:
Level 1: tracks SQL statements, including parsing, execution, extraction, submission, and rollback.
Level 4: includes detailed information about variables
Level 8: including waiting events
Level 12: Includes variable binding and wait events
Close the command to alter session set events '10046 trace name context off ';
# View the current 10046 level (11g seems to be faulty, to be studied)
Grant execute on dbms_system to xxxxx;
 
Set serveroutput on
Declare I _event number;
Begin
Sys. dbms_system.read_ev (10046, I _event );
Dbms_output.put_line ('the session SQL _trace level is: '| I _event );
End;
/
# If you want to monitor other users who have dbms_system.set_ev permissions
Exec dbms_system.set_ev (SID, SERIAL #, 10046, grade, ''); (SID, SERIAL # obtained on v $ session)
Level (, 0) --- 0 is disabled.
The tracked file is the tracefile of the tracked session.
3.5 SQL Execution Plan.

Set autotrace off ---------------- do not generate AUTOTRACE Report, which is the default mode
Set autotrace on explain ------ AUTOTRACE only displays the optimizer execution path report
Set autotrace on statistics -- display only execution STATISTICS
Set autotrace on ----------------- contains the execution plan and statistics
Set autotrace traceonly ------ same as set autotrace on, but no query output is displayed
Set autotrace traceonly explain ----- only displays the execution plan and does not execute statements.
AUTOTRACE Statistics common column explanations
Db block gets ----- number of blocks read from buffer cache
Number of undo data blocks read by consistent gets from buffer cache -----
Physical reads ----- number of blocks read from the disk
Redo size ----- size of the redo generated by DML
Sorts (memory) ----- sorting amount executed in memory
Sorts (disk) ----- sorting volume on the disk
3.6 tkprof explanation

Parse (analysis): Find the query (soft analysis) in the shared pool or create a new plan (hard analysis) for the query)
Execute: execute all the tasks of the query.
Fetch (extract): displays the extraction of select. For update, there is no content
Count: number of executions
Cpu: cpu time consumed in this phase, in milliseconds
Elapsed
Disk: Number of physical I/O executions
QUERY: the number of I/O operations performed in a consistent QUERY.
CURRENT (CURRENT): number of logical I/O executions to the CURRENT
ROW: the ROW to be processed or affected at this stage.
If the QUERY, CURRENT, and ROWS of an UPDATE statement EXECUTE are 2000 1000 500, the statement accesses 2000 blocks and finds the row records to be updated, only 1000 blocks are accessed during UPDATE, and a total of 500 rows are updated. If you only retrieve a small amount of data and access a large number of blocks, it indicates that SQL and optimization are required.
MISSES cache hit rate: 0 indicates that the cache has passed soft analysis.
Optimizer goal (optimization program objective)
 
3.7 Use rownum.

Remember that rownum is a pseudo-sample. It always starts from 1, that is, select xxxx from xxxxx where rownum> 5 is impossible to produce results. So what if you have to do this?
Select * from (select rownum as rn, t. * from xxxx t) where rn> 10;

Author: ERDP Technical Architecture"