sql 每天 oracle,oracle每天一句sql

1、查看SQL语句的解析情况：

SELECT *

FROM V$SYSSTAT

WHERE NAME IN

('parse time cpu', 'parse time elapsed', 'parse count (hard)');

这里"parse time cpu”是系统服务时间，"parse time elapsed"是响应时间，用户等待时间waite time = parse time elapsed - parse time cpu。

2、查看是什么SQL语句解析效率比较低：

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA WHERE ROWNUM<10 ORDER BY PARSE_CALLS;

可以优化这些语句，或者增加Oracle参数SESSION_CACHED_CURSORS的值。

查看使用频率最高的10条sql

select sql_text, executions,sysdate

from (select sql_text,

executions,

rank() over(order by executions desc) exec_rank

from v$sql)

where exec_rank <= 10;

3、根据v$process中的pid值到v$session中找到对应的sid：

SELECT SID, SERIAL#, USERNAME, MACHINE

FROM v$session b

WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid');

根据sid获取sql

select sql_text from v$sqltext a where a.hash_value=(select sql_hash_value from v$session b where b.SID='&sid')

ORDER BY piece ASC;

4、寻找CPU使用过量的session ，找出高CPU利用率的SQL：

SELECT 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 = '&pid'))

ORDER BY piece ASC;

5、查看每个Session的CPU利用情况：

select ss.sid, se.command, ss.value CPU, se.username, se.program

from v$sesstat ss, v$session se

where ss.statistic# in

(select statistic#

from v$statname

where name = 'CPU used by this session')

and se.sid = ss.sid

and ss.sid > 6

order by ss.sid;

6、比较上述Session(第5个)，看那个session的CPU使用时间最多，然后查看该Session的具体情况：

select s.sid, s.event, s.wait_time, w.seq#, q.sql_text

from v$session_wait w, v$session s, v$process p, v$sqlarea q

where s.paddr = p.addr

and s.sid = &p

and s.sql_address = q.address;

得到上述信息后，查看相应操作是否有hash joins 和 full table scans。如果有hash joins 和 full table scans那么必须创建相应的Index或者检查Index是否有效。7、用来查询数据文件、临时文件与表空间对应及数据文件序号：

select ts.tablespace_name, df.file_name, df.file_id, tf.file_name

from dba_tablespaces ts, dba_data_files df, dba_temp_files tf

where ts.tablespace_name = df.tablespace_name(+)

and ts.tablespace_name = tf.tablespace_name(+);

8、根据spid查出正在运行的sql

select b.sid,

b.serial#,

b.status,

b.osuser || ':' || b.terminal || ':' || b.program || '@' ||

b.machine as hostuserapp,

a.piece,

a.sql_text

from v$sqltext_with_newlines a, v$session b

where a.address(+) =

decode(b.sql_hash_value, 0, b.prev_sql_addr, b.sql_address)

and a.hash_value(+) =

decode(b.sql_hash_value, 0, b.prev_hash_value, b.sql_hash_value)

and b.type <> upper('background')

and b.sid in (select b.SID

from v$process a, v$session b

where a.addr = b.PADDR

and a.spid = &spid)

order by b.sid, a.piece;

9、定位消耗资源多的sql

select sql_text from v$sql where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 3000);

10、查询系统资源 (sessions和process连接数情况)

SELECT resource_name,

current_utilization,

max_utilization,

LIMIT,

ROUND(max_utilization / LIMIT * 100) || '%' rate

FROM (SELECT resource_name,

current_utilization,

max_utilization,

TO_NUMBER(initial_allocation) LIMIT

FROM v$resource_limit

WHERE resource_name IN ('processes', 'sessions')

AND max_utilization > 0);

alter system set processes=500 scope=spfile;

alter system set sessions=500 scope=spfile;

然后重启数据库

11、查询当前数据库使用全表扫描的SQL

select a.sid,

a.serial#,

a.username,

a.status,

a.program,

a.machine,

c.sql_text

from v$session a, v$session_wait b, v$sql c

where a.sid = b.sid

and a.sql_hash_value = c.hash_value

and a.sql_address = c.address

and b.event like 'db file scattered read%';

12、查询表空间使用、分配等情况

select (select decode(extent_management,'LOCAL','*',' ') ||

decode(segment_space_management,'AUTO','a ','m ')

from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,

nvl(a.tablespace_name,'UNKOWN')) name,

MB_Allocated MB_Allocated,

MB_Allocated-nvl(Free_MB,0) used,

nvl(Free_MB,0) free,

((MB_Allocated-nvl(Free_MB,0))/

nvl(Max_MB,MB_Allocated))*100 pct_used,

nvl(Max_MB,MB_Allocated) Max_Size,

decode( Max_MB, 0, 0, (MB_Allocated/Max_MB)*100) pct_max_used

from ( select sum(bytes)/1048576 Free_MB,

max(bytes)/1048576 largest,

tablespace_name

from sys.dba_free_space

group by tablespace_name ) a,

( select sum(bytes)/1048576 MB_Allocated,

sum(maxbytes)/1048576 Max_MB,

tablespace_name

from sys.dba_data_files

group by tablespace_name

union all

select sum(bytes)/1048576 MB_Allocated,

sum(maxbytes)/1048576 Max_MB,

tablespace_name

from sys.dba_temp_files

group by tablespace_name )b

where a.tablespace_name (+) = b.tablespace_name

order by 1

/

13、查询创建表空间的原始语句

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) FROM DBA_TABLESPACES TS;

14、查询oracle一张表的数据块上有多少条记录

select dbms_rowid.rowid_block_number(rowid),count(dbms_rowid.rowid_block_number(rowid)) from t group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

------------------------------------ -------------------------------------------

28584 68 －指第28584号块上有68条记录