PARSING_SCHEMA_NAME <> 'SYS' GROUP BY a.PARSING_SCHEMA_NAME, substr(sql_text, 1, 60) HAVING count(1) > 10) where ranking <= 50; 测试SQL如下所示: 代码语言:javascript 代码运行次数:0 运行 AI代码解释 declare v_ename scott.emp.ename%type; v_sal scott.emp.sal%type; v_sql clob; ...
OPTIMIZER_ENV_HASH_VALUE:环境的 HASH 值 PARSING_USER_ID:最先解析此游标的用户的 ID PARSING_SCHEMA_ID:最先解析此游标的方案 ID PARSING_SCHEMA_NAME:最先解析此游标的方案 ID KEPT_VERSIONS:是否使用 DBMS_SHARED_POOL 包将游标 Pin 到库缓存中 ADDRESS:父游标句柄的地址 TYPE_CHK_HEAP: HASH_VALUE:游标...
PARSING_USER_ID NUMBER 最初构建此子游标的用户ID PARSING_SCHEMA_ID NUMBER 最初构建子游标使用的模式ID PARSING_SCHEMA_NAME VARCHAR2(30) 最初构建子游标使用的模式名称 SERVICE VARCHAR2(64) 服务名称 SERVICE_HASH NUMBER SERVICE列展示的服务名称的哈希值 MODULE VARCHAR2(64) SQL语句第一次被解析时正在执行...
(*)||' | ') reason from v$sql_shared_cursor group by sql_id, address ) sc join v$sqlarea sq using (sql_id) where 1=1 and version_count > 2 -- and parsing_schema_name not in ('SYS') order by version_count desc; select * from vw_sql_version_count_reason_lhr where sql_id=...
如在sys和system都执行select * from test则V$SQL中有两条记录,两条记录的CHILD_NUMBER和PARSING_SCHEMA_NAME不一样。同一个用户下执行一样的语句如果大小写不一样或加了hint的话则会出现多个V$SQL记录,说明V$SQL对应的sql语句必须一模一样,如果alter system flush shared_pool(主站慎用)后再执行一样的语句,发现...
如在sys和system都执行select * from test则V$SQL中有两条记录,两条记录的CHILD_NUMBER和PARSING_SCHEMA_NAME不一样。同一个用户下执行一样的语句如果大小写不一样或加了hint的话则会出现多个V$SQL记录,说明V$SQL对应的sql语句必须一模一样,如果alter system flush shared_pool(主站慎用)后再执行一样的语句,发现...
WHERE parsing_schema_name = '<schema_name>' ORDER BY last_load_time DESC; ``` 上述查询将返回最近发生硬解析的SQL语句及其解析时间。 通过以上方法,可以查找出频繁硬解析或解析时间较长的SQL语句,进一步分析这些SQL语句的语法、索引、统计信息等方面,来解决硬解析问题。©...
ELSIFv_colname (i) = 'AUTH_CHECK_MISMATCH' THEN DECLARE v_pusr num_arr; v_pschid num_arr; v_pschname vc_arr; v_n num_arr; BEGIN IFv_version LIKE '9%' THEN v_query := 'select PARSING_USER_ID, PARSING_SCHEMA_ID, ''n/a'',count(*) n from v$sql ' ...
SQLSET_NAME => 'SQLSET_SFIT', SQLSET_OWNER => 'SYS', POPULATE_CURSOR => SQLSET_CUR, LOAD_OPTION => 'MERGE', UPDATE_OPTION => 'ACCUMULATE'); CLOSE SQLSET_CUR; OPEN SQLSET_CUR FOR SELECT VALUE(P) FROM TABLE( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'PARSING_SCHEMA_NAME NOT IN (''SY...
PARSING_USER_IDNUMBER User ID of the user that has parsed the very first cursor under this parent PARSING_SCHEMA_IDNUMBER Schema ID that was used to parse this child cursor PARSING_SCHEMA_NAMEVARCHAR2(30) Schema name that was used to parse this child cursor ...