DBA Blogs

Equvivalent function for ISDATE()

Tom Kyte - Tue, 2020-10-27 22:06
Hi Tom, I've been using Oracle for Sometime now, I use a lot of MSSQL and Sybase, I'm trying to load some ascii file using SQL Loader(sqlldr), I want to know if there is any oracle function equvivalent to ISDATE() function in MSSQL or Sybase. If my Ascii file contains data which is not of date datatype, I want to insert a NULL instead of loading some wrong data or getting an error in sqlload. Thanks for your Help Srini
Categories: DBA Blogs

IP address

Tom Kyte - Tue, 2020-10-27 22:06
I have got an Oracle version 8.1.1 I'd like to know how you get the IP address from users when they have already logged on the Database. Would you, please, send me infomations about versions older than 8.i? Thanhs beforehand.
Categories: DBA Blogs

Oracle Redaction

Tom Kyte - Tue, 2020-10-27 03:46
Hello I have a question on oracle redaction. Can oracle redaction be turned on based on a value in a column? please let me know Thanks subramanyam
Categories: DBA Blogs

Problem with the number 1/19

Tom Kyte - Tue, 2020-10-27 03:46
Oracle is under the impression that 1/19 * 19 - 1 = -6 x 10^(-40). You can replace 19 with 19 multiplied by any positive power of 100 and the problem remains. (When you multiply 19 by an odd power of 10, the answer is zero. For example, 1/1900 * 1900 - 1 = -6x10^(-40), but 1/190 * 190 - 1 = 0.) I understand it has to do with the way numbers are stored internally, but is there a simple way around it? Note that this also happens in version 10.2.4
Categories: DBA Blogs

How to reset sequences?

Tom Kyte - Tue, 2020-10-27 03:46
Sir, Greetings. I would just like to know if it is possible to truncate a sequence to reset back to its original starting number? Pls. help me. Thank you. Merry Christmass. - Vince Crismer C. Villena
Categories: DBA Blogs

Recursive Function

Tom Kyte - Tue, 2020-10-27 03:46
Can you please give me an example for recursive function in plsql Which takes i_date as an input variable using this i_date it should perform operations like it should fetch data from table into cursor From this cursor I should insert data into a file .This file should be generated seperately for date that is passed in as input Let us take that my input date is 01012016 the function should recursively perform until it reaches 31122016 (for every last day of a month i.e total 12 times)
Categories: DBA Blogs

Number of CPUs and Degree of Parallelism

Tom Kyte - Tue, 2020-10-27 03:46
Hi Tom, 1) Is there or What is the relationship between number of CPUS the host has to the degree of Parallelism selected say while running DBMS_STATS. 2) Is there a relationship between DB sessions and Degree of Parallelism. If I have say 4 CPUS and select degree of 4 When I run DBMS_STATs will it show 4 DB sessions and consume 4 CPUS ? Or It will definitely show 4 DB sessions but no. of CPU depends upon availabilty from OS side. So even 2 CPUS will be used for 4 sessions ? 3) Is there always one to one relationship between a DB session ( from v$session) and OS process (seen with ps command ) 4) DBMS_SCHEDULER does it need to have job_queue_processes set like the old days with dbms_job Please answer with respect to 9208 and 11107 version.
Categories: DBA Blogs

Materialized View Log

Tom Kyte - Tue, 2020-10-27 03:46
Db Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production <code>desc Par_trials; Column Name Pk Null? Data Type PARTY_TRIAL_ID 1 N NUMBER (11) PARTY_ID N NUMBER (11) PARTY_ROLE_TYPE_ID N NUMBER (11) CLINICAL_TRIAL_ID N NUMBER (11) START_DT Y DATE END_DT Y DATE CORE_CREATE_DT N DATE CORE_LAST_UPDT Y DATE EDC_FLAG Y VARCHAR2 (1 Byte) CPAC_FLAG Y VARCHAR2 (1 Char) ELMS_FLAG Y VARCHAR2 (1 Char) CREATE MATERIALIZED VIEW LOG ON par_trials WITH ROWID, SEQUENCE (party_id,party_role_type_id,clinical_trial_id,start_dt,end_dt,core_create_dt,core_last_updt,edc_flag,cpac_flag,elms_flag), PRIMARY KEY INCLUDING NEW VALUES Insert into PAR_TRIALS (PARTY_TRIAL_ID, PARTY_ID, PARTY_ROLE_TYPE_ID, CLINICAL_TRIAL_ID, START_DT, CORE_CREATE_DT) Values (365352, 50858, 528, 1055, TO_DATE('10/30/2019 20:53:51', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/07/2019 12:03:59', 'MM/DD/YYYY HH24:MI:SS')); COMMIT; update par_trials set core_create_dt=sysdate where party_Trial_id=365352; select * from MLOG$_PAR_TRIALS</code> Question: 1) Would like to include a new column called "OPERATION$" in MLOG$_PAR_TRIALS table which should have below column values "UU" which indicates before update and should contain all its corresponding old values "UN" which indicates after update and should contain all its corresponding new values 2) In mlog creation script, have included "INCLUDING NEW VALUES" and i do have requirement to "exclude old values". Expectation here is just to retain only new values in mlog. Is that possible? If yes, kindly share example. 3) Can we do delete operation against MLOG$_PAR_TRIALS?If yes, kindly share example.
Categories: DBA Blogs

Locking issue on self created Counter scheme in our HMIS Application

Tom Kyte - Tue, 2020-10-27 03:46
Dear Team We had recently upgraded our Application counter scheme ( Previously using oracle sequences) to our own created procedure to generate Next number/counter due to our requirement to generate seperate counters for our different Hospital Campuses. Every year we have to reset all sequences because our scheme is using year based counter values. Now the issue is locking problem on our Physician Encounter where we have to generate 7-8 different counters on the same time and our procedure sometimes get locked and does not return any counter value due to locking issue Our application user may loss data to No Counter return We have two options of Row update, wait 1 sec and Nowait, sample code is mentioned below. can you please guide us on method close to Oracle sequence so that we can update code and overcome this locking issue. Also Please guide do you recommend to use FOR UPDATE without wait or NOWAIT option in SQL ? <code> IF NVL(P_LOCK_WAIT, 'N') = 'Y' THEN SELECT ROWID INTO P_ROWID FROM COUNTERS.SYSTEM_COUNTERS_VALUES T WHERE T.COUNTER_ID = P_COUNTER_ID AND T.SERIAL_NO = P_SERIAL_NO FOR UPDATE WAIT 1; ELSE SELECT ROWID INTO P_ROWID FROM COUNTERS.SYSTEM_COUNTERS_VALUES T WHERE T.COUNTER_ID = P_COUNTER_ID AND T.SERIAL_NO = P_SERIAL_NO FOR UPDATE NOWAIT; END IF </code>
Categories: DBA Blogs

DB link access between (20 Containers in the same Zone ( Oracle Suppoer cluster)

Tom Kyte - Tue, 2020-10-27 03:46
Hi Tom, We have a customer, who deployed multiple databases on the same supercluster zone and required a full segergation between each database. Here let's talk about one of zones which are having 2 databases and required a full network segergation between each other. We configured for each database a seprate network with different subent on the cluster and for sure different scan IPs and VIPs. Everything went smootly except the connectivity between the databases in the same zone with each others so we agreed to implment TCP.EXCLUDED_NODES to prevent each database to connect to the other but the problem it is not working and still database A can connect on B and viceversa, we did a test between databases in different zones and this parameter "TCP.EXCLUDED_NODES" is working properly. My Question is why it is not working with two databases in the same zone? - DB 12.1 - Zone O.S is Solaris Sparc : 11.4 - (2) Containers with (2) Different databases in the same Zone , we need to stop DB access from one DB to the other
Categories: DBA Blogs

Oracle Cloud Infrastructure Data Flow - simple way to run Apache Spark applications

Beginning of February 2020, Oracle launched a new family of Data Management services such as Data Catalog, Data Science, Data Flow, and a new Big Data service. All these services help to create new...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Real Time SQL Monitoring using SQL Developer

Hemant K Chitale - Sat, 2020-10-24 11:41

Since a previous post with screenshots was very popular


I have created a  Video Demo on my Youtube Channel



Categories: DBA Blogs

Partner Webcast – Delivering your Application Solutions on Autonomous Database

Cloud has changed customer expectations and software buying preferences causing partners to adapt their deployment, development and customer support models. Increasingly customers consider &...

We share our skills to maximize your revenue!
Categories: DBA Blogs

How can I do a variable "in list"

Tom Kyte - Wed, 2020-10-21 01:06
I have a simple stored procedure, that I would like to have a passed in string(varchar2) for used in select from where col1 in (var1) in a stored procedure. I've tried everything but doesn't work. Followed is my proc. Thanks CREATE OR REPLACE PROCEDURE WSREVSECT_5 pSectNos varchar2, pRetCode OUT varchar2 ) AS nCount number; BEGIN SELECT count(fksrev) into nCount FROM SREVSECT WHERE sectno IN (pSectNos ) /* as in 'abc', 'xyz', '012' */ ; pRetCode:=to_char(ncount); End;
Categories: DBA Blogs

Complete transient session for testing

Tom Kyte - Wed, 2020-10-21 01:06
We have looked at dbms_flashback and dbms_wm but both don't deliver what we actually want: Is there a possibility to make an entire session transient? We would like to use this for our testing scenario's: 1. Start the session in "transient mode" 2. execute a lot of pl/sql with commits, rollbacks and savepoints 3. drop/stop the session ....and everything is magically back to before point 1. dbms_flashback.ENABLE_AT_SYSTEM_CHANGE_NUMBER is almost there by showing the data at a specified scn, but then you have to reverse every table that is hit by the -third party- code by hand. Not feasible in our environment I'm not very optimistic, but perhaps I missed a new capability of the DB. Is there a way?
Categories: DBA Blogs

Query never finish without clear reason

Tom Kyte - Wed, 2020-10-21 01:06
Some times an aleatory select statement stop working without a clear reason. Some times the statement is inside procedure, some times it is executed directly from ODAC FOR .NET 4 Then only thing in common it was always executed by ODAC client, so I never got this problem from one of my jobs ! When I check gv$sesion the session is active When I check plan using DBMS_XPLAN.DISPLAY_CURSOR I it is the best one When I simulate via pl/sql the query works fine with the very same plan When I kill the session and execute again from the ODAC , the same problem happen When I kill the session, SYS.DBMS_SHARED_POOL.PURGE(ADDRESS || ', ' || HASH_VALUE, 'C'), and execute again from the ODAC then BINGO the problem is SOLVED. ...however we know that it will happen again ... What kind of evidence am I missing? did you ever see this kind of behavior?
Categories: DBA Blogs

Select for update statement too slow

Tom Kyte - Wed, 2020-10-21 01:06
Hi Connor, Chris, I have a FOR UPDATE SQL used to lock certain rows in ORDERS table but it seems to be bit slow (takes around 1 min). I tried getting plan from dbms_xplan.display_awr. Could you please give me some lead from your past experience and I can look for the any SQL tuning stuff. <code> SELECT PT.ORDER_ID FROM STAGING_001 PN JOIN GTT_TAB IDS ON IDS.MSG_ID = PN.MSG_ID, XMLTABLE ( 'hsbcEnvelope/hsbcMessageBody/pymtTran' PASSING PN.XML_MSG COLUMNS REF_001 VARCHAR2 (50 CHAR) PATH 'REF_001', REF_002 VARCHAR2 (50) PATH 'REF_001', REF_003 VARCHAR2 (10 CHAR) PATH 'REF_001') PMT, ORDERS PT WHERE 1 = 1 AND ( ( PMT.REF_002 IS NOT NULL AND PMT.REF_001 IS NOT NULL AND PMT.REF_002 = PT.REF_002 AND PT.REF_001 = PMT.REF_001 AND NVL (PMT.REF_003, :B1) = PT.REF_003) OR ( PMT.REF_002 IS NOT NULL AND PMT.REF_002 = PT.REF_002 AND NVL (PMT.REF_003, :B1) = PT.REF_003) OR ( PMT.REF_001 IS NOT NULL AND PT.REF_001 = PMT.REF_001 AND NVL (PMT.REF_003, :B1) = PT.REF_003) ) FOR UPDATE OF PT.ORDER_ID NOWAIT; </code> <code> ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 27043 (100)| | | | | 1 | FOR UPDATE | | | | | | | | | 2 | BUFFER SORT | | | | | | | | | 3 | CONCATENATION | | | | | | | | | 4 | NESTED LOOPS | | 1003 | 1935K| 11972 (1)| 00:00:01 | | | | 5 | NESTED LOOPS | | 2940 | 3930K| 210 (1)| 00:00:01 | | | | 6 | NESTED LOOPS | | 10 | 13630 | 13 (0)| 00:00:01 | | | | 7 | INDEX FAST FULL SCAN | SYS_C006227 | 10 | 130 | 2 (0)| 00:00:01 | | | | 8 | TABLE ACCESS BY INDEX ROWID | STAGING_001 | 1 | 1350 | 2 (0)| 00:00:01 | | | | 9 | INDEX UNIQUE SCAN | PK_STG_INT | 1 | | 1 (0)| 00:00:01 | | | | 10 | XPATH EVALUATION | | | | | | | | | 11 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 1 | 607 | 4 (0)| 00:00:01 | ROWID | ROWID | | 12 | INDEX RANGE SCAN | IDX_PT_REF_001 | 1 | | 3 (0)| 00:00:01 | | | | 13 | NESTED LOOPS | | 1011 | 1950K| 14172 (1)| 00:00:01 | | | | 14 | NESTED LOOPS ...
Categories: DBA Blogs

SQL Profile example when best plan not clear

Bobby Durrett's DBA Blog - Tue, 2020-10-20 12:54

I resolved another production performance issue with a SQL Profile yesterday. I have several posts about SQL Profiles, so I do not want to be redundant, but this case was a little different because it was not clear that I had a better plan. I want to document the challenge that I had deciding if I had the best plan and show the resolution.

On September 21 when I think I was on vacation or otherwise not in the office there was a big performance issue on an older production database. The on-call DBA identified the sql_id of the top SQL statement as 30q69rbpn7g75. But he and an application support developer together could not connect that SQL statement back to the long running reports that were impacting the business. Eventually the issue went away later that night. Here is some of the execution history from that original issue:

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ---------------------------- ----------------------
30q69rbpn7g75      1463081877 21-SEP-20 10.00.04.123 AM                5         420798.922         144724    209959.863                  0                      0                      0           4824516.8           710748.4                            0                    870
30q69rbpn7g75      1732425686 21-SEP-20 10.00.04.123 AM               13         66.9426923     56.1538462    7.25523077                  0                      0                      0          6410.23077         249.769231                            0             414.538462
30q69rbpn7g75       592872749 21-SEP-20 10.00.04.123 AM                1           4144.437           3240       955.246                  0                      0                      0               63878              29725                            0                     89
30q69rbpn7g75      4175108531 21-SEP-20 10.00.04.123 AM               11         172328.731     92788.1818    62448.1775                  0                      0                      0             3463219         466919.273                            0             610.090909
30q69rbpn7g75      2823019537 21-SEP-20 11.00.17.704 AM               19         332354.362     212357.895     22580.778                  0                      0                      0          11145610.8         163882.105                            0             303.526316
30q69rbpn7g75      2908690533 21-SEP-20 11.00.17.704 AM                1          23377.169          13070     11680.972                  0                      0                      0             1582917              89479                            0                    258
30q69rbpn7g75       291832905 21-SEP-20 11.00.17.704 AM                2         40314.0255          15940      24490.36                  0                      0                      0             1831813             128525                            0                    248
30q69rbpn7g75      1467059382 21-SEP-20 11.00.17.704 AM                1          20179.636           5760     16155.407                  0                      0                      0              124599              84761                            0                    780
30q69rbpn7g75      1033740578 21-SEP-20 11.00.17.704 AM                1            1728.49           1570         2.906                  0                      0                      0                1525                 35                            0                     12
30q69rbpn7g75      4175108531 21-SEP-20 11.00.17.704 AM                8         147782.833        59617.5    65356.3268                  0                      0                      0             2280007          245985.25                            0                    402
30q69rbpn7g75      3938646712 21-SEP-20 11.00.17.704 AM                2         139722.393          55905     86105.482                  0                      0                      0             6080269             616766                            0                 1143.5
30q69rbpn7g75      2823019537 21-SEP-20 12.00.31.659 PM               48         238332.678     138706.875    19077.4738                  0                      0                      0          6928661.85         99573.2708                            0             145.395833
30q69rbpn7g75      2823019537 21-SEP-20 01.00.45.195 PM               64         147520.373     80835.1563    19092.0985                  0                      0                      0          4148771.28         106131.016                            0              79.890625
30q69rbpn7g75      2823019537 21-SEP-20 02.00.58.673 PM               58         180185.939     113102.931    14365.2987                  0                      0                      0          5926129.21         123920.569                            0             22.0344828
30q69rbpn7g75      2823019537 21-SEP-20 03.00.12.404 PM               37         307432.645     201436.216    22904.6901                  0                      0                      0          10204978.9         158950.973                            0             201.243243
30q69rbpn7g75      2823019537 21-SEP-20 04.00.26.543 PM               28         465140.082     326940.357    30687.9033                  0                      0                      0          16715547.3             263153                            0             460.571429
30q69rbpn7g75      2823019537 21-SEP-20 05.00.40.707 PM               14         934982.157     690958.571    41595.1995                  0                      0                      0          34940770.8         365038.357                            0             243.285714
30q69rbpn7g75      2823019537 21-SEP-20 06.00.54.453 PM               14         818768.534     640054.286    42596.9506                  0                      0                      0          33547406.9         451864.786                            0                  471.5
30q69rbpn7g75      2823019537 21-SEP-20 07.00.08.229 PM                4         2329248.39        2013515    75722.5718                  0                      0                      0           104343531         1027683.25                            0                    859
30q69rbpn7g75      2823019537 21-SEP-20 08.00.21.705 PM                1         4006478.22        3707840     88265.422                  0                      0                      0           186157328            1082000                            0                   2744
30q69rbpn7g75      2823019537 21-SEP-20 09.00.34.774 PM                1         1818375.63        1771470     20586.628                  0                      0                      0            88206433             374924                            0                   2692
30q69rbpn7g75      2823019537 21-SEP-20 10.00.49.484 PM                2            1742051        1399440     41061.122                  0                      0                      0          68750135.5             335797                            0                 1479.5
30q69rbpn7g75      2823019537 21-SEP-20 11.00.02.543 PM                0         3552963.71        3183770    142948.208                  0                      0                      0           154159601             633488                            0                      0

This output is from my sqlstat.sql script. Notice how earlier in the day there are several plans with widely varying elapsed times. Plan hash value 1732425686 averages 66 millisconds during the hour ending at 10 am. I do not know why the query has so many different plans. 2823019537 was the bad plan and it got locked in throughout the afternoon and into the night.

Later in that same week I reviewed the plans and found that the longest running plans used certain indexes on the top table and the shorter ones used another index. I looked at the “Segments by Logical Reads” section of an AWR report during the problem time and found that the top segment was index X6_WFCTOTAL. I used by getplans.sql to get all the plans for SQL id 30q69rbpn7g75 and found that the fastest ones used range scans against index X1_WFCTOTAL and the slow ones did range or skip scans against indexes X5_WFCTOTAL or X6_WFCTOTAL. So I picked one plan, 382022017, and used coe_xfr_sql_profile.sql to force 30q69rbpn7g75 to always run with plan 382022017 which used index X1_WFCTOTAL. Here is some execution history of the plan I picked:

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ---------------------------- ----------------------
30q69rbpn7g75       382022017 24-SEP-20 09.00.13.658 AM                2          1713.2625            865         5.805                  0                      0                      0                2135               76.5                            0                    141
30q69rbpn7g75       382022017 24-SEP-20 10.00.26.654 AM                2            355.329            120      265.0765                  0                      0                      0                8183             1324.5                            0                    673
30q69rbpn7g75       382022017 24-SEP-20 11.00.39.519 AM                1            190.386            180        28.981                  0                      0                      0               17505               1759                            0                   1878
30q69rbpn7g75       382022017 24-SEP-20 01.00.05.144 PM                4          217.37625            200        20.723                  0                      0                      0            21009.25             392.25                            0                   1865
30q69rbpn7g75       382022017 24-SEP-20 04.00.45.160 PM                5           507.1578            114      432.2858                  0                      0                      0              7076.6              936.2                            0                  407.8
30q69rbpn7g75       382022017 24-SEP-20 05.00.58.322 PM                3         47.5793333             10    35.1866667                  0                      0                      0          504.333333         81.3333333                            0                     54
30q69rbpn7g75       382022017 24-SEP-20 06.00.11.202 PM                1            313.107            110       229.071                  0                      0                      0                8178                399                            0                    396
30q69rbpn7g75       382022017 25-SEP-20 04.00.21.833 AM                3         30.7433333     16.6666667    15.0446667                  0                      0                      0                 927                 89                            0             110.666667

This plan was averaging less than 1800 milliseconds. But after putting in the supposedly better plan it seemed to have worse execution times that other plans. Also, the plan hash value was different than 382022017. Here is the execution history from when I put this SQL Profile in place:

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ---------------------------- ----------------------
30q69rbpn7g75       653445232 28-SEP-20 12.00.42.470 PM                1          92139.015          26930     61332.901                  0                 20.998                      0             2202073            2197909                            0                    171
30q69rbpn7g75       653445232 28-SEP-20 01.00.55.521 PM                9         83373.8866     25152.2222    56386.2852                  0             14.6918889                      0          2102264.11            2065530                            0             2613.88889
30q69rbpn7g75       653445232 28-SEP-20 02.00.08.369 PM                5         101830.312          29264    70157.0998                  0                 9.1714                      0           2439219.6          2432260.2                            0                    348
30q69rbpn7g75       653445232 28-SEP-20 03.00.21.759 PM                9          89705.846     26606.6667    61716.6917                  0             14.2046667                      0          2212464.67         2196829.33                            0             825.111111
30q69rbpn7g75       653445232 28-SEP-20 04.00.34.724 PM                2          91173.826          26425     63832.838                  0                22.1385                      0             2181959          2195820.5                            0                  310.5
30q69rbpn7g75       653445232 28-SEP-20 05.00.47.682 PM                4         90240.0368          26430    62587.1345                  0                 16.558                      0             2200555          2197764.5                            0                 168.75
30q69rbpn7g75       653445232 28-SEP-20 07.00.13.544 PM                3         107541.972     26816.6667     75418.071                  0                 16.164                      0             2193977         2193579.33                            0             39.3333333
30q69rbpn7g75       653445232 29-SEP-20 02.00.45.539 AM                1          92416.895          26410     66108.169                  0                 21.449                      0             2194591            2193764                            0                    158
30q69rbpn7g75       653445232 29-SEP-20 03.00.30.438 AM                1         103773.265          26510     75455.905                  0                 13.887                      0             2200242            2198725                            0                    122
30q69rbpn7g75       653445232 29-SEP-20 05.00.56.488 AM               13         74637.6784     20391.5385    51139.8206                  0             16.9292308                      0          1686133.69         1684935.15                            0             24.2307692
30q69rbpn7g75       653445232 29-SEP-20 06.00.09.283 AM               10         105894.074          28882    72971.0734                  0                20.1169                      0           2418827.8          2417314.8                            0                     56
30q69rbpn7g75       653445232 29-SEP-20 07.00.22.558 AM                4         89408.2108          26080    61537.7755                  0                10.0275                      0             2174791            2169846                            0                 421.75
30q69rbpn7g75       653445232 29-SEP-20 08.00.35.596 AM                7         71644.7906     20574.2857    48465.1234                  0                  8.331                      0             1697739         1694385.43                            0             232.857143
30q69rbpn7g75       653445232 29-SEP-20 09.00.48.530 AM                2         113993.942          32580    72589.2515                  0                 6.2465                      0             2672441            2667206                            0                    145
30q69rbpn7g75       653445232 29-SEP-20 10.00.01.174 AM                6         99793.2442          28600    69181.7687                  0             4.65783333                      0          2394135.83         2387505.17                            0                    246
30q69rbpn7g75       653445232 29-SEP-20 11.00.14.240 AM               10         94787.9044          28367    65535.8735                  0                10.6895                      0           2353904.6          2336951.3                            0                  476.8
30q69rbpn7g75       653445232 29-SEP-20 12.00.27.167 PM                5           89167.86          26462     61499.462                  0                14.4808                      0           2200557.8            2195895                            0                  201.8

Notice first that the plan hash value is 653445232 instead of 382022017. I usually see things like this when the plan has system generated table temporary table names, but I have not seen that in the plan. There must be something like that going on. Either that on the SQL Profile just locks in a slightly different plan. Anyway, 653445232 is the plan caused by the SQL Profile. Notice how the average elapsed time hangs around 90,000 to 100,000 milliseconds. But 382022017 has elapsed times under 1800 milliseconds. Seeing these results, I dropped the SQL Profile. Kind of like a doctor who swears to “do no harm” I did not want to slow down queries that were running very efficiently in my attempt to prevent another system slowdown or outage like we had on September 21. I dropped the SQL Profile on September 29.

Then yesterday, October 19, the problem recurred. This time the bad plan was 3908549093 and used a skip scan on X6_WFCTOTAL. I seem to see a lot of bad plans with skip scans. Here was the execution history before I put the SQL Profile back in:

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ---------------------------- ----------------------
30q69rbpn7g75      3908549093 19-OCT-20 11.00.07.389 AM               16         832180.951      450528.75    68650.1103                  0                      0                      0          21204921.8           495900.5                            0                    562
30q69rbpn7g75      3908549093 19-OCT-20 12.00.21.033 PM               33         392068.144     194336.364    47412.7197                  0                      0                      0          9271475.06         221593.545                            0             309.454545
30q69rbpn7g75      3908549093 19-OCT-20 01.00.34.570 PM                4         3543778.15        1793980    261653.391                  0                      0                      0          82176276.8            1088971                            0                 1036.5

Between 12 and 1 pm the system was really bogged down with executions of 30q69rbpn7g75 taking almost an hour, 3543778 milliseconds. So, I put the SQL Profile back in. I had the script sitting there from the first time I tried it. These are all kept in the sqlt/utl directory. After putting it in things cleared up and the backlog of reports slowly emptied. I think we killed one long running session and had the user rerun the report with the SQL Profile in place.

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ---------------------------- ----------------------
30q69rbpn7g75       653445232 19-OCT-20 02.00.48.739 PM               27         106287.519     26936.2963     61603.699                  0             7.41166667                      0          2226367.89         2204705.19                            0             1358.25926
30q69rbpn7g75      3908549093 19-OCT-20 02.00.48.739 PM                0         9380042.72        6465310    308289.185                  0                      0                      0           294469425            1581492                            0                   7067
30q69rbpn7g75       653445232 19-OCT-20 03.00.01.772 PM               80         103522.356       26259.25    67322.2418                  0             21.1012125                      0           2159835.1         2142347.26                            0               914.2625
30q69rbpn7g75       653445232 19-OCT-20 04.00.14.862 PM               31         99252.0065          26990    66069.6995                  0             24.5769355                      0          2227363.45         2213021.26                            0             885.709677
30q69rbpn7g75       653445232 19-OCT-20 05.00.27.768 PM                5         94587.0244          26988    64440.3338                  0                24.1514                      0           2223326.4          2204672.6                            0                 1553.6
30q69rbpn7g75       653445232 19-OCT-20 06.00.40.629 PM                5         93301.2074          26690    65105.9732                  0                14.0782                      0           2213653.6            2203033                            0                  736.4
30q69rbpn7g75       653445232 19-OCT-20 07.00.53.923 PM                3         101117.233     27193.3333    72020.9813                  0             15.4373333                      0          2225218.33            2207644                            0                   1623
30q69rbpn7g75       653445232 20-OCT-20 12.00.59.660 AM                1           92061.35          26550     64953.945                  0                 22.245                      0             2210157            2206170                            0                    235
30q69rbpn7g75       653445232 20-OCT-20 02.00.25.964 AM                1          92872.242          26470     66092.822                  0                 11.999                      0             2208305            2206231                            0                    158
30q69rbpn7g75       653445232 20-OCT-20 05.00.36.773 AM                2         88107.2095          26075     61670.129                  0                17.2175                      0           2205332.5            2203981                            0                    116
30q69rbpn7g75       653445232 20-OCT-20 06.00.49.657 AM                1          91007.493          26210     64276.474                  0                  9.972                      0             2208516            2206310                            0                    177
30q69rbpn7g75       653445232 20-OCT-20 07.00.02.613 AM                4         101878.314          26940     65491.475                  0                 30.476                      0          2210945.25         2204828.75                            0                  461.5
30q69rbpn7g75       653445232 20-OCT-20 08.00.15.526 AM               10         97441.3635          26496    67549.4579                  0                  3.178                      0           2197412.4          2192467.5                            0                  478.1
30q69rbpn7g75       653445232 20-OCT-20 09.00.28.457 AM                5         59362.9672          17038    41573.7714                  0                  7.767                      0           1416804.4            1416061                            0                   10.2
30q69rbpn7g75       653445232 20-OCT-20 10.00.41.653 AM                6         108681.505     30798.3333    75082.4997                  0             10.5146667                      0          2558854.67         2549531.83                            0                    405
30q69rbpn7g75       653445232 20-OCT-20 11.00.54.611 AM                1         186021.778          53440    131017.985                  0                 18.976                      0             4285650            4202806                            0                    814

But with the SQL Profile in place it still in consistently around 90,000 milliseconds even today. There are no executions under 1800 milliseconds like before. It is a puzzle, but it got us through the issue. I am planning to leave this in place to prevent another production outage, but I suspect that these reports may be running longer than normal in many cases. But at least they are consistent and the business needs are being met.

So, this is another example of a SQL Profile to the rescue in a production database performance issue. But this was different because it was not clear that the one plan was always the best one. I went so far as to put it in and take it out and then put it in again. I ended up leaving it in because it resolved a performance issue that we had twice and do not want to have in the future. Hopefully any less than optimal performance outside of these peak times will be worth it since we are preventing an outage with significant impact to the business.

Bobby

Categories: DBA Blogs

Generate string based on pattern

Tom Kyte - Tue, 2020-10-20 07:06
Hi Chirs, Connor, Could you please help or suggest a way to generate string based on pattern Pattern - <b>^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$</b> I need to generate millions of string like <b>f9f8c8e2-0b20-4160-8f74-e836f4661fc5</b> matches with the pattern. e.g. <code>SELECT * FROM dual WHERE REGEXP_LIKE('f9f8c8e2-0b20-4160-8f74-e836f4661fc5', '^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$');</code>
Categories: DBA Blogs

Update rows when cursor returns no data

Tom Kyte - Tue, 2020-10-20 07:06
Purchase_Mas contains data of purchase master and payment_amt contains data of payment received from party. Cursor c2 does not return any value when not found in payment but still i want to some calculation happen even data not found in payment table. run following script and when you fire a query " select * from purchase_mas_tmp ; " Look at ( ID =5 and party code=12 ) and ( ID = 6 and party code= 14 ) when payment amount not found in cursor c2 but i want to os_amt display as 10000 for 5 and 20000 for 6 id of payment. so how its possible <code>create table PURCHASE_MAS ( id NUMBER, party_code NUMBER, total_pcs NUMBER, total_amt NUMBER, purchase_date DATE, reg_flg CHAR(1), discount_amt NUMBER ); create table PAYMENT ( id NUMBER, party_code NUMBER, payment_date DATE, payment_amt NUMBER ); create global temporary table PURCHASE_MAS_TMP ( id NUMBER, party_code NUMBER, total_pcs NUMBER, total_amt NUMBER, purchase_date DATE, reg_flg CHAR(1), payment_date DATE, payment_amt NUMBER, os_amt NUMBER, discount_amt NUMBER ) on commit preserve rows; insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (5, 12, 456, 10000, to_date('01-08-2018', 'dd-mm-yyyy'), 'Y', 100); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (6, 14, 878, 20000, to_date('21-08-2018', 'dd-mm-yyyy'), 'N', 200); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (1, 11, 457, 30000, to_date('11-08-2018', 'dd-mm-yyyy'), 'Y', 300); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (2, 12, 658, 40000, to_date('10-08-2018', 'dd-mm-yyyy'), 'Y', 400); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (3, 11, 1454, 50000, to_date('07-08-2018', 'dd-mm-yyyy'), 'Y', 500); insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT) values (4, 13, 1254, 60000, to_date('18-08-2018', 'dd-mm-yyyy'), 'N', 600); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (1, 11, to_date('01-09-2018', 'dd-mm-yyyy'), 2500); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (2, 12, to_date('12-09-2018', 'dd-mm-yyyy'), 3000); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (3, 11, to_date('11-09-2018', 'dd-mm-yyyy'), 30000); insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT) values (4, 13, to_date('21-09-2018', 'dd-mm-yyyy'), 400); declare cursor c1 is select id, party_code, total_pcs, total_amt, purchase_date, reg_flg , discount_amt from purchase_mas; cursor c2 is select id, party_code, sum(payment_amt) payment_amt from payment group by id, party_code ; begin for i in c1 loop insert into purchase_mas_tmp (id, party_code, total_pcs, total_amt, purchase_date, reg_flg,discount_amt) values (i.id, i.party_code, i.total_pcs, i.total_amt, i.purchase_date, i.reg_flg, i.discount_amt); end loop; for i in c2 loop update purchase_mas_tmp tbl set payment_amt = nvl(i.payment_amt,0), os_amt = tbl.total_amt - nvl(tbl.discount_amt,0) - nvl(i.payment_amt,0) where id = i.id and party_code = i.party_code ; end loop; end; -- select * from purchase_mas_tmp ; /* drop table PURCHASE_MAS purge ; drop table PAYMENT purge ; drop table purchase_mas_tmp purge ; */</code>
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs