主页 / user-194130

Mikhail Aksenov's questions

Martin Hope
Mikhail Aksenov
Asked: 2021-01-22 04:36:03 +0800 CST
  • 1

我有使用 dbms_job 包的 Oracle 工作。我看到它变坏了：

Select job,what,BROKEN from dba_jobs; 
-------- ------------ -----------
1   statspack.snap; Y

我想知道它坏的原因

计划作业的 ASAIK 我们有 dba_scheduler_job_log 视图，我们可以在其中看到失败原因，但是 dbms_job 作业呢？

oracle jobs
Martin Hope
Mikhail Aksenov
Asked: 2020-11-10 06:29:13 +0800 CST
  • 10

我有一个 SQL Server 2014 实例 (12.0.2000.8) 和一个相当复杂的 SELECT 语句，大约有 20 个连接。此查询在 PostgreSQL、Oracle 和其他数据库上的相同数据集上运行良好，整个执行大约需要 1 分钟。

但在 SQL Server 上大约需要 40 分钟。我试图查看执行计划并开始等待……我试图通过从应用程序会话中执行查询来获取执行计划，但没有执行计划。

然后我得到了查询并在SQL Server Management Studio中询问“显示估计的执行计划”，我也开始等待。因此，看起来仅仅构建执行计划就需要花费太多时间。所有统计信息都使用“exec sp_updatestats”收集，我在 sys.stats 中检查了它——一切看起来都很好。所有指标都已到位。

我评论了所有加入并开始一一取消评论，并且

  SET STATISTICS TIME ON

表明每个未注释的连接都需要更长的时间来解析，例如 13 次连接的时间：

SQL Server parse and compile time:
   CPU time = 32250 ms, elapsed time = 32729 ms.

所以，这绝对是一个解析问题。

select count(*) from sys.index_columns
where object_id in (OBJECT_ID('tables_names'),...')

说有 128 列，当

select * from sys.indexes
where object_id in (OBJECT_ID('tables_names'),...')

返回具有 HEAP、CLUSTERED、NONCLUSTERED 索引的 43 行。

你能推荐看什么吗？为什么解析这么多？

更新：感谢“打破查询”和“使用 FORCE ORDER 提示”，但此 SQL 是由我们的应用程序生成的，因此使用应用程序逻辑可能需要付出很多努力，但在一般情况下他们应该是一个很好的解决方案。

第二次更新：应用 SP3 完成了整个工作 - 整个执行查询花费了不到一秒钟的时间。性能提高了两千倍:)

sql-server sql-server-2014
Martin Hope
Mikhail Aksenov
Asked: 2020-10-30 09:34:13 +0800 CST
  • 0

我们有 Oracle 18c 和 RMAN：

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+FRA';

我们也有增量备份。备份脚本中没有“备份当前控制文件”，但每次我们进行备份时都会备份控制文件。有人可以告诉为什么“DELETE OBSOLETE”在仍然需要数据库恢复时会删除自动备份控制文件的所有副本（除了最新的）吗？比如，WTF？例如，如果控制文件是多余的，为什么 RMAN 不会删除相关的备份集。无法得到它。是的 - 我们有大量没有控制文件的所谓“备份”。

TSPITR 失败并显示：

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/29/2020 17:39:13
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore
oracle rman
Martin Hope
Mikhail Aksenov
Asked: 2020-10-16 07:43:19 +0800 CST
  • 0

有一个包含大量查询的应用程序，其中一个导致 ORA-1722

检查：我打开会话跟踪，如：

alter session set events '10046 trace name context forever, level 12'

并对数据库说

SELECT TO_NUMBER('asdf') FROM DUAL;

我实际上在客户端收到 ORA-1722，但看不到该查询以错误代码完成：

PARSING IN CURSOR #140193170131176 len=34 dep=0 uid=9 oct=3 lid=9 tim=24633865068083 hv=3356444932 ad='d25d6b00' sqlid='cmpv4d740yk84'
SELECT TO_NUMBER('asdf') FROM DUAL
END OF STMT
PARSE #140193170131176:c=2315,e=2207,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=24633865068082
EXEC #140193170131176:c=27,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=24633865068204
WAIT #140193170131176: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=24633865068275
FETCH #140193170131176:c=19,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=24633865068331
STAT #140193170131176 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 str=1 time=1 us cost=2 size=0 card=1)'
WAIT #140193170131176: nam='SQL*Net break/reset to client' ela= 3 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=24633865068482
WAIT #140193170131176: nam='SQL*Net break/reset to client' ela= 27988 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=24633865096496
WAIT #140193170131176: nam='SQL*Net message from client' ela= 537077 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=24633865633643
CLOSE #140193170131176:c=8,e=9,dep=0,type=0,tim=24633865633723
PARSE #140193170129200:c=32,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=24633865633836
BINDS #140193170129200:

 Bind#0
  oacdty=01 mxl=32767(32767) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1400010 frm=01 csi=873 siz=32767 off=0
  kxsbbbfp=7f8143f31000  bln=32767  avl=00  flg=05
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1400000 frm=01 csi=873 siz=24 off=0
  kxsbbbfp=7f814424b060  bln=22  avl=00  flg=05
WAIT #140193170129200: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=24633865634181
EXEC #140193170129200:c=336,e=336,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=24633865634210
XCTEND rlbk=0, rd_only=1, tim=24633865634250

如何检测导致 ORA-1722（或任何其他逻辑/语法问题）的查询？

oracle trace
Martin Hope
Mikhail Aksenov
Asked: 2020-03-22 23:49:14 +0800 CST
  • 0

我有一堆asmdisk：

/dev/oracleasm/disks/DATA01

/dev/oracleasm/disks/DATA02

..

/dev/oracleasm/disks/DATA07

可能，它们被创建为：

oracleasm createddisk DATA01 /dev/sda1

oracleasm createddisk DATA01 /dev/sda2

但我不确定。

如何为创建的 ASM 磁盘定义依赖块设备？如何确定 DATA01 是由 /dev/sda1 组成的？

oracle oracle-asm
Martin Hope
Mikhail Aksenov
Asked: 2020-02-07 06:44:44 +0800 CST
  • 0

我在 Sybase 中的排序顺序为： 45 190 altdict - 替代（小写优先）字典排序我是否正确，替代意味着“aAbBcC..zZ”排序？

我应该怎么做才能将“ORDER BY”更改为 'ABC..YZabc..yz' ？选择什么样的排序？

哪里可以改？在会话中？在 SQL 语句中？据我所知，我需要更改整个服务器的排序顺序并重建索引。但是有一堆不同所有者和不同用户的数据库，这似乎有点极端

order-by collation
Martin Hope
Mikhail Aksenov
Asked: 2019-12-18 01:57:51 +0800 CST
  • 0

我正在尝试使用“barman-wal-archive”找不到解决问题的位置或如何调试它，无论如何有一个全新的 barman 2.10 并且它是日志：

2019-12-16 10:44:08,908 [16653] barman.server INFO: Received file '0000000100000004000000A3' with checksum 'e3fced48b20b874fc34e89dc0ce87adf' by put-wal for server 'postgres-server' (SSH host: 192.3.2.1)
2019-12-16 10:44:08,913 [16653] barman.server ERROR: Impossible to write already existing file '0000000100000004000000A3' in put-wal for server 'postgres-server' (SSH host: 192.3.2.1)
2019-12-16 10:44:13,132 [16670] barman.server INFO: Received file '0000000100000004000000A3' with checksum 'e3fced48b20b874fc34e89dc0ce87adf' by put-wal for server 'postgres-server' (SSH host: 192.3.2.1)
2019-12-16 10:44:13,137 [16670] barman.server ERROR: Impossible to write already existing file '0000000100000004000000A3' in put-wal for server 'postgres-server' (SSH host: 192.3.2.1)
2019-12-16 10:45:02,234 [16725] barman.wal_archiver INFO: Found 1 xlog segments from file archival for postgres-server. Archive all segments in one run.
2019-12-16 10:45:02,234 [16725] barman.wal_archiver INFO: Archiving segment 1 of 1 from file archival: postgres-server/0000000100000004000000A3
2019-12-16 10:45:16,625 [16743] barman.server INFO: Received file '0000000100000004000000A3' with checksum 'e3fced48b20b874fc34e89dc0ce87adf' by put-wal for server 'postgres-server' (SSH host: 192.3.2.1)
2019-12-16 10:45:16,638 [16743] barman.cli ERROR: [Errno 13] Permission denied
See log file for more details.
Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/barman/cli.py", line 1355, in main
    p.dispatch(pre_call=global_config)
  File "/usr/lib/python2.7/site-packages/argh/helpers.py", line 55, in dispatch
    return dispatch(self, *args, **kwargs)
  File "/usr/lib/python2.7/site-packages/argh/dispatching.py", line 174, in dispatch
    for line in lines:
  File "/usr/lib/python2.7/site-packages/argh/dispatching.py", line 277, in _execute_command
    for line in result:
  File "/usr/lib/python2.7/site-packages/argh/dispatching.py", line 231, in _call
    result = function(namespace_obj)
  File "/usr/lib/python2.7/site-packages/barman/cli.py", line 912, in put_wal
    server.put_wal(stream)
  File "/usr/lib/python2.7/site-packages/barman/server.py", line 1904, in put_wal
    fsync_file(item.path)
  File "/usr/lib/python2.7/site-packages/barman/utils.py", line 332, in fsync_file
    os.fsync(file_fd)
OSError: [Errno 13] Permission denied
2019-12-16 10:45:20,840 [16759] barman.server INFO: Received file '0000000100000004000000A3' with checksum 'e3fced48b20b874fc34e89dc0ce87adf' by put-wal for server 'postgres-server' (SSH host: 192.3.2.1)
2019-12-16 10:45:20,845 [16759] barman.server ERROR: Impossible to write already existing file '0000000100000004000000A3' in put-wal for server 'postgres-server' (SSH host: 192.3.2.1)
2019-12-16 10:45:25,059 [16776] barman.server INFO: Received file '0000000100000004000000A3' with checksum 'e3fced48b20b874fc34e89dc0ce87adf' by put-wal for server 'postgres-server' (SSH host: 192.3.2.1)
2019-12-16 10:45:25,064 [16776] barman.server ERROR: Impossible to write already existing file '0000000100000004000000A3' in put-wal for server 'postgres-server' (SSH host: 192.3.2.1)
2019-12-16 10:46:01,647 [16827] barman.wal_archiver INFO: Found 1 xlog seg

postgres-server.conf

[postgres-server]
description =  "Saint Petersburg PostgreSQL server to test backups"
conninfo = host=192.3.2.1 user=barman dbname=postgres
backup_method = postgres
# backup_method = rsync
archiver = on

postgresql.conf

archive_mode = on
wal_level = 'replica'
archive_command = 'barman-wal-archive barman-server postgres-server %p'

顺便说一句，当我在酒保服务器上发出 barman-wal-archive 时，它​​会要求输入密码：

[barman@barman-server ~]$ barman-wal-archive --test barman-server postgres-server DUMMY
barman@ldn-postgres-barman's password:
Ready to accept WAL files for the server postgres-server

[postgres@postgres-server ~] barman-wal-archive --test barman-server postgres-server DUMMY
Ready to accept WAL files for the server postgres-server

一些postgres日志：

2019-12-16 19:06:50.074 MSK,,,14552,,5df79de2.38d8,197,,2019-12-16 18:08:18 MSK,,0,LOG,00000,"archive command failed with exit code 1","The failed archive command was: barman-wal-archive barman-server postgres-server pg_wal/0000000100000004000000A3",,,,,,,,""
2019-12-16 19:06:54.306 MSK,,,14552,,5df79de2.38d8,198,,2019-12-16 18:08:18 MSK,,0,LOG,00000,"archive command failed with exit code 1","The failed archive command was: barman-wal-archive barman-server postgres-server pg_wal/0000000100000004000000A3",,,,,,,,""
2019-12-16 19:06:58.509 MSK,,,14552,,5df79de2.38d8,199,,2019-12-16 18:08:18 MSK,,0,LOG,00000,"archive command failed with exit code 1","The failed archive command was: barman-wal-archive barman-server postgres-server pg_wal/0000000100000004000000A3",,,,,,,,""
2019-12-16 19:06:58.509 MSK,,,14552,,5df79de2.38d8,200,,2019-12-16 18:08:18 MSK,,0,WARNING,01000,"archiving write-ahead log file ""0000000100000004000000A3"" failed too many times, will try again later",,,,,,,,,""
EXCEPTION: [Errno 13] Permission denied
See log file for more details.
ERROR: Remote 'barman put-wal' command has failed!
ERROR: Impossible to write already existing file '0000000100000004000000A3' in put-wal for server 'postgres-server' (SSH host: 192.3.2.1)
ERROR: Remote 'barman put-wal' command has failed!
ERROR: Impossible to write already existing file '0000000100000004000000A3' in put-wal for server 'postgres-server' (SSH host: 192.3.2.1)
ERROR: Remote 'barman put-wal' command has failed!

这件事是我看到第一个 WAL 被接收，然后整个过程就停留在第一个 WAL 上。并且再次重现相同的错误消息？如何定义缺少哪些权限？

我将 /usr/lib/python2.7/site-packages/* 的完全访问权限授予了酒保服务器上的所有用户。酒保权限似乎是文件，因为 WAL 可以由酒保创建。

我在配置中错过了什么？

试过：

[barman@barman-server ~]$ barman switch-wal --force --archive postgres-server
The WAL file 0000000100000004000000A8 has been closed on server 'postgres-server'
Waiting for the WAL file 0000000100000004000000A8 from server 'postgres-server' (max: 30 seconds)
Processing xlog segments from file archival for postgres-server
        0000000100000004000000A7
Processing xlog segments from file archival for postgres-server
        0000000100000004000000A7
Processing xlog segments from file archival for postgres-server
        0000000100000004000000A7
Processing xlog segments from file archival for postgres-server
        0000000100000004000000A7
ERROR: The WAL file 0000000100000004000000A8 has not been received in 30 seconds

它说 - “未收到”，文件在 wal 目录中，但过程因错误而中断。我们有 4 行“为 postgres-server 处理文件归档中的 xlog 段”是否可以？

更新。我安装了 cifs (samba) 并且酒保想使用它。这是与此处相同的未解决问题：https ://sourceforge.net/p/pgbarman/tickets/80/

postgresql barman
Martin Hope
Mikhail Aksenov
Asked: 2019-12-14 05:56:42 +0800 CST
  • 2

Redhat 上有 Postgres 9.6 我想解释分析慢查询（相当复杂的查询），它需要太多时间。我与 EXPLAIN ANALYZE 的会话消耗了一个 CPU 的 100%，即使 24 小时后也没有完成。如果 Postgres 无法构建执行计划，如何分析问题？

postgresql performance