Fast positioning of hidden SQL performance issues and tuning "reprint"

In the last few days, a development colleague asked me a question, in fact, is a technical rescue, he said in a job data processing frequency is relatively high, in the test environment is difficult to locate where there is a problem, and speed can also be accepted, but in the production environment is always slower, I hope I can in the test environment to assist them, See if there are any problems with the SQL statement or other related issues.
This is similar to real-time monitoring of the statement, from the first impression, it is likely to be captured through the AWR, if it is captured by ash, because there are dozens of sets of test environment in the test environment is running, even if you get some SQL statements at some point in time, It is still difficult to map the schema information directly in the report to the corresponding statement. Because the test time is really short, there are a lot of statements executed and may not necessarily be collected by ash.
I communicated with him first because I had no idea which application environment it was, so it took a few minutes to get acquainted with the environment and prepare it in advance.
There are about 50 sets of test environments in the database, with a session count of about 4,000. Overall, the amount of data in the test environment is small. Each environment is probably within 10g-30g.
Positioning to the established test environment, it is found that session occupancy is not high. Are some regular job use, not see other obvious session consumption, view the relevant lock information, there is no problem found.
After a simple confirmation, it is found that the AWR is not available at this time, and most of it can be viewed using a script, in addition to the real-time monitoring.
Operations like the following.
> getash.sh
I SID ser# USERNAME osuser STA rpid SPID Machine program Elap_sec TEMP_MB UNDO_MB sql_id TSPs SQL
-- ------ ------ ------------ ---------- --- ------- ------ ---------- -------------------- ----------- ------- ------- -- ----------- ------ -------------------------------------------------
1 16945 xxxx blwrk01 ACT 9442 9442 ccbdbprx [email protected] XX 05:35:02 B9xg175fbzu K5 INSERT into xxxx (cycle_seq_no, pay

The above statement can also be used to specify the frequency with watch to see real-time changes in the information under each user. The monitoring process can actually see a lot of information changes, but the execution time is really short, only to crawl a portion of the SQL statement. With a simple analysis, none of those statements found any problems.
This time still depends on the development assistance, I hope they hint some more detailed information, the business scenario to do and some of the specified data, they provide that the use of a table resource number x271051128 data, this time through the V$sql from the cache can quickly locate the statement, This time, together with Ash, will be able to confirm that the relevant user is calling.
Finally, a few statements are fetched, and a statement is positioned after the development confirmation, and the statement resembles the following form.

SELECTOWNER_ID,

L3_balance_amount,

Expiration_date,

CUSTOMER_ID,

C64_1,

L3_balance_status,

Sys_update_date,

Sys_creation_date

From accumulators

WHERECUSTOMER_ID in

(Select customer_id

From subscriber

where Prim_resource_val in (' x271051128 '))

and Owner_type = ' P '
By crawling the execution plan, the Subscriber table was found to have gone through a full table scan. This corresponds to the performance impact of the production environment is still relatively large.

The tuning of this problem can be fully optimized by the business level and can be referred to http://blog.itpub.net/23718752/viewspace-1312163/
The problem is similar, slightly different. We can introduce a larger resource table, the Resource table Agreement_resource and the user table Subscriber, using an indexed field to correlate, avoiding a full table scan of the Subscriber table.
The following statements are adjusted:

SELECTOWNER_ID,

L3_balance_amount,

Expiration_date,

CUSTOMER_ID,

C64_1,

L3_balance_status,

Sys_update_date,

Sys_creation_date

From Ape1_accumulators

WHERECUSTOMER_ID in

(

Select customer_id

From subscriber S

Where (Subscriber_no, PRIM_RESOURCE_TP) in

(Select Agreement_no, Resource_type

From Agreement_resource R

where R.resource_value in (' x271051128 '))

)

and Owner_type = ' P '
With the adjusted execution plan you can see that the performance improvement is still very large. This is the data of the test environment, if the data volume is large, the advantage is more obvious.

So for this problem, the cause is that there is a job data processing frequency is relatively high, in the test environment is difficult to locate where there is a problem, and the speed can also be accepted, but in the production environment is always slower, in fact, there is still a reason, only through a variety of details to diagnose the discovery.

Original: http://blog.itpub.net/23718752/viewspace-1656969/

Fast positioning of hidden SQL performance issues and tuning "reprint"