Home » RDBMS Server » Performance Tuning » Long running queries (Oracle, 18.0.0.0.0, Linux)
Long running queries [message #684966] Mon, 04 October 2021 01:28 Go to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Hi Oracle gurus,

I found the below sql id's in the long running section in the awr report in prod database.

89cf6rrncn00x
4vrwkq6321kny
ddhc1697xfnv7

Based on the report, I ran sql tuning advisor for sql id 89cf6rrncn00x and got below recommendation.

Quote:
Recommendation (estimated benefit: 74.42%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name =>
'sql_tuning_task_89cf6rrncn00x', task_owner => 'SYS', replace =>
TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

Executing this query parallel with DOP 4 will improve its response time
74.43% over the original plan. However, this will increase the statement's
resource consumption by an estimated 2.30% which may result in a reduction
of system throughput. Also, because these resources are consumed over a much
smaller duration, the response time of concurrent statements might be
negatively impacted.
Based on the recommendation, I ran the below sql profile on sql plus.
Could you please suggest if I have taken the correct action.

execute dbms_sqltune.accept_sql_profile(task_name =>
            'sql_tuning_task_89cf6rrncn00x', task_owner => 'SYS', replace =>
            TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);
Kindly find the awr report attached for your reference.

Regards,
Balaji

[Updated on: Mon, 04 October 2021 01:30]

Report message to a moderator

Re: Long running queries [message #684968 is a reply to message #684966] Mon, 04 October 2021 01:41 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Perhaps if you upload the original report and another following the change, then you will be able to determine if the change was a Good Thing.
Re: Long running queries [message #684969 is a reply to message #684968] Mon, 04 October 2021 02:16 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
I am sorry for that. Kindly find the awr report file attached.

Regards,
Balaji
Re: Long running queries [message #684970 is a reply to message #684969] Mon, 04 October 2021 03:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You cannot expect people to work with the text version of the reports. You need to generate the html version. And then you need to generate the SQL report (the awrsqrpt.sql script) for the SQLs you are worried about. Generate these reports for the period before you made the change, and repeat for the period after you made the change.
Re: Long running queries [message #684971 is a reply to message #684970] Mon, 04 October 2021 03:27 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
But I can't upload html files since it is prohibited here. That is why I uploaded text file.
Re: Long running queries [message #684976 is a reply to message #684971] Mon, 04 October 2021 04:23 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I've checked with the Account Moderator:
Quote:
Yes, this is true, "normal" account can't upload html files.
The problem with html files (as well as exe...) is that it could contain a virus, troyan or the like.
The workaround is to rename the file as .txt so we can inspect it before renaming and opening it as html.
So just rename the files before upload and explain that in your message.
Re: Long running queries [message #684980 is a reply to message #684976] Mon, 04 October 2021 06:29 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Even after renaming the file to txt, I am not able to attach the file.
When I try to attach the file it looks like this Sy2dw_11_13_prod.txt.html
Re: Long running queries [message #684981 is a reply to message #684980] Mon, 04 October 2021 06:42 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Come on, man! I think you have configured the Windows Explorer to hide file name extensions, and are renaming files with a right click. Learn to use Windows. Or even the CMD shell. It isn't difficult Smile
Re: Long running queries [message #684983 is a reply to message #684981] Mon, 04 October 2021 09:05 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Could you please suggest how to rename the file
Re: Long running queries [message #684988 is a reply to message #684983] Tue, 05 October 2021 01:02 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Hi,

Kindly find the awr report in text file. In this report, I found the following sql id's are long running queries.
89cf6rrncn00x
4vrwkq6321kny
ddhc1697xfnv7

Could you please help me to tune those queries.

I have also ran sql tuning advisor for those sql id's and it has provided some recommendations as below.
Quote:
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 74.42%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name =>
'sql_tuning_task_89cf6rrncn00x', task_owner => 'SYS', replace =>
TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

Executing this query parallel with DOP 4 will improve its response time
74.43% over the original plan. However, this will increase the statement's
resource consumption by an estimated 2.30% which may result in a reduction
of system throughput. Also, because these resources are consumed over a much
smaller duration, the response time of concurrent statements might be
negatively impacted.

Regards,
Balaji
Re: Long running queries [message #684989 is a reply to message #684988] Tue, 05 October 2021 01:25 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
That report is readable Smile
It raises some questions:

Why the instance caging? You are permitting the instance to use only 4 CPUs when the machine has 8. You may well have a good reason for this, but throwing parallel processing at it when you have restricted the CPUs may not be a good idea.
You have not configured parallel processing at all: you are running completely on defaults. You should consider at the very least setting parallel_degree_policy=adaptive to see if that improves those queries without the need to use profiles.
Why have you disabled adaptive execution plans? Again, you may have a good reason but it might well help with the queries you have identified. Particularly if parallelism is to be involved.
And speaking of those queries, why do think they need to be tuned? Do not say "because they are listed in the report", you need to check with your end users to determine if they are a problem. You certainly do not want to throw more resources at them if they are not crucial.

Your next step, AS I SAID BEFORE, is to get the AWR SQL reports for those queries and post them here. I even told you which script to run. Then run the queries with parallelism enabled, first through the parallel_degree_policy and then by hinting it. Then get the SQL reports again, and compare the results.

Follow the Scientific Method!! Gather information, construct hypotheses, and test them.
Re: Long running queries [message #684990 is a reply to message #684989] Tue, 05 October 2021 02:15 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Thanks for your suggestions.Could you please clarify my doubts on below.

Quote:
Why the instance caging? You are permitting the instance to use only 4 CPUs when the machine has 8. You may well have a good reason for this,
but throwing parallel processing at it when you have restricted the CPUs may not be a good idea.
Could you please let me know where do you find this in the report and what action needs to be taken from my end.

Quote:
You have not configured parallel processing at all: you are running completely on defaults. You should consider at the very least setting parallel_degree_policy=adaptive to see if that improves those queries without the need to use profiles.
Could you please let me know where do you find this in the report. Could you also please confirm the command to set PARALLEL_DEGREE_POLICY = adaptive;
ALTER SYSTEM SET PARALLEL_DEGREE_POLICY = adaptive;


Quote:
Why have you disabled adaptive execution plans? Again, you may have a good reason but it might well help with the queries you have identified.
Particularly if parallelism is to be involved.
Could you please let me know how to enable it and where do you find this in the report.

Regards,
Balaji

[Updated on: Tue, 05 October 2021 02:16]

Report message to a moderator

Re: Long running queries [message #684991 is a reply to message #684990] Tue, 05 October 2021 03:35 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Could you please let me know where do you find this in the report and what action needs to be taken from my end.

Quote:
Could you please let me know where do you find this in the report. Could you also please confirm the command to set PARALLEL_DEGREE_POLICY = adaptive;

Quote:
Could you please let me know how to enable it and where do you find this in the report.
Looking at the section init.ora parameters, I see that you have set about thirty parameters. If you read up on them (all of them) in the docs then all will become clear. I cannot understand how you perform your job without doing basic research such as that. Did you just choose a random set of parameters and assign them random values? In addition, as you have still failed to generate any SQL reports or to investigate which queries (if any) are causing your users problems, I see little hope for your career development.
Re: Long running queries [message #684992 is a reply to message #684991] Tue, 05 October 2021 04:45 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Kindly find the sql report for the sql id '89cf6rrncn00x' for your analysis.

Regards,
Balaji
Re: Long running queries [message #684995 is a reply to message #684992] Tue, 05 October 2021 23:28 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
I didn't expect this much delay for my answer.
Re: Long running queries [message #684996 is a reply to message #684995] Wed, 06 October 2021 01:13 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Are you asking for consultancy services?
Re: Long running queries [message #684997 is a reply to message #684996] Wed, 06 October 2021 01:50 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
No,since it took more time for your reply I seeked for your update.
Re: Long running queries [message #685002 is a reply to message #684997] Thu, 07 October 2021 00:05 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Long time no see Smile
Re: Long running queries [message #685006 is a reply to message #685002] Thu, 07 October 2021 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You demand answers but you refuse to post one to help people.
One example:
https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=96206&SkipA=0#261764

Re: Long running queries [message #685007 is a reply to message #685006] Thu, 07 October 2021 23:14 Go to previous message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
I am sorry. I might have forgot to read your post.

Previous Topic: Slow Archive Apply in Physical Standby
Next Topic: Correlated subquery iteration
Goto Forum:
  


Current Time: Thu Mar 28 11:09:42 CDT 2024