[20201208] why return 2-line record supplement.txt

Time:2021-4-15

[20201208] why return 2-line record supplement.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

[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

–//The above is my test yesterday. Maybe I didn’t explain some details clearly. In fact, the join of several tables uses nested loop, which leads to the return of 2 rows.
–//X $view Oracle can not guarantee the consistency of the query. When connecting to a nested loop, the waiting event is SQL * net message to client, which is displayed before the connection
–//When the SQL * net message from client wait event appears, the result is 2 lines.

2. If I am adding prompt use_ MERGE:
[email protected]> select sid from v$mystat where rownum=1;
       SID
———-
       325

[email protected]> select /*+ leading(e s w) USE_MERGE( [email protected]$4) */ sid ,osuser,EVENT#,event  from v$session where sid = 325;
       SID OSUSER                             EVENT# EVENT
———- —————————— ———- —————————————-
       325 oracle                                350 SQL*Net message to client

[email protected]> @ dpc ” ”
PLAN_TABLE_OUTPUT
————————————-
SQL_ID  argukfafpgxwf, child number 0
————————————-
select /*+ leading(e s w) USE_MERGE( [email protected]$4) */ sid
,osuser,EVENT#,event  from v$session where sid = 325
Plan hash value: 111210288
———————————————————————————————————————————————————
| Id  | Operation                  | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
———————————————————————————————————————————————————
|   0 | SELECT STATEMENT           |                 |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       |       |          |
|   1 |  MERGE JOIN                |                 |      1 |      1 |   142 |     2 (100)| 00:00:01 |      1 |00:00:00.01 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN     |                 |      1 |      1 |   116 |     0   (0)|          |    352 |00:00:00.01 |       |       |          |
|   3 |    FIXED TABLE FULL        | X$KSLED         |      1 |    100 |  4700 |     0   (0)|          |    352 |00:00:00.01 |       |       |          |
|   4 |    BUFFER SORT             |                 |    352 |      1 |    69 |     0   (0)|          |    352 |00:00:00.01 |  2048 |  2048 | 2048  (0)|
|*  5 |     FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |      1 |      1 |    69 |     0   (0)|          |      1 |00:00:00.01 |       |       |          |
|*  6 |   SORT JOIN                |                 |    352 |      1 |    26 |     1 (100)| 00:00:01 |      1 |00:00:00.01 |  2048 |  2048 | 2048  (0)|
|*  7 |    FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) |      1 |      1 |    26 |     0   (0)|          |      1 |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
   7 – SEL$88122447 / [email protected]$4
Predicate Information (identified by operation id):
—————————————————

   5 – filter((“S”.”INDX”=325 AND “S”.”INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“S”.”KSSPAFLG”,1)<>0 AND BITAND(“S”.”KSUSEFLG”,1)<>0))
   6 – access(“S”.”INDX”=”W”.”KSLWTSID” AND “W”.”KSLWTEVT”=”E”.”INDX”)
       filter((“W”.”KSLWTEVT”=”E”.”INDX” AND “S”.”INDX”=”W”.”KSLWTSID”))
   7 – filter(“W”.”KSLWTSID”=325)
       
–//You can find that using use_ Merge (W) is invalid, and the alias use must be added_ MERGE( [email protected] If you see this, you will return 1 line. Because use_ Merge to sort
–//Then output, so that you can see the first SQL * net message to client wait event.

3. Continue testing:
–//12C SQL plus provides the function of setting rowprefetch. In fact, 11g is 1 by default, while 12C SQL plus client can provide parameter rowprefetch to modify it.
–//That is, if you use 10046 tracking at 11g, you will see a fetch r = 1. Reference link:
http://blog.itpub.net/267265/viewspace-2712755/= >[20200818] 12C 10046 tracking timestamp.txt

–//Check tracking file:
=====================
PARSING IN CURSOR #646021600 len=19 dep=0 uid=81 oct=3 lid=81 tim=2572120902 hv=4071881952 ad=’7ff11e35c78′ sqlid=’4g0qfgmtb7z70′
select * from dept
END OF STMT
PARSE #646021600:c=0,e=238,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3383998547,tim=2572120899
EXEC #646021600:c=0,e=88,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3383998547,tim=2572121955
WAIT #646021600: nam=’SQL*Net message to client’ ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2572122196
FETCH #646021600:c=0,e=502,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=3383998547,tim=2572122875
~~~~~~~~~~~~~~~~=> r=1
WAIT #646021600: nam=’SQL*Net message from client’ ela= 6393 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2572129463
WAIT #646021600: nam=’SQL*Net message to client’ ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2572129793
FETCH #646021600:c=0,e=241,p=0,cr=4,cu=0,mis=0,r=3,dep=0,og=1,plh=3383998547,tim=2572129980
~~~~~~~~~~~~~~~~=> r=3
STAT #646021600 id=1 cnt=4 pid=0 pos=1 obj=22487 op=’TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 str=1 time=242 us cost=3 size=80 card=4)’

–//How can I use sqlplus of 12C as the client to connect to the database and modify the rowprefetch parameter?
[email protected]> show sqlpluscompatibility
sqlpluscompatibility 12.2.0

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

[email protected]> set rowprefetch 2
[email protected]> select /*+ leading(e s w) */ sid ,osuser,EVENT#,event  from v$session where sid = 32;
       SID OSUSER                   EVENT# EVENT
———- ——————– ———- ——————————
        32 Administrator               350 SQL*Net message to client

–//^_ ^, magically return 1 line.

[email protected]> set rowprefetch 1
[email protected]> select /*+ leading(e s w) */ sid ,osuser,EVENT#,event  from v$session where sid = 32;
       SID OSUSER                   EVENT# EVENT
———- ——————– ———- ——————————
        32 Administrator               350 SQL*Net message to client
        32 Administrator               354 SQL*Net message from client

–//^_ ^, magically return 2 lines.

4. Summary:
–//I just want to deepen my learning and understanding through some examples.

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 […]