Thinking about orcle 11g select count (*) from v $ lock, orcle11g

Thinking about orcle 11g select count (*) from v $ lock, orcle11g

<Strong> recently, we found that the query by orcle 11g select count (*) from v $ lock is very slow, and I think it is necessary to perform a detailed analysis: </strong>

select count(*) from v$lock;--------------------------------------------------------------------------------------| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------|   0 | SELECT STATEMENT        |            |     1 |    50 |     0   (0)| 00:00:01 ||   1 |  SORT AGGREGATE         |            |     1 |    50 |            |          ||*  2 |   HASH JOIN             |            |     1 |    50 |     0   (0)| 00:00:01 ||<span style="color:#ff0000;">   3 |    MERGE JOIN CARTESIAN |            |   100 |  3800 |     0   (0)| 00:00:01 ||*  4 |     FIXED TABLE FULL    | X$KSUSE    |     1 |    19 |     0   (0)| 00:00:01 ||   5 |     BUFFER SORT         |            |   100 |  1900 |     0   (0)| 00:00:01 ||   6 |      FIXED TABLE FULL   | X$KSQRS    |   100 |  1900 |     0   (0)| 00:00:01 |</span>|   7 |    VIEW                 | GV$_LOCK   |    10 |   120 |     0   (0)| 00:00:01 ||   8 |     UNION-ALL           |            |       |       |            |          ||*  9 |      <span style="color:#3333ff;">FILTER             |            |       |       |            |          ||  10 |       VIEW              | GV$_LOCK1  |     2 |    24 |     0   (0)| 00:00:01 ||  11 |        UNION-ALL        |            |       |       |            |          ||* 12 |         FIXED TABLE FULL| X$KDNSSF   |     1 |    64 |     0   (0)| 00:00:01 ||* 13 |         FIXED TABLE FULL| X$KSQEQ    |     1 |    64 |     0   (0)| 00:00:01 ||* 14 |      FIXED TABLE FULL   | X$KTADM    |     1 |    64 |     0   (0)| 00:00:01 |--|* 15 |      FIXED TABLE FULL   | X$KTATRFIL |     1 |    64 |     0   (0)| 00:00:01 ||* 16 |      FIXED TABLE FULL   | X$KTATRFSL |     1 |    64 |     0   (0)| 00:00:01 ||* 17 |      FIXED TABLE FULL   | X$KTATL    |     1 |    64 |     0   (0)| 00:00:01 ||* 18 |      FIXED TABLE FULL   | X$KTSTUSC  |     1 |    64 |     0   (0)| 00:00:01 ||* 19 |      FIXED TABLE FULL   | X$KTSTUSS  |     1 |    64 |     0   (0)| 00:00:01 |</span>

Production database 10046:

********************************************************************************SQL ID: ct78468spkzrt Plan Hash: 2384831130select count(*) from v$lockcall     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.01          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2     13.09      21.12          0          0          0           1------- ------  -------- ---------- ---------- ---------- ----------  ----------total        4     13.09      21.13          0          0          0           1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: SYSNumber of plan statistics captured: 1select count(*) from X$KSQRSRows (1st) Rows (avg) Rows (max)  Row Source Operation---------- ---------- ----------  ---------------------------------------------------         1          1          1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=21123213 us)       355        355        355   HASH JOIN  (cr=0 pr=0 pw=0 time=21083516 us cost=1 size=50 card=1)<span style="color:#ff0000;">  10715136   10715136   10715136    MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=10072178 us cost=0 size=3800 card=100)      1536       1536       1536     FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=6834 us cost=0 size=19 card=1)  10715136   10715136   10715136     BUFFER SORT (cr=0 pr=0 pw=0 time=3222054 us cost=0 size=1900 card=100)</span>      6976       6976       6976      FIXED TABLE FULL X$KSQRS (cr=0 pr=0 pw=0 time=1911 us cost=0 size=1900 card=100)       356        356        356    VIEW  GV$_LOCK (cr=0 pr=0 pw=0 time=11637 us cost=0 size=120 card=10)       356        356        356     UNION-ALL  (cr=0 pr=0 pw=0 time=11281 us)       352        352        352      FILTER  (cr=0 pr=0 pw=0 time=10570 us)       352        352        352       VIEW  GV$_LOCK1 (cr=0 pr=0 pw=0 time=10330 us cost=0 size=24 card=2)       352        352        352        UNION-ALL  (cr=0 pr=0 pw=0 time=9978 us)         0          0          0         FIXED TABLE FULL X$KDNSSF (cr=0 pr=0 pw=0 time=594 us cost=0 size=64 card=1)       352        352        352         FIXED TABLE FULL X$KSQEQ (cr=0 pr=0 pw=0 time=8792 us cost=0 size=64 card=1)         4          4          4      FIXED TABLE FULL X$KTADM (cr=0 pr=0 pw=0 time=12004 us cost=0 size=64 card=1)         0          0          0      FIXED TABLE FULL X$KTATRFIL (cr=0 pr=0 pw=0 time=17 us cost=0 size=64 card=1)         0          0          0      FIXED TABLE FULL X$KTATRFSL (cr=0 pr=0 pw=0 time=6 us cost=0 size=64 card=1)         0          0          0      FIXED TABLE FULL X$KTATL (cr=0 pr=0 pw=0 time=9 us cost=0 size=64 card=1)         0          0          0      FIXED TABLE FULL X$KTSTUSC (cr=0 pr=0 pw=0 time=17 us cost=0 size=64 card=1)         0          0          0      FIXED TABLE FULL X$KTSTUSS (cr=0 pr=0 pw=0 time=11 us cost=0 size=64 card=1)         0          0          0      FIXED TABLE FULL X$KTSTUSG (cr=0 pr=0 pw=0 time=12 us cost=0 size=64 card=1)         0          0          0      FIXED TABLE FULL X$KTCXB (cr=0 pr=0 pw=0 time=6216 us cost=0 size=64 card=1)Elapsed times include waiting on following events:  Event waited on                             Times   Max. Wait  Total Waited  ----------------------------------------   Waited  ----------  ------------  SQL*Net message to client                       2        0.00          0.00  asynch descriptor resize                       61        0.00          0.00  SQL*Net message from client                     2       30.66         30.66

GV ￥ lock has normal performance:

15:21:44 sys@gshx1(newgsdb01)>  select count(*) from gv$lock;  COUNT(*)----------       706Elapsed: 00:00:00.07Execution Plan----------------------------------------------------------Plan hash value: 483924080-----------------------------------------------------------------------------------------------------------------------| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |-----------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |            |     1 |       |     1 (100)| 00:00:01 |        |      |            ||   1 |  SORT AGGREGATE             |            |     1 |       |            |          |        |      |            ||   2 |   PX COORDINATOR            |            |    10 |       |     1 (100)| 00:00:01 |        |      |            ||   3 |    PX SEND QC (RANDOM)      | :TQ10000   |    10 |   370 |     1 (100)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  ||   4 |     VIEW                    | GV$LOCK    |       |       |            |          |  Q1,00 | PCWP |            ||*  5 |      HASH JOIN              |            |    10 |   370 |     1 (100)| 00:00:01 |  Q1,00 | PCWP |            ||*  6 |       HASH JOIN             |            |    10 |   180 |     1 (100)| 00:00:01 |  Q1,00 | PCWP |            ||   7 |        VIEW                 | GV$_LOCK   |    10 |   120 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||   8 |         UNION-ALL           |            |       |       |            |          |  Q1,00 | PCWP |            ||*  9 |          FILTER             |            |       |       |            |          |  Q1,00 | PCWP |            ||  10 |           VIEW              | GV$_LOCK1  |     2 |    24 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||  11 |            UNION-ALL        |            |       |       |            |          |  Q1,00 | PCWP |            ||* 12 |             FIXED TABLE FULL| X$KDNSSF   |     1 |    64 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||* 13 |             FIXED TABLE FULL| X$KSQEQ    |     1 |    64 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||* 14 |          FIXED TABLE FULL   | X$KTADM    |     1 |    64 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||* 15 |          FIXED TABLE FULL   | X$KTATRFIL |     1 |    64 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||* 16 |          FIXED TABLE FULL   | X$KTATRFSL |     1 |    64 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||* 17 |          FIXED TABLE FULL   | X$KTATL    |     1 |    64 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||* 18 |          FIXED TABLE FULL   | X$KTSTUSC  |     1 |    64 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||* 19 |          FIXED TABLE FULL   | X$KTSTUSS  |     1 |    64 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||* 20 |          FIXED TABLE FULL   | X$KTSTUSG  |     1 |    64 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||* 21 |          FIXED TABLE FULL   | X$KTCXB    |     1 |    64 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            || <span style="color:#ff6666;"> 22 |        FIXED TABLE FULL     | X$KSUSE    |   100 |   600 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            ||  23 |       FIXED TABLE FULL      | X$KSQRS    |   100 |  1900 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |</span>

<Strong> and it is normal to query select * from v $ lock </strong>

The information about the two tables should be correct. Let's look at the test environment again:

--------------------------------------------------------------------------------Plan hash value: 2329815124--------------------------------------------------------------------------------| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU--------------------------------------------------------------------------------|   0 | SELECT STATEMENT          |                 |     1 |    40 |     1 (100|   1 |  SORT AGGREGATE           |                 |     1 |    40 ||   2 |   NESTED LOOPS            |                 |     1 |    40 |     1 (100|*  3 |    HASH JOIN              |                 |     1 |    31 |     1 (100|*  4 |     FIXED TABLE FULL      | X$KSUSE         |     1 |    19 |     0   (0|   5 |     VIEW                  | GV$_LOCK        |    10 |   120 |     0   (0|   6 |      UNION-ALL            |                 |       |       ||*  7 |       FILTER              |                 |       |       ||   8 |        VIEW               | GV$_LOCK1       |     2 |    24 |     0   (0|   9 |         UNION-ALL         |                 |       |       ||* 10 |          FIXED TABLE FULL | X$KDNSSF        |     1 |    64 |     0   (0|* 11 |          FIXED TABLE FULL | X$KSQEQ         |     1 |    64 |     0   (0|* 12 |       FIXED TABLE FULL    | X$KTADM         |     1 |    64 |     0   (0|* 13 |       FIXED TABLE FULL    | X$KTATRFIL      |     1 |    64 |     0   (0|* 14 |       FIXED TABLE FULL    | X$KTATRFSL      |     1 |    64 |     0   (0|* 15 |       FIXED TABLE FULL    | X$KTATL         |     1 |    64 |     0   (0|* 16 |       FIXED TABLE FULL    | X$KTSTUSC       |     1 |    64 |     0   (0|* 17 |       FIXED TABLE FULL    | X$KTSTUSS       |     1 |    64 |     0   (0|* 18 |       FIXED TABLE FULL    | X$KTSTUSG       |     1 |    64 |     0   (0|* 19 |       FIXED TABLE FULL    | X$KTCXB         |     1 |    64 |     0   (0|* 20 |    FIXED TABLE FIXED INDEX| X$KSQRS (ind:1) |     1 |     9 |     0   (0--------------------------------------------------------------------------------

The execution plan of the production database and the test database is different. Why is there a Cartesian plan for the production database:
There are two solutions based on experience:

I. Collect all statistics of the memory table

Execute dbms_stats.gather_fixed_objects_stats ()

2. Add a prompt

Select/* + rule */count (*) from v $ lock

Unfortunately, at that time, I only considered statistics and did not consider adding a prompt:

Thoughts:

Collection of memory table information

SQL> begin 2 dbms_stats.gather_fixed_objects_stats (stattab => 'x $ KSQRS '); 3 end; 4/else (stattab => 'x $ KSQRS'); end; ORA-02030: only ORA-06512 can be queried from a fixed table/view: in "SYS. DBMS_STATS ", line 20508ORA-06512: In" SYS. DBMS_STATS ", line 20945ORA-06512: In" SYS. DBMS_STATS ", line 21498ORA-06512: In line 3 SQL> select table_name, num_rows, limit 2 from limit 3 where last_analyzed is not null 4 and table_name = 'x $ KSQRS '5/TABLE_NAME NUM_ROWS last_analyzed tables ---------- ----------- X $ KSQRS 1312 limit 22 SQL> begin 2 Limit ('sys ', 'X $ KSQRS '); 3 end; 4/PL/SQL procedure successfully completed SQL> select table_name, num_rows, last_analyzed 2 from tables 3 where last_analyzed is not null 4 and table_name = 'x $ KSQRS '5/TABLE_NAME NUM_ROWS LAST_ANALYZED tables ---------- ----------- SQL> begin 2 tables ('sys ', 'X $ KSQRS '); 3 end; 4/PL/SQL procedure successfully completed SQL> select table_name, num_rows, last_analyzed 2 from snapshot 3 where last_analyzed is not null 4 and table_name = 'x $ KSQRS '5/TABLE_NAME NUM_ROWS LAST_ANALYZED tables ---------- ------------- X $ KSQRS 1312 22

Note:
Gather_dictionary_stats --> for tables like table $, it exists in the physical database ~
Gather_fixed_objects_stats --> for memory tables such as x $ table, physical databases do not exist, but the base tables that are dynamically attempted exist only in the memory.
Gather_system_stats --> needle cpu/io

Fixed object statistics
Automatic statistics collection jobs do not collect statistics of fixed objects. when optimization statistics are lost, dynamic sampling is not automatically used for calling the X $ table in SQL statements, unlike other database tables. if their statistical information is lost, the optimizer uses the predefined default statistical information. these default statistics may not be representative and may lead to the selection of a sub-optimal execution plan, which may cause serious performance problems in the system. if this is the cause of performance problems, we strongly recommend that you manually collect statistics on fixed objects. you can use the dbms_stats.gather_fixed_objects_stats process to collect statistics of fixed objects. this is because it is very important for the system to collect the statistics of x $ fixed objects under a representative workload. in large systems, it is not always feasible to collect statistics on fixed objects because collecting statistics on fixed objects requires additional resources. if the statistical information of fixed objects cannot be collected during load peaks, the following three key types of fixed object tables should be collected after the system load is reduced:
Structural data -- for example, controlfile contents
Session based data-such as v $ session, v $ access
Workload data-such as v $ SQL and v $ SQL _plan
After the master database or application is upgraded, we recommend that you implement a new module or change the database configuration, and then re-collect the fixed object statistics. for example, if you increase the SGA size, the x $ table that contains the buffer cache and shared pool information will significantly change, for example, the x $ table used by the v $ buffer_pool or v $ shared_pool_advice view. system Statistical Information System statistical information allows the optimizer to execute actual system hardware information related to this statement, such as cpu speed and IO performance, to get more precise cost values for each step in the execution plan. system statistics are enabled by default, and are automatically initialized by default. These values are representative for most systems.