Shulou(Shulou.com)06/01 Report--

-tablespace utilization-SELECT D.TABLESPACE_NAME, SPACE | |'M' "SUM_SPACE (M)", BLOCKS "SUM_BLOCKS", SPACE-NVL (FREE_SPACE, 0) | |'M' "USED_SPACE (M)", ROUND ((1-NVL (FREE_SPACE, 0) / SPACE) * 100,2) | |'%'"USED_RATE (%)", FREE_SPACE |'M' "FREE_SPACE (M)" FROM (SELECT TABLESPACE_NAME) ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME (+) UNION ALL-if there is temporary tablespace SELECT D.TABLESPACE_NAME, SPACE | |'M' "SUM_SPACE (M)", BLOCKS SUM_BLOCKS USED_SPACE | |'M' "USED_SPACE (M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100,2) | |'%'"USED_RATE (%)", NVL (FREE_SPACE, 0) | |'M' "FREE_SPACE (M)" FROM (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME) ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME (+) ORDER BY 1

-query information about all dependent objects (stored procedures, triggers, etc., based on that table)-select * from dba_dependencies where referenced_owner='USERNAME'

-query the foreign key relations on the table-select A.* from user_constraints A, user_constraints B WHERE b.table_name = 'TABLENAME' and a.constraint_type =' R' and a.r_constraint_name = b.constraint_name

-query all primary and foreign key relationships-select a.table_name main table, b.table_name child table, column_name key, substr (position,1,1) P from user_constraints a, user_constraints b, user_cons_columns c where a.constraint_name = b.r_constraint_name and a.constraint_name = c.constraint_name order by 1, 2, 4

-View tables without primary keys-SELECT * FROM dba_tables A WHERE owner='CCPS' AND NOT EXISTS (SELECT * FROM dba_constraints b WHERE A .table _ name = b.table_name AND b.constraint_type ='P')

-session_cached_cursor and open_cursor usage-SELECT 'session_cached_cursors' PARAMETER, LPAD (VALUE, 5) VALUE, DECODE (VALUE, 0,' n USED / VALUE) | |'% 'USAGE FROM (SELECT MAX (S.VALUE) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME =' session cursor cache count' AND S.STATISTIC# = N.STATISTIC#) (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors') UNION ALL SELECT' open_cursors', LPAD (VALUE, 5), TO_CHAR (100 * USED / VALUE) | |'% 'FROM (SELECT MAX (SUM (S.VALUE)) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME IN (' opened cursors current', 'session cursor cache count') AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID), (SELECT VALUE FROM V$PARAMETER WHERE NAME =' open_cursors')

-query the size of all tables and indexes-select segment_name,sum (bytes) / 1024 size_GB from dba_extents where owner='USERNAME' group by segment_name order by 1024 size_GB from dba_extents where owner='USERNAME' group by segment_name order by 2 desc

-check out the results of oracle automatic tasks-select f.tasktrainnamereporto.typereo.attr1authorattr3 where o.task_id=f.task_id and o.object_id=f.object_id and f.task_id=e.task_id and e.execution_start where o.task_id=f.task_id and o.object_id=f.object_id and f.task_id=e.task_id and e.execution_start > sysdate-1

Select dbms_sqltune.report_auto_tuning_task from dual

-check to see if oracle enables block tracking. If not, all block files are compared during a level 1 backup, so the backup time is no less than a level 0 backup, or even longer. You can enable it by using the following statement: alter system set DB_CREATE_FILE_DEST='/home/oracle/backup' scope=both;-configure the location of the trace file alter database enable block change tracking;-enable block tracking

-View the sql statement being executed-select a.program requester, a.username login oracle user name, a.sid oracleSID, a.SERIALING, a.machine computer name, b.spid operating system ID, c.sql_text executing SQL, c.SQL_ID SQLID from v$session a, v$process b, v$sqlarea c where a.paddr = b.addr and a.sql_hash_value = c.hash_value and a.username is not null

-View locked tables and SQL- SELECT l.session_id sid, s.serialkeeper, l.locked_mode lock mode, l.oracle_username login user, l.os_user_name login machine user name, p.spid operating system ID, s.machine machine name, s.terminal terminal user name, o.object_name locked object name, s.logon_time login database time, q.sql_id SQLID Q.sql_text SQL statements FROM v$locked_object l, all_objects o, v$session smag process p Q WHERE l.object_id = o.object_id and s.paddr = p.addr AND l.session_id = s.sid and s.sql_hash_value = q.hash_value ORDER BY sid, s.serial#

-View oracle implied parameters-SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND ksppinm ='_ optim_peek_user_binds'

Modify: SQL > alter system set "_ optimizer_max_permutations" = 200scope=both sid='*'

-View the locked table-select b.ownerdiary b.objectroomnamemema.sessionroomidpapa.lockedwriting mode from v$locked_object a dbathing objects b where b.object_id = a.object_id

-check that user that process caused a deadlock-select b.usernameauthorb.sidrecoveryb.serialpaperrelogonedtime from v$locked_object a b.sid order by b.logon_time session b where a.session_id = lock

-find the lock table's sid, serial#,os_user_name, machine_name, os processes,terminal, lock's type,mode- SELECT s.sid, s.seriallocks, s.username, s.schemaname, s.osuser, s.process, s.machinemechine. Processing. Terminal, s.logon_time, l.type FROM v$session 's, v$lock l WHERE s.sid = l.sid AND s.username IS NOT NULL ORDER BY sid.

-View locked tables-SELECT l.session_id sid, s.serialtables, l.lockedusers WHERE l.object_id, l.oracleroomusernameauthorauthor.machine, s.terminal, o.object_name, s.logon_time FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial#

-query the executing SQL statement based on the operating system process number-SELECT / * + ORDERED * / sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value), DECODE (sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = 10769) ORDER BY piece ASC

-observe the total number and general classification of waiting events-select a. Waitworthy classrooms, waiting classbooks, waiting classcount (*) from v$event_name a group by wait_class#,wait_class_id,wait_class order by wait_class#

-query latch address-select addr,LATCH#,CHILD#,gets,misses,sleeps from v$latch_children where name = 'cache buffers chains' and rownum

< 21; -----根据latch地址确定数据块----- select dbarfil,dbablk from x$bh where hladdr in (select addr from (select addr from v$latch_children order by sleeps desc) where rownum < 1000); -----根据数据块确认具体对象----- select distinct a.owner,a.segment_name from dba_extents a, (select dbarfil,dbablk from x$bh where hladdr in (select addr from (select addr from v$latch_children order by sleeps desc) where rownum < 20) ) b where a.RELATIVE_FNO = b.dbarfil and a.BLOCK_ID b.dbablk and a.owner='USERNAME'; -----查询数据库中的热点块----- select /*+ rule */a.owner,a.object_name,b.tch from dba_objects a,x$bh b where a.data_object_id=b.obj order by tch desc; -----查看sql历史执行时间----- select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = '14nrwtwftffq5' and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta >

0 order by 1, 2, 3

-BLOCKING TREE- with lk as (select blocking_instance | |'. | | blocking_session blocker, inst_id | |'. | | sid waiter from gv$session where blocking_instance is not null and blocking_session is not null) select lpad (', 2* (level-1)) | | waiter lock_tree from (select * from lk union all select distinct 'root', blocker from lk where blocker not in (select waiter from lk)) connect by prior waiter=blocker start with blocker='root'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.