各类数据库 如何不输出执行结果只显示执行时间 / 执行计划_千里马临时数据库只显示当天的

进行sql优化时有些sql输出巨长，一执行疯狂刷屏。这里列一个小汇总，记录各类数据库如何不输出执行结果只显示执行时间 / 执行计划。

Oracle

set autotrace trace
set timing on
-- 恢复输出
set autottrace off

SqlServer

其实SqlServer基本没有这个需求，SqlServer主要都是用图形化的工具

--实际执行计划
set statistics profile on 
--返回执行时间和CPU时间
set statistics time on
--输出语句物理读和逻辑读数目
set statistics io on

PG

EXPLAIN ANALYZE sql语句

在pg中查看sql的解析时间

van=# set log_parser_stats=true;
SET
van=# set log_planner_stats=true;
SET
van=# set client_min_messages=log;
LOG:  duration: 0.264 ms
SET
van=#  select * from measurement where city_id>1002 and city_id<=1019 order by city_id limit 10 offset 20;
LOG:  PARSER STATISTICS
DETAIL:  ! system usage stats:
!       0.000033 s user, 0.000000 s system, 0.000031 s elapsed
!       [0.001808 s user, 0.002298 s system total]
!       21460 kB max resident size
!       0/0 [0/8] filesystem blocks in/out
!       0/0 [0/666] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [5/0] voluntary/involuntary context switches
LOG:  PARSE ANALYSIS STATISTICS
DETAIL:  ! system usage stats:
!       0.000054 s user, 0.000000 s system, 0.000054 s elapsed
!       [0.001903 s user, 0.002298 s system total]
!       21460 kB max resident size
!       0/0 [0/8] filesystem blocks in/out
!       0/0 [0/666] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [5/0] voluntary/involuntary context switches
LOG:  REWRITER STATISTICS
DETAIL:  ! system usage stats:
!       0.000004 s user, 0.000000 s system, 0.000003 s elapsed
!       [0.001923 s user, 0.002298 s system total]
!       21460 kB max resident size
!       0/0 [0/8] filesystem blocks in/out
!       0/0 [0/666] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [5/0] voluntary/involuntary context switches
LOG:  PLANNER STATISTICS
DETAIL:  ! system usage stats:
!       0.000062 s user, 0.000000 s system, 0.000062 s elapsed
!       [0.001997 s user, 0.002298 s system total]
!       21460 kB max resident size
!       0/0 [0/8] filesystem blocks in/out
!       0/0 [0/666] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [5/0] voluntary/involuntary context switches
LOG:  duration: 0.425 ms
 city_id | logdate | peaktemp | info
---------+---------+----------+------

MySQL

pager cat > /dev/null
-- 恢复输出
pager

一个小例子

mysql> select count(*) from orasup1;
+----------+
| count(*) |
+----------+
|   960896 |
+----------+
1 row in set (0.60 sec)
 
mysql> pager cat > /dev/null
PAGER set to 'cat > /dev/null'
mysql> 
mysql> select count(*) from orasup1;
1 row in set (0.65 sec)
 
mysql> pager
Default pager wasn't set, using stdout.
mysql> 
mysql> select count(*) from orasup1;
+----------+
| count(*) |
+----------+
|   960896 |
+----------+
1 row in set (0.63 sec)

参考 Fun with the MySQL pager command