Viewing queries in v$sqlarea

I am having a problem viewing the queries in v$sqlarea. I have a procedure (p1) with lots of insert, select, update and delete statements. I am calling procedure p1 from a test procedure p2 inside a loop to have a load test for p1. But when I try to see the v$sqlarea, it is not showing the insert, delete, etc. from p1, but instead showing the statements from p2. What is the problem?

I am having a problem viewing the queries in v$sqlarea. The scenario is as follows: I have a procedure A (suppose it is named p1) with lots of insert, select, update and delete statements. I am calling procedure p1 from a test procedure p2 inside a loop to have a load test for p1. But when I try to see the v$sqlarea, it is not showing the insert, delete, etc. from p1, but instead showing the statements from p2. What is the problem? I want to see the CPU usage of my queries in procedure p1 from v$sqlarea.

If your shared pool is large enough, all recent SQL and DML should be there!

Starting with Oracle9i, users have the ability to view the estimated CPU, TEMP and I/O costs for every SQL execution plan step. Oracle Corporation has noted that typical OLTP databases are becomingly increasingly CPU-bound and has provided the ability for the DBA to make the optimizer consider the CPU costs associated with each SQL execution step.

By submitting your personal information, you agree to receive emails regarding relevant products and special offers from TechTarget and its partners. You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

The developers of Oracle10g recognized this trend toward CPU-based optimization by providing the ability to choose CPU-based or I/O-based costing during SQL optimization, with the 10g default being CPU-costing. In Oracle10g, system stats are gathered by default, and in Oracle9i the DBA must manually execute the dbms_stat.gather_system_stats package to get CBO statistics.

0 comments

E-Mail

Username / Password

Password

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy