[20191227]別把資料庫當作垃圾場.txt

[20191227]別把資料庫當作垃圾場.txt

--//最近一直在我一個專案的最佳化工作,看到開發寫的模式有時候真心無語!!

xxxxx> select count(*) from BBB_QQQQ_FFFFF_TTTTTT;

  COUNT(*)
----------
         1

xxxxx> select count(*) from BBB_QQQQ_FFFFF_TTTTTT;
  COUNT(*)
----------
         0

--//可以發現插入後馬上刪除,查詢消失,好不容易抓到1次.

select sql_id,sql_text,executions,ROWS_PROCESSED from v$sqlarea where upper(sql_text) like '%BBB_QQQQ_FFFFF_TTTTTT%'
and module='JDBC Thin Client' order by executions desc;

SQL_ID        SQL_TEXT        EXECUTIONS ROWS_PROCESSED
------------- --------------- ---------- --------------
5naavb5pbarvv INSERT INTO BOS     145536         145536
              RIGGER_NAME, TR
              _TIME, STATE, J
              S_RECOVERY, PRI
               , :4 , :5 , :6

8qzr3jdpv8qny UPDATE BOS_QRTZ     145536         145536
              D_TIME = :2 , S
               JOB_GROUP = :6
              = :8  WHERE SCH


cknfn1yap02pt DELETE FROM BOS     145536         145536
              tzScheduler' AN

...
6 rows selected.

--//插入修改刪除的執行次數完全一樣.一個使用臨時表的經典例子.
--//中間休息60秒.再次查詢.看到的執行次數的變化:
--//145554->145563
--//145563-145554 = 9,1分鐘執行9次.

xxxxx> select count(*) from UUU_SSSSS_PPPP;
  COUNT(*)
----------
         0

select sql_id,sql_text,executions,ROWS_PROCESSED from v$sqlarea where upper(sql_text) like '%UUU_SSSSS_PPPP%' and module='JDBC Thin Client' order by executions desc;
SQL_ID        SQL_TEXT          EXECUTIONS ROWS_PROCESSED
------------- ----------------- ---------- --------------
....
fakvs4mf7kbt1 INSERT INTO   UUU      47882          47882
              ACEID, FBILLID,  
              CKEDTIME,FVERSION
              TETIME,FBUSTYPE,F
              :4 ,   :5 ,   :6
              2 ,   :13 ,   :14

5shxwhma80zhk SELECT * FROM UUU      47882          47882
              lBatchCode=:2  an

2rhu90tv83675 delete from UUU_S      47882          47882
...
7 rows selected.

--//中間休息60秒.再次查詢.看到的執行次數的變化:
--//47901->47911
--//47911-47901 = 10,1分鐘執行10次.
--//看到這樣的專案真心無語,建議開發好好學習然後在開發吧,不然簡直是坑害使用者...

--//貼一個圖,不然別人又在說我亂講.