Oracle performance parameters-classic Common

0. database parameter attributes
Col PROPERTY_NAME format a25
Col PROPERTY_VALUE format a30
Col DESCRIPTION format a100
Select * from database_properties;

Select * from v $ version;

1. Find the SID of the current session, SERIAL #
SELECT Sid, Serial # from v $ session
WHERE Audsid = Sys_Context ('userenv', 'sessionid ');

2. query the OS process ID of the session.
SELECT p. Spid "OS Thread", B. NAME "Name-User", s. Program, s. Sid, s. Serial #, s. OSUSEr, s. Machine
From v $ process p, V $ session s, V $ bgprocess B
WHERE p. Addr = s. Paddr
AND p. Addr = B. Paddr And (s. sid = & 1 or p. spid = & 1)
UNION ALL
SELECT p. Spid "OS Thread", s. Username "Name-User", s. Program, s. Sid, s. Serial #, s. Osuser, s. Machine
From v $ process p, V $ session s
WHERE p. Addr = s. Paddr
And (s. sid = & 1 or p. spid = & 1)
AND s. Username is not null;

3. view the SQL statement that the connection is running based on the sid.
SELECT/* + PUSH_SUBQ */Command_Type, SQL _Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,
Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,
Users_Executing, Loads, First_Load_Time, Invalidations, parse_cils,
Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time,
SYSDATE Finish_Time, '>' | Address SQL _Address, 'n' Status
From v $ sqlarea WHERE Address = (SELECT SQL _Address
From v $ session WHERE Sid = & sid );

4. Find out which processes the object is used
SELECT p. Spid, s. Sid, s. Serial # Serial_Num, s. Username User_Name,
A. TYPE Object_Type, s. Osuser OS _User_Name, a. Owner,
A. OBJECT Object_Name,
Decode (Sign (48-Command), 1, To_Char (Command), 'Action Code # '| To_Char (Command) Action,
P. Program Oracle_Process, s. Terminal, s. Program,
S. Status Session_Status
From v $ session s, V $ access a, V $ process p
WHERE s. Paddr = p. Addr
AND s. TYPE = 'user'
AND a. Sid = s. Sid
AND a. OBJECT = '& obj'
Order by s. Username, s. Osuser

5. Check which user connections are available.
SELECT s. Osuser OS _User_Name, Decode (Sign (48-Command), 1, To_Char (Command ),
'Action Code # '| To_Char (Command) Action,
P. Program Oracle_Process, Status Session_Status, s. Terminal,
S. Program, s. Username User_Name,
S. Fixed_Table_Sequence Activity_Meter, ''Query, 0 Memory,
0 Max_Memory, 0 Cpu_Usage, s. Sid, s. Serial # Serial_Num
From v $ session s, V $ process p
WHERE s. Paddr = p. Addr
AND s. TYPE = 'user'
Order by s. Username, s. Osuser

6. view the resource usage of the corresponding connection according to v. sid.
SELECT n. NAME, v. VALUE, n. CLASS, n. Statistic # from v $ statname n, V $ sesstat v
WHERE v. Sid = & sid
AND v. Statistic # = n. Statistic #
Order by n. CLASS, n. Statistic #

7. query resource-consuming processes (top sessions)
SELECT s. Schemaname Schema_Name, Decode (Sign (48-Command ),
1, To_Char (Command), 'Action Code # '| To_Char (Command) Action, Status Session_Status, s. osuser OS _User_Name, s. sid, p. spid, s. serial # Serial_Num, Nvl (s. username, '[Oracle process]') User_Name,
S. Terminal, s. Program, St. VALUE Criteria_Value
From v $ sesstat St, V $ session s, V $ process p
WHERE St. Sid = s. Sid
AND St. Statistic # = To_Number ('38 ')
AND ('all' = 'all' OR s. Status = 'all ')
AND p. Addr = s. Paddr
Order by St. value desc, p. Spid ASC, s. Username ASC, s. Osuser ASC

8. View lock status
SELECT/* + RULE */Ls. Osuser OS _User_Name, Ls. Username User_Name, Decode (Ls. TYPE,
'Rw ', 'row wait enqueue lock', 'Tm', 'dml enqueue lock', 'tx ', 'transaction enqueue lock', 'ul ', 'User supplied lock') Lock_Type, o. object_Name OBJECT, Decode (Ls. lmode, 1, NULL, 2, 'row Share ', 3, 'row Exclusive ',
4, 'shares', 5, 'share Row Exclusive ', 6, 'clusive', NULL) Lock_Mode, o. owner, Ls. sid, Ls. serial # Serial_Num, Ls. id1, Ls. id2 FROM Sys. dba_Objects o,
(SELECT s. osuser, s. username, l. TYPE, l. lmode, s. sid, s. serial #, l. id1, l. id2 from v $ session s, V $ lock l
WHERE s. Sid = l. Sid) Ls
WHERE o. Object_Id = Ls. Id1
AND o. Owner <> 'sys'
Order by o. Owner, o. Object_Name;

9. view the waiting status
SELECT Ws. CLASS, Ws. COUNT, SUM (Ss. VALUE) Sum_Value
From v $ waitstat Ws, V $ sysstat Ss
WHERE Ss. name in ('db block gets', 'consistent gets ')
Group by Ws. CLASS, Ws. COUNT;

10. Check the process/session Status
SELECT p. Pid, p. Spid, s. Program, s. Sid, s. Serial #
From v $ process p, V $ session s
WHERE s. Paddr = p. Addr;

11. Ask who has blocked a session (10 Gb)
SELECT Sid, Username, Event, Blocking_Session, Seconds_In_Wait, Wait_Time
From v $ session
WHERE State IN ('waiting ')
AND Wait_Class! = 'Idle ';

12. query session Blocking
Col user_name format a32
SELECT/* + rule */Lpad ('', Decode (l. Xidusn, 0, 3, 0) | l. Oracle_Username User_Name,
O. Owner, o. Object_Name, s. Sid, s. Serial #
From v $ locked_Object l, Dba_Objects o, V $ session s
WHERE l. Object_Id = o. Object_Id
AND l. Session_Id = s. Sid
Order by o. Object_Id, Xidusn DESC;
Col username format a15
Col lock_level format a8
Col owner format a18
Col object_name format a32
SELECT/* + rule */s. Username, Decode (l. TYPE, 'Tm ', 'table lock', 'tx', 'row lock', NULL) Lock_Level,
O. Owner, o. Object_Name, s. Sid, s. Serial #
From v $ session s, V $ lock l, Dba_Objects o
WHERE l. Sid = s. Sid
AND l. Id1 = o. Object_Id (+)
AND s. Username is not null;

13. Waiting events and session information/seeking session waiting and session information
SELECT Se. Sid, s. Username, Se. Event, Se. Total_Waits, Se. Time_Waited, Se. Average_Wait
From v $ session s, V $ session_Event Se
WHERE s. Username IS NOT NULL
AND Se. Sid = s. Sid
AND s. Status = 'active'
AND Se. Event not like '% SQL * Net %'
Order by s. Username;
SELECT s. Sid, s. Username, Sw. Event, Sw. Wait_Time, Sw. State, Sw. Seconds_In_Wait
From v $ session s, V $ session_Wait Sw
WHERE s. Username IS NOT NULL
AND Sw. Sid = s. Sid
AND Sw. Event not like '% SQL * Net %'
Order by s. Username;

14. Find the file_id/block_id waiting for the session
Col event format a24
Col p1text format a12
Col p2text format a12
Col p3text format a12
SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3
From v $ session_Wait
WHERE Event not like '% SQL %'
AND Event not like '% rdbms %'
AND Event not like '% mon %'
Order by Event;
Select name, Wait_Time
From v $ latch l
Where exists (SELECT 1
FROM (SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3
From v $ session_Wait
WHERE Event not like '% SQL %'
AND Event not like '% rdbms %'
AND Event not like '% mon %') x
WHERE x. P1 = l. Latch #);

15. Find the object waiting for the session
Col owner format a18
Col segment_name format a32
Col segment_type format a32
SELECT Owner, Segment_Name, Segment_Type
FROM Dba_Extents
WHERE File_Id = & File_Id
AND & Block_Id BETWEEN Block_Id AND Block_Id + Blocks-1;

16. Find a process and track it.
SELECT s. Sid, s. Serial #
From v $ session s, V $ process p
WHERE s. Paddr = p. Addr
AND p. Spid = & 1;
Exec Dbms_System.Set_ SQL _Trace_In_Session (& 1, & 2, TRUE );
Exec Dbms_System.Set_ SQL _Trace_In_Session (& 1, & 2, FALSE );

17. Find the tracking file of the current session
SELECT P1.VALUE | '/' | P2.VALUE | '_ ora _' | p. Spid | '. ora' Filename
From v $ process p, V $ session s, V $ parameter P1, V $ parameter P2
WHERE P1.NAME = 'user _ dump_dest'
AND P2.NAME = 'instance _ name'
AND p. Addr = s. Paddr
AND s. Audsid = Userenv ('sessionid ')
AND p. Background IS NULL
AND Instr (p. Program, 'cjq') = 0;

18. Find the locked object
SELECT Do. Object_Name, Session_Id, Process, Locked_Mode
From v $ locked_Object Lo, Dba_Objects Do
WHERE Lo. Object_Id = Do. Object_Id;

19. DB_Cache suggestions
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
From v $ DB_CACHE_ADVICE
WHERE name = 'default'
AND block_size = (SELECT value from v $ parameter where name = 'db _ block_size ')
AND advice_status = 'on ';

20. view various SGA parameters: SGA and SGASTAT.
Select substr (name, 1, 10) name, substr (value, 1, 10) value
From v $ parameter where name = 'Log _ buffer ';

Select * from v $ sgastat;

Select * from v $ sga;

Show parameters area_size # view the memory parameters of each region. sort_area is used as the sorting parameter;
 
Recommended parameter values for various views: V $ DB_CACHE_ADVICE, V $ SHARED_POOL_ADVICE), about PGA
There are also related views such as V $ PGA_TARGET_ADVICE.

21. The memory usage is locked in the physical memory:
AIX 5L (AIX 4.3.3 or later)
Logon aix as root
Cd/usr/samples/kernel
./Vmtune (information below) v_pingshm is already 1
./Vmtune-S 1
Then the oracle user modifies lock_sga = true in initSID. ora.
Restart the database

HP UNIX
Root Login
Create the file "/etc/privgroup": vi/etc/privgroup
Add line "dba MLOCK" to file
As root, run the command "/etc/setprivgrp-f/etc/privgroup ":
$/Etc/setprivgrp-f/etc/privgroup
Oracle user lock_sga = true in initSID. ora
Restart the database

SOLARIS (solaris2.6 or above)
The hidden parameter use_ism = true is used by default for databases later than 8i. The system automatically locks the SGA instance in the memory and does not need to set it.
Lock_sga. If lock_sga = true is set, an error will be returned when non-root users start the database.

WINDOWS (not very useful)
Lock_sga = true cannot be set. You can set pre_page_sga = true to enable all
Storage page load, which may play a role.

22. Memory Parameter Adjustment
Data Buffer hit rate
Select value from v $ sysstat where name = 'physical reads ';

Select value from v $ sysstat where name = 'physical reads direct ';

Select value from v $ sysstat where name = 'physical reads direct (lob )';

Select value from v $ sysstat where name = 'consistent gets ';

Select value from v $ sysstat where name = 'db block gets ';

The hit rate is calculated as follows:
Make x = physical reads direct + physical reads direct (lob)
Hit rate = 100-(physical reads-x)/(consistent gets + db block gets-x) * 100
If the hit rate is lower than 90%, you should adjust the application to determine whether to increase the data buffer;

Hit rate of the Shared Pool
Select sum (pinhits)/sum (pins) * 100 "hit radio" from v $ librarycache;

If the hit rate of the Shared Pool is lower than 95%, you should consider adjusting the application (usually not using bind var) or increasing the memory;

Sorting
Select name, value from v $ sysstat where name like '% sort % ';

If we find that the proportion of sorts (disk)/(sorts (memory) + sorts (disk) is too high, it usually means
Sort_area_size has a small amount of memory. You can adjust the corresponding parameters.

About log_buffer
Select name, value from v $ sysstat
Where name in ('redo entries', 'redo buffer allocation retries ');

If the ratio of redo buffer allocation retries/redo entries exceeds 1%, we can consider increasing log_buffer.