Joey_Tribbiani Posted February 26, 2013 Report Posted February 26, 2013 [quote name='Silence..Please' timestamp='1361888649' post='1303331277'] 1st time run chesinapudu .. cache lo store authaay results .. so again re=run chesav anuko same query .. oracle need not have to fetch all the data from datafiles which are in disk, as the data is already present in cache remember, fetching the data from memory is faster than fetching it from disk [/quote] Thanks Bhayya . Is there any way to check if query fetching results from Cache or hitting DB ??
nexus Posted February 26, 2013 Report Posted February 26, 2013 Can you run the sql through sqlplus? If so, please run the sql through the sqlplus after setting autotrace on using "set autotrace traceonly". This shows you the number of physical reads and consistent gets. If so, is this a production environment? Just curious, if not you can clear the buffer cache and try running the sql. In theory it should take 10 mins again if run again after clearing the cache. Also oracle version please. If 11gr2, then you can check if result_cache_mode parameter is set to auto.
KOTC Posted February 26, 2013 Author Report Posted February 26, 2013 [quote name='Joey_Tribbiani' timestamp='1361899221' post='1303332480'] Thanks Bhayya . Is there any way to check if query fetching results from Cache or hitting DB ?? [/quote] http://www.oracle-developer.net/display.php?id=503
Joey_Tribbiani Posted February 26, 2013 Report Posted February 26, 2013 [quote name='nexus' timestamp='1361900356' post='1303332632'] Can you run the sql through sqlplus? If so, please run the sql through the sqlplus after setting autotrace on using "set autotrace traceonly". This shows you the number of physical reads and consistent gets. If so, is this a production environment? Just curious, if not you can clear the buffer cache and try running the sql. In theory it should take 10 mins again if run again after clearing the cache. Also oracle version please. If 11gr2, then you can check if result_cache_mode parameter is set to auto. [/quote] Ok Bhayya ... Here is result_cache_mode SQL> show parameter result_cache_mode NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_mode string MANUAL Its in Dev bhayya .. i can clear cache .. below command correct ee kada ... [color=#000000][font=tahoma, verdana, sans-serif][size=2][background=rgb(242, 242, 242)]ALTER SYSTEM FLUSH BUFFER_CACHE;[/background][/size][/font][/color]
nexus Posted February 26, 2013 Report Posted February 26, 2013 [quote name='Joey_Tribbiani' timestamp='1361901029' post='1303332739'] Ok Bhayya ... Here is result_cache_mode SQL> show parameter result_cache_mode NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_mode string MANUAL Its in Dev bhayya .. i can clear cache .. below command correct ee kada ... [color=#000000][font=tahoma, verdana, sans-serif][size=2][background=rgb(242, 242, 242)]ALTER SYSTEM FLUSH BUFFER_CACHE;[/background][/size][/font][/color] [/quote] Yes, adhi correct also before running the query, can you do "set autotrace on" or "set autotrace traceonly" and run the query.
Joey_Tribbiani Posted February 26, 2013 Report Posted February 26, 2013 [quote name='KOTC' timestamp='1361900973' post='1303332732'] [url="http://www.oracle-developer.net/display.php?id=503"]http://www.oracle-de...play.php?id=503[/url] [/quote] Thanks Bhayya
Joey_Tribbiani Posted February 26, 2013 Report Posted February 26, 2013 [quote name='nexus' timestamp='1361901244' post='1303332770'] Yes, adhi correct also before running the query, can you do "set autotrace on" or "set autotrace traceonly" and run the query. [/quote] SQL level nunchi run cheyalem bhayya .. So kastam adi ... frontend nunche run cheyyali ..
nexus Posted February 26, 2013 Report Posted February 26, 2013 [quote name='Joey_Tribbiani' timestamp='1361901365' post='1303332790'] SQL level nunchi run cheyalem bhayya .. So kastam adi ... frontend nunche run cheyyali .. [/quote] artham aindhi... so, how did it go after you cleared the cache. Also, you can try enabling trace in the database on your front end session, and do a tkprof on it. The explain plans in tkprof would show you the number of consistent gets and physical reads. I am not sure how to get these data from the awr tables or v$ views
Joey_Tribbiani Posted February 26, 2013 Report Posted February 26, 2013 [quote name='nexus' timestamp='1361901841' post='1303332835'] artham aindhi... so, how did it go after you cleared the cache. Also, you can try enabling trace in the database on your front end session, and do a tkprof on it. The explain plans in tkprof would show you the number of consistent gets and physical reads. I am not sure how to get these data from the awr tables or v$ views [/quote] buffer cache clear chesina same bhayyya .. 10 to 15 Sec
nexus Posted February 26, 2013 Report Posted February 26, 2013 [quote name='Joey_Tribbiani' timestamp='1361905380' post='1303333230'] buffer cache clear chesina same bhayyya .. 10 to 15 Sec [/quote] can you trace the session and copy/paste the explain plan from tkprof output. It shows the number of consistent gets and physical reads. Also, if you enable the trace, you can capture the sql also and so you can play with it without running it from the front end.
Gachibowli_Diwakar Posted March 1, 2013 Report Posted March 1, 2013 ltt [img]http://www.desigifs.com/sites/default/files/brahmam_buildup5.gif?1290348491[/img]
KOTC Posted March 6, 2013 Author Report Posted March 6, 2013 4 -node rac vayya OEM 12c lo 95% chupistundi memory allocation top command kotinapudu kuda oka 1.6gb ala free chupistundi 32gb ram,16gb emo AMM(SGA+PGA) kani enduku OEM 95% chupsindi antaru os:solaris,db version vachi 11.2.0.3. asalu users connect avaledhu database ki oka vmware machine run avutundi anthe e 4 node paina, asalu users leni appude intha memory tesukunte malli users connect avute enti cinema ani questioning?
KOTC Posted March 11, 2013 Author Report Posted March 11, 2013 listener log,alert log 30days older files delete cheyadaniki command chepandi vayya find /u02/app/oracle/admin/orcl/adump/ -mtime +10 -exec rm -rf {} \; e command alert log lo 30 days old files delete cheytam le alane a cronjob ela set cheyalo chepandi vayya 30 days old file delete cheyadaniki
CheGuevara Posted March 11, 2013 Report Posted March 11, 2013 [quote name='KOTC' timestamp='1363027103' post='1303404145'] listener log,alert log 30days older files delete cheyadaniki command chepandi vayya find /u02/app/oracle/admin/SHARK/adump/ -mtime +10 -exec rm -rf {} \; e command alert log lo 30 days old files delete cheytam le alane a cronjob ela set cheyalo chepandi vayya 30 days old file delete cheyadaniki [/quote] edi oka .sh script lo petti danni crontab pettu: echo '::: STARTED ::: Oracle Log Clean up run started on: ' `date` >> /your_path/cleanup.log for x in `find /u02/app/oracle/admin/SHARK/adump -name "exp-*" -type f -atime +30` do if [ "$x" != "" ] then { echo "$x" 'marked to be deleted' >> /your_path/cleanup.log rm -v "$x" >> cleanup.log } else echo 'Nothing to clean up today' >> /your_path/cleanup.log fi done echo '::: FINISHED ::: Oracle Export Clean up run finished on: ' `date` >> /your_path/cleanup.log crontab lo ela pettu: 00 04 * * * /your_path/script.sh
KOTC Posted March 11, 2013 Author Report Posted March 11, 2013 [quote name='CheGuevara' timestamp='1363027769' post='1303404252'] edi oka .sh script lo petti danni crontab pettu: echo '::: STARTED ::: Oracle Log Clean up run started on: ' `date` >> /your_path/cleanup.log for x in `find /u02/app/oracle/admin/SHARK/adump -name "exp-*" -type f -atime +30` do if [ "$x" != "" ] then { echo "$x" 'marked to be deleted' >> /your_path/cleanup.log rm -v "$x" >> cleanup.log } else echo 'Nothing to clean up today' >> /your_path/cleanup.log fi done echo '::: FINISHED ::: Oracle Export Clean up run finished on: ' `date` >> /your_path/cleanup.log crontab lo ela pettu: 00 04 * * * /your_path/script.sh [/quote] thanks bhayya nuv ilanti scripts em ayina run chestava?
Recommended Posts