数据库性能优化---如何避免磁盘排序

SQL语句在order by,group by 或其他操作的时候会产生临时表或临时结果集。

这些临时结果集默认会优先在内存里面排序或存放（就是所谓的内存排序），如果排序内存不够用了，则会放入临时表空间，其实这个时候发生的就是磁盘排序。

Dbaccess sysmaster
select
trim(n.dbsname) tab_type, trim(n.owner) users,trim(n.tabname) tab_name,
dbinfo('UTC_TO_DATETIME',i.ti_created) index_createtime,
trim(dbinfo('DBSPACE', i.ti_partnum)) dbspace,
format_units(i.ti_nptotal,i.ti_pagesize) total_size,i.ti_nrows
FROM sysmaster:systabnames n, sysmaster:systabinfo i
WHERE (sysmaster:bitval(i.ti_flags, 32) = 1  OR sysmaster:bitval(i.ti_flags, 64) = 1
OR sysmaster:bitval(i.ti_flags, 128) = 1)        AND i.ti_partnum = n.partnum
order by 1,3

 

        参数DS_NONPDQ_QUERY_MEM用来设置排序内存的大小，因为DS_NONPDQ_QUERY_MEM最大只能是DS_TOTAL_MEMORY的1/4，所以要设置比较大的DS_NONPDQ_QUERY_MEM，就首先要设置较大的DS_TOTAL_MEMROY，以避免发生所谓的磁盘排序。

 

通如下SQL可以查询是否临时表都在tmpdbs上，如果发现有临时表不是在tmpdbs上的就需要排查并解决了。

select t.dbsname, t.tabname,
hex(p.partnum) partition, hex(p.flags) pflags
from sysmaster:systabnames t, sysmaster:sysptnhdr p
where t.partnum = p.partnum
and bitval(p.flags,32) = 1 -- Looking for temp tables
and trunc(p.partnum / 1048576) -- Filter: Only temps not in
in (select dbsnum -- temp dbspace
from sysdbspaces where is_temp = 0)
order by dbsname, tabname, partition