[20201204] why to return 2 rows of records.txt

Time:2021-4-16

[20201204] why to return 2 rows of records.txt

–//In itpub, http://www.itpub.net/thread-2140116-1-1.html . It took a little time to figure out what the problem was,
–//The problem is the consistency read, refer to the link http://blog.itpub.net/267265/viewspace-2213824/ =>[20180907] accessing V $view and consistent reading.txt

1
[email protected]> @ ver1

PORT_STRING                    VERSION        BANNER
—————————— ————– ——————————————————————————–
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

[email protected]> select sid from v$mystat where rownum=1;
       SID
———-
        32

[email protected]> select  sid ,osuser  from v$session where sid = 32;
       SID OSUSER
———- ——————————
        32 oracle
–//Return 1 line. If the prompt is added.

[email protected]> alter session set statistics_level = all;
Session altered.

[email protected]> select /*+ leading(s e w) */ sid ,osuser  from v$session where sid = 32;
       SID OSUSER
———- ——————————
        32 oracle
        32 oracle

–//To view the execution plan:
[email protected]> @ dpc ” ”
PLAN_TABLE_OUTPUT
————————————-
SQL_ID  41djy6556s87h, child number 1
————————————-
select /*+ leading(s e w) */ sid ,osuser  from v$session where sid = 32
Plan hash value: 1247622051
———————————————————————————————————————————————
| Id  | Operation                 | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
———————————————————————————————————————————————
|   0 | SELECT STATEMENT          |                 |      1 |        |       |     1 (100)|      2 |00:00:00.01 |       |       |          |
|   1 |  NESTED LOOPS             |                 |      1 |      1 |   108 |     0   (0)|      2 |00:00:00.01 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN    |                 |      1 |      1 |    82 |     0   (0)|   1367 |00:00:00.01 |       |       |          |
|*  3 |    FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |      1 |      1 |    69 |     0   (0)|      1 |00:00:00.01 |       |       |          |
|   4 |    BUFFER SORT            |                 |      1 |    100 |  1300 |     0   (0)|   1367 |00:00:00.01 | 50176 | 50176 |45056  (0)|
|   5 |     FIXED TABLE FULL      | X$KSLED         |      1 |    100 |  1300 |     0   (0)|   1367 |00:00:00.01 |       |       |          |
|*  6 |   FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) |   1367 |      1 |    26 |     0   (0)|      2 |00:00:00.01 |       |       |          |
———————————————————————————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
   1 – SEL$88122447
   3 – SEL$88122447 / [email protected]$4
   5 – SEL$88122447 / [email protected]$4
   6 – SEL$88122447 / [email protected]$4
Predicate Information (identified by operation id):
—————————————————
   3 – filter((“S”.”INDX”=32 AND “S”.”INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“S”.”KSSPAFLG”,1)<>0 AND BITAND(“S”.”KSUSEFLG”,1)<>0))
   6 – filter((“W”.”KSLWTSID”=32 AND “W”.”KSLWTEVT”=”E”.”INDX”))

–//You can see that if id = 6, a-row does return 2 lines. Why?

2. Continue to explore:
–//Open a new session, the original session does not exit.
[email protected]> select /*+ leading(s e w) */ sid ,osuser  from v$session where sid = 32;

       SID OSUSER
———- ——————————
        32 oracle

–//The same prompt returns only one line. Why?
–//First guess x $kslwt. You can guess from the name that it is the view related to the waiting event. Add more fields to the display:
[email protected]> select /*+ leading(s e w) */ sid ,osuser,EVENT#,event  from v$session where sid = 32;
       SID OSUSER                             EVENT# EVENT
———- —————————— ———- —————————————-
        32 oracle                                354 SQL*Net message from client

–//Return to the original session and repeat:
[email protected]> select /*+ leading(s e w) */ sid ,osuser,EVENT#,event  from v$session where sid = 32;
       SID OSUSER                             EVENT# EVENT
———- —————————— ———- —————————————-
        32 oracle                                350 SQL*Net message to client
        32 oracle                                354 SQL*Net message from client

–//This is a good way to parse why 2 lines are returned. Views do not guarantee consistency.
–//When the statement is executed, SQL * net message to client wait first, then SQL * net message from client wait.
–//Because view consistency cannot be guaranteed, 2 rows are returned. This is not the case with other conversations.

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]