DBA Blogs

May 17th AZORA Meetup – Last until September!

Bobby Durrett's DBA Blog - Tue, 2019-04-30 14:28
#meetup_oembed .mu_clearfix:after { visibility: hidden; display: block; font-size: 0; content: " "; clear: both; height: 0; }* html #meetup_oembed .mu_clearfix, *:first-child+html #meetup_oembed .mu_clearfix { zoom: 1; }#meetup_oembed { background:#eee;border:1px solid #ccc;padding:10px;-moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;margin:0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 12px; }#meetup_oembed h3 { font-weight:normal; margin:0 0 10px; padding:0; line-height:26px; font-family:Georgia,Palatino,serif; font-size:24px }#meetup_oembed p { margin: 0 0 10px; padding:0; line-height:16px; }#meetup_oembed img { border:none; margin:0; padding:0; }#meetup_oembed a, #meetup_oembed a:visited, #meetup_oembed a:link { color: #1B76B3; text-decoration: none; cursor: hand; cursor: pointer; }#meetup_oembed a:hover { color: #1B76B3; text-decoration: underline; }#meetup_oembed a.mu_button { font-size:14px; -moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;border:2px solid #A7241D;color:white!important;text-decoration:none;background-color: #CA3E47; background-image: -moz-linear-gradient(top, #ca3e47, #a8252e); background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0, #a8252e), color-stop(1, #ca3e47));disvplay:inline-block;padding:5px 10px; }#meetup_oembed a.mu_button:hover { color: #fff!important; text-decoration: none; }#meetup_oembed .photo { width:50px; height:50px; overflow:hidden;background:#ccc;float:left;margin:0 5px 0 0;text-align:center;padding:1px; }#meetup_oembed .photo img { height:50px }#meetup_oembed .number { font-size:18px; }#meetup_oembed .thing { text-transform: uppercase; color: #555; }
AZORA Meetup before summer heats up! Two presentations – Friday, May 17th

Friday, May 17, 2019, 12:00 PM

Republic Services – 2nd Floor Conference Rooms
14400 N 87th St (AZ101 & Raintree) Scottsdale, AZ

8 AZORAS Attending

AZORA Meetup before the summer heats up! We will be featuring two presentations on Friday May 17th meeting. Logistics are the same at friendly Republic Services, an afternoon with other Oracle types between Noon till 4pm. We will follow typical agenda of Lunch – Learn – Learn some more – Let us call it a weekend! Here are the details: Meeting Agend…

Check out this Meetup →

This is our last Meetup until our Summer break. Come check out two great presentations.

Doug Hood from Oracle will talk about the Oracle In-Memory database feature. We appreciate Oracle providing us with this technical content to support AZORA.

AZORA’s own Stephen Andert will be sharing a non-technical presentation on Networking. He just gave the same talk at the national Oracle user group meeting called Collaborate 19 so it will be great to have him share with his local user group.

Looking forward to seeing you there.

Bobby

P.S. AZORA is the Arizona Oracle User Group, and we meet in the Phoenix, Arizona area.

Categories: DBA Blogs

OGG-01298 could not find column TRANSACTION

VitalSoftTech - Mon, 2019-04-29 19:39
Question: When upgrading GoldenGate from 11 to 12c, on starting up the Replicat I get the following error. What caused this and how is it resolved? OGG-01298 Column function diagnostic message: could not find column “TRANSACTION”. Answer: The OGG-01298 error is returned on the Replicat startup after the GoldenGate is upgraded to 12c. This happens […]
Categories: DBA Blogs

.NET Core Connection String to RAC database

Tom Kyte - Mon, 2019-04-29 02:46
Hello, Ask Tom Team. We have a .Net Core app connecting to a Oracle 18c RAC database. We are not using tnsnames.ora file (there is no oracle client installed on the client-server side). We are using managed .Net Core oracle client and the followin...
Categories: DBA Blogs

Stored Procedure behaves randomly - sometimes takes 15 minutes where it should take 15 milli-seconds.

Tom Kyte - Mon, 2019-04-29 02:46
We developed a procedure to be used in database-A and in database-A. In this procedure we send an input parameter on the basis of which a select statement searches the data from a table located in remote database-B. The selected columns are set in Ou...
Categories: DBA Blogs

Cluster Waits Rac database

Tom Kyte - Mon, 2019-04-29 02:46
Hello, Ask Tom Team. I have a two-nodes RAC database running 18.4.0 and I'm running a load test (lots of inserts). Basically, two tables are being hit. These tables are using identity column and have a relationship (fk). I'm checking OEM 13c and t...
Categories: DBA Blogs

High commit wait on RAC database

Tom Kyte - Mon, 2019-04-29 02:46
Hello, Ask Tom Team. I have a two-nodes RAC database running 18.4.0 and I'm running a load test (lots of inserts). Basically, two tables are being hit. These tables are using identity column and have a relationship (fk). I'm checking OEM 13c and t...
Categories: DBA Blogs

Redo log file size and Database Performance

Tom Kyte - Mon, 2019-04-29 02:46
Hi there, We have a database setup as follows 1) SQL server -- Where transactions are happening here 2) Oracle(DSS)-- we are transferring the incremental data from SQL server(NOT ALL THE TABLES ONLY 22 TABLES) with the help of a 24/7 running ...
Categories: DBA Blogs

Redo logs Sizing

Tom Kyte - Mon, 2019-04-29 02:46
Hello, Ask Tom Team. What is the best practice: small redo log groups or have a few but with a bigger size? Thanks in advanced. Regards,
Categories: DBA Blogs

RMAN - Full vs. Incremental - performance problem

Tom Kyte - Thu, 2019-04-25 22:46
Hi. I am testing RMAN. I have run an incremental 0 and an incremental 1 cumulative test without having any database activity in-between these test. I am using OmniBack media manager with RMAN. I have three databases. Here are the timing results ...
Categories: DBA Blogs

Subtract time from a constant time

Tom Kyte - Wed, 2019-04-24 10:06
Hello there, I want to create a trigger that will insert a Time difference value into a table Example: I have attendance table Sign_in date; Sign_out date; Late_in number; Early_out number; Now I want to create a trigger that will insert la...
Categories: DBA Blogs

Clob column in RDBMS(oracle) table with key value pairs

Tom Kyte - Wed, 2019-04-24 10:06
In our product in recent changes, oracle tables are added with clob column having key value pairs in xml/json format with new columns. <b>example of employee:(Please ignore usage of parenthesis) </b> 100,Adam,{{"key": "dept", "value": "Marketi...
Categories: DBA Blogs

current value of sequence

Tom Kyte - Wed, 2019-04-24 10:06
Hi. Simple question :-) Is it possible to check current value of sequence? I though it is stored in SEQ$ but that is not true (at least in 11g). So is it now possible at all? Regards
Categories: DBA Blogs

Partner Webcast – Continuous Integration & Delivery on Oracle SOA Cloud Service

Oracle SOA Cloud Service provides an integration platform as a service (iPaaS) so that you can quickly provision your new platform, start developing and deploying your APIs and integration projects...

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

Bitmap Index On Column With 212552698 Distinct Values, What Gives? (I’d Rather Be High)

Richard Foote - Mon, 2019-04-22 21:45
In my previous post on Indexing The Autonomous Warehouse, I highlighted how it might be necessary to create indexes to improve the performance and scalability of highly selective queries, as it might on any Data Warehouse running on an Exadata platform. In the post, I created a Bitmap Index and showed how improve SQL performance […]
Categories: DBA Blogs

Understanding Nested Lists Dictionaries of JSON in Python and AWS CLI

Pakistan's First Oracle Blog - Sat, 2019-04-20 03:01

After lots of hair pulling, bouts of frustration, I was able to grasp this nested list and dictionary thingie in JSON output of AWS cli commands such as describe-db-instances and others. If you run the describe-db-instances for rds or describe-instances for ec2, you get a huge pile of JSON mumbo-jumpo with all those curly and square brackets studded with colons and commas. The output is heavily nested.


For example, if you do :

aws rds describe-db-instances

you get all the information but heavily nested within. Now if you only want to extract or iterate through, say VPCSecurityGroupId of all database instances, then you have to traverse all that nested information which comprises of dictionary consisting of keys which have values as arrays and those arrays have more dictionaries and so on.

After the above rant, let me try to ease the pain a bit by explaining this. For clarity, I have just taken out following chunk from describe-db-instance output. Suppose, the only thing you are interested in is the value of VpcSecurityGroupId from  following chunk:

mydb=rds.describe_db_instances(DBInstanceIdentifier=onedb)
mydb= {'DBInstances':
          [
            {'VpcSecurityGroups': [ {'VpcSecurityGroupId': 'sg-0ed48bab1d54e9554', 'Status': 'active'}]}
          ]
       }

The variable mydb is a dictionary with key DBInstances. This key DBInstances has an array as its value. Now the first item of that array is another dictionary and the first key of that dictionary is VpcSecurityGroups. Now the value this key VpcSecurityGroups another array. This another array's first item is again a dictionary. This last dictionary has a key VpcSecurityGroupId and we want value of this key.

If your head has stopped spinning, then read on and stop cursing me as I am going to demystify it now.

If you want to print that value just use following command:

mydb['DBInstances'][0]['VpcSecurityGroups'][0]['VpcSecurityGroupId']

So the secret is that if its a dictionary, then use key name and if its an array then use index and keep going. That's all there is to it. Full code to print this using Python, boto3 etc is as follows:

import boto3
import click

rds = boto3.client('rds',region_name='ap-southeast-2')
dbs = rds.describe_db_instances()

@click.group()
def cli():
    "Gets RDS data"
    pass

@cli.command('list-database')
@click.argument('onedb')
def list_database(onedb):
    "List info about one database"
    mydb=rds.describe_db_instances(DBInstanceIdentifier=onedb)
    #print(mydb)
    #Following line only prints value of VpcSecurityGroupId of RDS instance
    print(mydb['DBInstances'][0]['VpcSecurityGroups'][0]['VpcSecurityGroupId'])
    #Following line only prints value of OptionGroup of RDS instance
    print(mydb['DBInstances'][0]['OptionGroupMemberships'][0]['OptionGroupName'])
    #Following line only prints value of Parameter Group of RDS instance
    print(mydb['DBInstances'][0]['DBParameterGroups'][0]['DBParameterGroupName'])

if __name__ == '__main__':
    cli()


I hope that helps. If you know any easier way, please do favor and let us know in comments. Thanks.

Categories: DBA Blogs

Migrating Oracle Database & Non Oracle Database to Oracle Cloud

You can directly move / migrate various source databases into different target cloud deployments running the Oracle Cloud. Oracle automated tools for migration will move on premise database to the...

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

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6843, maximum: 2000)

Tom Kyte - Wed, 2019-04-17 13:26
HI iam using below query to read xml from blob and find the string but facing error buffer to small ora-22835 blob to raw conversion (actual 15569,mximum 2000) please help me out with below example <code> SELECT XMLTYPE (UTL_RAW.cast_to_varchar...
Categories: DBA Blogs

Merge two rows into one row

Tom Kyte - Wed, 2019-04-17 13:26
Hi Tom, I seek your help on how to compare two rows in the table and if they are same merge the rows. <code>create table test(id number, start_date date, end_date date, col1 varchar2(10), col2 varchar2(10), col3 varchar2(10)); insert into t...
Categories: DBA Blogs

Example of coe_xfr_sql_profile force_match TRUE

Bobby Durrett's DBA Blog - Wed, 2019-04-17 10:57

Monday, I used the coe_xfr_sql_profile.sql script from Oracle Support’s SQLT scripts to resolve a performance issue. I had to set the parameter force_match to TRUE so that the SQL Profile I created would apply to all SQL statements with the same FORCE_MATCHING_SIGNATURE value.

I just finished going off the on-call rotation at 8 am Monday and around 4 pm on Monday a coworker came up to me with a performance problem. A PeopleSoft Financials job was running longer than it normally did. Since it had run for several hours, I got an AWR report of the last hour and looked at the SQL ordered by Elapsed Time section and found a number of similar INSERT statements with different SQL_IDs.

The inserts were the same except for certain constant values. So, I used my fmsstat2.sql script with ss.sql_id = ’60dp9r760ja88′ to get the FORCE_MATCHING_SIGNATURE value for these inserts. Here is the output:

FORCE_MATCHING_SIGNATURE 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 rows processed
------------------------ ------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
     5442820596869317879 60dp9r760ja88         3334601 15-APR-19 05.00.34.061 PM                1         224414.511     224412.713         2.982                  0                      0                   .376             5785269                 40                   3707

Now that I had the FORCE_MATCHING_SIGNATURE value 5442820596869317879 I reran fmsstat2.sql with ss.FORCE_MATCHING_SIGNATURE = 5442820596869317879 instead of ss.sql_id = ’60dp9r760ja88′ and got all of the insert statements and their PLAN_HASH_VALUE values. I needed these to use coe_xfr_sql_profile.sql to generate a script to create a SQL Profile to force a better plan onto the insert statements. Here is the beginning of the output of the fmsstat2.sql script:

FORCE_MATCHING_SIGNATURE 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 rows processed
------------------------ ------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
     5442820596869317879 0yzz90wgcybuk      1314604389 14-APR-19 01.00.44.945 PM                1            558.798        558.258             0                  0                      0                      0               23571                  0                    812
     5442820596869317879 5a86b68g7714k      1314604389 14-APR-19 01.00.44.945 PM                1            571.158        571.158             0                  0                      0                      0               23245                  0                    681
     5442820596869317879 9u1a335s936z9      1314604389 14-APR-19 01.00.44.945 PM                1            536.886        536.886             0                  0                      0                      0               21851                  0                      2
     5442820596869317879 a922w6t6nt6ry      1314604389 14-APR-19 01.00.44.945 PM                1            607.943        607.943             0                  0                      0                      0               25948                  0                   1914
     5442820596869317879 d5cca46bzhdk3      1314604389 14-APR-19 01.00.44.945 PM                1            606.268         598.11             0                  0                      0                      0               25848                  0                   1763
     5442820596869317879 gwv75p0fyf9ys      1314604389 14-APR-19 01.00.44.945 PM                1            598.806        598.393             0                  0                      0                      0               24981                  0                   1525
     5442820596869317879 0u2rzwd08859s         3334601 15-APR-19 09.00.53.913 AM                1          18534.037      18531.635             0                  0                      0                      0              713757                  0                     59
     5442820596869317879 1spgv2h2sb8n5         3334601 15-APR-19 09.00.53.913 AM                1          30627.533      30627.533          .546                  0                      0                      0             1022484                 27                    487
     5442820596869317879 252dsf173mvc4         3334601 15-APR-19 09.00.53.913 AM                1          47872.361      47869.859          .085                  0                      0                      0             1457614                  2                    476
     5442820596869317879 25bw3269yx938         3334601 15-APR-19 09.00.53.913 AM                1         107915.183     107912.459         1.114                  0                      0                      0             2996363                 26                   2442
     5442820596869317879 2ktg1dvz8rndw         3334601 15-APR-19 09.00.53.913 AM                1          62178.512      62178.512          .077                  0                      0                      0             1789536                  3                   1111
     5442820596869317879 4500kk2dtkadn         3334601 15-APR-19 09.00.53.913 AM                1         106586.665     106586.665         7.624                  0                      0                      0             2894719                 20                   1660
     5442820596869317879 4jmj30ym5rrum         3334601 15-APR-19 09.00.53.913 AM                1          17638.067      17638.067             0                  0                      0                      0              699273                  0                    102
     5442820596869317879 657tp4jd07qn2         3334601 15-APR-19 09.00.53.913 AM                1          118948.54      118890.57             0                  0                      0                      0             3257090                  0                   2515
     5442820596869317879 6gpwwnbmch1nq         3334601 15-APR-19 09.00.53.913 AM                0          48685.816      48685.816          .487                  0                      0                  1.111             1433923                 12                      0
     5442820596869317879 6k1q5byga902a         3334601 15-APR-19 09.00.53.913 AM                1            2144.59        2144.59             0                  0                      0                      0              307369                  0                      2

The first few lines show the good plan that these inserts ran on earlier runs. The good plan has PLAN_HASH_VALUE 1314604389 and runs in about 600 milliseconds. The bad plan has PLAN_HASH_VALUE 3334601 and runs in 100 or so seconds. I took a look at the plans before doing the SQL Profile but did not really dig into why the plans changed. It was 4:30 pm or so and I was trying to get out the door since I was not on call and wanted to get home at a normal time and leave the problems to the on-call DBA. Here is the good plan:

Plan hash value: 1314604389

------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                |                    |       |       |  3090 (100)|          |
|   1 |  HASH JOIN RIGHT SEMI           |                    |  2311 |  3511K|  3090   (1)| 00:00:13 |
|   2 |   VIEW                          | VW_SQ_1            |   967 | 44482 |  1652   (1)| 00:00:07 |
|   3 |    HASH JOIN                    |                    |   967 | 52218 |  1652   (1)| 00:00:07 |
|   4 |     TABLE ACCESS FULL           | PS_PST_VCHR_TAO4   |    90 |  1980 |    92   (3)| 00:00:01 |
|   5 |     NESTED LOOPS                |                    | 77352 |  2417K|  1557   (1)| 00:00:07 |
|   6 |      INDEX UNIQUE SCAN          | PS_BUS_UNIT_TBL_GL |     1 |     5 |     0   (0)|          |
|   7 |      TABLE ACCESS BY INDEX ROWID| PS_DIST_LINE_TMP4  | 77352 |  2039K|  1557   (1)| 00:00:07 |
|   8 |       INDEX RANGE SCAN          | PS_DIST_LINE_TMP4  | 77352 |       |   756   (1)| 00:00:04 |
|   9 |   TABLE ACCESS BY INDEX ROWID   | PS_VCHR_TEMP_LN4   | 99664 |   143M|  1434   (1)| 00:00:06 |
|  10 |    INDEX RANGE SCAN             | PSAVCHR_TEMP_LN4   | 99664 |       |   630   (1)| 00:00:03 |
------------------------------------------------------------------------------------------------------

Here is the bad plan:

Plan hash value: 3334601

---------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |                    |       |       |  1819 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID       | PS_VCHR_TEMP_LN4   |  2926 |  4314K|  1814   (1)| 00:00:08 |
|   2 |   NESTED LOOPS                     |                    |  2926 |  4446K|  1819   (1)| 00:00:08 |
|   3 |    VIEW                            | VW_SQ_1            |     1 |    46 |     4   (0)| 00:00:01 |
|   4 |     SORT UNIQUE                    |                    |     1 |    51 |            |          |
|   5 |      TABLE ACCESS BY INDEX ROWID   | PS_PST_VCHR_TAO4   |     1 |    23 |     1   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                    |     1 |    51 |     4   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                    |     1 |    28 |     3   (0)| 00:00:01 |
|   8 |         INDEX UNIQUE SCAN          | PS_BUS_UNIT_TBL_GL |     1 |     5 |     0   (0)|          |
|   9 |         TABLE ACCESS BY INDEX ROWID| PS_DIST_LINE_TMP4  |     1 |    23 |     3   (0)| 00:00:01 |
|  10 |          INDEX RANGE SCAN          | PS_DIST_LINE_TMP4  |     1 |       |     2   (0)| 00:00:01 |
|  11 |        INDEX RANGE SCAN            | PS_PST_VCHR_TAO4   |     1 |       |     1   (0)| 00:00:01 |
|  12 |    INDEX RANGE SCAN                | PSAVCHR_TEMP_LN4   |   126K|       |  1010   (1)| 00:00:05 |
---------------------------------------------------------------------------------------------------------

Notice that in the bad plan the Rows column has 1 in it on many of the lines, but in the good plan it has larger numbers. Something about the statistics and the values in the where clause caused the optimizer to build the bad plan as if no rows would be accessed from these tables even though many rows would be accessed. So, it made a plan based on wrong information. But I had no time to dig further. I did ask my coworker if anything had changed about this job and nothing had.

So, I created a SQL Profile script by going to the utl subdirectory under sqlt where it was installed on the database server. I generated the script by running coe_xfr_sql_profile gwv75p0fyf9ys 1314604389. I edited the created script by the name coe_xfr_sql_profile_gwv75p0fyf9ys_1314604389.sql and changed the setting force_match=>FALSE to force_match=>TRUE and ran the script. The long running job finished shortly thereafter, and no new incidents have occurred in future runs.

The only thing that confuses me is that when I run fmsstat2.sql now with ss.FORCE_MATCHING_SIGNATURE = 5442820596869317879 I do not see any runs with the good plan. Maybe future runs of the job have a different FORCE_MATCHING_SIGNATURE and the SQL Profile only helped the one job. If that is true, the future runs may have had the correct statistics and run the good plan on their own.

I wanted to post this to give an example of using force_match=>TRUE with coe_xfr_sql_profile. I had an earlier post about this subject, but I thought another example could not hurt. I also wanted to show how I use fmsstat2.sql to find multiple SQL statements by their FORCE_MATCHING_SIGNATURE value. I realize that SQL Profiles are a kind of band aid rather than a solution to the real problem. But I got out of the door by 5 pm on Monday and did not get woken up in the middle of the night so sometimes a quick fix is what you need.

Bobby

Categories: DBA Blogs

Help with v$statname and v$sysstat

Tom Kyte - Tue, 2019-04-16 19:06
Tom, Can you please provide info on how can I find the full table scan and index table scan activities in the database using v$statname and v$sysstat? Do I need to set TIMED_STATISTICS=TRUE before running queries against v$sysstat?...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs