DBA Blogs

Efficient way to fetch data using rownumber from a billion record table??

Tom Kyte - Fri, 2019-02-15 10:06
I'm trying to fetch required data from a billion record table using the row number approch (i.e, select "ingest_ts_utc" from (SELECT to_char(sys_extract_utc(systimestamp), 'YYYY-MM-DD HH24:MI:SS.FF') as "ingest_ts_utc" ,ROWNUM as rno from XYZ.ABC ) ...
Categories: DBA Blogs

using sql loader for a table where you only have insert privileges via a non default role

Tom Kyte - Fri, 2019-02-15 10:06
TeamTOM, I'm trying to use Sql Loader to load a text file into a normal Oracle table (as opposed to an external one). Unfortunately I only have insert rights to the table via a non default role. If I were just doing a simple insert statement in sq...
Categories: DBA Blogs

Access Table Without Synonym and Without Schema Name

Tom Kyte - Thu, 2019-02-14 16:06
I have a schema called HCR_SCHEMA, in which I have one table called TEST_EMP, and I have a user say USER_A and granted him select on the TEST_EMP table. I connected as USER_A as tried to execute SELECT * FROM TEST_EMP. Obviosuly it threw me error ...
Categories: DBA Blogs

print an XMLTYPE Record Type variable

Tom Kyte - Thu, 2019-02-14 16:06
we have a following definition TYPE response_t IS RECORD ( doc XMLTYPE ); A local variable is defined with that record type lv_resp response_t; I want to print the value this local variable has stored.I tried ...
Categories: DBA Blogs

Expdb views_as_tables failes after view recreated, bug or feature?

Tom Kyte - Thu, 2019-02-14 16:06
This has been giving me fits. I?m running 12.2.0.1 with no maintenance. In the test case below I create a view TEST_VIEW with three columns A,B,C returning one row. Both select from the view and export of the view work as expected. I ...
Categories: DBA Blogs

Partner Webcast – Next-Gen Automated Systems Management for Oracle Exadata

Oracle Exadata Database Machine has been critical for the digital transformation of many companies. But in many cases Oracle Exadata customers have to use multiple tools for monitoring,...

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

Update production database from ETL Process

Tom Kyte - Wed, 2019-02-13 21:46
Hello, Ask Tom Team. I am using a Microsoft SSIS ETL Package to insert data to Oracle (source and destination). I want to do an incremental load every day but I have to flag the rows that are already loaded in the destination. It is not just s...
Categories: DBA Blogs

DB Link between Oracle 12c (Linux) and Oracle 11.2 R2 (Windows)

Tom Kyte - Wed, 2019-02-13 21:46
Hi everyone, I have a problem with DB Link. I'm trying to create a dblink from and Oracle 12c (installed on Linux Server) and Oracle 11.2 R2 (installed on Windows Server). I've correctly created the dblink, but when i try to query the result is: "<b...
Categories: DBA Blogs

Merge using collections

Tom Kyte - Wed, 2019-02-13 21:46
Hi Tom, I am just trying to understand if the table function will work with collections. Please find my below code. Script: <code>create table setm_students (id number, name varchar2(10)); create table setm_students_target as select * fro...
Categories: DBA Blogs

getting rows N through M of a result set

Tom Kyte - Wed, 2019-02-13 21:46
I would like to fetch data after joining 3 tables and sorting based on some field. As this query results into approx 100 records, I would like to cut the result set into 4, each of 25 record. and I would like to give sequence number to each r...
Categories: DBA Blogs

Permission issues when loading text file to external table

Tom Kyte - Wed, 2019-02-13 21:46
Hey Tom, First of all, I'm sorry if this has been asked already, I simply couldn't find an answer after days of googling. Here it goes: I've created a SP to load a text file into an external table on 10g as per instructed here and many more places...
Categories: DBA Blogs

Best practices for keeping passwords hidden

Tom Kyte - Wed, 2019-02-13 03:26
Can you recommend best practices for keeping passwords hidden in command lines for impdp/expdp, shell scripts etc.?
Categories: DBA Blogs

Object View To Manipulate Data In Tables

Tom Kyte - Wed, 2019-02-13 03:26
Hi, I have been using Object View, User Defined Types in Oracle for sometime. I tried to follow the approach here. <code>https://docs.oracle.com/cd/A64702_01/doc/server.805/a58241/ch_ov.htm</code> I am wondering if we put all the data manipul...
Categories: DBA Blogs

Lob: Basic - Secure

Tom Kyte - Wed, 2019-02-13 03:26
We observed frequent wait event 'enq HW - contention' in performance reports with SQL includes LOB Objects. Lob object Details ? Object - WF_WORKFLOW Owner - IBMUCD What changes we can face if we convert LOB from basic to secure?...
Categories: DBA Blogs

Distinct count across multiple tables

Tom Kyte - Wed, 2019-02-13 03:26
I have three possible places where accounts data can be requested. On the MONTHLY USAGE, HISTORY USAGE or ENROLLMENT tables. The same account(s) could be on all three tables, or on the table(S) multiple times, but some accounts could be on one or ...
Categories: DBA Blogs

lost update

Tom Kyte - Wed, 2019-02-13 03:26
Tom, I was reading your book expert one-on-one and in Chapter 3: Locking and Concurrency, you have mentioned a scenario of lost update. I was trying to simulate that and I did not succeed in doing the test. I tried to update emp table in session...
Categories: DBA Blogs

Update a column after multiple criteria

Tom Kyte - Wed, 2019-02-13 03:26
Hello, I have the following data: Id AMAOUNT NO_PRATITION ID_STRATEGY 1 100 99 XXX 2 200 99 XXX 3 0 99 YYY 4 100 99 YYY 5 200 99 YYY 6 0 99 ZZZ 7 100 99 ZZZ 8 200 99 ...
Categories: DBA Blogs

How Oracle Database gets the data from multiple left joins

Tom Kyte - Wed, 2019-02-13 03:26
Hi Tom, I came across a scenario today. table1 id integer name varchar table2 id integer name varchar designation varchar table3 id integer name varchar relation_status varchar I have the query as below which is working fine ...
Categories: DBA Blogs

One query in MySQL Performance Schema

Bobby Durrett's DBA Blog - Tue, 2019-02-12 16:48

I am learning about MySQL performance tuning. I read the Performance Schema chapter of the MySQL 5.7 manual and I have a MySQL 5.7.20 database running under Linux. I have a test table and I am running a simple query that does a full scan of the table and returns the sum of a couple of columns. The goal was to look at some Performance Schema tables to see what they would tell me about this simple query. I bounce the MySQL database service before running my test script so that the query pulls data from disk and not memory. A zip of my test script and its output is here: zip

I looked at the tables that had this pattern events_*_history_long. These are the four tables:

There seems to be a hierarchical relationship among these tables something like this:

  • statements->transactions
  • statements->stages->waits->waits

There may be other relationships that my simple test did not capture. If you look at the zip you will see all the levels of the hierarchy that I explored but, in this case, the bottom level seems the most interesting. Here is the query that explores the bottom of the statements->stages->waits->waits path:

select * from events_waits_history_long
where
NESTING_EVENT_ID in 
(select EVENT_ID from events_waits_history_long
where
NESTING_EVENT_ID in 
(select EVENT_ID from events_stages_history_long
where
NESTING_EVENT_ID in 
(select EVENT_ID from events_statements_history_long
where SQL_TEXT like concat('%te','st%'))
and NESTING_EVENT_TYPE = 'STATEMENT')
and NESTING_EVENT_TYPE = 'STAGE')
and NESTING_EVENT_TYPE = 'WAIT'
order by EVENT_ID;

The output reminds me of a trace in Oracle. It shows the individual disk reads and waits for internal locks and mutexs.

+-----------+----------+--------------+-------------------------------------------+-----------------+---------------+---------------+------------+-------+---------------+-------------------------------+------------+-------------+-----------------------+------------------+--------------------+----------------+-----------------+-------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME                                | SOURCE          | TIMER_START   | TIMER_END     | TIMER_WAIT | SPINS | OBJECT_SCHEMA | OBJECT_NAME                   | INDEX_NAME | OBJECT_TYPE | OBJECT_INSTANCE_BEGIN | NESTING_EVENT_ID | NESTING_EVENT_TYPE | OPERATION      | NUMBER_OF_BYTES | FLAGS |
+-----------+----------+--------------+-------------------------------------------+-----------------+---------------+---------------+------------+-------+---------------+-------------------------------+------------+-------------+-----------------------+------------------+--------------------+----------------+-----------------+-------+
|        28 |    11162 |        11162 | wait/io/file/innodb/innodb_data_file      | fil0fil.cc:5778 | 5744476160960 | 5744479752316 |    3591356 |  NULL | NULL          | /var/lib/mysql/bobby/test.ibd | NULL       | FILE        |       139648405042752 |              203 | WAIT               | read           |           16384 |  NULL |
|        28 |    11163 |        11163 | wait/synch/mutex/innodb/buf_pool_mutex    | buf0lru.cc:1320 | 5744479934370 | 5744479974470 |      40100 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66655496 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11164 |        11164 | wait/synch/mutex/innodb/buf_pool_mutex    | buf0buf.cc:5150 | 5744480619278 | 5744480646546 |      27268 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66655496 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11165 |        11165 | wait/synch/sxlock/innodb/hash_table_locks | buf0buf.cc:5153 | 5744480749202 | 5744480858274 |     109072 |  NULL | NULL          | NULL                          | NULL       | NULL        |              70197752 |              203 | WAIT               | exclusive_lock |            NULL |  NULL |
|        28 |    11166 |        11166 | wait/synch/mutex/innodb/fil_system_mutex  | fil0fil.cc:1032 | 5744481202332 | 5744481236016 |      33684 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66654712 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11167 |        11167 | wait/io/file/innodb/innodb_data_file      | fil0fil.cc:5778 | 5744481464586 | 5744485206718 |    3742132 |  NULL | NULL          | /var/lib/mysql/bobby/test.ibd | NULL       | FILE        |       139648405042752 |              203 | WAIT               | read           |           16384 |  NULL |
|        28 |    11168 |        11168 | wait/synch/mutex/innodb/buf_pool_mutex    | buf0lru.cc:1320 | 5744485374336 | 5744485415238 |      40902 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66655496 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11169 |        11169 | wait/synch/mutex/innodb/buf_pool_mutex    | buf0buf.cc:5150 | 5744485590876 | 5744485618144 |      27268 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66655496 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11170 |        11170 | wait/synch/sxlock/innodb/hash_table_locks | buf0buf.cc:5153 | 5744485730424 | 5744485815436 |      85012 |  NULL | NULL          | NULL                          | NULL       | NULL        |              70197624 |              203 | WAIT               | exclusive_lock |            NULL |  NULL |
|        28 |    11171 |        11171 | wait/synch/mutex/innodb/fil_system_mutex  | fil0fil.cc:1032 | 5744486328716 | 5744486357588 |      28872 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66654712 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11172 |        11172 | wait/io/file/innodb/innodb_data_file      | fil0fil.cc:5778 | 5744486586960 | 5744490523176 |    3936216 |  NULL | NULL          | /var/lib/mysql/bobby/test.ibd | NULL       | FILE        |       139648405042752 |              203 | WAIT               | read           |           16384 |  NULL |
|
... edited for length...

Most of the time seems to be taken up on reads from a file, which is what I expected. If you look at the wait/io/file/innodb/innodb_data_file waits they seem to be 16384 byte reads from the file associated with the table. I could use this information to build a query to show a profile of the time spent by the query based on EVENT_NAME. It would be kind of like a tkprof of an Oracle trace.

This post is just a simple first test. I am new to MySQL tuning so feel free to leave a comment or email me if you have suggestions or corrections related to this post.

Bobby

Categories: DBA Blogs

Please help understand expiration_secs in sys.dbms_lock.allocate_unique

Tom Kyte - Tue, 2019-02-12 09:06
Please help understand the meaning of the param <b>expiration_secs</b> in <code>sys.dbms_lock.allocate_unique</code> Does it represent the time till which the named DB lock stays on, if not unlocked/commit/rollback by the same session? https:/...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs