--- title: 使用临时表导致系统登录慢的问题-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 使用临时表导致系统登录慢的问题相关的常见问题和使用技巧，帮助您快速解决 使用临时表导致系统登录慢的问题的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 使用临时表导致系统登录慢的问题 更新时间：2026-05-15 09:06 适用版本： V2.1.x、V2.2.x、V3.1.x、V3.2.x 内容类型：Troubleshoot ## 适用版本 OceanBase 数据库 V3.2.4 BP5（oceanbase-3.2.4.5-105000012023081513）之前的版本。 ## 问题现象 集群中存在业务租大量使用临时表（CGTT）的情况，业务租户登录很慢，甚至无法登录，OBProxy 日志中 COM_LOGIN 耗时很久或报 -4152 错误。 ```shell OB_MYSQL,,,COM_LOGIN,,failed,-4152,,1233729us,0us,0us,0us,Y0-7F9A1447AD60,,,Null value ``` ## 问题原因 临时表中的数据只对本 session 可见，其生命周期随着 session 断开而终止。在 OceanBase 数据库 V3.2.4 BP5（oceanbase-3.2.4.5-105000012023081513）之前，由于 session id 可能存在复用的情况，会在登陆时对当前 session id 的数据进行检查，如果存在则需要额外进行一次清理。当同样的 session id 曾经执行过大量的临时表时，清理动作耗时较久，会导致登录缓慢甚至无法登录的问题。 对于因清理临时表导致登录缓慢的问题，可以从 `observer.log` 中分析判断，如下示例。 1. `19:42:11.23791` 时租户 ttt 上有一次成功的登录，登录 session 的 `proxy_sessid=65523`。 ```shell [2023-05-11 19:42:11.237915] INFO [SERVER] obmp_connect.cpp:374 [3115205][0][Y0-0000000000000000-0-0] [lt=10] [dc=0] MySQL LOGIN(direct_client_ip="xx.xx.xx.15", client_ip=xx.xx.xx.14, tenant_name=ttt, tenant_id=1001, user_name=xxx, host_name=%, sessid=3224142387, proxy_sessid=65523, sess_create_time=0, from_proxy=true, from_java_client=false, capability=681288327, proxy_capability=2631, use_ssl=false, c/s protocol="OB_MYSQL_CS_TYPE", proc_ret=0, ret=0) ``` 2. 搜索之前的日志，发现大量的清理临时表的操作 `DELETE FROM TMP_TB WHERE SYS_SESSION_ID = 65523`。 ```shell [2023-05-11 19:41:51.517947] INFO [SQL.SESSION] ob_sql_session_info.cpp:660 [3115205][0][Y0-0000000000000000-0-0] [lt=14] [dc=0] succeed to delete rows in oracle temporary table(sql=DELETE FROM "XXX"."XXX_TMP1" WHERE SYS_SESSION_ID = 65523 AND SYS_SESS_CREATE_TIME <> 1683805307139382 AND ROWNUM <= 1000, affect_rows=0) [2023-05-11 19:41:54.102287] INFO [SERVER] ob_inner_sql_connection.cpp:706 [3115205][0][YB421E164672-0005F9E7619A936D-0-0] [lt=15] [dc=0] slow inner sql(last_ret=0, sql={ObIExecutor:, sql:"DELETE FROM __all_column_stat_v2 WHERE tenant_id = 0 and table_id = 111783 ;"}, process_time=2560769) [2023-05-11 19:41:55.210163] INFO [SERVER] ob_inner_sql_connection.cpp:706 [3115205][0][YB421E164672-0005F9E7619A936E-0-0] [lt=28] [dc=0] slow inner sql(last_ret=0, sql={ObIExecutor:, sql:"DELETE FROM __all_histogram_stat_v2 WHERE tenant_id = 0 and table_id = 111783 ;"}, process_time=1107736) [2023-05-11 19:41:56.440965] INFO [SERVER] ob_inner_sql_connection.cpp:706 [3115205][0][YB421E164672-0005F9E7619A9370-0-0] [lt=15] [dc=0] slow inner sql(last_ret=0, sql={ObIExecutor:, sql:"DELETE FROM "XXX"."XXX_TMP2" WHERE SYS_SESSION_ID = 65523 AND SYS_SESS_CREATE_TIME <> 1683805307139382 AND ROWNUM <= 1000"}, process_time=1228185) [2023-05-11 19:41:56.441142] INFO [SQL.SESSION] ob_sql_session_info.cpp:660 [3115205][0][Y0-0000000000000000-0-0] [lt=14] [dc=0] succeed to delete rows in oracle temporary table(sql=DELETE FROM "XXX"."XXX_TMP2" WHERE SYS_SESSION_ID = 65523 AND SYS_SESS_CREATE_TIME <> 1683805307139382 AND ROWNUM <= 1000, affect_rows=0) [2023-05-11 19:41:57.942354] INFO [SERVER] ob_inner_sql_connection.cpp:706 [3115205][0][YB421E164672-0005F9E7619A9376-0-0] [lt=29] [dc=0] slow inner sql(last_ret=0, sql={ObIExecutor:, sql:"DELETE FROM "XXX"."XXX_TMP3" WHERE SYS_SESSION_ID = 65523 AND SYS_SESS_CREATE_TIME <> 1683805307139382 AND ROWNUM <= 1000"}, process_time=1238371) [2023-05-11 19:41:57.942612] INFO [SQL.SESSION] ob_sql_session_info.cpp:660 [3115205][0][Y0-0000000000000000-0-0] [lt=34] [dc=0] succeed to delete rows in oracle temporary table(sql=DELETE FROM "XXX"."XXX_TMP3" WHERE SYS_SESSION_ID = 65523 AND SYS_SESS_CREATE_TIME <> 1683805307139382 AND ROWNUM <= 1000, affect_rows=0) ``` ## 解决方法 - 升级到 OceanBase 数据库 V3.2.4 BP5（oceanbase-3.2.4.5-105000012023081513）。 - 在 OceanBase 数据库 V3.2.4 BP5（oceanbase-3.2.4.5-105000012023081513）之前，尽量避免使用临时表，使用普通表来代替。 上一篇 [数据删除后的空表查询耗时长](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000225644) 下一篇 [相同 SQL 生成不同 SQL_ID，未命中执行计划的原因和解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003172874) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线