我有一个看起来像这样的数据集。
其中business_day
指示是否是transaction_created_date
工作日。我试图line_amount
对假期或周末发生的值进行求和,以便将其添加到下一个工作日,如下所示:
本质上,如果我可以捕获下一个工作日,business_day = 0
那么我就可以对分区进行求和。
这适用于 Windows 上的 PostgreSQL 16。
我使用 archive_command = 'copy "%p" "C:\PostgreSQL\16\data\pg_wal\archive_status\%f"' 打开 archive_mode 并将 archive_timeout 设置为 3600
执行了select pg_switch_wal(); 几次(Oracle 的 ALTER SYSTEM SWITCH LOGFILE??)
现在,我在 pg_wal 文件夹中生成了 16 MB 的文件。我的 pg_wal\archive_status 文件夹中也有文件。pg_wal 和 pg_wal\archive_status 中的一些文件是相似的。pg_wal\archive_status 中的一些文件,例如大小为 16MB 的 00000001000000000000007A 和大小为 0 KB 的 00000001000000000000007A.done。
我正在努力理解这一切。来自Oracle,我了解归档日志和重做日志。我认为 pg_wal 中的文件是重做日志,而 pg_wal\archive_status 中的文件是归档日志?当我执行select pg_switch_wal();时 ,我正在切换重做日志并且旧日志已存档?
我现在的问题是
谢谢
我正在考虑以下场景:
*----------- Physical Processor 0
-*---------- Physical Processor 1
--*--------- Physical Processor 2
---*-------- Physical Processor 3
----*------- Physical Processor 4
-----*------ Physical Processor 5
------*----- Physical Processor 6
-------*---- Physical Processor 7
--------*--- Physical Processor 8
---------*-- Physical Processor 9
----------*- Physical Processor 10
-----------* Physical Processor 11
Logical Processor to Socket Map:
******------ Socket 0
------****** Socket 1
Logical Processor to NUMA Node Map:
************ NUMA Node 0
但我看到的一个症状是,当实例 1运行一组密集查询时,CPU 运行在 50% 上,单核使用 100%/很少的 CPU,交替出现:
虽然这本身没问题,但此时实例 2的性能突然变得很糟糕,甚至失去了与数据库的连接。
有谁有想法或提示在哪里查看以进一步缩小这个问题的范围?请随时询问您需要的任何信息。:-)
编辑: @ErikDarling 的 vSphere 配置:
错误日志输出:
2023-09-05 23:15:51.95 Server Microsoft SQL Server 2019 (RTM-CU22) (KB5027702) - 15.0.4322.2 (X64)
Jul 27 2023 18:11:00
Copyright (C) 2019 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
2023-09-05 23:15:51.95 Server UTC adjustment: 2:00
2023-09-05 23:15:51.95 Server (c) Microsoft Corporation.
2023-09-05 23:15:51.95 Server All rights reserved.
2023-09-05 23:15:51.95 Server Server process ID is 4852.
2023-09-05 23:15:51.95 Server System Manufacturer: 'VMware, Inc.', System Model: 'VMware7,1'.
2023-09-05 23:15:51.95 Server Authentication mode is MIXED.
2023-09-05 23:15:51.95 Server Logging SQL Server messages in file 'D:\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2023-09-05 23:15:51.95 Server The service account is 'NT Service\MSSQLSERVER'. This is an informational message; no user action is required.
2023-09-05 23:15:51.95 Server Registry startup parameters:
-d D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf
-e D:\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2023-09-05 23:15:51.95 Server Command Line Startup Parameters:
-s "MSSQLSERVER"
2023-09-05 23:15:53.75 Server SQL Server detected 2 sockets with 6 cores per socket and 6 logical processors per socket, 12 total logical processors; using 12 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2023-09-05 23:15:53.75 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2023-09-05 23:15:53.76 Server Detected 92159 MB of RAM. This is an informational message; no user action is required.
2023-09-05 23:15:53.76 Server Using conventional memory in the memory manager.
2023-09-05 23:15:53.76 Server Page exclusion bitmap is enabled.
2023-09-05 23:15:53.87 Server Buffer Pool: Allocating 4194304 bytes for 2621440 hashPages.
2023-09-05 23:15:54.03 Server Default collation: Latin1_General_CI_AS (us_english 1033)
2023-09-05 23:15:54.03 Server Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.
2023-09-05 23:15:54.15 Server Buffer pool extension is already disabled. No action is necessary.
2023-09-05 23:15:54.32 Server Query Store settings initialized with enabled = 1,
2023-09-05 23:15:54.33 Server The maximum number of dedicated administrator connections for this instance is '1'
2023-09-05 23:15:54.33 Server This instance of SQL Server last reported using a process ID of 4524 at 05.09.2023 23:15:29 (local) 05.09.2023 21:15:29 (UTC). This is an informational message only; no user action is required.
2023-09-05 23:15:54.34 Server Node configuration: node 0: CPU mask: 0x000000000000003f:0 Active CPU mask: 0x000000000000003f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2023-09-05 23:15:54.34 Server Node configuration: node 1: CPU mask: 0x0000000000000fc0:0 Active CPU mask: 0x0000000000000fc0:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2023-09-05 23:15:54.37 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2023-09-05 23:15:54.38 Server In-Memory OLTP initialized on standard machine.
2023-09-05 23:15:54.40 Server [INFO] Created Extended Events session 'hkenginexesession'
2023-09-05 23:15:54.40 Server Database Instant File Initialization: disabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2023-09-05 23:15:54.40 Server Total Log Writer threads: 3. This is an informational message; no user action is required.
2023-09-05 23:15:54.43 Server clwb is selected for pmem flush operation.
2023-09-05 23:15:54.45 Server CLR version v4.0.30319 loaded.
sys.dm_os_schedulers:
scheduler_address parent_node_id scheduler_id cpu_id status is_online is_idle preemptive_switches_count context_switches_count idle_switches_count current_tasks_count runnable_tasks_count current_workers_count active_workers_count work_queue_count pending_disk_io_count load_factor yield_count last_timer_activity failed_to_create_worker active_worker_address memory_object_address task_memory_object_address quantum_length_us total_cpu_usage_ms total_cpu_idle_capped_ms total_scheduler_delay_ms ideal_workers_limit
0x0000025918220040 0 0 0 VISIBLE ONLINE 1 1 1153043 16864086 133496581 4 0 9 4 0 0 4 285671940 10880995620 0 0x00000259186BC160 0x000002591804E040 0x0000025918050040 4000 0 0 7283681 57
0x0000025918240040 0 1 1 VISIBLE ONLINE 1 1 1447172 19240663 140078602 5 0 10 4 0 0 4 301808773 10880994760 0 0x00000259183A8160 0x0000025918058040 0x000002591805A040 4000 0 0 7212441 58
0x0000025918260040 0 2 2 VISIBLE ONLINE 1 0 214859549 566109938 1285209060 4 1 9 4 0 0 6 -1417541559 10880995669 0 0x0000026F4AD94160 0x0000025918060040 0x0000025918062040 4000 0 0 32962149 55
0x0000025918480040 0 3 3 VISIBLE ONLINE 1 1 183703151 539137658 1240154119 3 0 9 3 0 0 3 -1502852373 10880995416 0 0x000002591850E160 0x0000025918068040 0x000002591806A040 4000 0 0 37429531 56
0x00000259184A0040 0 4 4 VISIBLE ONLINE 1 1 9690121 55468504 372230585 5 0 10 5 0 0 4 807572752 10880995541 0 0x0000025918580160 0x0000025918070040 0x0000025918072040 4000 0 0 14392226 56
0x00000259184C0040 0 5 5 VISIBLE ONLINE 1 1 86901162 272690595 959567522 4 0 11 3 0 0 2 2129958437 10880995650 0 0x0000026BFFCBE160 0x0000025918078040 0x000002591807A040 4000 0 0 24265549 57
0x00000259184E0040 0 1048578 0 HIDDEN ONLINE 1 0 10806 0 2405 1 0 1 1 0 0 1 2078088 10880994401 0 0x00000259186B6160 0x00000259186B0040 0x00000259186B2040 4000 0 0 0 2
0x0000025918C20040 1 6 6 VISIBLE ONLINE 1 1 14364691 48529713 395946180 4 0 9 3 0 0 2 862720484 10880993363 0 0x00000268144D2160 0x0000025918620040 0x0000025918622040 4000 0 0 14308350 57
0x0000025918C40040 1 7 7 VISIBLE ONLINE 1 1 981117 15276400 106523681 4 0 8 3 0 0 3 225092127 10880995322 0 0x0000025918E02160 0x0000025918628040 0x000002591862A040 4000 0 0 6992313 57
0x0000025918C60040 1 8 8 VISIBLE ONLINE 1 1 278403126 713103290 1467283639 2 0 8 2 0 0 1 -1025444988 10880995657 0 0x00000267DA07C160 0x0000025918630040 0x0000025918632040 4000 0 0 37662853 55
0x0000025918F00040 1 9 9 VISIBLE ONLINE 1 1 12156863 43687653 376792761 3 0 8 3 0 0 2 821501532 10880993419 0 0x0000026882FAC160 0x0000025918638040 0x000002591863A040 4000 0 0 12790071 56
0x0000025918F20040 1 10 10 VISIBLE ONLINE 1 1 183639496 503573475 1376124782 2 0 7 2 0 0 1 -1243539994 10880995250 0 0x00000268A8E1C160 0x0000025918640040 0x0000025918642040 4000 0 0 30034933 55
0x0000025918F40040 1 11 11 VISIBLE ONLINE 1 1 10686428 37944165 326803213 5 0 9 4 0 0 3 712439435 10880986711 0 0x0000025B1CADA160 0x0000025918648040 0x000002591864A040 4000 0 0 12708950 57
0x0000025918F60040 1 1048579 6 HIDDEN ONLINE 1 0 0 0 2215 1 0 1 1 0 0 1 2048291 10880993266 0 0x0000025919104160 0x000002591867E040 0x0000025919100040 4000 0 0 0 2
0x00000259191A0040 64 1048576 0 VISIBLE ONLINE (DAC) 1 1 4 9 12095 2 0 3 1 0 0 0 12102 10880209924 0 0x00000259186BA160 0x0000025919122040 0x0000025919124040 4000 0 0 0 5
0x000002591F9A0040 0 1048580 1 HIDDEN ONLINE 1 1 0 0 100614190 1 0 1 1 0 0 1 200151374 10880995666 0 0x000002591F96E160 0x000002591F968040 0x000002591F96A040 4000 0 0 0 2
0x000002591F980040 1 1048581 7 HIDDEN ONLINE 1 1 0 0 761882074 1 0 1 1 0 0 1 1210023305 10880995572 0 0x000002591F976160 0x000002591F970040 0x000002591F972040 4000 0 0 0 2
0x000002591F9C0040 1 1048582 8 HIDDEN ONLINE 1 1 0 0 755215122 1 0 1 1 0 0 1 1199890707 10880995666 0 0x000002591F97E160 0x000002591F978040 0x000002591F97A040 4000 0 0 0 2
0x000002591F9E0040 1 1048583 9 HIDDEN ONLINE 1 1 0 0 749447107 1 0 1 1 0 0 1 1191192769 10880995666 0 0x000002591FB80160 0x000002591F67C040 0x000002591F67A040 4000 0 0 0 2
0x0000025920320040 0 1048584 2 HIDDEN ONLINE 1 1 6 0 12128 1 0 1 1 0 0 1 12189 10880901314 0 0x0000025921A1E160 0x0000025921A18040 0x0000025921A1A040 4000 0 0 0 2
0x0000025920340040 0 1048585 3 HIDDEN ONLINE 1 1 2 0 12123 1 0 1 1 0 0 1 12179 10880901330 0 0x0000025921A32160 0x0000025921A2E040 0x0000025921A2C040 4000 0 0 0 2
0x0000025920240040 0 1048586 4 HIDDEN ONLINE 1 1 565500 0 2766233 1 0 1 1 0 0 1 5482068 10880994369 0 0x0000025924210160 0x000002592420A040 0x000002592420C040 4000 0 0 0 2
0x00000259285C0040 0 1048587 5 HIDDEN ONLINE 1 1 2972 0 3393964 0 0 1 0 0 0 0 6777584 10880866350 0 0x000002594F878160 0x000002594F860040 0x000002594F868040 4000 0 0 0 1
0x00000259285E0040 0 1048588 0 HIDDEN ONLINE 1 1 985 0 881850 0 0 1 0 0 0 0 1751250 10880866350 0 0x000002596012A160 0x000002594F87A040 0x000002594F87C040 4000 0 0 0 1
0x0000025960420040 1 1048589 10 HIDDEN ONLINE 1 1 1883 0 1831914 0 0 1 0 0 0 0 3652749 10880866350 0 0x0000025A85804160 0x0000025A85850040 0x0000025A85876040 4000 0 0 0 1
0x0000025960400040 1 1048590 11 HIDDEN ONLINE 1 1 2129 0 1854492 0 0 1 0 0 0 0 3698022 10880566076 0 0x0000025A85812160 0x0000025A85808040 0x0000025A85810040 4000 0 0 0 1
0x0000025A715A0040 0 1048591 1 HIDDEN ONLINE 1 1 2858 0 2369960 0 0 1 0 0 0 0 4730473 10880866319 0 0x00000259D154A160 0x0000025A46240040 0x0000025A46242040 4000 0 0 0 1
0x00000259283C0040 0 1048592 2 HIDDEN ONLINE 1 1 1431 0 1262788 0 0 1 0 0 0 0 2514230 10880176213 0 0x0000025A84230160 0x0000025A84264040 0x0000025A84244040 4000 0 0 0 1
0x000002596A680040 0 1048593 3 HIDDEN ONLINE 1 1 2599 0 2576020 0 0 1 0 0 0 0 5141377 10880866319 0 0x0000025A7982C160 0x0000025A79826040 0x0000025A79828040 4000 0 0 0 1
0x00000259283E0040 0 1048594 4 HIDDEN ONLINE 1 1 2634 0 2455946 0 0 1 0 0 0 0 4901224 10880176229 0 0x0000025A8426C160 0x0000025A8424A040 0x0000025A8426A040 4000 0 0 0 1
0x000002596A6A0040 0 1048595 5 HIDDEN ONLINE 1 1 2801 0 2731176 0 0 1 0 0 0 0 5451858 10880866335 0 0x0000025A79834160 0x0000025A7982E040 0x0000025A79830040 4000 0 0 0 1
0x000002592E8C0040 1 1048596 6 HIDDEN ONLINE 1 1 2416 0 2279872 0 0 1 0 0 0 0 4549033 10880176213 0 0x0000025A8298C160 0x0000025A829AA040 0x0000025A829A8040 4000 0 0 0 1
0x000002592E8A0040 1 1048597 7 HIDDEN ONLINE 1 1 3045 0 2940803 0 0 1 0 0 0 0 5871356 10880866319 0 0x00000259D13D6160 0x0000025A864FE040 0x00000259D13D2040 4000 0 0 0 1
0x000002595F9A0040 0 1048598 0 HIDDEN ONLINE 1 1 1173 0 1000858 0 0 1 0 0 0 0 1990374 10880866319 0 0x00000259D0E64160 0x0000025A84BFE040 0x00000259D0E52040 4000 0 0 0 1
0x00000259C0E80040 1 1048599 8 HIDDEN ONLINE 1 1 2070 0 2225588 0 0 1 0 0 0 0 4440225 10880814028 0 0x0000025A830BA160 0x0000025A830F4040 0x0000025A830B6040 4000 0 0 0 1
0x00000259C0EA0040 1 1048600 9 HIDDEN ONLINE 1 1 2337 0 2287844 0 0 1 0 0 0 0 4564136 10880866335 0 0x0000025A830D2160 0x0000025A830C2040 0x0000025A830C4040 4000 0 0 0 1
0x0000025A715C0040 0 1048601 1 HIDDEN ONLINE 1 1 2939 0 3071534 0 0 1 0 0 0 0 6134000 10880866319 0 0x0000025A47534160 0x00000259D1544040 0x00000259D1548040 4000 0 0 0 1
0x0000025A715E0040 0 1048602 2 HIDDEN ONLINE 1 1 2093 0 2161732 0 0 1 0 0 0 0 4312590 10880176229 0 0x0000025A4751E160 0x0000025A4751A040 0x0000025A47500040 4000 0 0 0 1
0x00000259240E0040 0 1048603 3 HIDDEN ONLINE 1 1 1518 0 1618964 0 0 1 0 0 0 0 3226729 10880866319 0 0x0000025A79FC2160 0x0000025A47508040 0x0000025A4752A040 4000 0 0 0 1
0x000002592E8E0040 1 1048604 10 HIDDEN ONLINE 1 1 1897 0 1558224 0 0 1 0 0 0 0 3105359 10880866319 0 0x00000259D13F6160 0x00000259D13E0040 0x00000259D13EA040 4000 0 0 0 1
0x0000025926DC0040 0 1048605 4 HIDDEN ONLINE 1 1 1875 0 2196012 0 0 1 0 0 0 0 4380991 10880176213 0 0x0000025A74E1C160 0x0000025A74E2E040 0x0000025A74E0A040 4000 0 0 0 1
0x0000025A86580040 1 1048606 11 HIDDEN ONLINE 1 1 1189 0 984507 0 0 1 0 0 0 0 1957588 10880566076 0 0x00000259D14C6160 0x00000259D14E2040 0x00000259D14F6040 4000 0 0 0 1
0x000002592A640040 1 1048607 6 HIDDEN ONLINE 1 1 1882 0 1544327 0 0 1 0 0 0 0 3077654 10880866319 0 0x0000025A82A42160 0x0000025A82A2C040 0x0000025A82A3A040 4000 0 0 0 1
0x0000025926DE0040 0 1048608 5 HIDDEN ONLINE 1 1 1308 0 1039508 0 0 1 0 0 0 0 2067713 10880814028 0 0x0000025A74E7E160 0x0000025A74E04040 0x0000025A74E02040 4000 0 0 0 1
0x000002595F9C0040 0 1048609 0 HIDDEN ONLINE 1 1 2829 0 2412064 0 0 1 0 0 0 0 4813658 10880866319 0 0x00000259D0E7C160 0x00000259D0E3A040 0x00000259D0E2E040 4000 0 0 0 1
0x000002595F9E0040 0 1048610 1 HIDDEN ONLINE 1 1 2379 0 2192673 0 0 1 0 0 0 0 4375512 10880866335 0 0x0000025A856EE160 0x00000259D0E42040 0x0000025A85690040 4000 0 0 0 1
0x0000025960440040 1 1048611 7 HIDDEN ONLINE 1 1 1386 0 1352646 0 0 1 0 0 0 0 2693439 10880866319 0 0x0000025A85842160 0x0000025A85854040 0x0000025A85858040 4000 0 0 0 1
0x0000025A86600040 0 1048612 2 HIDDEN ONLINE 1 1 3015 0 2869906 0 0 1 0 0 0 0 5729329 10880176229 0 0x0000025A6D2CE160 0x0000025A6D2F4040 0x0000025A6D2A8040 4000 0 0 0 1
0x0000025960460040 1 1048613 8 HIDDEN ONLINE 1 1 1610 0 1571447 0 0 1 0 0 0 0 3131717 10880866335 0 0x0000025A85862160 0x0000025A85864040 0x0000025A8584A040 4000 0 0 0 1
0x000002592A620040 1 1048614 9 HIDDEN ONLINE 1 1 3448 0 3469913 0 0 1 0 0 0 0 6929661 10880176213 0 0x0000025A82A4E160 0x0000025A82A46040 0x0000025A82A48040 4000 0 0 0 1
0x0000025A86620040 0 1048615 3 HIDDEN ONLINE 1 1 3332 0 3070622 0 0 1 0 0 0 0 6130958 10880176213 0 0x0000025A6D298160 0x0000025A6D2BC040 0x0000025A6D2E6040 4000 0 0 0 1
0x0000025A86700040 0 1048616 4 HIDDEN ONLINE 1 1 2185 0 1890908 0 0 1 0 0 0 0 3770898 10880866335 0 0x0000025A856E4160 0x0000025A856F0040 0x0000025A85686040 4000 0 0 0 1
0x0000025A86680040 1 1048617 10 HIDDEN ONLINE 1 1 2 0 15639 0 0 1 0 0 0 0 19194 10880884265 0 0x0000025A861F4160 0x0000025A861B8040 0x0000025A861EC040 4000 0 0 0 1
0x0000025A866A0040 1 1048618 11 HIDDEN ONLINE 1 1 3 0 14081 0 0 1 0 0 0 0 16078 10880884202 0 0x00000259D07F2160 0x0000025A861FE040 0x00000259D0668040 4000 0 0 0 1
sys.dm_os_nodes:
node_id node_state_desc memory_object_address memory_clerk_address io_completion_worker_address memory_node_id cpu_affinity_mask online_scheduler_count idle_scheduler_count active_worker_count avg_load_balance timer_task_affinity_mask permanent_task_affinity_mask resource_monitor_state online_scheduler_mask processor_group cpu_count
0 ONLINE 0x0000025918032040 0x000002591802A640 0x0000025918600160 0 63 6 6 23 3 45 60 1 63 0 6
1 ONLINE 0x0000025918604040 0x000002591802AD90 0x0000025919080160 0 4032 6 6 16 1 64 0 1 4032 0 6
64 ONLINE DAC 0x0000025919106040 0x000002591802B4E0 0x00000259186B8160 64 0 1 1 1 0 0 0 0 63 0 6
系统配置:
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
1535 affinity mask 0 -2147483648 2147483647 0 affinity mask 1 1
DBCC Tracestatus: 没有打印任何表,所以我认为这证明没有跟踪正在运行?
我有下表,包含 462541359 行。
create table "Prices"
(
"Id" bigint generated by default as identity
constraint "PK_Prices"
primary key,
"Timestamp" timestamp with time zone not null,
"DieselPrice" real not null,
"E5Price" real not null,
"E10Price" real not null,
"DieselChanged" boolean not null,
"E5Changed" boolean not null,
"E10Changed" boolean not null,
"StationId" uuid not null
constraint "FK_Prices_Stations_StationId"
references "Stations"
on delete cascade
);
alter table "Prices"
owner to postgres;
create index "IX_Prices_DieselChanged"
on "Prices" ("DieselChanged");
create index "IX_Prices_E10Changed"
on "Prices" ("E10Changed");
create index "IX_Prices_E5Changed"
on "Prices" ("E5Changed");
create index "IX_Prices_StationId"
on "Prices" ("StationId");
create index "IX_Prices_Timestamp"
on "Prices" ("Timestamp");
我将查询精简为这个(作为一个最小的例子)
select
count(*)
FROM "Prices"
where "StationId" = 'f38e56c1-e9ba-428f-adb0-bdefa428559b'
and "Timestamp" >= '2023-01-07'
其中StationId
只是 17000 个电台 ID 之一。
当我过滤此表时,初始运行的性能很差(大约 8 秒)。当我重新运行查询时,速度更快(大约 300 毫秒)。当我更改StationId
第一个查询时,它又变慢了。
我尝试使用来分析性能EXPLAIN (ANALYZE, BUFFERS)
并得到以下结果
Aggregate (cost=124159.66..124159.67 rows=1 width=8) (actual time=7734.619..7734.620 rows=1 loops=1)
Buffers: shared read=38398
-> Bitmap Heap Scan on ""Prices"" (cost=358.69..124141.54 rows=7246 width=0) (actual time=6668.499..7732.704 rows=9678 loops=1)
Recheck Cond: (""StationId"" = 'b07d169a-2856-4903-baee-d17e496ebfd0'::uuid)
Filter: (""Timestamp"" >= '2023-01-07 00:00:00+00'::timestamp with time zone)
Rows Removed by Filter: 28645
Heap Blocks: exact=38323
Buffers: shared read=38398
-> Bitmap Index Scan on ""IX_Prices_StationId"" (cost=0.00..356.88 rows=33107 width=0) (actual time=21.983..21.983 rows=38364 loops=1)"
Index Cond: (""StationId"" = 'b07d169a-2856-4903-baee-d17e496ebfd0'::uuid)
Buffers: shared read=34
Planning Time: 0.082 ms
JIT:
Functions: 7
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.296 ms, Inlining 0.000 ms, Optimization 0.196 ms, Emission 2.469 ms, Total 2.961 ms
Execution Time: 7734.984 ms
从我读到的内容来看,Bitmap Heap Scan
仅部分使用了Timestamp
列上的索引,我怀疑这是性能低下的原因。
查询最初缓慢的原因是什么?如何更好地利用索引来加快按日期的过滤速度?当我将过滤器与更多过滤器结合使用时,如何确保保持索引的性能E5Changed
?
在我意识到存在重新验证对象的过程(SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS)之前,我编写了自己的过程。现在,我尝试将其替换为标准的,但似乎对 SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS 的调用破坏了我正在循环的游标。旧程序示例:
db2 -td@ +c "BEGIN FOR v AS c1 CURSOR FOR SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS DO CALL TOOLBOX.COMPILE_SCHEMA2(v.schemaname); END FOR; END @"
DB20000I The SQL command completed successfully.
使用 ADMISN_REVALIDATE_DB_OBJECTS 的示例
db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>'TMP')"
Return Status = 0
db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>'TMP')"
Return Status = 0
尝试在光标上循环使用它:
db2 -td@ +c "BEGIN FOR v AS c1 CURSOR FOR SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS DO CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>v.schemaname); END FOR; END @"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0501N The cursor specified in a FETCH statement or CLOSE statement is not
open or a cursor variable in a cursor scalar function reference is not open.
SQLSTATE=24501
请注意,它对硬连接架构没有帮助:
db2 -td@ +c "BEGIN FOR v AS c1 CURSOR FOR SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS DO CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>'TMP')
[...]
也会产生错误。我尝试了在光标上循环的变体:
BEGIN
DECLARE s VARCHAR(128);
DECLARE v_at_end INTEGER default 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE C1 CURSOR for
SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS;
DECLARE CONTINUE HANDLER FOR not_found SET v_at_end = 1 ;
OPEN C1;
fetch_loop:
LOOP
FETCH FROM C1 INTO s;
IF v_at_end <>0 THEN LEAVE fetch_loop; END IF;
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>s);
END LOOP;
CLOSE C1;
END
@
但它也不起作用。任何线索,需要做什么才能在循环中调用 ADMIN_REVALIDATE_DB_OBJECTS ?
我有不止一台带有 SQL Server 数据库的服务器。我想begin transaction
对两个数据库都执行一个操作,以便在出现错误时它会自动回滚。我尝试过链接服务器,这些服务器已经过测试,可以使用 IP 相互连接,并且可以在服务器之间进行选择,包括更新和删除,但是当我尝试开始事务时,会发生错误。
链接服务器“名称”的 OLE DB 提供程序“SQLNCLI11”返回消息“没有活动的事务。”。
无法执行该操作,因为链接服务器“名称”的 OLE DB 提供程序“SQLNCLI11”无法开始分布式事务。
我已经寻找了一个解决方案,并做了几件事,例如将分发事务的启用升级更改为 false,尝试确保服务器相互连接,重新安装链接服务器驱动程序。但还是不行,有谁知道连接失败的原因吗?我使用的是带有 Windows NT 6.2 操作系统的 SQL Server 2012 和带有 Redhat 8.4 的 SQL Server 2019。
有没有另一种方法可以在没有链接服务器的情况下在不同的服务器上进行交易?
请考虑这种情况:我有 2 个模式:SCHEMA1, SCHEMA2
在每个模式中我都有一些表。
我每个模式都有两个登录名。
我想创建一个可以访问(CRUD 操作)SCHEMA1 中所有表的用户,但只能插入SCHEMA2.Table1
.
我怎样才能创建这个用户?
谢谢
我目前正在学习一个关于数据库开发的单元,我正在学习一个关于规范化的模块,我相信这个ERD已经完成了第二种形式的规范化,因为每个表都有一个主键,所有数据都是原子的,并且没有重复的组,并且我不认为存在任何部分关键依赖性。但我是新人,理解还很薄弱。所以我想知道是否有人可以看看并让我知道他们的想法以及可能的任何帮助,我将不胜感激。
编辑:我不确定是否要抓取 PostCodeAndCity 表并将城市数据包含在地址表中,但是我的学习材料建议这样做。
编辑2:由于创建了这个问题的帐户,我没有声誉来支持你的答案,但我只是想感谢所有花时间分享你的知识的人,我真的很感激它,它进一步提供了帮助我的大大!
假设我们的数据库中有几个主表(a、b 和 c),然后另一个 (x) 存储一个复杂的、仅半可预测的 JSONb 对象,该对象引用了所有主表。就我而言,JSONb 看起来像这样:
{
entries: [
[
[
{table: 'a', id: '1'},
{table: 'b', id: '4', entries: [
{table: 'a', id: '3'},
{table: 'a', id: '1'}
...
]},
{table: 'c', id: '5', entries: [
{table: 'a', id: '2'},
{table: 'b', id: '4', entries: [
{table: 'a', id: '1'},
{table: 'a', id: '6'},
...
]},
...
]},
...
],
...
],
...
]
}
从表中选择记录时,x
我们希望按其他表的属性过滤结果 - 例如。仅x
包含a
具有包含特定枚举值的字段的记录的记录。
使用此 JSONb 数据结构在单个查询中执行此操作是否可能或完全有效?看起来这需要对 ID 进行一些认真的聚合,并且为每个查询执行此操作似乎需要大量工作。
我正在考虑的替代方案是保持 JSONb 字段不变,但也创建连接表(我可能在那里的名称错误)来跟踪所有表记录x
依赖项。因此,您将拥有表x_a
、x_b
、等,并且只为记录中弹出的每个、、 或IDx_c
存储一个唯一的连接记录。这样,在编写查询时,可以使用简单且非 JSON 的方法来执行常规连接过滤器。a
b
c
x
作为初级到中级 SQL 程序员,这似乎至少会产生更具可读性的代码,但是我不确定这是否违反“仅输入数据一次”规则。
欢迎任何和所有的意见,包括阅读有关如何做出这些决定的材料。
语言是PostgresQl
EMPLOYEE
我导出了Oracle DB 中表的DDL 。这个指数是什么?该索引适用于哪些列?
CREATE UNIQUE INDEX "MY_USER"."SYS_IL0000126968C00005$$" ON "MY_USER"."EMPLOYEE" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA"
PARALLEL (DEGREE 0 INSTANCES 0) ;
该查询不显示上述索引。如何查询上述唯一索引?
select
TABLE_NAME,
INDEX_NAME,
UNIQUENESS,
NUM_ROWS,
CONSTRAINT_INDEX,
OWNER,
TABLE_OWNER
from
SYS.ALL_INDEXES;