Select count (*) from v$lock query is slow

Description:

Select count (*) from v$lock query slow solution. See the official optimized document for specific reasons.

SQL > set time on 00:51:52 SQL > select count (*) from vlockout; COUNT (*)-35 color-it takes more than ten seconds to return the result. 00:55:10 SQL > select / * + rule * / count (*) from Vlockout; COUNT (*)-3500 from 5538 SQL > method 1: add hint method 2: collect statistics MOS reference documentation Query Against v$lock Run from OEM Performs Slowly (document ID 1328789.1) collect statistics as follows: 13.2.5 Gathering Statistics for Fixed ObjectsFixed objects are dynamic performance tables and their indexes. These objects record current database activity.Unlike other database tables, the database does not automatically use dynamic statistics for SQL statement referencing X $tables when optimizer statistics are missing. Instead, the optimizer uses predefined default values. These defaults may not be representative and could potentially lead to a suboptimal execution plan. Thus, it is important to keep fixed object statistics current.Oracle Database automatically gathers fixed object statistics as part of automated statistics gathering if they have not been previously collected. You can also manually collect statistics on fixed objects by calling DBMS_STATS.GATHER_FIXED_OBJECTS_STATS. Oracle recommends that you gather statistics when the database has representative activity.PrerequisitesYou must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.To gather schema statistics using GATHER_FIXED_OBJECTS_STATS:In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.Example 13-2 Gathering Statistics for a TableThis example uses the DBMS_STATS package to gather fixed object statistics.BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;END / See Also: "Configuring Automatic Optimizer Statistics Collection" Oracle Database PL/SQL Packages and Types Reference to learn about the GATHER_TABLE_STATS procedureParent topic: Gathering Optimizer Statistics Manually