DBA Blogs

SQL queries consuming host memory

Tom Kyte - Fri, 2016-11-18 15:46
Hi Tom, I would like to know which queries currently running is consuming host memory From OS level i came to know that the below process is consuming memory - $ ps aux | head -1; ps aux | sort -rn +3 | head USER PID %CPU %MEM VSZ...
Categories: DBA Blogs

Role behaviour change in 12c

Tom Kyte - Fri, 2016-11-18 15:46
I have created one package(pk_data_updates) in schema A and assigned Execute permission to existing Role Role1(ROLE Role1 is password protected). this Role (R1) is assigned to user venkat. CREATE ROLE Role1 IDENTIFIED BY roc_super; GRANT EXECU...
Categories: DBA Blogs

when redolog turns into archivelog?

Tom Kyte - Fri, 2016-11-18 15:46
Hi, My confiurations says that my redologs are of <b>200MB</b> max, and they are switched every 30minutes. <code>SQL> show parameter ARCHIVE_LAG_TARGET; archive_lag_target 1800 </code> Of course, with more activity, redo logs, are archived ...
Categories: DBA Blogs

Parse HTML in PL/SQL

Tom Kyte - Fri, 2016-11-18 15:46
Hi, Tom! I need some advice in what is the best way to solve the following task in PL/SQL. I have the following link https://wiki.xmldation.com/Support/EPC/List_of_SEPA_countries. I need to get IBAN-s (the third column) from this site and c...
Categories: DBA Blogs

Dynamic Query is running Slow in 12 C

Tom Kyte - Fri, 2016-11-18 15:46
Hi Tom, I had 1 sql file which was running perfectly fine in 10G but after migration to 12 c performance is very poor. The query logic is : A table has around 550 columns and data is getting inserted into this table from XL Sheet. Once loaded then...
Categories: DBA Blogs

Oracle Container Cloud Service is available now!

I am happy to let you know that finally Oracle Container Cloud Service is publicly available. Oracle Container Cloud Service, a new service where customers can bring their own Docker containers and...

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

Links for 2016-11-17 [del.icio.us]

Categories: DBA Blogs

Diff BW Optimizer SQL Engine

Tom Kyte - Thu, 2016-11-17 03:06
Hi, I would like to know the difference Between Optimizer,SQL Engine and PL/SQL Engine? I asked this question So many times But I did not get answers from you. Thank you
Categories: DBA Blogs

Oracle Database Migration from HP-UX 64bit (Big-endian) to RHEL 64bit (Little-endian)

Tom Kyte - Thu, 2016-11-17 03:06
Hi Tom, We are planning to migrate Oracle Database from HP-UX 64bit (Big-endian) to RHEL 64bit (Little-endian). My question is can we migrate database from Big-endian format to Little-endian format using RMAN convert command? (like converting d...
Categories: DBA Blogs

comparing two databases, put result into new table

Tom Kyte - Thu, 2016-11-17 03:06
Hi Tom Thank you for having this service available I'm trying to create an application that will allow me to compare data from two tables by a common ID I would like to either: put the results (non matches) into a new table OR remove th...
Categories: DBA Blogs

cursor with dynamic sql

Tom Kyte - Thu, 2016-11-17 03:06
Hi , I need to write a procedure which has a cursor that stores the list of table names. We need to loop the cursor and for each table name it should look for a code in that particular table. The final output of the procedure should be the list of ta...
Categories: DBA Blogs

Track users who make updates to a EBS database table from backend

Tom Kyte - Thu, 2016-11-17 03:06
Hi, We have custom triggers on a few Oracle EBS tables to track & log any insert, update & delete. The trigger captures any changes made through the front end screens. However, if a user does an insert, update or delete to a table in the back end ...
Categories: DBA Blogs

Spool is starting new line after 500 characters

Tom Kyte - Thu, 2016-11-17 03:06
I've got Spool working in that the output from a query is returning everything, but after 500 characters, it starts a new line. So if if I had a line that was 502 characters ending with 'bananasarecool', then it would look something along the lin...
Categories: DBA Blogs

RMAN backup

Tom Kyte - Thu, 2016-11-17 03:06
Hi,guy! I use RMAN to backup the database on Nov 13 through the COMMVAULT software,but on Nov 15,the oracle server CPU is high,then I connect to the server and check,found that the wait event is 'RMAN backup&rcovery I/O',and the logon time is the ...
Categories: DBA Blogs

Update query in after insert trigger

Tom Kyte - Thu, 2016-11-17 03:06
Hello Tom- my requirement is When a record is inserted into ERR table, a post-event trigger will fire updating the D field to accept sys date (3 columns has to update in my original requirement). I've written the below code, however after the recor...
Categories: DBA Blogs

Last DML Operation timestamp on Production

Tom Kyte - Thu, 2016-11-17 03:06
Hi, I need to get timestamp of last DML operation performed on table from Production. I tried using - SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) from mytablename; SELECT MAX(SCN_TO_TIMESTAMP(ORA_ROWSCN)) from mytablename; Non of them worked...
Categories: DBA Blogs

Improving performance of top query

Bobby Durrett's DBA Blog - Wed, 2016-11-16 16:56

I’m on call this week. Here are the steps that I took to speed up a query today.

First I got an AWR report and found the top query. Also, someone from support told me to look at November 11 before the latest release and I found a similar top query.

I got a plan for both the new and old top queries – they both use the same plan which makes me think that the two queries are similar.

I looked at the top segments on the AWR report and found a particular table at the top of the logical reads. An index of that table was like number 5 on that list.

I looked at the columns of the table’s index to see how many distinct values there were. None of the three columns had more than 300 distinct values so they were not very selective. I noticed that there was a unique index on the table and the first column of that index had millions of distinct values.

I extracted some sample bind variable values for the query and find that the second bind variable was null or something like that. But, the index we were using included this second variable.

In looked at the bind variables and found that the first column from the unique index was part of the join conditions in the query. (The query had like 20 joins).

Then I extracted the query text and replaced the bind variables with literals to see how it would run. It used the unique index. I used hints to force the original index and compared to running with the unique index. It ran about 30 times faster with the unique index. I ran a few times to make sure it was all cached.

Then I tried to use SQLT’s coe_xfr_sql_profile.sql to force the plan that used the unique index but got an error. Had to download the latest version of SQLT to get it to work.

Now, on average, the query seems to run about 1000 times faster.

It is a delivered vendor package so it was nice to find the better plan and go behind the scenes to fix it. But, if another release comes out and changes this sql to a new sql_id we will have to create a new profile. It’s not perfect but its a good quick fix for my on call.

Bobby

Categories: DBA Blogs

Number of rows of a value from an unbounded, ignore nulls NEXT_VALUE or FIRST_VALUE

Tom Kyte - Wed, 2016-11-16 08:46
I understand the sql in the LiveSQL link. What I am wondering is how I find the number of rows the analytic function has to look to get the last/next value if IGNORE NULLS is part of the statement. If I look at the second row (REPORT_MONTH = 01-FE...
Categories: DBA Blogs

Synchronize specific data based on date using DBMS_COMPARISON

Tom Kyte - Wed, 2016-11-16 08:46
Hi Tom, I have already synchronizing data successfully between remote table and local table using DBMS_COMPARISON with scan mode FULL. But the synchronization performance very slow as of data growth. When I read the documentation, there is n...
Categories: DBA Blogs

database block and redo blocks

Tom Kyte - Wed, 2016-11-16 08:46
1.how the same blocks are copy/exist(while update transactions) in database block buffer and redolog buffer 2.which one (DBBC and RLB) is first got activated
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs