Query Running Slow |
Vložit
- čas přidán 21. 07. 2024
- In this first episode of #dailyDBA, I will be answering some of the most asked questions by our #DBA community.
00:00 Intro
02:04 In a live production database if I want to add a column in one of the most used tables, what will happen?
04:34 How to handle heavy concurrent environment in oracle? What architecture solutions can you provide to achieve exceedingly good performance?
13:49 How can I check active bg process inside an Oracle session!
14:35 What are the things you must consider before upgrading a database in real-time?
19:37 What are the different upgrade methods available?
21:36 What if I set RMAN retention policy to none?
22:52 FRA location has space available at OS level but still Oracle alert log throws error: FRA location running out of space?
25:11 Do I need to setup backups for recovery catalog too?
26:08 How can I trigger cold backups in Oracle multi-tenant environment?
Bonus Question
29:05 My query is running slow what can i do?
Website: www.dbagenesis.com/
Facebook: / dbagenesis
Instagram: / dbagenesis
Twitter: / dbagenesis - Věda a technologie
Great initiative :-) lot of useful tips, a must watch series for fellow Oracle DBAs
Yup and we all must share these videos to help fellow DBAs across the globe !!
Thanks a lot for taking your time to explain questions with alternatives point of views! Very useful videos.
Glad you like them!
Very good initiative Arun😊, thanks lot.. All the best👍💯
Thanks for uploading the video sir, good work keep coming.
Very good initiative for busy DBAs to refresh their DBA knowledge - this is go to youtube channel - keep up your good work Arun
Thanks a ton
Really that you Arun bhaiya ! It was really helpful. Just now Started watching CZcams series of "DBA genesis".
Yes!
Thanks for sharing it. I just started watching your videos.
Thanks for watching!
Arun Sir, your tips and DBA Genesis video are really helpful for me.
Glad, its helping you !
1. In this case of adding new column it will impact on the user, user trying to access the table while adding a column.the query which has fired by user it was hung and those sessions was block till alter table got completed.
2.I agree with your concern. If we split mode of accessing like DQL will be in the standby and Others in live. So in this you will definitely improve you performance in environment.
I really like this videos.
Awesome!
Great video Arun, it is really informative, THANKS!
My pleasure
Wow, very much excited 😊😄😄😄
I want to know about the important parameters of export/import in real time
Very informative, Thank you. I am a PLSQL Developer with 10+ yrs experience, Now I see very less openings for PLSQL developers. Considering the current situation where more customers are migrating towards cloud & less opening for PLSQL developers, do I need to sharpen my skills in Oracle DBA or right time to learn any other new technology.
Thanks you so much for the video :)
Very interesting and useful. Thank you.
Glad it was helpful!
Your explanations is very good. Can you please make a vidio session on plugable databases and multitent topics for clear understanding.
Sure.. sometime in upcoming sessions!
Which value from AWR report will give the information that xyz index needs a rebuild?
Great work Arun..
I have upgraded (manually) my 11.2.0.4 database to 12.1.0.2 and also I have upgraded the dst_timezone to 18 .. But after running the post_upgrade_fixups.sql ... It's still showing old_time_zone.So, my question is whether this is some sort of bug in oracle 12.1 or do I need to upgrade to timezone to some other values??
Please do respond... :)
very usefull ! Which path did you choose for becoming Web designer
Thx arun, loved it .
Awesome!
That’s good, keep post daily
Yes, we will !
Great job bro 👍
Great work! thanks!
Thank you too!
If I had a performance spike on a particular 2 mins is it a good way to look in awr or how can I troubleshoot?
Please keep it continue.
Definitely
How can we find or auery benefits in library cache when we start using bind variables for similar SQLs instead of hard coded literals.
Nice viedo sir..i need viedo on performance tuning
Hi, could you also speak about question to ask client or user about what considerations required to build a DB and how do we suggest best options from oracle on the same so that its cost effective
Depends, most of the time the requirements come from application that will be using the database. You won't have much control or would not think much about Oracle configuration.
Very useful. Request you to raise volume of video for clarity.
Good feedback, taken into consideration ;)
Great Sir..
Good,keep more videos on pt
Salute sir🙏
Thanks a lot Sir..
Thanks for the video Arun.. Can we import a single table from full export dump?
Yes, you can. Just specify the tables parameter to the table name which you want to import and rest all tables will be ignored.
Arun sir, What will be checklist for database upgradation activity in real time?
What does stats gather actually do and how does it would help to improve the performance of the database ??
DBMS_STATS package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection. This package is concerned with optimizer statistics only.
Can you explain please latch and mutex concepts in oracle ?
Thank you.
Thanks you for the information. One correction control_file_record_keep_time is 7 default.
You right, I just missed it coz all DBs that I work have 30 days default setting. Thanks for the correction!
@@dbagenesis no problem..you are welcome..you are doing awesome work..by sharing knowledge👌👌👌👍👍👍👍🙏🙏🙏🙏
Hi Arun,
Kindly explain about blocks and how to recover the corrupted blocks
Checkout my earlier videos published on youtube related to DB blocks:
1. czcams.com/video/fODsaRUqt28/video.html
Hello,
How to find query generating more redo/archive log in Oracle database?
Thanks
awesome video
Thanks!
Great initiative 👍 Thank you Arun for sharing your knowledge... where can I post questions ?
Welcome, just post your doubts below any video that you are watching or send via email: support@dbagenesis.com
DBA support ❤❤❤❤
Thanks a lot
Nice video sir
Hi Arune, could you please explain what are differences between flashback database and guaranteed restore point ?
Good question, picked it up for upcoming episode! Will answer it soon!
DBA Genesis , could you please confirm which episode 6/7 ?
My database size is 500 GB and what's the recommended size for FRA ?
What are the parameters need to be considered before we set the FRA ?
Good question, picked it up for upcoming episode! happy learning!
Thank you sir for your good video. Can you make videos about oracle applications ebs 12.1 or 12.2 please
give answers with examples also its very help full every one
Thank u arun
Difference between Data Guard and Golden Gate and which is recommended for which scenario.
Check out one of my live events that answers your question: czcams.com/video/89jYzLNddHs/video.html
Thanks for video
Most welcome
Can you tell me how cursors work actually in shared sql area...I am confused why and when the same sql Id will be having its child cursors and version counts??
Sometimes when I try to migrate the base line from one environmnt to another it is confusing me which plan do i actually need to migrate because I see two r three plans for the same sql id but with its child cursors having different sql plans..
Sure, picked it up for future episodes!
@@dbagenesis thank you
Hi All, is there any option to manage the export/import without Linux level access.. Can you help on this..
I recently find your channel. For oracle sql plsql developers which videos are useful regarding performance tuning in your channel
Question 2:
Same SQL query was running fine before, But now it is taking 3hrs to 4hrs. And also overall database is slow as per user during this query execution.
So I checked
- Stale stats = No stale stats are there on the objects that are used in this query.
- No other query/transactions was there during this query execution
- Ran SQL Tuning pack - no recommendation mentioned except to pin other plan has value. I did the same also. Still issue is there.
- Ran ADDM report, no satisfactory recommendation mentioned
So could you please explain what else area to check to identify issue.
trace the user session and read the trace file via tkprof, that will give the pin point problem
@@dbagenesis How to trace the user session? v$session?
Please make a video on PRAGMA COVERAGE,DEPRECATE,INLINE,RESTRICT_REFERENCES.
Before RMAN backup started, what prepration done inside the RMAN..? is it use current control file to read backup informantion or else its create snapshot control file for the period of backup time.!! pls help for this question.
Will answer in the upcoming episode!
Thanks!😊
Sir, how can we gather statistics for all tables (ex-500 tables) in a schema in one go as we can do the rebuild indexes in one go.
You can also gather stats at schema level
Sir, I'm using Oracle 10g database, after 2 to 3 months programmes runs slowly, when drop user cascade with table data and again create the same the application runs faster but again after 2 to 3 months the problems remains same, what should I do.
After dropping user and creating the same user, restored table with data through exported .dmp backup file.
User is complaining insert statement ( if has a sql query) is very slow now. It is suppose to complete 10 min but now a days it is running 9 hours.
I checked this sql query is every time creating new sql_id and this is creating parallel sessions also for same sql_id. Could you please help me how to find the issue.
You need to check if the insert statement is raw insert of it is INSERT INTO .. SELECT .. FROM; statement. If its later, then check if the Oracle going for full table scan or Index scan.
Else, enable trace in user session and see where Oracle is spending time while inserting records.
Hi Arun.....
Would you please help me with how to retrieve billions of records as much faster on Oracle database.
Great content Arun
Thank you!
Hi sir
This is Sheikshavali
I have one question
I have multipule ORACLE_HOMES. In that oracle homes when I was updating patch like version updated patch. Client says you can apply only one ORACLE_HOME no need to apply another Home? (This is interview question )
I hope you understood my question
Please give a solution.
Thank you sir
Sheikshavali
It's straightforward what client is saying, apply patch to only one ORACLE_HOME!
Hi Team , in my DB server when I run "top" command the load average : 3.27,3.47,3.68
And vmstat command result :
r = 4 , b = 2 (procs)
Swpd = 2202188, free= 170640, buff= 1384 (memory)
si = 321 , so=115 (swap)
bi = 3933 , bo= 3933 (io)
Few more parameters are there but here I am not able to type ... have given some inputs for you and same have send you in Fb messenger .
Actually, my client system is getting hang , is there any prob in my db server ?
Send complete details via email to support@dbagenesis.com
Supper sir
please start a video searies on oracle development course project base ....
Why do you prefer manual upgrade instead of DBUA?
If DBUA fails, you have to perform manual upgrade, so its a good idea to master manual upgrade over DBUA.
Good idea
Keep watching
Hello Arun sir, long time no episode of Daily DBA?
Sir,
One of scheduled job suddenly start taking...more time ...before it was running properly..and we don't even change anything in database.
As i checked there is no BLOCK session.
Please give me some solution to rectify this.
Depends on what job the scheduler is running. How was the CPU load at the time of job run? How was the instance load? Investigate in these areas and also try to get ASH report during the scheduler run and look into it.
@@dbagenesis thanknyou so much for reply sir
Hi,
I have a datawarehouse environment. 2 node RAC, Oracle DB 12.1.0.2, Golden Gate 18c. Configuration of GG is downstream. Everyday around 350 GB archive is being generated. DB size is currently 3.4 TB. Can you please advise on a backup strategy to make backup faster? Do you recommend Intelli Snapshots based on hardware?
I would like to know how many channels do you allocate in your archivelog RMAN backup scripts and what is the archivelog backup frequency
@@dbagenesis 2 channels have been allocated
backup
incremental level = 0
filesperset = 8
database
plus archivelog not backed up;
frequency is daily. After backup, I purge the logs
delete archivelog until time 'sysdate-1';
Do one thing, remove archive backup from the DB backup script and schedule it separately. See example below:
DB Backup script:
----------------------------
Just trigger DB backup and also make sure you taking LEVEL 0 backup only on weekend or non peak hours. Rest all the days of the week, take Level 1 backups.
ARCHIVE BACKUP SCRIPT:
--------------------------------------------
Create a new archive only backup script and schedule it to run every 4 hours. See if your environment is stable and if not, you can increase the archive backup frequency to every 2 hours.
This should be good enough and also, please increase number channels in backups to at least 4. This will speedup the backups.
Implement above and send me progress report to support@dbagenesis.com and I am sure you will not have to use any other hardware level backup tools.
Enjoy!
@@dbagenesis Thanks Arun. The golden gate tables are located in a specific schema. Is it necessary to perform daily RMAN backup of the DB? The point is in case I will perform an RMAN restore, Golden Gate will need to be reconfigured and export and import of all the tables(using flashback_scn) will need to be done for Golden Gate config. In my opinion, an export dump (daily) of specific schemas will be much better. Maybe a full backup once a week will be ok? or still do you suggest me to do daily level 1 RMAN backup? If yes, in case of restore, how should I resync the Golden Gate tables?
Can you please tell me, what DBA do on daily?
I am new for oracle DBA
Sure, checkout this video published on CZcams earlier:
1. czcams.com/video/MnUqK00HFME/video.html
2. czcams.com/video/5KQBB4nSdPc/video.html
3. czcams.com/video/eV5lbSs-xMA/video.html
@@dbagenesis thank you so much....
hi sir, we are facing low volume/sound issue for most of your videos,please improve sound quality.Ty
Have fixed this from episode #2!
Hi, what if some of the archives deleted by mistake?
It happens sometimes in realtime that RMAN backups will delete the archives from disk and standby is still waiting. In such cases, you will have to restore archives again from RMAN backups and then apply it on standby.
In case you have completely lost archives, no possibility of recovering it, then you will have to rebuild the standby.
Just in case, not sure if you asked this question in regards to Oracle data guard or some other sense?
hlw arun,
i am currently working as network administrator,
but i want to become DBA.
pls suggest how and where to start.
You must start with Linux and follow below order (strictly):
- Linux (Oracle Linux or Red Hat)
- Oracle SQL (go with 12c version)
- Formal course in database administration
- Get OCA certified
Above is enough to get a job as a DBA, once you are in DBA job / project, you can always upgrade your skillset to RAC, Golden Gate etc...
Keep it up it's a Good Job .. Could you please make a common WhatsApp group for this channel it's alos a good option
I am not a fan of WhatsApp but will consider it in future.
A lot of DBA's are woriied about future of what will happen once autonomous DB comes....Many thinks they will lose jobs ..please answer..
Already added to upcoming episodes !
After upgrading database do we need to keep old binaries or can we remove it.
Yes you can only after you are sure that you are not going to rollback to previous version.
after removing it will not impact anything to currently running database.
No, coz your DB is now running from new home.
ok thanks alot and Happy new year🎂🎂🎊
If The diag directory is not in the new home also FRA?
So shall we add and configure it?
If we perform a switchover operation in real time. How will we have to manage the connection string for end users? since after switchover standby will act as a primary database. Do we have to modify connection string for all the user at client side? or is there any other thing which is need to be done?
Answering this question in the upcoming episode!
Hai iam freshers Iam intresting towards DBA it's good option for freshers ... In DBA which Technology now a days trending
Where is best platform form Rac and datagard and golden gate any CZcams channel please provide us
Is it necessary for DBA's to know "How to write a query" /
Yes
Else how would you query data inside database? If you want to interact with database, you must know how to write sql queries.
📣
Hi Arun do you know hindi language if yes so requested you make video in hindi so lots of our Indian people they understand properly hope you take it as positive ways Thanks in advance
Really good information but what you mentioned in the title that you don't know. I think you need to change the title so that other people don't waist there time.
Not 30 days default is 7
Hi Sir,
Nice video.
Do you have any what's app group for DBA / DATABASE DEVELOPERS.??
No whatsapp group available at the moment!