View full table Scanning table Select sw.SID, E.SEGMENT_NAME, E.SEGMENT_TYPE FROM DBA_EXTENTS E, V $ session_wait sw where sw.p2 between E.BLOCK_ID-1 and E.BLOCK_ID blocks and sw.event = 'DB File Scattered read 'and E.file_id = sw.p1;
View idle table space: table space name, total, maximum continuous air idle block, idle block number Select TableSpace_name, SUM (BYTES) / 1024/1024 SUM_MB, MAX (BYTES) / 1024/1024 MAX_MB, Count (*) from DBA_FREE_SPACEGROUP By TableSpace_name;
View the size of the database, and space usage COL TABLESPACAT A20 SELECT B.FILE_ID file ID, B.TABLESPACE_NAME table space, B.File_name physical file name, B.bytes total byte number, (B.bytes-sum (NVL A.bytes, 0))) Use, SUM (NVL (A.BYTES, 0))), SUM (NVL (A.Bytes, 0)) / (B.bytes) * 100 Remaining percentage from DBA_FREE_SPACE A, DBA_DATA_FILES B Where a.file_id = b.file_id group by b.tablespace_name, b.file_name, b.File_ID, B.BYTES ORDER BY B.TABLESPACE_NAME / - DBA_FREE_SPACE_NAME / - DBA_FREE_SPACE - Table Space Remaining Space Status - DBA_DATA_FILES - Data File Space Occupancy
View 回 段: 回 名, current byte number, extended number, maximum expansion SELECT TRIM (segment_name), bytes, extents, max_extents from dba_segments where segment_type = 'rollback' View a table space is the largest space segment: the owner of the object, section name, the number of bytes select owner, trim (segment_name), bytes / 1024/1024 from dba_segments where tablespace_name = 'BILLDATA1' and bytes> 10000000 order by bytes desc select sEGMENT_NAME, PARTITION_NAME, sEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, bytes / (1024 * 1024) as "size (M)" from user_segmentswhere segment_name = upper ( '& table_name')
See what objects in a table space Select * from user_segments where tablespace_name = 'billdata'; view a user's session: Status, SID, Serial #, SPID (process number), user name, execution program, machine name SELECT S.Status, SID, S.Serial #, SPID, S.USERNAME, S.Program, Machine, ModuleFrom V $ Session S, V $ process Pwhere s.paddr = p.addrand S.Username = 'lbas' - and S.Status = 'Active'and Machine Like'% & cccnc% 'Order by logon_time View the lock in the current database: SID, Serial #, user name, machine name, lock object, lock type, operating system process number Spidselect S.SID, S.Serial #, S.USERNAME, Machine, A. Object_name, Decode (Locked_Mode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'ROW EXCLUSIVE', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') Lock_Type, Paddr, P.Addr, P.spidFrom V $ Session S, V $ located_Object L, All_Objects a, V $ Process Pwhere S. SID = L.Session_idand P.Addr = Paddrand L.Object_id = a.Object_ID
View Oracle Process Process Number: SELECT SPID From V $ Processwhere Addr in (SELECT PADDR FROM V $ SESSION WHERE SID = & a) If the operating system is UNIX, copy the SPID to log in to UNIX with orract, use the command Determine the process: PS-EF | GREP SPID If you have the process number copy the use command to kill the process: Kill -9 SPID
View SQL: Session Status, SID, SID, SERIAL #, execute program, SQLSELECT S.STATUS, SID, S.SERIAL #, S.PROGRAM, V.SQL_TEXT from V $ Session S, V $ SQLText vwhere s.sql_hash_value = v.hash_value and v.sql_text limited '% & a%'
View environment variable: SELECT * FROM V $ NLS_PARAMETERS
View timer: JOB number, execute content, suspend execution, date of the last execution, date of the last execution (seconds), the date of execution of the next time, the date (second), failure SELECT JOB, WH , Broken, Last_Date, Last_sec, Next_Date, Next_Sec, FailuresFrom User_Jobs
Handmade order returning segment contraction (RBS01 fills in the actual rollback segment name): Alter Rollback Segment RBS01 Shrink
View all or most sorting in memory use disk sorting and memory sorting ratio Select A.Value "Disk Sorts", B.Value "Memory Sorts", ROUND (A.Value / (B.Value a.value) * 100, 2) "Disk Sort Percentage" from v $ sysstat a, v $ sortstat bwhere a.name = 'sorts (disk)' and b.name = 'sorts (memory)' --------- -------------------------------------------------- ---------------------- How to determine the highest price query / ******************** *********************************************************** ******* The high-cost judgment of statement is based on: a), consuming I / O resource (maximum disk read) B), consuming memory and CPU (largest buffer_gets) ****** *********************************************************** **************** / Find the script of I / O largest statement SELECT B.SQL_Text "Statement", A.Disk_reads "Disk Reads", A. executions "EXECUTIONS", a.disk_reads / decode (a.executions, 0,1, a.executions) "RATIO", c.usernameFROM v $ sqlarea a, v $ sqltext_with_newlines b, dba_users cWHERE a.parsing_user_id = c.user_id AND A.address = B.Address and a.disk_reads> & threshold_disk_reads - Disk Read Order by A.Disk_reads DESC, B.PIECE; Find the script of the most statements that consume memory (approximately CPU) Sales SELECT B.SQL_Text "Statement", A .buffer_gets "buffer gets", a.executions "EXE CUTIONS ", A.Buffer_gets / decode (a.executions, 0, 1, a.executions)" Ratio ", C.usenameFrom V $ SQLAREA A, V $ SQLText_with_Newlines B, DBA_USERS CWHERE A.PARSING_USER_ID = C.user_id and A. Address = B.Address and a.buffer_gets> & threshod_buffer_getsorder by A.Buffer_gets Desc, B.PIECE
How to optimize the data buffer cache / * data buffer is the memory structure of the Oracle System Global Zone (SGA). The Oracle server can directly access the cache data blocks directly from the memory data buffer instead of reading them from the disk, which reduces the performance of I / O to improve performance * / 1, how to calculate data buffer hit rate
2, increase db_block_buffers
Measure and optimize the performance of cache
-------------------------------------------------- -----------------------------
select tablespace_name, table_name, next_extentfrom dba_tables outerwhere not exists (select 'X' from sys.dba_free_space inner where outer.tablespace_name = inner.tablespace_name and bytes> = next_extent); - Specifies the operation more cpu / * parallel (kl 5) parallel (s 5) Parallel (A 5) Parallel (C 5) Parallel (SA 5) * / - format Description / * Parallel can not have spaces, the front of the front is a table name or The alias of the table, the number of the rear is the number of CPUs used - this statement is added after SELECT. Before this parameter, you should add the following to modify the session command EXEC ('ALTER Session Enable Parallel DML');
