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

Cautions using GMT/UTC in AWS RDS instance when located in EST region

Tom Kyte - Tue, 2019-02-12 09:06
Background: 1. I have just created a Database out on an AWS RDS instance on the East Coast. 2. I noted that the TimeZone was set to GMT/UTC (Greenwich Mean Time) when I setup DB in AWS. 3. I note that when I query <select sysdate from dual;> from ...
Categories: DBA Blogs

Log Stored Procedure calls

Tom Kyte - Tue, 2019-02-12 09:06
Our solution exposes a Stored Procedure to a DB user having the right grants to execute it. We need to know the date and time for each execution for that stored procedure. We are in production, so we can not instrument the running code of the store...
Categories: DBA Blogs

Unable to read trace file generated by EXPDP

Tom Kyte - Tue, 2019-02-12 09:06
HI THERE, We are running EXPDP on a daily basis and size for the schema is only 14GB but the job is taking around 6 hours to complete. so yesterday i executed the expdp with TRACE=480300 and i got One trace file for the master processes testd...
Categories: DBA Blogs

How to create a cursor of select * from dual with input arguments?

Tom Kyte - Tue, 2019-02-12 09:06
Hi Tom, Recently I am using the rqEval function which is part of Oracle Advanced Analytics. The input of this function includes a cursor such as SELECT * FROM table(rqEval(cursor(SELECT 50 "divisor", 500 "numDots" FROM dual), 'SELECT 1 ...
Categories: DBA Blogs

APEX Licensing

Tom Kyte - Mon, 2019-02-11 14:46
Why are you talking about the license, there is a license to have the software?
Categories: DBA Blogs

Getting Started with APEX

Tom Kyte - Mon, 2019-02-11 14:46
From where I start my career in Apex Mean how to start apex
Categories: DBA Blogs

JSON Simple Dot-Notation Access Returning Null

Tom Kyte - Mon, 2019-02-11 14:46
Hi All, I am having an issue to retrieve data from JSON column based on key. Find below the json. I already validate the JSON. I didn't find any issue with the JSON. <code>"{ "Test": "123.40.4", "allowedtables": [{ "name": "t", "attri...
Categories: DBA Blogs

SQL to find indexed and unindexed queries

Tom Kyte - Mon, 2019-02-11 14:46
Hi Guys, Is there a way to determine the indexed and unindexed queries running on the database? Thanks.
Categories: DBA Blogs

Getting results based on dates

Tom Kyte - Mon, 2019-02-11 14:46
Hello, Ask tom Team. I want to make a procedure (if it's the best way) to be execute every day based on two possible dates. I thing I have to fill two variables first. --<b>Gettings dates</b> If today's date is <= 15th day of the current mont...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs