DBA Blogs

Shared Undo Quota - Can We Dedicate specific amount for a single user?

Tom Kyte - Wed, 2018-03-07 09:46
Hi, I am facing an issue, where one single process (eg process x) can consume most of the undo-quota, and then fails for rollback segment error. When I'll increase my database undo retention, and increase my undo tablespace size, it'll grow for a...
Categories: DBA Blogs

My New David Bowie Blog

Richard Foote - Tue, 2018-03-06 17:11
Just a short note to say I’ve started a new blog that we focus on the one and only David Bowie: https://davidbowiewordpresscom.wordpress.com/ Those of you that only vaguely know me, know I’m a huge David Bowie fan, so I thought it might be a bit of fun to write about his extraordinary body of work. I’m […]
Categories: DBA Blogs

Compare the Indexes of tables from two different databases

Tom Kyte - Tue, 2018-03-06 15:26
hi Tom I would like to compare indexes of tables from defferent database. challenge is that the tables have index name different. example db1 schema1 table1--> --> index1 (col1,col2) --> index2 (col11,col22,col33) --> index3 (col111,col2...
Categories: DBA Blogs

ORA-01652: Unable to extend temp segment by 2048 in table space TEMP

Tom Kyte - Tue, 2018-03-06 15:26
Hi Team, We have facing the issue with 1 query is consuming 331GB of TEMP tablespace. WE have configured 340GB of TEMP TS and have some limitation to add more temp files on DB server. due to it the other session get starved with TEMP tablespace an...
Categories: DBA Blogs

Load External table trimmed with varchar2 without null

Tom Kyte - Tue, 2018-03-06 15:26
Hi all, i have this CTL FILE: <code>CREATE TABLE EXT_T_SI_EG_RAI_170630E1166A ( WAEHRUNG CHAR(3) ,GESCHAEFT_ID VARCHAR2(48) ,GESCHAEFTSKATEGORIE CHAR(1) ,UNTERGESCHAEFT_ID VARCHA...
Categories: DBA Blogs

Index size got doubled after rebuild

Tom Kyte - Tue, 2018-03-06 15:26
Hi, We did index rebuild for the partition table. Index is also partitioned one. Before rebuilding index, the size of the index was 170 GB. But after rebuilding the size got doubled and it was 327GB. Used below command: alter index index_na...
Categories: DBA Blogs

How to trace PL/SQL procedure for tuning

Tom Kyte - Tue, 2018-03-06 15:26
Hi tom, There is an procedure which is running very long/ taking time So, how can i trace that procedure to check where is the issue. like - tkprod or trace session
Categories: DBA Blogs

Need help to select dates in Israel time from a database server located in US

Tom Kyte - Tue, 2018-03-06 15:26
select example_dt from tab where tabid='123'--example_dt is a date column in the table 'tab' In this above query how can I get/convert example_dt in Israel time if I am running this query from my US database server?
Categories: DBA Blogs

TDE and rekey master key

Tom Kyte - Tue, 2018-03-06 15:26
Hello, I want to use TDE tablespace encryption on my database. One part of my study is "how can I manage my Master key" : my security officer ask me to change regularly the key. I read in oracle documentation (https://docs.oracle.com/cd/E11882_0...
Categories: DBA Blogs

Pushing predicates into MySQL subqueries

Bobby Durrett's DBA Blog - Tue, 2018-03-06 13:16

I am just getting started looking at MySQL performance tuning but I thought I would share something that I may have figured out. I’m working with a query that does a count and a group by in a subquery and it runs too long. It seems that the query reads every row from a table instead of passing a predicate into the subselect and accessing only a couple of rows. MySQL 5.7 seems to behave this way normally. I have built a simple test case to show that MySQL 5.7 works differently than Oracle 12.1 in this situation.

I build a table called TEST with about 1,000,000 rows (1,1) and one row (2,2) and put an index on the first column.

create table test(a int,b int);

insert into test values(1,1);

insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;

-- 1048576 rows

select count(*) from test;

create index testi on test (a);

insert into test values (2,2);

analyze table test;

Then I have a subselect with a SUM and a GROUP by and a where clause condition outside of the subselect that would cause the subselect to look at only one row – the one with values (2,2).

select sum_b from
(select a,sum(b) sum_b
from test
group by a) inner_query
where 
a=2;

This takes almost 2 seconds running in an Amazon RDS instance:

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (1.53 sec)

The corresponding Oracle query running in an on-premises Linux VM runs faster:

SQL> select sum_b from
  2  (select a,sum(b) sum_b
  3  from test
  4  group by a) inner_query
  5  where
  6  a=2;

     SUM_B
----------
         2

Elapsed: 00:00:00.08

I realize that the hardware is not the same but I think they are reasonably close. Best I can tell the Oracle version is faster because Oracle pushes the a=2 predicate into the subselect but MySQL doesn’t. The Oracle execution plan shows the a=2 predicate in the inner index scan:

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |     6 |     4   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |       |     1 |     6 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |     1 |     6 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TESTI |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
...
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"=2)

I am still learning MySQL plans but it seems that MySQL does a full index scan:

As I said, I am new to MySQL so I may have missed some way around this. One simple solution would be to write your query without a subselect so that the predicate is in the group by query. This runs about as fast as the Oracle example above:

select sum(b) sum_b
from test
where 
a=2
group by a
--------------

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (0.10 sec)

This does a simple index lookup.

I have uploaded a zip of my Oracle and MySQL test here: zip

Maybe if you have used Oracle in the past and are now using MySQL 5.7 you should keep in mind that MySQL 5.7 does not push predicates into subqueries in the same situations that Oracle does and build your queries around this difference.

Bobby

 

 

Categories: DBA Blogs

nvl from different tables

Tom Kyte - Mon, 2018-03-05 21:06
Hi Tom, Thanks for being a great support for me over the years. I always look for your solutions whenever I am in need. Could not find anything on this hence posting my first question. In my query I am using few big tables with millions of r...
Categories: DBA Blogs

Generate totals based on group while selecting all columns

Tom Kyte - Mon, 2018-03-05 21:06
Hello- I need to create an output broken down by Account numbers and Locations. Here is my sample table: <code> Create table sample_dat ( POSITION_NO number (5), NAME varchar2(50), TITLE varchar2(50), ACCOUNT number(15), GAAP_A...
Categories: DBA Blogs

Analytical function RANK() vs. DENSE_RANK()

Tom Kyte - Mon, 2018-03-05 21:06
Hi, I have a very plain question about Analytical functions RANK() and DENSE_RANK(). Can you pls tell me a real scenario when we would use "RANK() and why not DENSE_RANK()" and vice versa. Something other than the students rank calculation. Thanks i...
Categories: DBA Blogs

Oracle Database 18c: Introduction to Memoptimized RowStore (Memory of a Free Festival)

Richard Foote - Sun, 2018-03-04 23:29
One of the potentially cool new features introduced in Oracle Database 18c is the Memoptimized RowStore, designed to improve the performance and scalability of key-value based queries. A new pool in the SGA called the Memoptimize Pool can be configured to store specific heap tables that you may wish to optimize, in a manner not […]
Categories: DBA Blogs

Oracle Forms 11G difference SET_ITEM_PROPERTY PROPERTY_TRUE/FALSE en PROPERTY_ON/OFF

Tom Kyte - Sun, 2018-03-04 08:26
Hi, A simple question, but hard to find: We have Oracle Forms 11G and for SET_ITEM_PROPERTY the following options: PROPERTY_ON / PROPERTY_OFF are used and also PROPERTY_TRUE / PROPERTY_FALSE (in the same Form!).... What is the difference bet...
Categories: DBA Blogs

nulls with NOT and booleans

Tom Kyte - Sun, 2018-03-04 08:26
Hi Tom, I've always understood it to be the case that a=b and a!=b both evaluate to FALSE if one or both is NULL. However, I'm seeing some odd behavior which I'm trying to distill and understand. <code> DECLARE v_Var1 VARCHAR2(12) := 'A'; ...
Categories: DBA Blogs

Problem with Oracle 11g at startup

Tom Kyte - Sun, 2018-03-04 08:26
Hello, i have problem with my Oracle 11G XE I can log into db with : sqlplus "sys as sysdba" Then to modify : alter system set processes=5000 scope=spfile; I do : shutdown immediate; And then startup but receive this error : ORA-04031: ...
Categories: DBA Blogs

Analytical function

Tom Kyte - Sun, 2018-03-04 08:26
Hi TOM group, Since yesterday I read the new version of Oracle 18c some of the elements like Analytical functions(As you said calculations), while reading I didn't find any examples to better understand about it like Rank_*...etc. I read the only the...
Categories: DBA Blogs

Pivoting via SQL

Tom Kyte - Sun, 2018-03-04 08:26
Hai, Tom sir...I have table with columns like this...I have 6 columns in my table with data in below.I need to write an SQL code to get the in below output.Out i mentioned at the ending.Please look at and give me the sol. Create table t3 ( ...
Categories: DBA Blogs

SQL with Apache Spark, easy!

Kubilay Çilkara - Sat, 2018-03-03 03:10
Reading about cluster computing developments like Apache Spark and SQL I decided to find out.

What I was after was to see how easy is to write SQL in Spark-SQL. In this micro-post I will show you how easy is to SQL a JSON file.

For my experiment I will use my chrome_history.json file which you can download from your chrome browser using the extension www.JSON-XLS.com. To run the SQL query on PySpark on my laptop I will use the PyCharm IDE. After little bit of configuration on PyCharm, setting up environments (SPARK_HOME), there it is: It only takes 3 lines to be able SQL query a JSON document in Spark-SQL.

(click image to enlarge)



Think of the possibilities with SQL, the 'cluster' partitioning and parallelisation you can achieve

Links:
Apache Spark: https://spark.apache.org/downloads.html
PyCharm: https://www.jetbrains.com/pycharm/
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs